1 / 40

Object-Relational Databases Salman Azhar

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. Merging Relational and Object Models.

aquene
Download Presentation

Object-Relational Databases Salman Azhar

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related