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

Outline for today 11/24/03 n n Objectives Introduction n n OO Fundamentals n n Need for OO/OR-DBMS How (OO)DBMS incorporates OO ideas Database Modeling/Querying Conceptual n Conclusions OODBMS ORDBMS ODL/UML EER/PEER

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

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

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

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

Introduction – Motivating Example n Relations n POINT(pname, x, y), EDGE(ename, a. Point), RECTANGLE(rname, an. Edge). POINT RECTANGLE EDGE r 5 e 1 p 1 r 5 e 2 e 1 p 2 r 5 e 3 e 2 p 2 r 5 e 4 e 2 p 3 e 3 p 4 e 4 p 1 3 4 p 2 3 10 p 3 10 10 p 4 10 4 1 6 2 5 4 0, 0 8 7 3

Introduction – Motivating Example n Logical Flow rname Edge_length (e. Name, e. Length) Rectangle r Edge_Pnt_List (e. Name, st. Pnt, end. Pnt) Point p 1 Point p 2 Edge e 1 E 1. ename = e 2. ename E 1. a. Point <> e 2. a. Point Edge e 2

Introduction – Motivating Example CREATE VIEW pnt_list (ename, st. Pnt, end. Pnt) AS SELECT e. ename, e 1. a. Point, e 2. a. Point FROM edge e 1, edge e 2 WHERE e 1. ename = e 2. ename AND e 1. a. Point <> e 2. a. Point; CREATE VIEW edge_length (ename, elength) AS SELECT e. ename, sqrt(sq(p 1. x –p 2. x) + sq(p 1. y – p 2. y) FROM pnt_list e, point p 1, point p 2 WHERE e. st. Pnt = p 1. pname AND e. end. Pnt = p 2. pname;

Introduction – Motivating Example Discussion Question – Print the square names

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

Introduction n Though we can model and query these simple geometric objects, its n n 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 n n Support for complex data types Support for predicates (e. g. , area, intersect, length)

OODBMS-Fundamentals n n Has its origin in OO programming languages Object n n n State – current value Behavior - what operations are permitted Difference between PL Object and DB Object n n n 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

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

OODBMS-Fundamentals n Type constructors n n 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) n n n I – OID C – type constructor V – object state (current value)

OODBMS-Fundamentals n Examples n n n o 1 o 2 o 3 o 4 o 5 = = = (i 1, (i 2, (i 3, (i 4, atom, ‘Pen-Chung Yew’) atom, ‘Minneapolis’) atom, ‘Computer Science’) set, {i 1, i 2, i 3}) tuple, <DNAME: i 3, DCHAIR: i 1>)

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

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

OODBMS-Fundamentals n example n n GEOMETRY_OBJECT: Shape, Area, Reference. Point RECTANGLE subtype-of GEOMETRY_OBJECT (Shape=‘rectangle’): edge 1, edge 2, edge 3, edge 4

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

Database Modeling n n 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

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

Database Modeling - EER SSN Person last. Name dob status first. Name d Student Faculty N major N worksin 1 code 1 Department name rank

Database Modeling - EER n Find a suitable entity and define a overlapping type of relationship

Database Modeling - UML Person SSN dob first. Name last. Name Faculty 0. . 1 Department chair rank chair. Of Code majors. In 1 works. In * dept Name major 1 Student status * {disjoint, mandatory}

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

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

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

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

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

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

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

Database Modeling - PEER n State Park (PEER) Volume Name supplies Length Name Crosses River M N Road M access Name Elevation Facility Belongs_to 1 Name M manages Fire_Station Name 1 Forest 1 No. Of. Lanes N 1 Part_of M N captures M Fire_Image-id Forest_Stand-id Specie

Summary and Conclusions n Learning Objectives n n n RDBMS limitations n n No support for complex data types and predicates OO n n n Understand OO Concepts Conceptual Modeling Rich set of features – encapsulation, inheritance, . . Helps manage complexity Conceptual Modeling – Simple Extensions n EER, UML, PEER

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

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)