What is an ORDBMS ?
Need for ORDBMSs
Characteristics of an ORDBMS
SQL3 – ‘object-oriented’ SQL
State of the art: Oracle 8 and IBM DB2 UDB
What is an ORDBMS ?
A combination of:
(1) OO features
Inheritance and overloading
(2) RDBMS features
Transactions, recovery, indexing, optimization
A brief look at the object-relational model
Add ‘object oriented-ness’ to tables
Data is still stored in tables
Support for abstract data types, complex objects &
user defined functions allows complex relationships
and data to be stored and queried
SQL3 (‘object-oriented’ SQL) is the language for data
definition, manipulation, and query.
Need for the O-R approach
Simple Data Complex Data
Need for the O-R approach..
New data types are appearing (e.g., multimedia)
Real-world data doesn't fit neatly into tables
Entities and relationships (vs. tables)
Variance among entities (vs. homogeneity)
Set-valued attributes (vs. normalization)
Advanced applications bring complex data
E.g., CAD/CAM data management, web data
management, geographic information management,
medical data management
So maybe objects are the answer...?
Yes, if we can keep all the relational "goodies"!
Characteristics of object-relational
SQL support for Base Data Type Extension
SQL support for Complex Objects
SQL support for Inheritance
Support for a production Rule System
Base Data Type Extension
1. Creating a new base data type:
Create type my_type (
internallength = 8,
input = mytypeInput,
output = mytypeOutput);
Create table my_table (name varchar(20) ,
Base Data Type Extension..
2. Creating a user defined function:
create function hourly_pay(int) returns float
as select ($1/2000);
Select name from emp where
Functions can also be defined in C, Java and
used in SQL queries.
Dynamic linking of user-defined
Client or Server Activation of user-
Secure User-Defined Functions
Arbitrary-length data types
create type phone_t ( area varchar(3), number
varchar(3), description varchar(20));
create type dept_t (dname varchar(30), floor
int, phone phone_t, manager varchar(30),
create table dept of type dept_t;
SQL extensions needed for composite
1. User-defined functions take arguments or return a result of a
e.g.: select sum_digits(phone) from dept where dname = ‘HR’;
2. Functions returning a composite type can appear in the from
clause of an SQL query.
3. The ‘cascaded dot notation’ references attributes of a composite
e.g.: select phone.number from dept where dname = ‘HR’;
These extensions are also required for sets of composites.
References are a substitute for the primary
key-foreign key concept.
The manager_ref column is a reference
(pointer) to a record of type employee_t.
SQL extensions needed:
A deref function that returns the composite
Allow functions to take arguments or return
results of type reference.
Inheritance only applies to types.
Data inheritance example:
create type person_t(name varchar(20));
create type student_t (gpa float, address varchar(10)) under
create type employee_t(salary int, department varchar(20),
address varchar(60)) under person_t;
create type student_employee_t(percent float) under
Ambiguity: ‘Address’ appears in both
student_t and employee_t types.
Redefine the address field in one of super
Database administrator resolves the
Select name from emp where
salary = 10000; will examine
‘emp’ and ‘student_emp’
emp student tables.
To examine ONLY the ‘emp’
Select name from only(emp)
where salary = 10000;
person_t The student_emp_t type
Overpaid Overpaid inherits the overpaid
function from employee_t.
Select e.name from emp e
is evaluated on the emp and
student_emp_t student_emp tables.
Production Rule system
1. Update update rule (triggers)
2. Query update rule
3. Update query rule
4. Query query rule
Multiple rules fired by 1 event
Chain rules cause infinite loops
Aborting action part of a rule terminates whole
The SQL3 standard
An ANSI, ISO standard
Object-oriented features added to SQL2
The foundation for ORDBMS products such as Oracle8 and DB2
New data types
LOB (large object)
Composite types: ROW and ARRAY
Object oriented extensions to SQL3:
Support for structured user-defined types
Support for user-defined functions
Support for triggers
Support for references
IBM DB2 UDB
OSF (Object Strike Force) group at IBM
Focus: O-R extensions in DB2 Universal Database
V5.2 of UDB contains new O-R features
Structured types with inheritance
Object tables and table hierarchies
References and path expressions
Object views and view hierarchies
New O-R features in DB2 UDB 5.2
Structured types and references
Named types with attributes, O-O subtyping model
Ref(T) for directly modelling relationships
Typed tables and table hierarchies
Oid (user-provided) plus a column per attribute of T
Subtables for querying and managing subtype instances
Query language extensions
Substitutability for queries/updates (data independence ++)
Path expressions for querying relationships easily
Functions/predicates for runtime type inquiries
Object views (via a novel approach)
Virtual table hierarchies for flexible access control
Also facilitates O-O views of legacy tables
Object views in DB2 UDB
create type VPerson_t as (
vdept create type VEmp_t under
VPerson_t as (dept Ref(VDept_t));
mgr create type VStudent_t under
VPerson_t as (kind Varchar(8));
create type VDept_t as ( name
Varchar(20), mgr Ref(VEmp_t));
Typed view hierarchies
Now create typed views (and subviews)
create view vperson of VPerson_t (ref is oid user generated) as
select VPerson_t(Varchar(oid)), name from only (person);
create view vemp of VEmp_t under vperson
(dept with options scope vdept) as
select VEmp_t(Varchar(oid)), name, VDept_t(Varchar(dept))
from emp where salary > 0;
create view vstudent of VStudent_t under vperson as
select VStudent_t(Varchar(oid)), name,
case when major like '%Engineer%' then 'Geek'
else 'non-Geek' end from student;
create view vdept of VDept_t ...;
Claimed as “object-enabled” RDBMS.
Oracle takes an evolutionary approach
to object-orientation. In general, Oracle
supports OO with a special layer on top
of the relational database.
1. User-Defined datatype (UDT)
2. User-Defined function (UDF)
3. Object View and Object Cache, Object
Applications that have complex data and
queries should use ORDBMSs.
Major vendors (Oracle, DB2, Illustra) have
A better and standardized solution to the
multiple inheritance problem is needed.
Commercial ORDBMS products should
conform to SQL3, not just support variants
of it – interoperability issues.
1. Object-Relational DBMSs The Next Great Wave –
2. SQL:1999, formerly known as SQL3
Andrew Eisenberg, Jim Melton
3. O-O, What are they doing to Relational databases?
Michael J. Carey IBM Almaden January 1999
4. Modeling Object Relational Databases, DBMS April
5. Object Database vs. Object-Relational Databases
Steve McClure IDC Bulletin #14821E - August 1997