Object Relational Concepts and Systems

1
Object Relational Concepts and Systems

• Principles
• User Defined Types
• Nested Tables
• Queries

References (e.g.) Ullman et al. book 4.5,
9.4-5 Elmasri ch. 13
Hohenstein, Pleßer Oracle 9i
2
Motivation Objects and Relations

• Object Relational Systems
• Principles
• Keep the goodies of RDB
• Enhance relational systems by constructed types,
  inheritance, methods
• Supported by SQL-99 standard
• Issues
• Technologically outdated solutions
  stabilizede.g. type system, baroque constructs
  of SQL
• Add-on usually worse than completely new approach
• Important aspect Save investment into your
  software by upward compatibility of..
• .. DBS, application software
• ORDBS Postgres, Oracle, Informix, DB2

3
Object Relational Concepts Object types

• SQL-99 Additional Types
• Literal types Collections Array, List, Set
  and table type Lobs ("large objects") Binary
  (Blob), Character (CLOB)
• image blob movie(2GB)
• Object typesA row may be an object which has
  attributes and can be referenced
• CREATE TYPE AdressType AS OBJECT (
• city VARCHAR(30),
• zipCode VARCHAR(15),
• street VARCHAR (30),
• number VARCHAR(5),
• country VARCHAR(15)
• )
• /

4
Object Relational Concepts Object types

• Object Types, Object Tables

CREATE TYPE MovieType AS OBJECT ( mId
Int , title VARCHAR(30),
director VARCHAR(25), year date ) /
Object table
SQLgt CREATE TABLE MovieObjects OF MovieType
Objects may only live in a table
5
Object Relational Concepts References

• REFerences
• REF types have values, can be seen by useras
  opposed to OO languages

CREATE TABLE MovieTape ( Id Int,
movie REF MovieType, since date,
back date )
Table with object reference
6
Object Relational Concepts References

• References in Oracle
• System generated OIDs, system wide unique values,
  even in a distributed environment
• or
• OIDs derived from a primary key
• Relationship between system generated and primary
  keys have to be established by application
  program
• Structure of OIDs implementation defined, don't
  use them explicitly

7
Object Relational Concepts Object types

• and Tables with structured attribute types
  (Non First Normal Form)
• No difference between structured types and object
  types

1 Create TABLE TapeStruct ( 2 id INT,
3 format CHAR(5), 4 movie
MovieType, 5 since date, 6 back
date) 7
Object as Structured attribute
SQLgt CREATE OR REPLACE TYPE Foo (bar Int) 2
/ Warnung Typ wurde mit Kompilierungsfehlern
erstellt.
8
Object Relational Concepts Using Object types

• Type constructors
• Each type defined by AS OBJECT has a type
  constructor ltobjectnamegt
• Constructor wraps the fields
• e.g. MovieType(65, 'To be or not to
  be','Anti-war',0.5, 'Lubitsch',
  toDate('1942', 'YYYY'))
• Used in insert statement
• And more

SQLgt Insert INTO MovieObjects VALUES (
MovieType(65, 'To be or not to be',
'Lubitsch', to_Date('1942', 'YYYY')))
9
Object Relational Concepts Using Object types

• Inserting into tables with structured and
  unstructured attributes (fields)
• Doesn't work with REF attributes object
  different from ref

SQLgt INSERT INTO TapeStruct
VALUES(10,'DVD',MOVIETYPE(65, 'To be or not to
be', 'Lubitsch', '01.05.42'),NULL,NULL)
1 INSERT INTO MovieTape VALUES(10, 2
MOVIETYPE(65, 'To be or not to be', 'Lubitsch',
'01.05.42'),NULL,NULL) 3 MOVIETYPE(65, 'To
be or not to be', 'Lubitsch', '01.05.42'),NULL,NUL
L) FEHLER in Zeile 2 ORA-00932 nicht
übereinstimmende Datentypen
10
Object Relational Concepts Using Object types

• Inserting REFerences in two steps
• How to access fields of referenced objects?
• Dereferencing
• SQL-99 with explicite dereferencing
  MovieTape.movie-gttitle Oracle implicitly
  (like Java)
  ltMovieTape-aliasgt.movie.title

INSERT INTO MovieTape VALUES(10,NULL,NULL,NULL)
1 UPDATE MovieTape SET movie 2 (SELECT
ref(p) FROM MovieObjects p 3 WHERE p.mId
65) 4 WHERE id10 5
11
Object Relational Concepts References and Select

• Selection of REFerenced objects
• REF(x) Selecting References (!)

SQLgt SELECT movie FROM MovieTape -- movie is a
REF type MOVIE ----------------------------------
---------------------------------------- 000022020
8F5CD054159CB4F8681237734D7841FAF46316976DEF1481EB
A83DA5399F84E12
12
Object Relational Concepts References and Select

• Accessing referenced objects DEREF operator
• Accessing values of objects VALUE operator

SQLgt Select DEREF(p.movie) from MovieTape
p DEREF(P.MOVIE)(MID, TITLE,
DIRECTOR, YEAR) ----------------------------------
---------------------- MOVIETYPE(65, 'To be or
not to be', 'Lubitsch 01.05.42')
SQLgt select value(p) from movieObjects
p VALUE(P)(MID, TITLE, DIRECTOR,
YEAR) --------------------------------------------
------------ MOVIETYPE(65, 'To be or not to
be','Lubitsch', '01.05.42') MOVIETYPE(67,
'Jurassic Parc', 'Spielberg', '01.05.97')
13
Object Relational Concepts Select

• Emulation of table select
• Means object tables may be queried like ordinary
  tables
• But relational tables are different from object
  tables even if no oo features are used

select from MovieObjects MID TITLE
DIRECTOR YEAR --- -------------------
------------- -------- 65 To be or not to be
Lubitsch 01.05.42 67 Jurassic Parc
Spielberg 01.05.97
14
Object Relational Concepts Methods

• An example using methods
• Points, lines and line length

SQLgt create TYPE Point_T AS OBJECT ( 2 x
NUMBER, 3 y NUMBER 4 ) 5 / SQLgt CREATE
TYPE Line_T AS OBJECT ( 2 end1 Point_T, 3
end2 Point_T 4 ( 5 / SQLgt CREATE TABLE
Lines ( 2 lineId INT, 3 line Line_T) --
value, no REF
15
Object Relational Concepts Methods

• Insertion of lines
• Abstract Data TypesUser defined type specifies
  fields and the signature of methods ("Member
  functions")
• Implementation of body separately

SQLgt INSERT INTO Lines 2 VALUES (10, Line_T
( 3 Point_T(0.0,0.0), 4
Point_T(1.0, 2.0) 5 ) 6
)
16
Object Relational Concepts Methods

• Adding of a method lineLength to Type Line
• IN, OUT and INOUT parameter in signature
• Pragma "Write No Database State" (WNDS) needed if
  function is to be used in SELECTs

SQLgt ALTER TYPE Line_T REPLACE AS OBJECT( 2
end1 Point_T, 3 end2 Point_T, 4 MEMBER
FUNCTION lineLength (scale IN NUMBER) RETURN
NUMBER, 5 PRAGMA RESTRICT_REFERENCES
(lineLength, WNDS) 6 )
17
Object Relational Concepts Methods

• Implementing a method
• No parameter mode in method implementation

CREATE TYPE BODY Line_T AS MEMBER FUNCTION
lineLength (scale NUMBER) RETURN NUMBER IS
BEGIN RETURN scale
SQRT((SELF.end1.x
SELF.end2.x)(SELF.end1.x-SELF.end2.x)
(SELF.end1.y-SELF.end2.y)
(SELF.end1.y-SELF.end2.y)
) END END /
18
Object Relational Concepts Methods

• Selection with user-defined methods

SELECT lineId, l.line.lineLength(1.0) FROM
Lines l LINEID L.LINE.LINELENGTH(1.0) ------
---------------------- 10
2,23606798 30 ,282842712
SELECT l.line.end1, l.line.end2 FROM Lines
l WHERE l.line.lineLength(1) gt 1.5 LINE.END1(X,
Y) LINE.END2(X, Y) ------------------------
----------------------- POINT_T(0, 0)
POINT_T(1, 2)
19
Object Relational Concepts Collection types

• Collection Types
• SET, Multiset, List constructors for primitive
  and constructed typese.g. Phones SET
  (VARCHAR(20)) Polygon LIST (Point_T)
• Value of constructed coolection type may be
  regarded as a table
• Non First Normal Form Relations

20
Object Relational Concepts

• Collection Types in Oracle 9
• Varraysvariable number of values of some type

CREATE TYPE ExtentT as VARRAY(4) OF Int CREATE
OR REPLACE TYPE Address_T AS OBJECT ( zipcode
CHAR(6), ciy VARCHAR(20), street
VARCHAR(25), no CHAR(5)) CREATE
TABLE PhoneBook ( name VARCHAR(30),
firstName VARCHAR(20), addr Address_T,
phones ExtentT)
21
Object Relational Concepts

• Using VARRAY
• No way to address by positione.g. phones2

INSERT INTO PhoneBook VALUES( 'Abel',
'Hendrik', ADDRESS_T('12347', 'Berlin',
'Takustr.','9'), ExtentT(2347, 1139)) SELECT
b.name, b.addr.street, b.phones FROM PhoneBook
b WHERE 2347 IN (SELECT FROM TABLE(b.phones)) N
AME ADDR.STREET PHONES ---------------------
------------ Abel Takustr.
EXTENSION_T(2347, 1139)
Cast of a varray to a table
22
Object Relational Concepts Nested tables

• Nested table
• Table (type) used as column type
• Restriction only one level of nesting (Oracle
  9i no restriction)

CREATE TYPE Polygon_T AS TABLE OF
Point_T CREATE TABLE Polygons ( pid
CHAR(5), points Polygon_T) NESTED TABLE
points STORE AS PointsTab
23
Object Relational Concepts

• Nested tables another example

CREATE TYPE SetNum_AS TABLE OF Number CREATE
TYPE B AS OBJECT (b1 INTEGER, b2 SetNum) CREATE
TYPE SetB AS TABLE OF B CREATE TABLE Rel (a1
NUMBER, a2 SetB) NESTED TABLE a2 STORE AS
TabSetB (Nested Table COLUMN_VALUE STORE AS
TabSetNum)
24
Object Relational Concepts

• Nested Tables insertion
• Insert values of 'inner table' as a list of
  constructed values (here of type Point_T )

INSERT INTO Polygons VALUES( 'squ01',
Polygon_T(Point_T(0.0, 0.0),
Point_T(0.0, 1.0), Point_T(1.0, 0.0),
Point_T(1.0, 1.0) ) )
25
Object Relational Concepts

• Querying nested tables
• Querying the inner table

SELECT points FROM Polygons WHERE pid
LIKE 'squ' -- finds
all squares
26
Object Relational Concepts

• Querying nested tables using inner tables

SELECT t.pid FROM Polygons t WHERE EXISTS (SELECT
ss.x FROM TABLE(SELECT points
FROM Polygons t2 WHERE t2.pid t.pid
) ss WHERE ss.x
ss.y) AND pid ltgt 'squ01'
Select one row in polygons and select atable
value (points)
Inner qualification on table value
Additional outer qualification predicate
27
Object Relational Concepts insert

• From relations to object-relations
• Suppose there is a flat table

CREATE TABLE LinesFlat( id INT, x1
NUMBER,y1 NUMBER,x2 NUMBER,y2 NUMBER )
INSERT INTO Lines SELECT id,
Line_T(Point_T(x1,y1),
Point_T(x2,y2)) FROM LinesFlat
Insertion, values supplied by SELECT,
constructed by appropriatetype
28
Object Relational Concepts Collection types
insert

• Insertion into complex structured table
• Suppose, there is a flat Polygon table
• Steps to insert all the points with name
  'triangle' into the Polygon Table with nested
  table attribute points
• Query PolyFlat to get all points belonging to
  triangle
• Turn the collection of answers into relation
  using keyword MULTISET
• Turn relation into a Polygon_T value by CAST AS
  Polygon_T

CREATE TABLE PolyFlat ( name
VARCHAR2(20), x NUMBER, y NUMBER
)
29
Object Relational Concepts

• Inserting from a flat into a nested table

INSERT INTO Polygons VALUES('square',
CAST( MULTISET(SELECT x, y
FROM PolyFlat
WHERE name 'square' )
AS Polygon_T ) )
30
Object Relational Concepts

• Final remarks
• No inheritance in Oracle 8i, but SQL-99.. and
  Oracle gt 9
• Object-relational technology is mature
• Lack of standards prevents heavy usage despite
  SQL-99
• Might improve the 'impedance mismatch' between
  programming languages and relational database
  while keeping the advantages of tabular data
• Use of ORDB in O/R mapping??
• The more concepts the more implementations
  the more solutions the more bugs?
• Does the base Principle of RDM still hold
  Keep it simple, stupid!