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. Outline for today 11/24/03 • Objectives • Introduction • Need for OO/OR-DBMS • OO Fundamentals • How (OO)DBMS incorporates OO ideas • Database Modeling/Querying • Conclusions

3. Outline for next class 11/26/03 • Objectives • ORDBMS Fundamentals • How ORDBMS incorporates OO ideas • Mapping Conceptual Model into Logical Model • OQL vs. SQL-99 • Comparison of OODBMS and ORDBMS • Conclusions

4. 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, …)

5. 1 2 6 7 5 3 4 8 0,0 Introduction • Why OODBMS? • Let us start with modeling this simple example in a RDBMS • Assume that we are given the following diagram

6. 1 2 6 7 5 3 4 8 0,0 Introduction – Motivating Example • Our objective is to store this graph in a RDBMS and support queries on these simple geometries • What kind of relations we need? • Print names of rectangles which are squares • Find all objects which are inside the rectangle 7.

7. 1 2 6 7 5 3 4 8 0,0 Introduction – Motivating Example • Relations • POINT(pname, x, y), EDGE(ename, aPoint), RECTANGLE(rname, anEdge). POINT RECTANGLE EDGE

8. rname Edge_length (eName, eLength) Rectangle r Edge_Pnt_List (eName, stPnt, endPnt) Point p1 E1.ename = e2.ename E1.aPoint <> e2.aPoint Point p2 Edge e1 Edge e2 Introduction – Motivating Example • Logical Flow

9. Introduction – Motivating Example CREATE VIEW pnt_list (ename, stPnt, endPnt) AS SELECT e.ename, e1.aPoint, e2.aPoint FROM edge e1, edge e2 WHERE e1.ename = e2.ename AND e1.aPoint <> e2.aPoint; CREATE VIEW edge_length (ename, elength) AS SELECT e.ename, sqrt(sq(p1.x –p2.x) + sq(p1.y – p2.y) FROM pnt_list e, point p1, point p2 WHERE e.stPnt = p1.pname AND e.endPnt = p2.pname;

10. Introduction – Motivating Example Discussion Question – Print the square names

11. Introduction – Motivating Example Solution SELECT r.rname FROM rectangle r, edge_length e WHERE r.anEdge = e.ename GROUP BY r.rname HAVING max(e.elength) ~ MIN (e.elength);

12. Introduction • Though we can model and query these simple geometric objects, its • Not clean • Complexity increases with complex objects (like irregular polygons) • Even with these simple objects, how do you answer queries like ‘find nearest objects to point 2’ or ‘find all intersecting objects’ • What is lacking • Support for complex data types • Support for predicates (e.g., area, intersect, length)

13. OODBMS-Fundamentals • Has its origin in OO programming languages • Object • State – current value • Behavior - what operations are permitted • Difference between PL Object and DB Object • PL – Objects exist only during program execution (transient objects). • DB – Objects persist beyond program termination; stored permanently on a secondary storage • DB – allows sharing of objects among multiple programs and applications

14. OODBMS-Fundamentals • OID – unique system generated object identifier for each object • Compare this with RDBMS primary key • Object structure • Arbitrarily complex in order to contain all necessary information about the object • Compare this with RDBMS • (rectangle object) – information about complex object is often scattered • The state of complex object may be constructed from other objects using type constructors

15. OODBMS-Fundamentals • Type constructors • Basic – atom, tuple, and set • Others – list, bag, and array (collection/bulk types) • tuple – also called as a structured type • Formally an object can be thought of as a triple (I,C,V) • I – OID • C – type constructor • V – object state (current value)

16. OODBMS-Fundamentals • Examples • o1 = (i1, atom, ‘Pen-Chung Yew’) • o2 = (i2, atom, ‘Minneapolis’) • o3 = (i3, atom, ‘Computer Science’) • o4 = (i4, set, {i1,i2,i3}) • o5 = (i4, tuple, <DNAME:i3, DCHAIR:i1>)

17. OODBMS-Fundamentals • Type hierarchies and Inheritance • OODB permits definition of new types based on other predefined types, leading to a type hierarchy • Example • TYPE_NAME: function, function, …, function • PERSON: firstName, lastName, dob, SSN • STUDENT: firstName, lastName, dob, SSN, status, GPA • FACULTY: firstName, lastName, dob, SSN, rank, salary • STUDENT subtype_of PERSON: major, GPA • FACULTY subtype_of PERSON: rank, salary

18. 1 2 6 7 5 3 4 8 0,0 OODBMS-Fundamentals • Exercise: Consider the geometry objects defined in our first example and defined class hierarchies

19. OODBMS-Fundamentals • example - • GEOMETRY_OBJECT: Shape, Area, ReferencePoint • RECTANGLE subtype-of GEOMETRY_OBJECT (Shape=‘rectangle’): edge1, edge2, edge3, edge4

20. OODBMS-Fundamentals • Standards: • ODMG • Made up of several parts • Object Model • Object Definition Language (ODL) • Object Query Language (OQL) • Binding to OO programming languages (C++, Smalltalk, Java) • OOBMS • O2 • ObjectStore • Jasmine

21. Database Modeling • Enhance Entity Relationship (EER – Chap 4, Elmasri/Navathe) • Pictogram Enhanced Entity Relationship (PEER – Chapter 2, SD A Tour). • Unified Modeling Language (UML) • Object Definition Language

22. Database Modeling • Example • We use two examples (school-DB) and park-DB (from SDB-A Tour). • Identify typical objects (and hierarchies) in school-DB • Person, Student, Faculty, Department • Identify relationships • 1:1, 1:M, M:N, partial participation, … • Let us start with EER • Includes all the modeling concepts of ER • Additionally includes oo concepts of – subclass, superclass, specialization and generalization, attribute and relationship inheritance

23. firstName SSN Person lastName dob d rank status Student Faculty N N major worksin 1 1 name Department code Database Modeling - EER

24. Database Modeling - EER • Find a suitable entity and define a overlapping type of relationship

25. {disjoint, mandatory} Student status Faculty rank 0..1 Department Code Name chair chairOf majorsIn 1 * worksIn * dept major 1 Database Modeling - UML Person SSN dob firstName lastName

26. Exactly one Many (0 or More) Optional (0 or One) One or More Numerically Specified Aggregation Inheritance Derived Class 1+ Database Modeling - PEER OGC-Geometry

27. Name Name Name Image Image-id Geometry Name Name Length Geometry Stand-id Geometry Elevation Volume Geometry Geometry Specie Geometry NoOfLanes Crosses supplies within Belongs_to Part_of access captures manages River Road M M N N M 1 M 1 Facility Forest Forest_Stand 1 1 1 N M M Fire_Station Fire_Image Database Modeling - PEER • State Park (ER Model)

28. Database Modeling - PEER • Pictogram : miniature version of geographic object inserted inside of a box • Entity pictograms • Relationship pictograms • Advantages • Ease of use • Complete grammar • Translation rules • Pictogram inserted ER diagram to SQL3 level constructs

29. Database Modeling - PEER • Pictograms <Pictogram> <Shape> * // any possible shape ! // user-defined shapes <Shape> <Basic Shape> <Multi-Shape> <Derived Shape> <Alternate Shape>

30. n 0, n Polygon Point Line Pictograms for Basic Shapes Pictograms for Multi-Shapes Database Modeling - PEER <Basic Shape> <Cardinality> 0,1 1 1,n 0,n n

31. Pictograms for Derived Shapes Pictograms for Alternate Shapes Database Modeling - PEER <Derived Shape> <Basic Shape> <Basic Shape> <Basic Shape> <Derived Shape> <Basic Shape>

32. * ! Raster TIN Thesian Pictograms for Raster Shapes Part_of (Partition) Part_of (Network) Pictograms for Relationships Database Modeling - PEER Any Possible Shape User Defined Shape Raster Shape Raster TIN Thesian

33. Elevation Name Name Name NoOfLanes Volume Length Stand-id Specie Name Image-id Name access supplies manages captures Part_of Crosses Belongs_to River Road M M N N M 1 Facility Forest Forest_Stand 1 1 1 N M M Fire_Station Fire_Image Database Modeling - PEER • State Park (PEER)

34. Summary and Conclusions • Learning Objectives • Understand OO Concepts • Conceptual Modeling • 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

35. Next Week • ORDMBS (SQL-3) in depth • Mapping of Conceptual Model onto ORDBMS (SQL-3 DDL constructs) • Examples using Postgresql/PostGIS • Comparison of OODBMS and ORDBMS • Conclusions

36. 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)