Theory Practice Methodology of Relational Database Design and

Theory, Practice & Methodology of Relational Database Design and Programming Copyright © Ellis Cohen 2002 -2006 Introduction to Objects & Databases These slides are licensed under a Creative Commons Attribution-Non. Commercial-Share. Alike 2. 5 License. For more information on how you may use them, please see http: //www. openlineconsult. com/db © Ellis Cohen 2001 -2006

Topics Object Mapping and ODL Object Query Language Object Relational Mapping Object-Oriented Databases (OODB's) Summary © Ellis Cohen 2001 -2006 2

Object Mapping and ODL © Ellis Cohen 2001 -2006 3

Drivers for OO & DB Integration • Persistence Object Storage – Need for a way to easily save and restore computation state of programs built via OOPL's + other RDB benefits • OO Client-Side Programming for RDB's – Treat rows of a table (plus asssociated data in other tables) like an object for client program access & modification • OO Server-Side Programming for RDB's – Brings benefits of OO approach to RDB's © Ellis Cohen 2001 -2006 4

Object & Relational Features Object Model Encapsulation Object Types Attributes Methods Relational Model Persistence Storage Optimization Indexing Inheritance Queries (SQL) Object Identity Constraints & Triggers Transactions Backup & Recovery Polymorphism References Navigation Collections Versioning Query Optimization © Ellis Cohen 2001 -2006 5

Rows as Objects/Entities Emps empno ename sal comm 7499 ALLEN 1600 300 7654 MARTIN 1250 1400 7698 BLAKE 2850 7839 KING 5000 7844 TURNER 1500 7986 STERN 1500 an Employee Object empno: 7654 ename: MARTIN sal: 1250 comm: 1400 0 It can be useful to think of each row as an object or entity (i. e. an instance of an entity class) and the table as a collection of these objects The columns of the table correspond to the instance variables for each object © Ellis Cohen 2001 -2006 6

Object Mapping [manager] Employee [worker] manages works for empno ename job address Dept deptno dname Relational Mapping an ER model to a Relational Model Object Mapping an ER model to an Object Model © Ellis Cohen 2001 -2006 7

Object Model Object Classes & Instances – Correspond to entity classes and instances Collections Set (no duplicates) Bag (duplicates) Lists (ordered, duplicates) Array (list w efficient indexing) References (Pointers) © Ellis Cohen 2001 -2006 8

Object Definition Language (ODL) class Employee { attribute int empno; attribute string ename; attribute string job; attribute Struct[street, city, state, zip] address; relationship Dept dept inverse Dept: : empls; } class Dept { attribute int deptno; attribute string dname; relationship Set<Employee> empls inverse Employee: : dept; © Ellis Cohen 2001 -2006 9

ODL Relationships emp 1 dept 1 emp 2 dept 2 emp 3 dept emp 4 dept 3 emp 5 empls © Ellis Cohen 2001 -2006 10

ODL Exercise Represent the manages relationship in ODL © Ellis Cohen 2001 -2006 11

Representing Manages class Employee { attribute int empno; attribute string ename; attribute string job; attribute Struct[street, city, state, zip] address; relationship Dept dept inverse Dept: : empls; relationship Employee mgr inverse managees; relationship Set<Employee> managees inverse mgr; } class Dept { attribute int deptno; attribute string dname; relationship Set<Employee> empls inverse Employee: : dept; © Ellis Cohen 2001 -2006 12

Referencing / Navigation Given an employee e e. dept - department of e e. deptno – the # of e's dept e. dept. dname – the name of e's dept Given a department d d. empls – the set of employees who work in dept d © Ellis Cohen 2001 -2006 13

OQL (Object Query Language) © Ellis Cohen 2001 -2006 14

OQL (Object Query Language) Given a department d d. empls the collection of employees who work in dept d OQL SELECT iterates through the objects in a collection SELECT e. empno FROM e IN d. empls the employee numbers of the employees who work in department d SELECT e. empno, e. ename FROM e IN d. empls the employee numbers & names of the employees who work in department d © Ellis Cohen 2001 -2006 15

OQL SELECT Returns Collections SELECT e. empno FROM e IN d. emps the employee numbers of the employees who work in department d Bag<int> SELECT e. empno, e. ename FROM e IN d. emps the employee numbers & names of the employees who work in department d Bag<Struct[int, string]> SELECT e FROM e IN d. emps WHERE e. job = 'CLERK' the clerks who work in department d Set<Employee> © Ellis Cohen 2001 -2006 16

Extents and Keys How do iterate through all the employees? We can associate an extent with a class, which corresponds to the set of instances in that class. We can associate a key with an extent © Ellis Cohen 2001 -2006 17

ODL with Extents class Employee (extent emps, key empno) { attribute int empno; attribute string ename; attribute string job; attribute Struct[street, city, state, zip] address; relationship Dept dept inverse Dept: : empls; relationship Employee mgr inverse managees; relationship Set<Manager> managees inverse mgr; } class Dept (exptent depts, key deptno) { attribute int deptno; attribute string dname; relationship Set<Employee> empls inverse Employee: : dept; © Ellis Cohen 2001 -2006 18

Collections and Relationships emp 1 dept 1 emp 2 dept 2 emp 3 depts emp 4 dept 3 emps emp 5 empls © Ellis Cohen 2001 -2006 19

Queries involving Extents SELECT e. empno FROM e IN emps WHERE e. ename = 'SMITH' SELECT e. empno, e. ename FROM e IN emps WHERE e. job = 'CLERK' © Ellis Cohen 2001 -2006 20

Joins & Navigation SELECT e. ename, d. dname FROM e IN emps, d IN depts WHERE e. dept = d – An ordinary expensive join SELECT e. ename, e. dept. dname FROM e IN emps WHERE e. dept IS NOT NULL – Lists every employee & their dept – Replaces joins by navigation SELECT e. ename, d. dname FROM d IN depts, e IN d. empls – Lists the employees in each department – Uses correlated navigation © Ellis Cohen 2001 -2006 21

Object-Relational Mapping © Ellis Cohen 2001 -2006 22

Object Relational Mapping The idea of OR Mapping is to treat a RDB in terms of its object model. In particular – the DB is modelled in ODL – queries are written in ODL – ODL queries are automatically mapped to SQL © Ellis Cohen 2001 -2006 23

Mapping OQL to SQL SELECT e. ename, e. dept. dname FROM emps e in OQL is mapped to the SQL: SELECT e. ename, (SELECT d. dname FROM depts d WHERE e. deptno = d. deptno) FROM emps e A good optimizer will treat this as equivalent to SELECT ename, dname FROM emps NATURAL JOIN depts © Ellis Cohen 2001 -2006 24

Returning Collections of Objects SELECT e FROM e IN emps WHERE e. job = 'ANALYST' This query returns a collection of objects (i. e. the employees who are analysts) to the client Using an ordinary OO programming language (e. g. Java/C++), it is possible to • update these objects directly, and then • arrange for the changes to be automatically reflected in the corresponding database objects on commit. © Ellis Cohen 2001 -2006 25

Embedded OQL Programming Imagine a PL with embedded OQL a (possible) server-side language for an OODB clerk. Emps Set<Employee> : = SELECT e FROM e IN emps WHERE job = 'CLERK'; FOR e IN clerk. Emps LOOP IF e. dept. dname = 'ACCOUNTING' THEN e. sal : = e. sal * 1. 1; END IF; END LOOP; COMMIT; /* Once the transaction has been committed, the changed objects are persisted to the database */ © Ellis Cohen 2001 -2006 26

Middle-Tier Caching Most OO Models for databases allow code manipulating DB objects to be in the middle-tier as well Objects are brought over to the middle-tier as the result of a query, while updated objects are sent back to the database upon commit. Often uses an optimistic concurrency control © Ellis Cohen 2001 -2006 27

Current Approaches JDO – Java standard for database object modelling embedded in Java – Uses JDOQL, a variant of OQL, with very limited capabilities – Many commercial implementation, including KODO UML – UML's Model Driven Architecture assumes that people will do design at the UML level, and have it mapped automatically onto real implementation – OCL is an assertion language that is based on UML's object model. © Ellis Cohen 2001 -2006 28

Object-Oriented Databases (OODB's) © Ellis Cohen 2001 -2006 29

Types of Databases Hierarchical DB XML DB Network DB OO/OR DB Relational DB (RDB) Multi. Dimensional DB Deductive DB © Ellis Cohen 2001 -2006 30

Timeline for Database Systems Before 1960 transition from punched card and tape 1960 s, from file management to databases IMS from IBM, Hierarchical Data Model IMS DB/DC, Network Model and communication SABRE, multi-user access with network 1970 s, CODASYL and Relational Model Codd (IBM) Relational Model Chen introduced Entity Relationship Model Query languages developed (SQL) 1980 s, Client/Server RDBs, Oracle, DB 2 PC databases, DBase, Paradox, etc. SQL standard for definition and manipulation 1990 s, web-based information delivery Object DB's for Object Persistence Multidimensional DB's for Data Warehousing Deductive Databases for Data Mining XML DB's for Semi-structured Data © Ellis Cohen 2001 -2006 31

OODB's In an OODB Objects exist independently, not just as rows in a table An object may have a reference to another object (allowing navigation) Instead of a table, there are collections, which contain references to objects © Ellis Cohen 2001 -2006 32

Object Identity Primary Key Identifies row in an RDB Can be changed or reused Not usually globally unique Reference via foreign key, may not necessarily always refer to same row OID (Object ID) Uniquely identifies object, independent of all of its values (e. g. my axe) Can't be changed; also can't be reused (unlike ROWIDs) Sometimes is globally unique (e. g. also includes IP address & DB id ) A reference effectively holds an OID, and always refers/points to the same object © Ellis Cohen 2001 -2006 33

ROWIDs vs OIDs ROWIDs uniquely identify rows as long as they exist • If a row is deleted, the ROWID can be reused. An OID (Object ID) is like a ROWID, but it is never reused • OIDs are used to identify and refer to Why? objects in OODBs • An OID could be constructed from a ROWID plus a usage-number e. g. The OID AAAGDx. AABAAAH 9 EAAD 27 is the 27 th use of the ROWID AAAGDx. AABAAAH 9 EAAD • Other UID techniques are widely used © Ellis Cohen 2001 -2006 34

Extents & Objects in ODB's an Emp 6291 SMITH … Objects are not in tables; each one is independent! deptno 622 AAAGDx. AABA AAH 9 EAAD 27 Objects from different extents/classes may all share the same block! dname loc Auditing CHICAGO … … © Ellis Cohen 2001 -2006 35

Summary © Ellis Cohen 2001 -2006 36

ORDB's and OODB In an ORDB A row may have a direct references to another row (allowing navigation) A row can be used as an object A table acts like a collection of objects In an OODB Objects exist independently, not just as rows in a table An object may have a reference to another object (allowing navigation) Instead of a table, there are collections, which contain references to objects © Ellis Cohen 2001 -2006 37

OR Databases In an ORDB A table may be defined as a collection of objects, where every row in the table corresponds to an object A row object is uniquely identified by its OID An object may have a reference to another row object (allowing navigation) SELECT e. ename, e. dept. loc FROM emps e The ORDB iterates through the table emps For each employee object e in emps, it extracts the ename and dept attributes The dept attribute is a department object reference, so the ORDB navigates to the corresponding department row object (in the depts table) and extracts the loc attribute. © Ellis Cohen 2001 -2006 38

OO Databases In an OODB Objects exist independently, not just as rows in a table An object is uniquely identified by its OID An object may have a reference to another object (allowing navigation) Instead of tables, there are collections, which contain references to objects SELECT e. ename, e. dept. loc FROM e IN emps The OODB iterates through the collection emps For each employee e referenced in emps, it navigates to that employee object, and extracts the ename and dept attributes The dept attribute is a department object reference, so the OODB navigates to the department and extracts the loc attribute. © Ellis Cohen 2001 -2006 39

OR Mapping Layer When using an OR Mapping Layer An ordinary RDB table may be treated as a collection of objects, where every row in the table corresponds to an object A row object is uniquely identified by the combination of its table name and its primary key. Foreign keys act like references to the object in the foreign table with that primary key. SELECT e. ename, e. dept. loc FROM e IN emps and depts are ordinary tables, with primary keys empno and deptno, respectively. The emps table contains deptno as well, which is a foreign key referencing the depts table. The OR mapping layer maps the original OO query to a corresponding pure SQL query. © Ellis Cohen 2001 -2006 40