he relational database management system (RDBMS) is based on the relational model, which is simply represented through the relations of rows and columns in two-dimensional tables. Meanwhile, the object-oriented DBMS (ODBMS or OODBMS) offers everything needed for object representation. The newer object-relational DBMS (ORDBMS) like Oracle9i and IBM DB2 results from combining the RDBMS and the ODBMS.

Two object-relational languages that enable the logical representation of objects in a database are SQL3 (or SQL:1999) and Zigzag. SQL3 (which is not yet in final release nor completely supported by all the well known ORDBMSs) is a calculus language, while Zigzag is an algebra object-relational language.

In this article, I discuss the three properties (complex data, type inheritance, and object behavior) that characterize the O(R)DBMS. I also explain how object-oriented Oracle and Informix SQL compares with Zigzag when used with each property.

Complex Data in the ORDBMS
Are you obligated to define schema before filling your database? Historically, the answer was yes for two reasons. First, the definition of database schema essentially helped to control the type of input data. Second, the data type limitation enabled the early DBMSs to organize data with maximal memory and processor efficiency.

Nevertheless, developing database schema is a most laborious process. Often, precise schema description is not necessary or it unreasonably slows project development. Furthermore, both reasons for schema predefinition are no longer valid: control of input data should be executed by supporting an input form (e.g., via JavaScript on the Web-client side). With the hardware advances in modern database systems, the influence of data type limitation on performance is practically insignificant.

Complex data creation in most SQL ORDBMSs is based on preliminary schema definition via the user-defined type (UDT). Table 1 is the clearest representation of complex data in any ORDBMS.

Table 1: Representation of Complex Data in ORDBMS

The “name” attribute (or field or column) consists of the “first” and “last” attributes. The value of the “course” attribute is a set of the “Economy” and “Planning” elements. You can define this structure, for example, with Informix SQL, as below:

 CREATE ROW TYPE Student (  id CHAR(5),   name ROW (first VARCHAR(12), last VARCHAR(20)),  course SET (VARCHAR(128) NOT NULL));CREATE TABLE students OF TYPE Student;INSERT INTO studentsVALUES (  'st031',   ROW('Jane', 'Hunter'),  SET('Economy', 'Planning'));INSERT INTO studentsVALUES (  'st072',   ROW('Richard', 'White'),  SET('Computers in Engineering'));

Other SQL ORDBMSs suggest different composite type constructors (e.g., VARRAY or ARRAY instead of the SET, and OBJECT instead of the ROW). Constructors of the simple built-in types, with limitations like CHAR(5), came from SQL2. Zigzag assumes the above table without using the type constructors, as in the following code through the readTable procedure:

 $readTable() ; 

The first row of input data declares attribute names. The “id” name may be missed, and the “student” will denote both a primary key attribute and a table.

Type Inheritance in the ORDBMS
Hierarchy within structured complex data offers an additional property, type inheritance. That is, a structured type can have subtypes that reuse all of its attributes and contain additional attributes specific to the subtype. Consider the data in Table 2:

Table 2: Example Data Types

These data types make up the hierarchy illustrated here:

                Employee(Name,Salary)              /                     Programmer(Language,Project)   Representative(Region)

SQL Representation
The following Oracle SQL snippet creates this database:

 CREATE TYPE Employee AS OBJECT (  Name      VARCHAR2(20),  Salary    NUMBER(6,2)) NOT FINAL;CREATE TYPE Programmer UNDER Employee (  Language   VARCHAR2(12),  Project    VARCHAR2(30));CREATE TYPE Representative UNDER Employee (  Region    VARCHAR2(30));CREATE TABLE employees OF Employee;CREATE TABLE programmers OF Programmer;CREATE TABLE representatives OF Representative;INSERT INTO employees   VALUES (Employee('Sylvia Karsen', 30000.00));INSERT INTO programmers  VALUES (Programmer('William Helprin', 40000.00, 'C++', 'Seestorm'));INSERT INTO representatives  VALUES (Representative('Akiko Yokomoto', 50000.00, 'Asia'));

The “Programmer” and “Representative” subtypes inherit all their attributes from the “Employee” supertype. A request for “employees” objects of the “Employee” type is also therefore a request for objects of its subtypes, namely “programmers” and “representatives”. For example, the result of the following SQL statement:

 SELECT e.Name  FROM employees e;

Would be:

 Name--------------------Sylvia KarsenWilliam HelprinAkiko Yokomoto

Zigzag Representation
The same data hierarchy can be expressed in Zigzag. A separate type definition is not needed:

 $readTable() ;$readTable() ;$readTable() ;

A Zigzag request for objects of the “Employee” type is also a request for objects of the “Programmer” and “Representative” types. To get the same result as the previous SQL statement, you would make the following request:

 = Name:(Employee:);

With Zigzag, a type is really an object that determines a class (in the sense of multitude) of other objects. In other words, types in Zigzag are also data. Moreover, a type inheritance means not only an inheritance of attribute names but also an inheritance of attribute values. For example, assume all programmers are located in one “E” department. SQL3 demands that the “E” value be inserted in all the rows of the “programmers” table. With Zigzag, you can set up the “E” only for the “Programmer” data object:

 $readTable() ;

To test an inheritance of “E” value, you’d enter the following Zigzag request, which translates to “the names of employees of E department”:

 = Name:(Employee:(Department:E));

The result will be:

 Name:William Helprin

Object Behavior in the ORDBMS
To maximize computer capabilities, DBMSs provide developers with some sort of connection between an external programming language and the internal DBMS language (e.g., ODBC). An additional advantage of transforming (R)DBMS to O(R)DBMS is gaining access from the internal DBMS language to the program objects, namely objects of an external or internal object-oriented programming (OOP) language. Such program objects have to be storable and transportable for database processing, therefore they usually are named as persistent objects.

Inside a database, all the relations with a persistent program object are relations with its object identifier (OID). Mapping the external persistent program objects to a database is not a problem (see, for example, JDO tools and others).

You can describe a restricted real object through traditional database elements like records or even fields. It will be a data object (or database object). The complex data elements I previously described are also data objects. What distinguishes a program object? Each program object can be formally defined as an instance received from the type mapping:

Most significant in this definition, which approximates a program object to a real object, is that object type includes not only attributes but also methods mapped into an object behavior. Some ORDBMSs like Oracle and DB2 make it possible to place methods inside of a data type definition. It enables developers to develop a SQL application similar to an OOP language (and ODBMS) application. Method is a function or procedure defined for a certain class of the objects. The invoking of a method can be represented as follows:

 object.method(parameters) = function(object,parameters);

or

 object.method(parameters) = procedure(object,parameters);

Object behavior (methods), which is usually denoted by one name, may be distinguished by the type of object itself or the situation (parameters). The OOP languages call this the polymorphism principle, which in short is “one interface, many implementations.” Two other OOP principles, inheritance and encapsulation, are related with both behavior and with the state of an object. Since I’ve discussed inheritance already in the previous section, I will focus on encapsulation at this point.

The principle of encapsulation in the ORDBMS has other shades. As advanced as they are, even Oracle 9i and IBM DB2 v8 do not take an encapsulation degree through PUBLIC, PRIVATE, and PROTECTED like Java or C++ does. Encapsulation in the ORDBMS is reduced to replacing an attribute with a method or, in other words, using virtual attributes (i.e., using the getPrice() method instead of the price attribute). In the following section, I demonstrate the OOP principles through Oracle SQL and Java/Zigzag.

SQL Representation
The SQL example below creates an “equipments” table of an abstract type “Equipment”. Really, a row of the “equipments” table may be an object of either the “Platform” or “Engine” subtype. Each subtype defines a unique getPrice() method:

 CREATE TYPE Equipment AS OBJECT (  name VARCHAR2(24),  NOT INSTANTIABLE MEMBER FUNCTION getPrice() RETURN NUMBER ) NOT INSTANTIABLE NOT FINAL;CREATE TYPE Platform UNDER Equipment (  size NUMBER,  OVERRIDING MEMBER FUNCTION getPrice() RETURN NUMBER );CREATE TYPE BODY Platform AS   MEMBER FUNCTION getPrice() IS  BEGIN    RETURN size * 6  END getPrice;END;CREATE TYPE Engine UNDER Equipment (  power NUMBER,  OVERRIDING MEMBER FUNCTION getPrice() RETURN NUMBER );CREATE TYPE BODY Engine AS   MEMBER FUNCTION getPrice() IS  BEGIN    RETURN 40 + power * 5  END getPrice;END;CREATE TABLE equipments OF Equipment;INSERT INTO equipments   VALUES (Platform('Tower X04', 4));INSERT INTO equipments   VALUES (Engine('Ford U14', 14));SELECT name, getPrice() price  FROM equipments;

The result is:

 name        price-------------------Tower X04    24Ford U14    110

Java/Zigzag Representation
The free realization of Zigzag is based on Java, and it complements Java. It’s easy to create a persistent Java object in a Zigzag database and get its attributes or call methods via Zigzag. The object types are described in Java via the following classes:

 public abstract class Equipment implements java.io.Serializable {  public String name;  public abstract int getPrice();}//Equipmentpublic class Platform extends Equipment {  public int size;  public Platform(String name, int size) {    this.name = name;    this.size = size;  }//Platform  public int getPrice() {    return size * 6;  }//getPrice}//Platformpublic class Engine extends Equipment {  public int power;  public Engine(String name, int power) {    this.name = name;    this.power = power;  }//Engine  public int getPrice() {    return 40 + power * 5;  }//getPrice}//Engine

The following Zigzag fragment creates program objects of “Platform” and “Engine” type, maps their content in the database via $mapState, and prints the table with “name” and “price” attributes like the previous SQL example:

 equipment:[  @po Platform.(Tower X04, 4),  @po Engine.(Ford U14, 14)];$mapState(equipment:);$printTable(equipment:, name, price);

In addition, Zigzag has the unique ability to set not only program objects but also their attributes and methods as multitude. That is a method or attribute that can be expressed indefinitely through the request expression:

Types in the Role of Objects
The previous sections in this article demonstrate that the Zigzag language correlates with object SQL?at least in object-oriented data representation. Zigzag is more expressive and helps when working with more structurally flexible data. Nevertheless, SQL with its type schematization allows stricter control of uniform data. The principal difference appears to be that Zigzag can see a type as a data object and a data object as a type for other objects. This enables it to construct data hierarchies, not only type hierarchies, which is semantically more precise, for example:

                              production(equipment)                             /                             printed(equipment:printer,periodicity)      video(equipment:camera,...)                       /                                                  |    magazine(periodicity:regular,...)    book(periodicity:occasional,purpose)   /   |                                /                 |                           fiction(purpose:entertainment)    tutorial(purpose:education)     ...

The book object inherits not only the fact that its production is characterized by equipment and periodicity but the fact that equipment is printer. Moreover, book is a type for objects (fiction, tutorial, and others), which inherit attribute purpose and attribute periodicity with value occasional. If instances of book (fiction, tutorial, etc.) are researched target objects, SQL3 represents inheritance with the following type hierarchy:

                        production(id,equipment)                        /                             printed(periodicity)                  video(...)          /                                   /     magazine(...)    book(purpose)      

The book objects stored in a “books” table may be represented with the corresponding attributes’ order:

books = [  book('fiction', 'printer', 'occasional', 'entertainment'),  book('tutorial', 'printer', 'occasional', 'education')]

As you can see, attribute values are not inherited. So the “printer” and “occasional” are repeated in each object. The next versions of SQL supposedly will fix such problems?type will contain a static attribute with value, like a static field in Java (static methods already exist in Oracle and DB2 SQL). Such a static attribute is useful in a request that selects all the objects of types with a needed value.

To create objects of the “tutorial” and “fiction” types, a SQL3 developer creates new types and new tables. Or better yet, he or she transforms the existing table to the new. Zigzag developers use “tutorial” and “fiction” objects as already existing types. For example, they can add a “physics” object of the “tutorial” type directly in the database with a single statement like :tutorial:physics(...)=.

In the future, SQL has to possess less expressive constructions than Zigzag, but they have to be reasonably simple to consider types in the role of objects. Flexibility based on the consideration of objects as types remains a prerogative of languages like Zigzag.