oql: Ellis Cohen

8/9/2019 oql: Ellis Cohen

1/40

1

Theory, Practice & Methodologyof Relational DatabaseDesign and Programming

Copyright Ellis Cohen 2002-2006

Introduction toObjects & Databases

These slides are licensed under a Creative CommonsAttribution-NonCommercial-ShareAlike 2.5 License.

For more information on how you may use them,please see http://www.openlineconsult.com/db


2/40

2 Ellis Cohen 2001-2006

Topics

Object Mapping and ODL

Object Query Language

Object Relational Mapping

Object-Oriented Databases(OODB's)

Summary


3/40


Object

MappingandODL


4/40


Drivers for OO & DB Integration

Persistence Object Storage Need for a way to easily save and restore

computation state of programs built viaOOPL'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 forclient program access & modification

OO Server-Side Programming for RDB's Brings benefits of OO approach to RDB's


5/40


Object & Relational Features

Relational ModelPersistence

Storage Optimization

Indexing

Queries (SQL)Query Optimization

Constraints &Triggers

Transactions

Backup & Recovery

Object ModelEncapsulation

Object TypesAttributesMethods

InheritancePolymorphism

Object IdentityReferencesNavigation

CollectionsVersioning


6/40


Rows as Objects/Entities

empno: 7654

ename: MARTINsal: 1250comm: 1400

an Employee Object

It can be useful to think of each row as

an object or entity(i.e. an instance ofan entity class) and the table as acollection of these objects

The columns of the table correspond tothe instance variables for each object

empno ename sal comm

Emps

7499 ALLEN 1600 300

7654 MARTIN 1250 1400

7698 BLAKE 2850

7839 KING 50007844 TURNER 1500 0

7986 STERN 1500


7/40


Object Mapping

Relational MappingMapping an ER model to a Relational Model

Object MappingMapping an ER model to an Object Model

Dept

worksfor

deptnodname

empnoename

jobaddress

Employee

manages

[manager]

[worker]


8/40


Object Model

Object Classes & InstancesCorrespond to entity classes

and instances

CollectionsSet (no duplicates)Bag (duplicates)Lists (ordered, duplicates)

Array (list w efficient indexing)

References (Pointers)


9/40


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 emplsinverse Employee::dept;


10/40


ODL Relationships

dept1

dept2

dept3

emp1

emp3

emp5

emp4

emp2

empls

dept


11/40


ODL Exercise

Represent the manages

relationship in ODL


12/40


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 managees

inverse mgr;}

class Dept {

attribute int deptno;attribute string dname;relationship Set empls

inverse Employee::dept;


13/40


Referencing / Navigation

Given an employee ee.dept - department of e

e.dept.deptno the # of e's dept

e.dept.dname the name of e's dept

Given a department dd.empls the set of employees

who work in dept d


14/40


OQL(Object Query

Language)


15/40


OQL (Object Query Language)

Given a department dd.empls

the collection of employees who work in dept d

SELECT e.empnoFROM e IN d.emplsthe employee numbers of the employees who

work in department d

SELECT e.empno, e.enameFROM e IN d.emplsthe employee numbers & names of the

employees who work in department d

OQL SELECT iterates through the objects in a collection


16/40


OQL SELECT Returns Collections

SELECT e.empnoFROM e IN d.empsthe employee numbers of the employees who

work in department d

Bag

SELECT e.empno, e.enameFROM e IN d.empsthe employee numbers & names of the

employees who work in department d

Bag

SELECT e FROM e IN d.empsWHERE e.job = 'CLERK'the clerks who work in department d

Set


17/40


Extents and Keys

How do iterate through all theemployees?

We can associate an extent with

a class, which corresponds tothe set of instances in thatclass.

We can associate a key with an

extent


18/40


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 managees

inverse mgr;}

class Dept (exptent depts, key deptno) {

attribute int deptno;attribute string dname;relationship Set empls

inverse Employee::dept;


19/40


Collections and Relationships

deptsemps

dept1

dept2

dept3

emp1

emp3

emp5

emp4

emp2

empls

dept


20/40


Queries involving Extents

SELECT e.empnoFROM e IN empsWHERE e.ename = 'SMITH'

SELECT e.empno, e.enameFROM e IN empsWHERE e.job = 'CLERK'


21/40


Joins & Navigation

SELECT e.ename, d.dnameFROM e IN emps, d IN deptsWHERE e.dept = d An ordinary expensive join

SELECT e.ename, e.dept.dnameFROM e IN empsWHERE e.dept IS NOT NULL Lists every employee & their dept Replaces joins by navigation

SELECT e.ename, d.dnameFROM d IN depts, e IN d.empls Lists the employees in each department Uses correlated navigation


22/40


Object-RelationalMapping


23/40


Object Relational Mapping

The idea of OR Mapping is totreat a RDB in terms of itsobject model.

In particular the DB is modelled in ODLqueries are written in ODL

ODL queries are automaticallymapped to SQL


24/40


Mapping OQL to SQL

SELECT e.ename, e.dept.dnameFROM emps e

in OQL is mapped to the SQL:

SELECT e.ename,

(SELECT d.dname FROM depts dWHERE e.deptno = d.deptno)FROM emps e

A good optimizer will treat this as

equivalent toSELECT ename, dname

FROM emps NATURAL JOIN depts


25/40


Returning Collections of Objects

SELECT e FROM e IN empsWHERE 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 beautomaticallyreflected in thecorresponding database objects on commit.


26/40


Embedded OQL Programming

Imagine a PL with embedded OQLa (possible) server-side language for an OODB

clerkEmps Set :=SELECT e FROM e IN emps WHERE job = 'CLERK';

FOR e IN clerkEmps LOOP

IF e.dept.dname = 'ACCOUNTING' THENe.sal := e.sal * 1.1;

END IF;END LOOP;COMMIT;

/* Oncethetransactionhas beencommitted,thechangedobjectsare persistedtothedatabase */


27/40


Middle-Tier Caching

Most OO Models for databasesallow code manipulating DBobjects to be in the middle-tieras well

Objects are brought over to themiddle-tier as the result of aquery, while updated objectsare sent back to the database

upon commit.Often uses an optimisticconcurrency control


28/40


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 atthe UML level, and have it mapped

automatically onto real implementation OCL is an assertion language that is

based on UML's object model.


29/40


Object-OrientedDatabases(OODB's)


30/40


Types of Databases

Hierarchical DB

Network DB

XML DB

OO/OR DB

Relational DB (RDB)

Deductive DBMultiDimensional DB


31/40


Timeline for Database Systems

Before 1960 transition from punched card and tape1960s, from file management to databases

IMS from IBM, Hierarchical Data Model

IMS DB/DC, Network Model and communication

SABRE, multi-user access with network

1970s, CODASYL and Relational Model

Codd (IBM) Relational ModelChen introduced Entity Relationship Model

Query languages developed (SQL)

1980s, Client/Server RDBs, Oracle, DB2

PC databases, DBase, Paradox, etc.

SQL standard for definition and manipulation

1990s, 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


32/40


OODB's

In an OODBObjects exist independently, not just as

rows in a table

An object may have a reference toanother object (allowing navigation)

Instead of a table, there are collections,which contain references to objects


33/40


Object Identity

Primary KeyIdentifies 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 ofallof

its values (e.g. my axe)

Can't be changed; also can't be reused

(unlike ROWIDs)Sometimes is globallyunique

(e.g. also includes IP address & DB id )

A reference effectively holds an OID, and alwaysrefers/points to the same object


34/40


ROWIDs vs OIDs

ROWIDs uniquely identify rows as long asthey exist

If a row is deleted, the ROWID can bereused.

An OID (Object ID) is like a ROWID, but itis never reused

OIDs are used to identify and refer toobjects in OODBs

An OID could be constructed from aROWID plus a usage-number

e.g. The OID AAAGDxAABAAAH9EAAD27 is the27th use of the ROWID AAAGDxAABAAAH9EAAD

Other UID techniques are widely used

Why?


35/40


Extents & Objects in ODB's

622 Auditing CHICAGO

deptno dname loc

Objects from differentextents/classes may allshare the same block!

6291 SMITH AAAGDxAABAAAH9EAAD27

an Emp

Objects arenot in tables;each one is

independent!


36/40


Summary


37/40


ORDB's and OODB

In an ORDBA 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 OODBObjects exist independently, not just as

rows in a table

An object may have a reference toanother object (allowing navigation)

Instead of a table, there are collections,which contain references to objects


38/40


OR Databases

In an ORDBA table may be defined as a collection of objects,where every row in the table corresponds to anobject

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 extractsthe ename and dept attributes

The dept attribute is a department objectreference, so the ORDB navigates to thecorresponding department row object (in thedepts table) and extracts the loc attribute.


39/40


OO Databases

In an OODBObjects exist independently, not just as rows in atable

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, whichcontain references to objects

SELECT e.ename, e.dept.loc FROM e IN empsThe OODB iterates through the collection emps

For each employee e referenced in emps, it

navigates to that employee object, and extractsthe ename and dept attributes

The dept attribute is a department object reference,so the OODB navigates to the department andextracts the loc attribute.


40/40


OR Mapping Layer

When using an OR Mapping LayerAn ordinary RDB table may be treated as a

collection of objects, where every row in thetable corresponds to an object

A row object is uniquely identified by thecombination of its tablename and itsprimary

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 empsemps and depts are ordinary tables, with primary

keys empno and deptno, respectively.

The emps table contains deptno as well, which is aforeign key referencing the depts table.

The OR mapping layer maps the original OO queryto a corresponding pure SQL query.

oql: Ellis Cohen

Documents

Transcript of oql: Ellis Cohen