1 / 30

Object Relational Concepts and Systems

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. Motivation Objects and Relations. Object Relational Systems

nicole
Download Presentation

Object Relational Concepts and Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 SQL> CREATE TABLE MovieObjects OF MovieType; Objects may only live in a table

  5. ud: 007 movie: since: … back: … 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 SQL> 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 <objectname> • 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… SQL> 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 SQL> 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,NULL) * 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->titleOracle implicitly (like Java) <MovieTape-alias>.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 id=10 5 ;

  11. Alias essential SQL> SELECT m.movie.title FROM MovieTape m; MOVIE.TITLE ------------------------------ To be or not to be Object Relational Concepts References and Select • Selection of REFerenced objects • REF(x): Selecting References (!) SQL> SELECT movie FROM MovieTape; -- movie is a REF type MOVIE -------------------------------------------------------------------------- 0000220208F5CD054159CB4F8681237734D7841FAF46316976DEF1481EBA83DA5399F84E12

  12. Object Relational Concepts References and Select • Accessing referenced objects: DEREF operator • Accessing values of objects: VALUE operator SQL> 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') SQL> 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 SQL> create TYPE Point_T AS OBJECT ( 2 x NUMBER, 3 y NUMBER 4 ); 5 / SQL> CREATE TYPE Line_T AS OBJECT ( 2 end1 Point_T, 3 end2 Point_T 4 (; 5 / SQL> 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 SQL> 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 SQL> 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) > 1.5; LINE.END1(X, Y) LINE.END2(X, Y) ----------------------------------------------- POINT_T(0, 0) POINT_T(1, 2)

  19. Name Phones …. id polygon …. Abel (432,8764) … [(432,874) (101,200) (111,343)] • … …. 13 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. phones[2] 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)); NAME ADDR.STREET PHONES --------------------------------- Abel Takustr. EXTENSION_T(2347, 1139) Cast of a varray to a table

  22. Stored outside table Polygons, Has to be made explicit 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. Rel a1: NUMBER, a2: SetB 1 11 111 112 21 22 2 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. SELECT ss.x FROM THE(SELECT points -- keyword THE FROM Polygons WHERE pid ='squ01' ) ss -- Alias for inner WHERE ss.x = ss.y; -- table -- qualification Must identify exactly one row 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 <> '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 >= 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!

More Related