Object-Oriented Databases

1. Object-Oriented Databases

2. Outline • Advanced database applications • Shortcomings of Relational DBs • Object-oriented concepts • Object Relational Systems (ORDBMSs) • SQL:1999 object extensions • Object-oriented database Systems (OODBMSs) • ODMG Data Model • ODL – data definition language • OQL – query language

3. Advanced Database Applications • Computer-Aided Design/Manufacturing (CAD/CAM) • Computer-Aided Software Engineering (CASE) • Network Management Systems • Office Information Systems (OIS) and Multimedia Systems • Digital Publishing • Geographic Information Systems (GIS) • Interactive and Dynamic Web sites • Other applications with complex and interrelated objects and procedural data.

4. Expected features for new applications • Complex objects • Behavioral data • Meta knowledge • Long duration transactions

5. Weaknesses of RDBMSs • Poor representation of “Real World” entities • Normalization leads to relations that do not correspond to entities in “real world”. • Semantic overloading • Relational model has only one construct for representing data and data relationships: the relation. • Relational model is semantically overloaded

6. Weaknesses of RDBMSs • Limited operations • only a fixed set of operations which cannot be extended. • Difficulty handling recursive queries • Impedance mismatch • Most DMLs lack computational completeness. • To overcome this, SQL can be embedded in a high-level language. • This produces an impedance mismatch - mixing different programming paradigms. • Estimated that as much as 30% of programming effort and code space is expended on this type of conversion.

7. Object-Oriented Concepts • Abstraction, encapsulation, information hiding. • Objects and attributes. • Object identity. • Methods and messages. • Classes, subclasses, superclasses, and inheritance. • Overloading. • Polymorphism and dynamic binding.

8. Complex Objects An object that consists of sub-objects but is viewed as a single object. • Objects participate in a A-PART-OF relationship. • Contained object can be encapsulated within complex object, accessed by complex object’s methods. • Or have its own independent existence, and only an OID is stored in complex object.

9. Database Systems First Generation DBMS: Network and Hierarchical • Required complex programs for even simple queries. • Minimal data independence. • No widely accepted theoretical foundation. Second Generation DBMS: Relational DBMS • Helped overcome these problems. Third Generation DBMS: OODBMS and ORDBMS.

10. History of Data Models

11. Origins of the Object-Oriented Data Model

12. ORDBMS

13. ORDBMSs • Vendors of RDBMSs conscious of threat and promise of OODBMS. • Agree that RDBMSs not currently suited to advanced database applications, and added functionality is required. • Reject claim that extended RDBMSs will not provide sufficient functionality or will be too slow to cope adequately with new complexity. • Can remedy shortcomings of relational model by extending model with OO features.

14. ORDBMSs - Features • OO features being added include: • user-extensible types, • encapsulation, • inheritance, • polymorphism, • dynamic binding of methods, • complex objects including non-1NF objects, • object identity.

15. Stonebraker’s View

16. 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]

17. 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 Only the ARRAY construct is – a fixed size array. SETOF and LISTOF are not supported.

18. 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 CREATE TABLE PERSON ( Name CHAR(20), AddressROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) )

19. Row Types (Contd.) • 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.ZIP = ‘55555’ UPDATE PERSON SET Address = ROW(21, ‘Main St’, ‘12345’) WHERE Address = ROW(123, ‘Maple Dr.’, ‘54321’) AND Name = ‘J. Public’

20. User Defined Types (UDT) • 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

21. A UDT Example CREATE TYPEPersonType AS ( Name CHAR(20), AddressROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) ); CREATE TYPE StudentTypeUNDERPersonType 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

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

23. Objects • Only typed tables contain objects (ie, tuples with oids) • Compare: CREATE TABLE STUDENTOFStudentType; 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. • This disparity is motivated by the need to stay backward compatible with SQL-92.

24. 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.Studenthas the typeStudentType. The attribute Name is not declared explicitly in StudentType, but is inherited from PersonType.

25. Updating User-Defined Types • Inserting a record into TRANSCRIPT: INSERT INTO TRANSCRIPT(Student,Course,Semester,Grade) VALUES (????, ‘CS308’, ‘2000’, ‘A’) • The type of the Student attribute 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

26. 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 TRANSCRIPTis not part of a UDT

27. 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 forStudentType: • Id: INTEGER  StudentType • Name: CHAR(20)  StudentType • Address: ROW(INTEGER, CHAR(20), CHAR(5))  StudentType

28. 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

29. 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 ANDCrsCode = ‘CS532’ AND Semester = ‘S2002’ • Mutators are used to change the values of the attributes Address and Name Change Name Change Address

30. Referencing Objects • Consider again CREATE TABLE TRANSCRIPT ( StudentStudentType, 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 • Bad design, which distinguishes objects from their references • Not truly object-oriented

31. 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

32. Reference Types and Self-Referencing Columns • To reference objects, use self-referencing columns + reference types: REF(some-UDT) CREATE TABLE TRANSCRIPT1 ( StudentREF(StudentType)SCOPESTUDENT2, 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

33. Querying Reference Types • Recall: StudentREF(StudentType)SCOPESTUDENT2in 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 

34. Inserting REF Values • How does one give values to REF attributes, like Student in TRANSCRIPT1? • 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

35. Object-Oriented OracleAn Analysis of the Object-Oriented Features of Oracle’s Database Management System

36. Background • Beginning with Oracle 8 Universal Data Server, Oracle started implementing object-oriented (OO) principals within the database management system. • Oracle is not a true OO database – object-relational. • Oracle’s goals for OO support: • Allow users to model business objects via types. • Provide infrastructure to support OO access.

37. OO Features/Advantages of Objects in Oracle OO Features: • Abstraction • Encapsulation • Inheritance Advantages: • Object re-use • Use of methods • Efficiencies • Model real-world business objects

38. Object Type Implementation Creating Types Similar to creating a “class” with attributes: CREATE TYPE addr_ty AS OBJECT (street varchar2(60), city var char2(30), state char(2), zip varchar(9));

39. Object Type Implementation Imbedding Objects and Nesting Create a person type with address type nested inside: CREATE TYPE person_ty AS OBJECT (name varchar2(25), address addr_ty); Create a student type with person type nested inside: CREATE TYPE student_ty AS OBJECT (student_id varchar2(9), person person_ty);

40. Object Type Implementation Creating an Object Table Now that the student_ty object type has been defined it can be used in creating an object table like the following: CREATE TABLE STUDENT (full_student student_ty);

41. Object Type Implementation To extract data, the following query can be entered: SELECT s.full_student.student_id ID, s.full_student.person.name NAME, s.full_student.person.address.street STREET FROM student s WHERE s.full_student.student_id = 100 ID NAME STREET --------- ------------------------- ------------- 100 John Q. Student 1000 Chastain Rd.

42. Object Type Implementation • Updating and deleting is similar to what one would do in the relational model: UPDATE STUDENT s SET s.full_student.person.name = 'JOHN NEWNAME' WHERE s.full_student.student_id = 100; DELETE FROM STUDENT s WHERE s.full_student.student_id = 100;

43. Implementing Methods To define a method in a type object: create or replace type newperson_ty as object (firstname varchar2(25), lastname varchar2(25), birthdate date, member function AGE(BirthDate in DATE) return NUMBER; Then define the method itself: create or replace type body newperson_ty as member function AGE(BirthDate in DATE) return NUMBER is begin RETURN ROUND(SysDate - BirthDate); end; end;

44. Implementing Methods To test the method first set up a table holding the person_ty object type: create table NEWPERSON of newperson_ty; insert into NEWPERSON values (newperson_ty('JOHN', 'DOE', TO_DATE('03-FEB-1970', 'DD-MON-YYYY'))); To call the AGE function we can do the following: select P.PERSON.AGE(P.PERSON.Birthdate) from NEWPERSON P; P.PERSON.AGE(P.PERSON.Birthdate) ---------------------------------------- 12005

45. Referencing • Every row object has a unique identifier called the object identifier (OID). • OID allows other objects to reference an existing row object. • REF function can be used to reference an OID: create table NEWDEPARTMENT (DeptName VARCHAR(30), PersonIn REF NEWPERSON_TY); • Table NEWDEPARTMENT holds a reference to a NEWPERSON_TY object, but does not hold any real values.

46. Referencing To get a full description of the table just created: Set describe depth 2 Desc NEWDEPARTMENT Name Null? Type --------------- -------- ------------------- DEPTNAME VARCHAR2(30) PERSONIN REF OF NEWPERSON_TY FIRSTNAME VARCHAR2(25) LASTNAME VARCHAR2(25) BIRTHDATE DATE

47. Referencing • To insert a record into NEWDEPARTMENT, the REF is needed to store the NEWPERSON reference in the PersonIn column: insert into NEWDEPARTMENT select 'Research',REF(P) from NEWPERSON P where LastName = 'DOE'; • The literal value “Research” is inserted into the NEWPERSON table. • The REF function returns the OID from the query on the selected NEWPERSON object. • The OID is now stored as a pointer to the row object in the NEWPERSON object table.

48. Referencing • The referenced value cannot be seen unless the DREF function is used. The DREF function takes the OID and evaluates the reference to return a value. select DEREF(D.PersonIn) from NEWDEPARTMENT D where DEPTNAME = 'Research' DEREF(D.PERSONIN)(FIRSTNAME, LASTNAME, BIRTHDATE) ---------------------------------------------------- NEWPERSON_TY('JOHN', 'DOE', '03-FEB-70') • This shows that the NEWPERSON record JOHN DOE is referenced by the Research record in NEWDEPARTMENT.

49. Referencing • To gather the same structure of the object type of an object table the VALUE function is required. select value(p) from newperson p where lastname = 'DOE' VALUE(P)(FIRSTNAME, LASTNAME, BIRTHDATE) ----------------------------------------- NEWPERSON_TY('JOHN', 'DOE', '03-FEB-70')

50. Referencing PL/SQL Sample: set serveroutput on declare v_person NEWPERSON_TY; begin select value(p) into v_person from NEWPERSON p where lastname = 'DOE'; DBMS_OUTPUT.PUT_LINE(v_person.firstname); DBMS_OUTPUT.PUT_LINE(v_person.lastname); DBMS_OUTPUT.PUT_LINE(v_person.birthdate); end; JOHN DOE 03-FEB-70