ObjectRelational Databases Salman Azhar UserDefined Types Object IDs

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

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

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

SQL-99 and DBMS Features u. SQL-99 includes many of the objectrelational features to be described. u. However, being so new, different DBMSs use different approaches. w We’ll sometimes use features and syntax from Oracle and SQL Server. 2/1/04 Database Systems: Salman Azhar 4

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

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

Example: UDT Definition CREATE TYPE Dealer. Type AS ( name CHAR(20), addr CHAR(20) ); CREATE TYPE Car. Type AS ( name CHAR(20), manf CHAR(20) ); 2/1/04 Database Systems: Salman Azhar 7

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

Example: REF CREATE TYPE Sells. Type AS ( dealer REF Dealer. Type, car REF Car. Type, price FLOAT ); u Sells. Type objects look like: REF Dealer. Type REF Car. Type FLOAT 30000 Reference to a Dealer. Type object 2/1/04 Reference to a Car. Type object Database Systems: Salman Azhar 9

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

Example: Creating Tables CREATE TABLE Dealer OF Dealer. Type; CREATE TABLE Car OF Car. Type; CREATE TABLE Sells OF Sells. Type; 2/1/04 Database Systems: Salman Azhar 11

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

Type Constructors u. Each UDT has a type constructor of the same name that wraps objects of that type. 2/1/04 Database Systems: Salman Azhar 13

Example: Type Constructor u. The query SELECT * FROM Dealer; u. Produces “tuples” such as: Dealer. Type(‘Auto. Nation’, ‘Maple St. ’) 2/1/04 Database Systems: Salman Azhar 14

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

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

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

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

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

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

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

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

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

Example: Field Access in Oracle u. Wrong (can’t have nothing): SELECT fav. Car. name FROM Buyers; u. Wrong (can’t have the table name): SELECT Buyers. fav. Car. name FROM Buyers; u. Right (must have alias name): SELECT b. fav. Car. name FROM Buyers b; 2/1/04 Database Systems: Salman Azhar 24

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

Following REF’s: Oracle Style u. REF-following is implicit in the dot u. Just follow a REF by a dot and a field of the object referred to Field name in object car in u. Example: table aliases as ss SELECT ss. car. name FROM Sells ss WHERE ss. dealer. name = ‘Auto. Nation’; 2/1/04 Database Systems: Salman Azhar 26

Oracle’s DEREF Operator -Motivation u. If we want the set of car objects for the cars sold by Auto. NAtion, we might try: object car (ref) in SELECT ss. car table aliases as ss FROM Sells ss WHERE ss. dealer. name = ‘Auto. Nation’; u. Legal, but ss. car is a REF, hence gibberish! w Need to add DEREF 2/1/04 Database Systems: Salman Azhar 27

Using DEREF u. To see the Car. Type objects, use: SELECT DEREF(ss. car) FROM Sells ss WHERE ss. dealer. name = ‘Auto. Nation’; u. Produces values like: Car. Type(‘Mini. Cooper’, 2/1/04 Database Systems: Salman Azhar ‘BMW’) 28

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

Method Definition u. Form of create-body statement: CREATE TYPE BODY <type name> AS PROCEDURE method. Name(arg <ARGTYPE>) RETURN <RETURNTYPE> BEGIN … END; 2/1/04 Database Systems: Salman Azhar 30

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

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

Method Use u object. Name. method. Name(arguments if any) u. Example: SELECT ss. car. name, ss. price. Convert(1. 33) FROM Sells ss WHERE ss. dealer. name = ‘Auto. Nation’; 2/1/04 Database Systems: Salman Azhar 33

Example: Nested Table Type CREATE TYPE Car. Type AS OBJECT ( name CHAR(20), edition CHAR(20) ); GO CREATE TYPE Car. Table. Type AS TABLE OF Car. Type; GO 2/1/04 Database Systems: Salman Azhar 34

Example --- Continued u. Use Car. Table. Type 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. Table. Type ); 2/1/04 Database Systems: Salman Azhar 35

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

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

Example: Query a Nested Table u. Find the cars by Toyota: SELECT car FROM Manfs WHERE name = ‘Toyota’; u. Produces one value: Car. Table. Type( Car. Type(‘Camry’, ‘LE’) Car. Type(‘Corolla’, ‘L’) …. ) 2/1/04 Database Systems: Salman Azhar 38

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

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