Object Relational Databases 1 ObjectRelational Data Model A

Object Relational Databases 1

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 2

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 (completely new) object = (oid, tuple-value) tuple = tuple-value = [Attr 1: v 1, …, Attrn: vn] 3

SQL: 1999 Tuple Values • Tuple value: value [Attr 1: v 1, …, Attrn: vn] – Attri are 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 4

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 toplevel types CREATE TABLE PERSON ( Name CHAR(20), Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) ) 5

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’ 6

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 7

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

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

Objects • Only typed tables contain objects (ie, tuples with oids) • Compare: CREATE TABLE STUDENT OF Student. Type; and CREATE TABLE STUDENT 1 ( 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 STUDENT 1 – have oids • Will see later how to reference objects, create them, etc. 10

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 Student. Type. The attribute Name is not declared explicitly in Student. Type, but is inherited from Person. Type. 11

Updating User-Defined Types • Inserting a record into TRANSCRIPT: INSERT INTO TRANSCRIPT(Student, Course, Semester, Grade) VALUES (? ? , ‘CS 308’, ‘ 2000’, ‘A’) The type of the Student attribute is Student. Type. How does one insert a value of this type (in place of ? ? )? Further complication: the UDT Student. Type 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 12

Observer Methods • For each attribute A of type T in a UDT, an SQL: 1999 DBMS is supposed to supply an observer method, method A: ( ) T, which returns the value of A (the notation • “( )” means that the method takes no arguments) Observer methods for Student. Type: • 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. ’ Name and Address are observer methods, since T. Student is of type Student. Type 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 13

Mutator Methods • An SQL: 1999 DBMS is supposed to supply, for each attribute A of type T in a UDT U, a mutator 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 Student. Type: • Id: INTEGER Student. Type • Name: CHAR(20) Student. Type • Address: ROW(INTEGER, CHAR(20), CHAR(5)) Student. Type 14

Example: Inserting a UDT Value INSERT INTO TRANSCRIPT(Student, Course, Semester, Grade) VALUES ( NEW Student. Type(). Id(11111). Status(‘G 5’). Name(‘Joe Public’). Address(ROW(123, ’Main St. ’, ‘ 54321’)) , ‘CS 532’, ‘S 2002’, ‘A’ ) Add a value for Id Create a blank Student. Type object Add a value for the Address attribute Add a value for Status ‘CS 532’, ‘S 2002’, ‘A’ are primitive values for the attributes Course, Semester, Grade 15

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

Referencing Objects • Consider again CREATE TABLE TRANSCRIPT ( Student. Type, Crs. Code CHAR(6), Semester CHAR(6), Grade CHAR(1) ) • Problem: TRANSCRIPT records for the same student refer to distinct values of type Student. Type (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 17

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 STUDENT 2 OF Student. Type REF IS stud_oid; Self-referencing columns can be used in queries just like regular columns Their values cannot be changed, however 18

Reference Types and Self-Referencing Columns • To reference objects, use self-referencing columns + reference types: types REF(some-UDT) CREATE TABLE TRANSCRIPT 1 ( Student REF(Student. Type) SCOPE STUDENT 2, Crs. Code CHAR(6), Semester CHAR(6), Grade CHAR(1) ) Reference type Typed table where the values are drawn from • 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! 19

Querying Reference Types • Recall: Student REF(Student. Type) SCOPE STUDENT 2 in TRANSCRIPT 1. 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 TRANSCRIPT 1 T WHERE T. Student Address. Street = “Main St. ” Not crossing REF(…) boundary, use “. ” Crossing REF(…) boundary – use 20

Inserting REF Values • How does one give values to REF attributes, like Student in TRANSCRIPT 1? • Need to use explicit self-referencing columns, like stud_oid in STUDENT 2 • Example: Creating a TRANSCRIPT 1 record whose Student attribute has an object reference to an object in STUDENT 2: INSERT INTO TRANSCRIPT 1(Student, Course, Semester, Grade) SELECT S. stud_oid, ‘HIS 666’, ‘F 1462’, ‘D’ FROM STUDENT 2 S WHERE S. Id = ‘ 11111’ Explicit self-referential column of STUDENT 2 21

Collection Data Types • The lack of Set data type severely cripples the objectoriented 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 Student. Type UNDER Person. Type AS ( Id INTEGER, Status CHAR(2), Enrolled SETOF(REF(Course. Type)) SCOPE COURSE ) A bunch of references to objects in a typed table COURSE 22

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. Crs. Code IN ( SELECT E Crs. Code FROM S. Enrolled E ) • Note: E is bound to a set of object references, so E Crs. Code is also a set 23

Oracle Object Relational Features 24

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 25

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

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 27

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

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); 29

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; 30

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

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 32

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’)); 33

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; 34

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

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); 36

Comparison • Methods used to do compare object instances. • ORDER – takes another instance and compares it to the current instance negative zero positive this < arg this = arg this > arg • MAP – Returns a number that is used to rank instances of object types 37

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

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; 39

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; / 40

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

Invoking the method CREATE TABLE p 2 tab OF person 2_o; SELECT p. fullname() FROM p 2 tab p WHERE p. fullname() LIKE ‘john%’; 42

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. WNDS WNPS RNDS RNPS write no database state write no package state read no database state read no package state 43

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

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

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

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

VARRAY Constructor INSERT INTO bike_table VALUES ('RF 900 R', 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 ('RF 900 R', bike_tire_vtype(tire_o(32, 'metzler'), NULL)); INSERT INTO bike_table VALUES ('RF 900 R', bike_tire_vtype(tire_o(32, 'metzler')); 48

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; / 49

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') 50

From a Database Fetch (PL/SQL) CREATE OR REPLACE Function getpair(base in sets. code%type)RETURN sets. pair%type IS --DECLARE temp sets. pair%type; BEGIN SELECT s. pair INTO temp FROM sets s WHERE s. code = base; return temp; END; / 51

Nested Table • Table within another table. • The tables don’t have a fixed maximum size, because the data is all out-of-line in the external table. • The nesting has a depth of one. • The tables are unordered. • The tables can have triggers and indexes. • The nested table can’t be directly queried. 52

Creating a simple nested table • Create the nested table datatype CREATE TYPE fruit_ntypes AS TABLE OF varchar 2(20); / • Nested table as a column CREATE TABLE fruit_basket( id NUMBER, fruits fruit_ntypes) nested table fruits store as fruit_selections; 53

Creating the Nested Table CREATE TYPE tire_ntype AS TABLE OF tire_o; / CREATE TYPE auto_table AS OBJECT ( mfg varchar 2(20), model varchar 2(20), tires tire_ntype ); / CREATE TABLE auto_info OF auto_table ( primary key (model, mfg) ) nested table tires store as tire_detail; 54

Inserting into the Object Tables INSERT INTO auto_info values ( 'FORD', 'RANGER', tire_ntype (tire_o(32, 'bridgestone'), tire_o(33, 'yokohama'), tire_o(32, 'yokohama'))) / 55

Collection Functions THE Flattens the nested table. CAST Maps a collection of one type to another VARRAY <-> Nested MULTISET Maps a database to a collection TABLE Maps a collection to a table 56

THE • What is the average tire pressure of a Ford Ranger? SELECT avg(p. psi) FROM THE (SELECT tires FROM auto_info WHERE mfg = 'FORD' AND model = 'RANGER') p 57

THE SELECT column_name [, column_name]… FROM THE( SELECT nested_table_column FROM parent_table_name [WHERE condition parent table]) [WHERE condition_nested_table] • You can work on at most one row of the parent table. 58

CAST • THE works on nested tables but doesn’t work on VARRAYs. • Use CAST to cast the VARRAY into a nested table. 59

THE update • Replace all of the firestone tires on the Ford Ranger w/ yokohama tires. UPDATE THE(SELECT tires FROM auto_info WHERE mfg = 'FORD' AND model = 'RANGER') SET mfg = 'yokohama' WHERE mfg = 'firestone' / 60

THE insert • Insert a new row representing the spare tire in all Ford autos INSERT INTO THE(SELECT tires FROM auto_info WHERE mfg = ‘FORD’) VALUES (50, ‘GENERIC SPARE’); 61

THE complicated insert • Add a spare tire to the Ford Ranger if it has more than two firestone tires. INSERT INTO THE( SELECT tires FROM auto_info WHERE mfg = 'FORD' AND model = 'RANGER') SELECT 50, 'GENERIC SPARE' FROM THE(SELECT tires FROM auto_info WHERE mfg = 'FORD' AND model = 'RANGER') WHERE mfg = 'firestone' HAVING count(*) >= 2; 62

THE delete • Remove all of the firestone tires from the Ford Ranger. DELETE THE( SELECT tires FROM auto_info WHERE mfg = 'FORD' AND model = 'RANGER') WHERE mfg = 'firestone'; 63

THE CAST • THE does not work with VARRAYs. • You use the CAST function to cast a VARRAY into a NESTED table. • You can't access VARRAY 64

CAST and COLUMN_VALUE CREATE TYPE my_byte_type AS VARRAY(8) OF VARCHAR 2(1); CREATE TABLE my_color ( color VARCHAR 2(20), code my_byte_type ); SELECT COLUMN_VALUE FROM THE(SELECT CAST(code AS my_byte_type) FROM my_color WHERE color = 'orange'); 65

MULTISET • It converts a set of data into a collection type. • MULTISET works w/ CAST SELECT CAST (MULTISET (SELECT column FROM table) AS collection_type) FROM DUAL; • When do you use it? 66

MULTISET CREATE TABLE data ( key NUMBER, PRIMARY KEY (key)); CREATE TABLE data_point ( key NUMBER, value NUMBER, FOREIGN KEY (key) REFERENCES data (key)); 67

MULTISET CREATE TYPE data_ntype AS TABLE OF NUMBER; CREATE TABLE data_tab_o ( key NUMBER, vals data_ntype) NESTED TABLE vals STORE AS data_val_tabs; 68

MULTISET (PL/SQL) DECLARE CURSOR data_c IS SELECT key, CAST( MULTISET ( SELECT dp. value FROM data_point dp WHERE dp. key = d. key) AS data_ntype) FROM data d; tkey NUMBER; tdata_ntype; --continued next slide 69

MULTISET (PL/SQL) --anonymous block continued BEGIN OPEN data_c; LOOP FETCH data_c INTO tkey, tdata; EXIT WHEN data_c%NOTFOUND; INSERT INTO data_tab_o VALUES (tkey, tdata); END LOOP; CLOSE data_c; END; 70

TABLE • Converts a collection column into a logical table that you can select on. TABLE(alias. column); • Lets you determine if a row contains a collection that satisfies a criteria. 71

TABLE • Find the make and model of the autos that have exactly two bridgestone tires. SELECT mfg, model FROM auto_info ai WHERE (SELECT count(p. mfg) FROM TABLE(ai. tires) p WHERE mfg = 'bridgestone') = 2; 72

References • Oracle 8 documentation • Pribyl. B. & Dawes C. Oracle PL/SQL Language Pocket Reference. O’Reilly. 1999 • Sunderraman. R. Oracle 8 Programming Primer. Addison Wesley. 1999. 73