Object-Relational Databases Salman Azhar

1. Object-Relational DatabasesSalman Azhar User-Defined Types Object IDs Nested Tables These slides use some figures, definitions, and explanations from Elmasri-Navathe’s Fundamentals of Database Systemsand Molina-Ullman-Widom’s Database Systems Database Systems: Salman Azhar

2. Merging Relational and Object Models • Object-oriented models support • interesting data types --- not just flat files. • E.g., Maps, multimedia, etc. • The relational model supports • very-high-level queries • Object-relational databases are an attempt to get the best of both. Database Systems: Salman Azhar

3. Evolution of DBMSs • Object-oriented DBMSs fell short • because they did not offer the efficiencies of a relational DBMS. • Object-relational extensions to relational DBMSs • capture much of the advantages of OO • yet retain the relation as the fundamental abstraction. Database Systems: Salman Azhar

4. SQL-99 and DBMS Features • SQL-99 includes many of the object-relational features to be described. • However, being so new, different DBMSs use different approaches. • We’ll sometimes use features and syntax from Oracle and SQL Server. Database Systems: Salman Azhar

5. User Defined Types • A user-defined type, or UDT, is essentially a class definition, with a structure and methods. • Two uses: • As a row-type, that is, the type of a relation. • As an column-type, that is, the type if attribute in a relation. Database Systems: Salman Azhar

6. UDT Definition CREATE TYPE <typename> AS ( <list of elements, as in CREATE TABLE> ); • Oracle syntax: • Add “OBJECT” as in CREATE TYPE <name> AS OBJECT. • Follow with / to have the type stored. Database Systems: Salman Azhar

7. Example: UDT Definition CREATE TYPE DealerType AS ( name CHAR(20), addr CHAR(20) ); CREATE TYPE CarType AS ( name CHAR(20), manf CHAR(20) ); Database Systems: Salman Azhar

8. References • If T is a type, then REF T is the type of a reference to T • this means a pointer to an object of type T. • often called an “object ID” in OO systems. • Unlike object ID’s, a REF is visible, • although it is usually gibberish. Database Systems: Salman Azhar

9. Example: REF CREATE TYPE SellsType AS ( dealer REF DealerType, car REF CarType, price FLOAT ); • SellsType objects look like: REF DealerType REF CarType FLOAT 30000 Reference to a DealerType object Reference to a CarType object Database Systems: Salman Azhar

10. UDTs as Row Types • A table may be defined to have a schema that is a row type, rather than by listing its elements. • Syntax: • CREATE TABLE <table name> OF <type name>; • Creates a table where each row is of <type name> Database Systems: Salman Azhar

11. Example: Creating Tables CREATE TABLE Dealer OF DealerType; CREATE TABLE Car OF CarType; CREATE TABLE Sells OF SellsType; Database Systems: Salman Azhar

12. Values of Relations with a Row-type • Technically, a relation declared to have a rowtype DealerType (such as Dealer), • is not a set of pairs • it is a unary relation • whose tuples are objects with two components: • name and addr. Database Systems: Salman Azhar

13. Type Constructors • Each UDT has a type constructor of the same name that wraps objects of that type. Database Systems: Salman Azhar

14. Example: Type Constructor • The query SELECT * FROM Dealer; • Produces “tuples” such as: DealerType(‘AutoNation’, ‘Maple St.’) Database Systems: Salman Azhar

15. Aliasing:Accessing Values From a Rowtype • In Oracle and MS SQL Server, the dot works as expected • but it may not work in all DBMS • so it is a good idea, to use an alias for every relation, when O-R features are used. • Example: SELECT dd.name, dd.addr FROM Dealer dd; Database Systems: Salman Azhar

16. Accessing Values: SQL-99 Approach • In SQL-99, each attribute of a UDT has: • Get method • Called Generator methods (get the value) and • Set method • Called Mutator methods (change the value) • These methods have the same name as the attribute. • The generator for A takes no argument, as A( ). • The mutator for A takes a new value as argument, as A(v). Database Systems: Salman Azhar

17. Example: SQL-99 Value Access • Consider SELECT dd.name, dd.addr FROM Dealer dd; • The same query in SQL-99 is SELECT dd.name( ), dd.addr( ) FROM Dealer dd; Explicitly access generators Database Systems: Salman Azhar

18. Inserting Row Type Values(Oracle Style) • We can use a standard INSERT statement, • remembering that a relation with a row type is really unary and needs that type constructor. • Example: INSERT INTO Dealer VALUES( DealerType(‘AutoNation’, ‘Maple St.’) ); • Note: DealerType is a constructor Database Systems: Salman Azhar

19. Inserting Values: SQL-99 Style • Create a variable X of the suitable type, using the constructor method for that type. • Use the mutator methods for the attributes to set the values of the fields of X. • Insert X into the relation. Database Systems: Salman Azhar

20. Example: SQL-99 Insert • The following must be part of a procedure, so we have a variable newDealer. SET newDealer = DealerType( ); newDealer.name(‘AutoNation’); newDealer.addr(‘Maple St.’); INSERT INTO Dealer VALUES(newDealer); Construct a new dealer Mutator methods change newDealer’s name and addr components. Insert new values Database Systems: Salman Azhar

21. UDTs as Column Types • A UDT can be the type of a column (an attribute) • in either another UDT definition • or in a CREATE TABLE statement • Use the name of the UDT as the type of the attribute Database Systems: Salman Azhar

22. Values of addr and favCar components are objects with 3 and 2 fields, respectively. Example: Column Type CREATE TYPE AddrType AS ( street CHAR(30), city CHAR(20), zip INT ); CREATE TABLE Buyers ( name CHAR(30), addr AddrType, favCar CarType ); Database Systems: Salman Azhar

23. Oracle Problem With Field Access • You can access a field F of an object that is the value of an attribute A by A.F . • However, you must use an alias, say tt, for the table T with attribute A, as tt.A.F . Database Systems: Salman Azhar

24. Example: Field Access in Oracle • Wrong (can’t have nothing): SELECT favCar.name FROM Buyers; • Wrong (can’t have the table name): SELECT Buyers.favCar.name FROM Buyers; • Right (must have alias name): SELECT b.favCar.name FROM Buyers b; Database Systems: Salman Azhar

25. Following References (REFs) • AB denotes the value of the B component of the object pointed to by A • AB makes sense if: • A is of type REF T • B is an attribute (component) of objects of type T Database Systems: Salman Azhar

26. Following REF’s: Oracle Style • REF-following is implicit in the dot • Just follow a REF by a dot and a field of the object referred to • Example: SELECT ss.car.name FROM Sells ss WHERE ss.dealer.name = ‘AutoNation’; Field name inobject car intable aliases as ss Database Systems: Salman Azhar

27. Oracle’s DEREF Operator -- Motivation • If we want the set of car objects for the cars sold by AutoNAtion, we might try: SELECT ss.car FROM Sells ss WHERE ss.dealer.name = ‘AutoNation’; • Legal, but ss.car is a REF, hence gibberish! • Need to add DEREF object car (ref) intable aliases as ss Database Systems: Salman Azhar

28. Using DEREF • To see the CarType objects, use: SELECT DEREF(ss.car) FROM Sells ss WHERE ss.dealer.name = ‘AutoNation’; • Produces values like: CarType(‘MiniCooper’, ‘BMW’) Database Systems: Salman Azhar

29. Methods • Classes are more than structures; they may have methods. • Declare in CREATE TYPE • Define methods in a CREATE TYPE BODY statement • Use T-SQL syntax for methods. • (T-SQL: MS SQL Server = PL/SQL: Oracle) • Variable SELF refers to the object to which the method is applied (this) Database Systems: Salman Azhar

30. Method Definition • Form of create-body statement: CREATE TYPE BODY <type name> AS PROCEDURE methodName(arg <ARGTYPE>) RETURN <RETURNTYPE> BEGIN … END; END; Database Systems: Salman Azhar

31. Method Definition – Oracle Style Method definitions are called PL/SQL procedure definitions in Oracle Oracle uses “MEMBER FUNCTION” in place of “PROCEDURE” Database Systems: Salman Azhar

32. Example: Method Definition (parenthesis only if an argument is passed) CREATE TYPE BODY SellsType AS MEMBER FUNCTION priceConvert(rate FLOAT) RETURN FLOAT IS BEGIN RETURN rate * SELF.price; END; END; Argument & argument type Sorta like “this” keyword in C++/Java Database Systems: Salman Azhar

33. Method Use • objectName.methodName(arguments if any) • Example: SELECT ss.car.name, ss.priceConvert(1.33) FROM Sells ss WHERE ss.dealer.name = ‘AutoNation’; Database Systems: Salman Azhar

34. Example: Nested Table Type CREATE TYPE CarType AS OBJECT ( name CHAR(20), edition CHAR(20) ); GO CREATE TYPE CarTableType AS TABLE OF CarType; GO Database Systems: Salman Azhar

35. Example --- Continued • Use CarTableType in a Manfs relation that stores the set of cars by each manufacturer in one tuple for that manufacturer. CREATE TABLE Manfs ( name CHAR(30), addr CHAR(50), car carTableType ); Database Systems: Salman Azhar

36. Nested Tables • Allows values of row components to be whole relations • If T is a UDT, • we can create a type S whose values are relations with rowtype T, by: CREATE TYPE S AS TABLE OF T ; Database Systems: Salman Azhar

37. Querying a Nested Table • We can print the value of a nested table like any other value • But these values have two type constructors: • For the table • For the type of tuples in the table Database Systems: Salman Azhar

38. Example: Query a Nested Table • Find the cars by Toyota: SELECT car FROM Manfs WHERE name = ‘Toyota’; • Produces one value: CarTableType( CarType(‘Camry’, ‘LE’) CarType(‘Corolla’, ‘L’) …. ) Database Systems: Salman Azhar

39. Querying Within a Nested Table • A nested table can be converted to an ordinary relation by applying THE(…) • This relation can be used in FROM clauses like any other relation Database Systems: Salman Azhar

40. Example: Use of THE • Find the cars made by Toyota: SELECT dd.edition FROM THE( SELECT car FROM Manfs WHERE name = ‘Toyota’; ) dd WHERE dd.name = ‘Camry’; Database Systems: Salman Azhar