Object-Relational Databases

Chapter 22

Introduction to object-relational DBMS

• Object-relational DBMS are emerging systems that enhance the capabilities of a relational DBMS

• There is a clear need to design databases than can develop, manipulate, and maintain complex objects that oop languages manipulate

• ANSI/ISO SQL-99 has introduced an SQL language standard that supports the ORDBMS model

• ORDBMS systems are upward compatible, so they also run RDBMS SQL

• This is a rapidly changing field that has not been fully standardized, so there are several different dialects of ORDBMS systems right now.

Object-relational history

• The object-relational model had a number of factors motivating it development

• Object-oriented programming languages such as Java and C++ led to a desire to create database systems that would interact naturally with them.

• In the mid 80s a number of object-oriented database products began to appear

– Complex objects such as arrays and classes could be used to contain the data

– Such complex objects are very useful in applications such as machine parts design, where some machine parts have extremely detailed subassemblies

– To model such designs in relations products can require a very large number of joins

• Even though OODBMS was the best choice for some applications, the idea of a table was absent, and queries were rather primitive.

• The ORDBMS model evolved to fill a number of needs

• Commercial users were willing to accept a “relational system” without the first normal form restriction

• The most important early ORDBMS prototype product was POSTGRES

ORSQL capabilities

• The first thing to notice about Object-Relational DB is that the first normal form is violated

– First Normal Form: The relational model insists that the values in columns must be atomic values.

• The user can define objects; these then become types that can be used in either rows or columns

• Row in a table can contain one attribute that is an object

– Remember, each attribute has a type; Object Relational lets this type be user defined (thus an object)

• Rows can be objects: then the columns are the data members of the object

– Of course, a data member may itself be an object

• A table can then be defined to contain rows of this user-defined type.

Object type in Oracle

• An object type has attributes of various types, just like an entity.

• Example: Create an object type name_t to represent a person’s name, with last name, first name and middle initial

• create type name_t as object (
lname varchar(30),
fname varchar(30),
mi char(1) );

• Then the name type can be used as a type in tables

• create table teachers (
tid int,
tname name_t,
room int);

• Inserting values looks like this:
insert into teachers values (1234, name_t(‘DeFrance’, ‘Anne’, ‘M’) 361)

Object constructors

• In the previous example

insert into teachers values (1234, name_t(‘DeFrance’, ‘Anne’, ‘M’) 361);

• The object_type_name( ) form is called the object constructor

• It builds an object from the values of its attributes

Querying the teachers table

• select t.tid
from teachers t
where t.room = 123;

• select t.tid, t.name.fname, t.name.lname
from teachers t
where t.room = 123;

• The table alias must always be used when accessing an object table.

– This is true even if an attribute name is unique.

– In Oracle, a table is called a object table if its rows are of object type

Creating another object type

• create type person_t as object (
ssno int,
pname name_t,
age int);

– person_t is dependent on name_t

– An attempt to drop a type on which another type is dependent will result in an error

• Now we will create an object table called people to contain person_t objects.

create table people of person_t (
primary key (ssno) );

• In an object table the object attributes provide the columns of the table

Row objects and column objects

• The people table contains two types of objects

– The person_t objects are called row objects because they sit in table rows

– Since the column-like attribute name pname is itself of object type, pname values are called column objects

• The attributes of pname are accessible within the people table by using the dot notation

Examples accessing attributes in object tables

• select p.age
from people p where p.ssno =123456789;

• select p.name from people p where p.age > 25;

– This selects the column object where the condition is met

• select * from people p where p.age > 25;

– Notice the object constructor name_t

• select value(p) from people where p.age > 25;

– This will select the entire row object where the condition is met

– Notice how the column objects and row objects are displayed with their object constructor

Another query

• Display the full person_t object, including SSN and age for Jose F. Sanchez.

• Select value(p) from p
where p.name = name_t(‘Sanchez’, ‘Jose’, ‘F’);

– Notice that you must compare p.name to an object of type name_t,

– So you must construct a name_t object.

Nested dot notation

• If you want to access an attribute of a column object, you use nested dot notation

• Query: Find the names and ages of all people whose first name start with “Pat” and are over 50 years of age.

• select p.name, p.age from people p
where p.name.fname like ‘Pat%’ and p.age > 50;

The REF object reference

• Objects that appear

– in object tables are called row objects,

– as table columns (or attributes within other objects) are called column objects.

• Oracle provides all row objects (but not column objects) with a unique identifier

– called an object identifier.

• An attribute of a table can be declared to have a built-in data type called a REF to allow it to “point to” a row object of another table.

• These REFs can be used to avoid what may be inefficient joins between tables

Using REFs

• Example: the CAP database

• In a relational db, the tables are tied together with foreign keys.

• It is possible to replace the foreign keys in the orders table with three objects REFs

• But it turns out it is better to keep all the FK and just add three REF columns.

• First, each of the tables needs to be changed to rows of objects, so an object type must be declared for each table.

The CAP db as object-relational

• Create object types for customer, agent and product

• create type customer_t as object(….);
create type agent_t as object (…);
create type product_t as object(….);

• Now create the orders type

– Three new columns to point to the three tables above are added

– See overhead or handout

The order_t object

• Note that the order table now has ten attributes, three of them are the foreign keys

• Each orders row object also has an ordcust column, a REF to the customers row object with the same cid.

– It also has REFs to the agent and product objects with the same aid or pid.

Using REFs

• Dot notation is used to follow a REF.

• Query: print out all order number for dollar amounts over $20,000, together with the ordering customers’ names.

• select o.ordno, o.ordcust.cname
from orders o
where o.dollars > 200.00;

• This is not only simpler than a join, but is more efficient

Another query using REFs

• Query : Retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent.

• select distinct o.ordcust.cname,
o.ordagent.aname
from orders o;