Object Relational Databases

1. Object Relational Databases

2. Object-Relational Data Model • A straightforward subset of ODM: only tuple types at the top level • More precisely: • Set of classes, where each class has a tuple type (the types of the tuple component can be anything) • Each tuple is an object of the form (oid, tuple-value) • Pure relational data model: • Each class (relation) has a tuple type, but • The types of tuple components must be primitive • Oids are not explicitly part of the model – tuples are pure values

3. Objects in SQL:1999 • Object-relational extension of SQL-92 • Includes the legacy relational model • SQL:1999 database = a finite set of relations • relation = a set of tuples (extends legacy relations) OR a set of objects (completelynew) • object = (oid, tuple-value) • tuple = tuple-value • tuple-value = [Attr1: v1, …, Attrn: vn]

4. SQL:1999 Tuple Values • Tuple value: [Attr1: v1, …, Attrn: vn] • Attriare all distinct attributes • Each vi is one of these: • Primitive value: a constant of type CHAR(…), INTEGER, FLOAT, etc. • Reference value: an object Id • Another tuple value • A collection value This one is a disappointment. SETOF and LISTOF are not supported. Only the ARRAY construct is – a fixed size array

5. Row Types • The same as the original (legacy) relational tuple type. However: • Row types can now be the types of the individual attributes in a tuple • In the legacy relational model, tuples could occur only as top-level types CREATE TABLE PERSON ( Name CHAR(20), Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) )

6. Row Types • Use path expressions to refer to the components of row types: SELECT P.Name FROM Person P WHERE P.Address.ZIP = ‘11794’ • Update operations: INSERT INTO PERSON(Name, Address) VALUES (‘John Doe’, ROW(666, ‘Hollow Rd.’, ‘66666’)) UPDATE PERSON SET Address.ZIP = ‘66666’ WHERE Address = ‘55555’ UPDATE PERSON SET Address = ROW(21, ‘Main St’, ‘12345’) WHERE Address = ROW(123, ‘Maple Dr.’, ‘54321’) AND Name = ‘J. Public’

7. User Defined Types (UDT)OR ABSTRACT DATA TYPE (ADT) • UDTs allow specification of complex objects/tuples, methods, and their implementation • Like ROW types, UDTs can be types of individual attributes in tuples • UDTs can be much more complex than ROW types (even disregarding the methods): the components of UDTs do not need to be elementary types

8. A UDT Example CREATE TYPE PersonType AS ( Name CHAR(20), AddressROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) ); CREATE TYPE StudentType UNDER PersonType AS ( Id INTEGER, Status CHAR(2) ) METHODaward_degree() RETURNS BOOLEAN; CREATE METHODaward_degree() FOR StudentType LANGUAGE C EXTERNAL NAME ‘file:/home/admin/award_degree’; File that holds the binary code

9. Using UDTs in CREATE TABLE • As an attribute type: CREATE TABLE TRANSCRIPT ( Student StudentType, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1) ) • As a table type: CREATE TABLE STUDENT OFStudentType; Such a table is called typed table. A previously defined UDT

10. Objects • Only typed tables contain objects (ie, tuples with oids) • Compare: CREATE TABLE STUDENT OFStudentType; and CREATE TABLE STUDENT1 ( Name CHAR(20), Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)), Id INTEGER, Status CHAR(2) ) • Both contain tuples of exactly the same structure • Only the tuples in STUDENT – not STUDENT1 – have oids • Will see later how to reference objects, create them, etc.

11. Querying UDTs • Nothing special – just use path expressions SELECT T.Student.Name, T.Grade FROM TRANSCRIPT T WHERE T.Student.Address.Street = ‘Main St.’ Note: T.Student has the type StudentType. The attribute Name is not declared explicitly in StudentType, but is inherited from PersonType.

12. Updating User-Defined Types • Inserting a record into TRANSCRIPT: INSERT INTO TRANSCRIPT(Student,Course,Semester,Grade) VALUES (????, ‘CS308’, ‘2000’, ‘A’) The type of the Studentattribute is StudentType. How does one insert a value of this type (in place of ????)? Further complication: the UDT StudentType is encapsulated, ie, it is accessible only through public methods, which we did not define Do it through the observer and mutator methods provided by the DBMS automatically

13. Observer Methods • For each attribute A of type T in a UDT, an SQL:1999 DBMS is supposed to supply an observer method, A: ( )  T, which returns the value of A(the notation “( )” means that the method takes no arguments) • Observer methods for StudentType: • Id: ( ) INTEGER • Name: ( )  CHAR(20) • Status: ( )  CHAR(2) • Address: ( )  ROW(INTEGER, CHAR(20), CHAR(5)) • For example, in SELECT T.Student.Name, T.Grade FROM TRANSCRIPT T WHERE T.Student.Address.Street = ‘Main St.’ Nameand Address are observer methods, since T.Student is of type StudentType Note: Grade is not an observer, because TRANSCRIPT is not part of a UDT, but this is a conceptual distinction – syntactically there is no difference

14. Mutator Methods • An SQL:1999 DBMS is supposed to supply, for each attribute A of type T in a UDT U, amutator method A: T  U For any object o of type U, it takes a value t of type T and replaces the old value of o.A with t; it returns the new value of the object.Thus, o.A(t) is an object of type U • Mutators for StudentType: • Id: INTEGER  StudentType • Name: CHAR(20)  StudentType • Address: ROW(INTEGER, CHAR(20), CHAR(5))  StudentType

15. Example: Inserting a UDT Value INSERT INTO TRANSCRIPT(Student,Course,Semester,Grade) VALUES ( NEW StudentType().Id(111111111).Status(‘G5’).Name(‘Joe Public’) .Address(ROW(123,’Main St.’, ‘54321’)) , ‘CS532’, ‘S2002’, ‘A’ ) ‘CS532’, ‘S2002’, ‘A’ are primitive values for the attributes Course, Semester,Grade Add a value for Id Add a value for the Address attribute Create a blank StudentType object Add a value for Status

16. Example: Changing a UDT Value UPDATE TRANSCRIPT SET Student = Student.Address(ROW(21,’Maple St.’,’12345’)).Name(‘John Smith’), Grade = ‘B’ WHERE Student.Id = 111111111 AND CrsCode = ‘CS532’ AND Semester = ‘S2002’ • Mutators are used to change the values of the attributes Address and Name Change Name Change Address

17. Referencing Objects • Consider again CREATE TABLE TRANSCRIPT ( Student StudentType, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1) ) • Problem: TRANSCRIPT records for the same student refer to distinct values of type StudentType (even though the contents of these values may be the same) – a maintenance/consistency problem • Solution: use self-referencing column (next slide) • Bad design, which distinguishes objects from their references • Not truly object-oriented

18. Self-Referencing Column • Every typed table has a self-referencing column • Normally invisible • Contains explicit object Id for each tuple in the table • Can be given an explicit name – the only way to enable referencing of objects CREATE TABLE STUDENT2 OF StudentType REF ISstud_oid; Self-referencing columns can be used in queries just like regular columns Their values cannot be changed, however Self-referencing column

19. Reference Types and Self-Referencing Columns • To reference objects, use self-referencing columns + reference types: REF(some-UDT) CREATE TABLE TRANSCRIPT1 ( Student REF(StudentType)SCOPE STUDENT2, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1) ) • Two issues: • How does one query the attributes of a reference type • How does one provide values for the attributes of type REF(…) • Remember: you can’t manufacture these values out of thin air – they are oids! Reference type Typed table where the values are drawn from

20. Querying Reference Types • Recall: Student REF(StudentType)SCOPE STUDENT2 in TRANSCRIPT1. How does one access, for example, student names? • SQL:1999 has the same misfeature as C/C++ has (and which Java and OQL do not have): it distinguishes between objects and references to objects. To pass through a boundary of REF(…) use “” instead of “.” SELECT T.StudentName, T.Grade FROM TRANSCRIPT1 T WHERE T.StudentAddress.Street = “Main St.” Not crossing REF(…) boundary, use “.” Crossing REF(…) boundary – use 

21. Inserting REF Values • How does one give values to REF attributes, like Student in TRANSCRIPT1? • Need to use explicit self-referencing columns, likestud_oidinSTUDENT2 • Example: Creating a TRANSCRIPT1 record whose Student attribute has an object reference to an object in STUDENT2: INSERT INTO TRANSCRIPT1(Student,Course,Semester,Grade) SELECT S.stud_oid, ‘HIS666’, ‘F1462’, ‘D’ FROM STUDENT2 S WHERE S.Id = ‘111111111’ Explicit self-referential column of STUDENT2

22. Collection Data Types • The lack of Set data type severely cripples the object-oriented capabilities of SQL:1999. However, sets will likely be added during the next update of SQL. Sets will look something like the following: CREATE TYPE StudentType UNDER PersonType AS ( Id INTEGER, Status CHAR(2), Enrolled SETOF(REF(CourseType)) SCOPE COURSE ) A bunch of references to objects in a typed table COURSE

23. Querying Collection Types • For each student, list the Id, street, and the courses in which the student is enrolled: SELECT S.Id, S.Address, C.Name FROM STUDENT S, COURSE C WHERE C.CrsCode IN ( SELECT E CrsCode FROM S.Enrolled E ) • Note: E is bound to a set of object references, so E CrsCode is also a set

24. Oracle Object Relational Features

25. Oracle’s GOAL: • Add object-oriented features on top of the existing relational database. • Add support for complex data types. • Add support for encapsulation ISSUES • Only a partial implementation. • No concept of inheritance. • PL/SQL • No polymorphism • Overloading • PL/SQL

26. Objects • Object types • ADT • Collection types • VARRAY • Nested table

27. Object Types Each object has the following: • Name - uniquely identifies it within a schema. • Attributes – primitive data types or other complex objects. • Methods – written in PL/SQL

28. Declaring a type CREATE TYPE person_o AS OBJECT ( fname VARCHAR2(20), lname VARCHAR2(20) ); CREATE TYPE emp_o AS OBJECT ( emp person_o, eid NUMBER );

29. Object Table • Creates a table where each row represents an object • CREATE TABLE person_table OF person_o; • CREATE TABLE emp_table OF emp_o; • Inserting data • INSERT INTO person_table VALUES (‘John’, ‘Doe’); • INSERT INTO person_table VALUES (person_o(‘John’, ‘Doe’)); • INSERT INTO emp_table VALUES (person_o(‘John’, ‘Doe’), 1);

30. Querying the data • You can view the table in two fashions: • As an object where a column represents an object • SELECT VALUE(p) from person_table p; • As a relation where each object attributes is mapped to a column. • SELECT * from person_table;

31. Dot notation CREATE TYPE emp_o AS OBJECT ( emp person_o, eid NUMBER ); SELECT * FROM emp_table; SELECT p.emp.fname, p.emp.lname, p.eid FROM emp_table p;

32. Reference Pointers • REFs are used to point from one object to another . The target of the reference must be an object table. • REFS must be valid when they are stored • REFS do not have to remain valid – dangling REF. ISDANGLING • REFs are strongly typed, but they can be scoped or non-scoped

33. Reference Pointers (cont) • Non-scoped CREATE TABLE dept_table (dno NUMBER, manager REF emp_o); • Scoped – constrained to a table CREATE TABLE dept_table (dno NUMBER, manager REF emp_o, SCOPE FOR (manager) IS emp_table); • Insertion • INSERT INTO dept_table VALUES (1, (SELECT REF(p) FROM emp_table p WHERE fname = ‘john’ AND lname = ‘doe’));

34. Using REFs SELECT * FROM dept_table; SELECT d.dno, DEREF(d.manager) FROM dept_table d; SELECT d.dno d.manager.lname FROM dept_table d;

35. Methods • Functions or procedures written in PL/SQL or Java are stored in the database. • Methods fall under three categories: • Constructor • Comparison • Static

36. Constructor • Constructors are automatically created by the system. • You already saw this – person_o(‘john’, ‘doe’); • NULL values • INSERT INTO person_table VALUES (person_o(NULL,NULL)) • INSERT INTO person_table VALUES (NULL);

37. Comparison • Methods used to do compare object instances. • ORDER – takes another instance and compares it to the current instance • MAP – Returns a number that is used to rank instances of object types

38. MAP and ORDER • An object can have a single MAP method or a single ORDER method. • Which one should you use and why?

39. Map Method CREATE TYPE foo AS OBJECT ( value NUMBER, MAP MEMBER FUNCTION mp RETURN NUMBER); / CREATE OR REPLACE TYPE BODY foo AS MAP MEMBER FUNCTION mp RETURN NUMBER IS BEGIN RETURN value; END; END;

40. Order Method CREATE TYPE foo AS OBJECT ( value NUMBER, ORDER MEMBER FUNCTION ord (that foo) RETURN NUMBER); / CREATE OR REPLACE TYPE BODY foo AS ORDER MEMBER FUNCTION ord(that foo) RETURN NUMBER IS BEGIN IF (value < that.value) THEN RETURN -1; ELSIF (value > that.value) THEN RETURN +1; ELSE RETURN 0; END IF; END; END; /

41. Method CREATE TYPE person2_o AS OBJECT ( fname VARCHAR2(20), lname VARCHAR2(20), MEMBER FUNCTION fullname RETURN VARCHAR2, PRAGMA RESTRICT_REFERENCES(fullname, WNDS)); / CREATE OR REPLACE TYPE BODY person2_o AS MEMBER FUNCTION fullname RETURN VARCHAR2 IS BEGIN RETURN fname || ' ' || lname; END; END; /

42. Invoking the method CREATE TABLE p2tab OF person2_o; SELECT p.fullname() FROM p2tab p WHERE p.fullname() LIKE ‘john%’;

43. PRAGMA • Directives to the compiler that restrict what can be done in a method. • Add line to table declaration PRAGMA RESTRICT_REFERENCES( fullname, WNDS, WNPS, RNDS, RNPS) • WNDS is mandatory if you want to call the methods from SQL.

44. Collection Types • Oracle provides two techniques for modeling one-to-many relationships. • VARRAY – stores a fixed number of repeating attributes in a column. • Nested Tables – table within a table. • Collections can be columns in tables or attributes of object types.

45. VARRAY • Just like a C array. • It has a fixed size • It contains objects of the same datatype. • Each element has an index • VARRAYs can be used as columns in tables or as attributes in objects. • Data stored is stored in the VARRAY as a raw or BLOB.

46. Declaring a VARRAY CREATE TYPE type_name AS VARRAY (limit) OF data_type; CREATE TYPE tire_o AS OBJECT( PSI NUMBER, MFG VARCHAR2(20)); / CREATE TYPE bike_tire_vtype AS VARRAY(2) OF tire_o; / CREATE TABLE bike_table ( mfg VARCHAR2(20), wheels bike_tire_vtype);

47. Initializing VARRAY • Constructor • Using the database • Using direct assignment

48. VARRAY Constructor INSERT INTO bike_table VALUES ('RF900R', bike_tire_vtype(tire_o(32,'metzler'), tire_o(35,'metzler'))); • You don’t have to set all of the values INSERT INTO bike_table VALUES ('RF900R', bike_tire_vtype(tire_o(32,'metzler'), NULL)); INSERT INTO bike_table VALUES ('RF900R', bike_tire_vtype(tire_o(32,'metzler'));

49. Direct Assignment DECLARE src bike_tire_vtype:= bike_tire_vtype (tire_o(32, 'cheap'),tire_o(35, 'cheap')); tgt bike_tire_vtype; BEGIN src(1) := tire_o(42, 'cheaper'); tgt := src; FOR cnt IN 1..tgt.COUNT LOOP DBMS_OUTPUT.PUT(tgt(cnt).psi||tgt(cnt).mfg); DBMS_OUTPUT.NEW_LINE; END LOOP; END; /

50. From a Database Fetch CREATE TABLE sets ( code number primary key, pair bike_tire_vtype default bike_tire_vtype(tire_o(1,'generic'), tire_o(2, 'generic'))); / INSERT INTO sets(code) values (1); / • SQL insert into sets (SELECT 2, s.pair from sets s where code = '1')