Object Oriented & Object Relational Databases

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

2. Last Class (11/24/03) • RDBMS limitations • No support for complex data types and predicates • OO • Rich set of features – encapsulation, inheritance, .. • Helps manage complexity • Conceptual Modeling – Simple Extensions • EER, UML, PEER

3. Learning Objectives • 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, …)

4. Mapping Conceptual Model onto Logical Model

5. Mapping Conceptual Model onto Logical Model • OO – Object Definition Language • OR – SQL3 DDL • ODL is an extension of Interface Description Language. • ODL class definition includes • Attributes • Relationships • Methods

6. Mapping - ODL • Example • interface Student { • attribute string status; • attribute Department major; • relationship set<Department> majorOf inverse Department::students; • …. • }

7. ORDBMS Fundamentals • Try to unify aspects of both relational and object databases • Relation is still central • No standard of what constitutes an ORDBMS • Won Kim’s (UniSQL) white paper • Michael Stonebraker – ORDBMS, The Next Great Wave • Query language – SQL3

8. ORDBMS Fundamentals (SQL3) • OO Features in SQL3 • Objects • Type constructors • Collection types • User-defined functions and procedures • Support for large objects • Inheritance

9. ORDBMS Fundamentals (SQL3) • Objects in SQL3 comes in two flavors • 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

10. ORDBMS Fundamentals (SQL3) • Here is a general form of ADT specification • 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 ); • Example • CREATE TYPE DepartmentADT ( Code int, Name char(10), EQUALS deptEQ, //deptEQ – we will define it later LESS THAN NONE //DEFAULT Defintion of other functions goes here … );

11. ORDBMS Fundamentals (SQL3) • Defining ADT’s methods • 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 • := assignment • local variables can be declared inside function (:a DepartmentADT) • dot op is used to access components of structure • BEGIN and END are used to collect several stmts.

12. ORDBMS Fundamentals (SQL3) • Here is an example constructor method • FUNCTION DepartmentADT(:id INT, :dname CHAR(10)) RETURNS DepartmentADT; :d DepartmentADT; BEGIN :d := DepartmentADT(); :d.code := :id; :d.name := :dname; RETURN :d; END; • Discussion question – define method deptEQ

13. ORDBMS Fundamentals (SQL3) • Function deptEQ(:d1 DepartmentADT, :d2 DepartmentADT) RETURNS BOOLEAN; RETURN (:d1.code = :d2.code AND :d1.name = :d1.name); • We could have used DEFAULT (system defined)

14. ORDBMS Fundamentals (SQL3) • External functions • ADT’s can have methods that are written in host language (e.g. C, C++, …) • Only signature appears in ADT definition DECLARE EXTERNAL <functionName> <signature> LANGUAGE <language name> • Example DECLARE EXTERNAL Square POLYGON RETURNS BOOLEAN LANGUAGE C;

15. ORDBMS Fundamentals (SQL3) • Row type objects • Essentially tuples and they roughly resembles struct/class • CREATE ROW TYPE <typename> (<listOfAttributes-and-their-types>) • CREATE ROW TYPE DepartmentType( code INT, name CHAR(10) );

16. ORDBMS Fundamentals (SQL3) • Creating Relations of Row Type • OF TYPE <row-type-name> • Example • CREATE TABLE Department OF TYPE DepartmentType; • References – A component attribute of tuple may be a reference to a tuple of another (or posibly same) relation. • CREATE ROW TYPE Department ( code INT, name CHAR(10), chair REF (Faculty_Row_Type) );

17. ORDBMS Fundamentals (SQL3) • CREATE ROW TYPE Department_Row_Type ( code INT, name CHAR(10), chair REF (Faculty_Row_Type) ); • CREATE TABLE Department OF TYPE Department_Row_Type; • Q? Print chair name of EECS department • SELECT d.chair->name • FROM Department d • WHERE d..name = ‘EECS’;

18. ORDBMS Fundamentals (SQL3) • Collection types • setof • Example • CREATE TABLE Rectangles (rname CHAR(10), pnts setof(Points)). • SELECT r.rname • FROM Rectangles r • WHERE count(r.pnts) = 5;

19. ORDBMS Fundamentals (SQL3) • Support for large objects • blob, clob • Consider class recording • Class_video_recordings(cid: integer, cmdate: date, loc char(10), video: BLOB)

20. ORDBMS Fundamentals (SQL3) • Inheritance • Used in two ways: • reusing and refining types, and • for creating hierarchies of collections of similar but not identical objects • UNDER • CREATE TYPE Student UNDER Person (addr address) • Creates an explicit relationship between subtype Student and supertype Person. • An object of subtype is considered to be an object of supertype.

21. ORDBMS Fundamentals (SQL3) • OO Features in SQL3 • Objects • Type constructors • Collection types • User-defined functions and procedures • Support for large objects • Inheritance

22. Outline for today’s class 11/26/03 • Objectives • Mapping Conceptual Model into Logical Model • ORDBMS Fundamentals • How ORDBMS incorporates OO ideas • SQL3 • Physical Design and efficiency Issues • Demo • Conclusions

23. Physical design and efficiency issues • Need • 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 • Domain specific (large collection of indices) • Extendible indices

24. Physical design and efficiency issues • Query processing • 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). • One solution – interpreted rather than compiled (Java and procedural portions of SQL:1999). • Run in different address space than the DBMS • Method Caching • Cache of input and output of methods • Pointer Swizzling • Technique to reduce cost of cached object

25. Comparison of OO and ORDBMS • Fundamental difference is in philosophy • 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

26. Outline for today’s class 11/26/03 • Objectives • Mapping Conceptual Model into Logical Model • ORDBMS Fundamentals • How ORDBMS incorporates OO ideas • SQL3 • Physical design and efficiency issues • Demo • Summary and Conclusions

27. Demo • Postgresql/PostGIS

28. Summary and Conclusions • 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/SQL3 offer much promise

29. Additional Readings • 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): • Fundamentals of Database Systems – Elmasri & Navathe • A First Course In Database Systems – Ullman & Widom. • http://www-users.cs.umn.edu/~shekhar/5705/ • Spatial Database – A Tour (Chapter 2 and 3)

30. Acknowledgements PFF Faculty Prof. Shekhar Class Happy Thanks Giving and good luck on final exams.