IOPC2

Object-relational databases offer higher level of abstraction over the problem domain. They extend relational databases with object-oriented features to minimise the gap between relational and object representation of application data, known as the impedance mismatch problem. For detailed information about user defined types and other features of object-relational databases refer to [05], [06]. This area is introduced by an 1999 revision of the ISO/IEC 9075 family of standards, often referred to as SQL3 or SQL: 1999. Because the level of implementation of the standard varies between available products, you may need to see their manuals too. For Oracle 10g refer to [07]. Main features of the object-relational databases are summarised below.

User-defined types are custom data types which can be created by users using the new features of object-relational database systems. These types are used in table definitions the same way as built-in types like NUMBER or VARCHAR. There are several kinds of UDTs - for example - distinct (derived) types, named row types, and most importantly the abstract data types (ADT), which we will focus on in the following paragraphs.

ADT is a structured, user defined type defined by specifying a set of attributes and operations much in a similar way to object-oriented languages like C++ or Java. Attributes define the value of the type and operations its behaviour. ADTs can be inherited from other abstract data types (in terms of object-oriented programming) and can create type hierarchies. These hierarchies can reflect the structure of data objects defined in application-tier modules. Instances of ADTs are called objects and can be persisted in database tables. See Example 2.1, “Using object types in Oracle” for an illustration how these types are defined and used in Oracle ORDBMS.

-- type definitions
CREATE TYPE TPerson AS OBJECT (
  name VARCHAR2(50),
  age NUMBER(3)
  ) NOT FINAL;
CREATE TYPE TStudent UNDER TPerson(
  studcardid VARCHAR2(20)
) NOT FINAL;
CREATE TYPE TPhdStudent UNDER TStudent(
  scholarship NUMBER(10)
) NOT FINAL;
CREATE TYPE TEmployee UNDER TPerson(
  salary NUMBER(10)
) NOT FINAL;

-- storage
CREATE TABLE Person OF TPerson;

-- fill it with data
INSERT INTO Person VALUES(
  TPerson('Mary Major', 60)
);
INSERT INTO Person VALUES(
  TStudent('Richard Doe', 22, 'WCD-3223')
);
INSERT INTO Person VALUES(
  TPhdStudent('Joe Bloggs', 27, 'PHD-1234', 12000)
);
INSERT INTO Person VALUES(
  TEmployee('Ola Nordmann', 45, 60000)
);

First, the supertype TPerson and its descendants TStudent, TPhdStudent and TEmployee are defined. The NOT FINAL keyword allows us to create subtypes of given types. Then a physical storage table Person is created. This table can hold not only instances of the TPerson type but also instances of its descendants. Accessing these instances is demonstrated in the following Example 2.2, “Accessing objects in Oracle”.

SELECT VALUE(x) FROM Person x;
-- returns:
TPERSON('Mary Major', 60)
TSTUDENT('Richard Doe', 22, 'WCD-3223')
TPHDSTUDENT('Joe Bloggs', 27, 'PHD-1234', 12000)
TEMPLOYEE('Ola Nordmann', 45, 60000)

-- accessing descendant attributes:
SELECT 
  TREAT(VALUE(x) AS TStudent).studcardid AS studcardid 
FROM Person x 
WHERE VALUE(x) IS OF (TStudent);
-- returns:
WCD-3223
PHD-1234

Nested tables. Nested tables violate the first normal form in a way that they allow the standard relational tables to have non-atomic attributes. Attribute can be represented by an atomic value or by a relation. Example 2.3, “Nested tables in Oracle” illustrates how to create and use nested tables in Oracle database system. The example modifies the Person type by adding a list of phone numbers to it. Interesting is the last step in which we perform a SELECT on the nested table. To retrieve the content of the nested table in a relational form, the nested table has to be unnested using the TABLE expression. The unnested table is then joined with the row that contains the nested table.

-- a type representing one phone number
CREATE TYPE TPhone AS OBJECT (
  num VARCHAR2(20),
  type CHAR(1)
  );

-- a type representing a list of phone numbers
CREATE TYPE TPhones AS TABLE OF TPhone;

-- the modified TPerson type
CREATE TYPE TPerson AS OBJECT (
  name VARCHAR2(50),
  age NUMBER(3),
  phones TPhones
  ) NOT FINAL;

-- storage
CREATE TABLE Person OF TPerson 
  NESTED TABLE phones STORE AS PhonesTable;

-- fill it with data
INSERT INTO PERSON VALUES(
  TPerson('Mary Major', 60, TPhones(
      TPhone('123-456-789', 'W'),
      TPhone('987-654-321', 'H')
) ) );
 
-- obtaining a list of phones of a particular person
SELECT y.num, y.type 
FROM Person x, TABLE(x.phones) y
WHERE x.name = 'Mary Major';

-- returns rows:
123-456-789 W
987-654-321 H

Collection types. SQL3 defines also other collection types like sets, lists or multisets. In addition to nested tables, Oracle implements the VARRAY construct which represents an ordered set (list). The main difference is that VARRAY collection is stored as a raw value directly in the table or as a BLOB^[8], whereas nested table values are stored in separate relational tables.

Reference types. We can think of the database references as of pointers in the C/C++ languages. References model the associations among objects. They reduce the need for foreign keys - users can navigate to associated objects through the reference. In the following Example 2.4, “References in Oracle” we will add a new subtype TEmployee and modify the TStudent type from previous examples by adding a reference to the student's supervisor, which is an employee, to it. Note that we need to cast the reference type REF(x) to REF TEmployee in the INSERT statement because REF(x) refers to the base type TPerson.

-- type definitions
CREATE TYPE TPerson AS OBJECT (
  name VARCHAR2(50),
  age NUMBER(3)
  ) NOT FINAL;
CREATE TYPE TEmployee UNDER TPerson(
  salary NUMBER(10)
);
CREATE TYPE TStudent UNDER TPerson(
  studcardid VARCHAR2(20),
  supervisor REF TEmployee
);

-- storage
CREATE TABLE Person OF TPerson;

-- insert an employee into the Person table
INSERT INTO Person VALUES(TEmployee('Ola Nordmann', 45, 60000))
-- insert a student with a reference to his supervisor
INSERT INTO Person 
  SELECT TStudent('Richard Doe', 22, 'WCD-3223', 
    TREAT(REF(x) AS REF TEmployee)))
  FROM Person x
  WHERE x.name = 'Ola Nordmann';
    
-- select all students with their supervisors
-- dereferencing uses dot notation
SELECT x.name, TREAT(value(x) as TStudent).supervisor.name
FROM Person x
WHERE VALUE(x) IS OF (TStudent);

-- returns a row:
Richard Doe, Ola Nordmann