Object Oriented Object Relational Databases Ranga Raju Vatsavai
Object Oriented & Object Relational Databases Ranga Raju Vatsavai Teaching Mentor (Prof. Shekhar) CSci 5708 : Architecture and Implementation of Database Management Systems, Fall 2003 Week 15 (11/24, 11/26/03) Lecture Notes 1
Last Class (11/24/03) n RDBMS limitations n n OO n n n No support for complex data types and predicates Rich set of features – encapsulation, inheritance, . . Helps manage complexity Conceptual Modeling – Simple Extensions n EER, UML, PEER 2
Learning Objectives n n n Basic concepts of OO and OR models Extensions at conceptual modeling Mapping Conceptual Model into Logical Model Exposure to additional features in SQL: 1999 standard. Ability to model and implement a broader class of applications (spatial, multimedia, engineering, biological, scientific, …) 3
Mapping Conceptual Model onto Logical Model EER Model OO/ODL Entity or Subclass Class (all attributes, multivalued attributes – set, bag or list, composite - tuple Binary relationships Add relationship properties or reference attributes Include appropriate operations for each class N-ary relationshps Separate classes with appropriate references to each participating class Subclass Class inherits the type and methods of its super class, and all attributes, relationship references, and operations 4
Mapping Conceptual Model onto Logical Model n n OO – Object Definition Language OR – SQL 3 DDL ODL is an extension of Interface Description Language. ODL class definition includes n n n Attributes Relationships Methods 5
Mapping - ODL n Example n n n interface Student { attribute string status; attribute Department major; relationship set<Department> major. Of inverse Department: : students; …. } 6
ORDBMS Fundamentals n n n Try to unify aspects of both relational and object databases Relation is still central No standard of what constitutes an ORDBMS n n n Won Kim’s (Uni. SQL) white paper Michael Stonebraker – ORDBMS, The Next Great Wave Query language – SQL 3 7
ORDBMS Fundamentals (SQL 3) n OO Features in SQL 3 n n n Objects Type constructors Collection types User-defined functions and procedures Support for large objects Inheritance 8
ORDBMS Fundamentals (SQL 3) n Objects in SQL 3 comes in two flavors n n n ADTs and Row objects ADTs – user defined arbitrary data types is a key feature of ORDBMS ADTs are a combination of atomic data types and associated methods DBMS doesn’t need to know about how ADT’s are stored or their methods work? It just has to know about ADT’s signature Hiding ADT internals is called encapsulation 9
ORDBMS Fundamentals (SQL 3) n Here is a general form of ADT specification n CREATE TYPE <type-name> ( list of component attributes with individual types Optional declaration of = and < functions for the type declaration of other functions (methods) for the type ); n Example n CREATE TYPE Department. ADT ( Code int, Name char(10), EQUALS dept. EQ, //dept. EQ – we will define it later LESS THAN NONE//DEFAULT Defintion of other functions goes here … ); 10
ORDBMS Fundamentals (SQL 3) n Defining ADT’s methods n n FUNCTION <name> ( <arguments> ) RETURNS <type>; Functions are of two types – internal and external External functions are written in host language and only signature appears in ADT definition. Internal functions are written in extended SQL n n : = assignment local variables can be declared inside function (: a Department. ADT) dot op is used to access components of structure BEGIN and END are used to collect several stmts. 11
ORDBMS Fundamentals (SQL 3) n Here is an example constructor method n FUNCTION Department. ADT(: id INT, : dname CHAR(10)) RETURNS Department. ADT; : d Department. ADT; BEGIN : d : = Department. ADT(); : d. code : = : id; : d. name : = : dname; RETURN : d; END; n Discussion question – define method dept. EQ 12
ORDBMS Fundamentals (SQL 3) n Function dept. EQ(: d 1 Department. ADT, : d 2 Department. ADT) RETURNS BOOLEAN; RETURN (: d 1. code = : d 2. code AND : d 1. name = : d 1. name); n We could have used DEFAULT (system defined) 13
ORDBMS Fundamentals (SQL 3) n External functions n n ADT’s can have methods that are written in host language (e. g. C, C++, …) Only signature appears in ADT definition DECLARE EXTERNAL <function. Name> <signature> LANGUAGE <language name> n Example DECLARE EXTERNAL Square POLYGON RETURNS BOOLEAN LANGUAGE C; 14
ORDBMS Fundamentals (SQL 3) n Row type objects n n n Essentially tuples and they roughly resembles struct/class CREATE ROW TYPE <typename> (<list. Of. Attributes-andtheir-types>) CREATE ROW TYPE Department. Type( code INT, name CHAR(10) ); 15
ORDBMS Fundamentals (SQL 3) n Creating Relations of Row Type n n Example n n OF TYPE <row-type-name> CREATE TABLE Department OF TYPE Department. Type; References – A component attribute of tuple may be a reference to a tuple of another (or posibly same) relation. n CREATE ROW TYPE Department ( code INT, name CHAR(10), chair REF (Faculty_Row_Type) ); 16
ORDBMS Fundamentals (SQL 3) n CREATE ROW TYPE Department_Row_Type ( code INT, name CHAR(10), chair REF (Faculty_Row_Type) ); n n CREATE TABLE Department OF TYPE Department_Row_Type; Q? Print chair name of EECS department n n n SELECT d. chair->name FROM Department d WHERE d. . name = ‘EECS’; 17
ORDBMS Fundamentals (SQL 3) n Collection types n n setof Example n n CREATE TABLE Rectangles (rname CHAR(10), pnts setof(Points)). rname points r 1 {{1, 1}, {1, 2}, {2, 1}, {1, 1}} SELECT r. rname FROM Rectangles r WHERE count(r. pnts) = 5; 18
ORDBMS Fundamentals (SQL 3) n n Support for large objects n blob, clob Consider class recording n Class_video_recordings(cid: integer, cmdate: date, loc char(10), video: BLOB) 19
ORDBMS Fundamentals (SQL 3) n Inheritance n Used in two ways: n n reusing and refining types, and for creating hierarchies of collections of similar but not identical objects UNDER CREATE TYPE Student UNDER Person (address) n n Creates an explicit relationship between subtype Student and supertype Person. An object of subtype is considered to be an object of supertype. 20
ORDBMS Fundamentals (SQL 3) n OO Features in SQL 3 n n n Objects Type constructors Collection types User-defined functions and procedures Support for large objects Inheritance 21
Outline for today’s class 11/26/03 n n n Objectives Mapping Conceptual Model into Logical Model ORDBMS Fundamentals n n n How ORDBMS incorporates OO ideas SQL 3 Physical Design and efficiency Issues Demo Conclusions 22
Physical design and efficiency issues n Need n n n efficiently storage of ADT’s and structured objects efficient indexed access Main problem is size, so disk layout is important BLOBS require special storage, typically different location on disk (separate from the tuple) Indexing n n Domain specific (large collection of indices) Extendible indices 23
Physical design and efficiency issues n Query processing n n n User defined aggregate functions (SQL defined may not be useful) ORDBMS allows registering new aggregate functions Security – external methods of ADT’s – can compromise the database or crash (if its buggy). n n n Method Caching n n One solution – interpreted rather than compiled (Java and procedural portions of SQL: 1999). Run in different address space than the DBMS Cache of input and output of methods Pointer Swizzling n Technique to reduce cost of cached object 24
Comparison of OO and ORDBMS n Fundamental difference is in philosophy n n n OODMSs try to add DBMS functionality to Prog. Lang. ORDBMS try to add richer data types and predicates to an RDBMS OODBMS – aimed at applications where object-centric viewpoint is appropriate ORDBMS – aimed at applications where large data collections are the focus Query (OQL) is not efficiently supported in OODBMS Query processing is the centerpiece of an ORDBMS 25
Outline for today’s class 11/26/03 n n n Objectives Mapping Conceptual Model into Logical Model ORDBMS Fundamentals n n n How ORDBMS incorporates OO ideas SQL 3 Physical design and efficiency issues Demo Summary and Conclusions 26
Demo n Postgresql/Post. GIS 27
Summary and Conclusions n n n Basic concepts of OO and OR models Extensions at conceptual modeling Mapping Conceptual Model into Logical Model Exposure to additional features in SQL: 1999 standard. Ability to model and implement a broader class of applications (spatial, multimedia, engineering, biological, scientific, …) ORDBMS/SQL 3 offer much promise 28
Additional Readings n n n http: //www. cs. umn. edu/~vatsavai/oo-ordbms. ppt Main – Database Management Systems by Raghu Ramakrishnan (Chapter 24: Object Database Systems). Additional References (relevant chapters): n n Fundamentals of Database Systems – Elmasri & Navathe A First Course In Database Systems – Ullman & Widom. n n http: //www-users. cs. umn. edu/~shekhar/5705/ Spatial Database – A Tour (Chapter 2 and 3) 29
Acknowledgements PFF Faculty Prof. Shekhar Class Happy Thanks Giving and good luck on final exams. 30
- Slides: 30