The concept of a pointer to an array of pointers is extremely confounding, but it is a very common type of data structure within an object database. Pictorially, it is represented in Figure 4.3. What we see is a condition where a single value in an object contains a structure, or a pointer to another object. At first glance this seems very strange, since we are used to seeing single, “atomic” data items in a definition. While this violates First Normal Form, Chris Date has stated in his “third manifesto” that repeating groups and aggregate data items can be viewed as a “set,” and are therefore compliant with relational theory.

Figure 4.3  How pointers are used to build aggregate objects.

As you can see, we have managed to represent the “real world” through a very sophisticated network of internal pointers. The question now becomes one of data access. How can we address the data in these report cards using SQL? Let’s assume that the grade field is a numeric field, with 4 for an A, 3 for a B, and so on. We would like to query our object database to gather the names of all students that are qualified for the dean’s list. The SQL query might appear like this:

SELECT last_name
FROM STUDENT
WHERE
semester = 'Fall 94'
AND
avg(report_card.grade) > 3.5;

Here we see that the pointer structures are simply referenced by prefacing the data name with the name of the structure in which they participate. The object engine will take care of the pointer navigation on our behalf, returning the proper list of students.

Pointer-Based Navigation

This sample query is written using the dialect of SQL that supports object constructs, and will not function within Oracle at this time. However, these are the concepts that are being discussed for Oracle version 8, where object extensions will be added to the base relational engine. This query returns the names of employees in the marketing department with an expertise in SQL.

For example, let’s review a query to see a list of all employees in the Marketing department. To run this query against relational tables we would perform a relational join as in the following example. This SQL statement could also execute correctly against an object model database if the foreign key field (dept_id) was introduced into the employee record to indicate that the relationship with the department table was available:

SELECT last_name
FROM EMPLOYEE
WHERE
EMPLOYEE.dept_id = DEPARTMENT.dept_id
AND
department_name = 'marketing';

The SQL code example below uses object extensions to directly navigate the relationship to a department object and tests its value. There is no need to join two tables, since the employee object contains a pointer that takes the query directly to the department name:

SELECT last_name FROM employee WHERE
    TRAVERSE(oid, "employee", "DEPARTMENT.Dept_name") = 'marketing'
    AND
    Expertise_area = 'SQL';

The traversal operation uses the relationship between employees and departments to test the value of the department to which the employee belongs. In this case, the query directly navigates the relationship to acquire this value, without requiring any join operations. Since the employee object contains a pointer to the department in which it participates, the TRAVERSE operator can be used to chase the up-level pointer to get the name of the department object. The example demonstrates the natural use of an object model to capture information about relationships. These can be modeled directly, without requiring the developer to define keys and additional tables. Secondly, standard SQL can be used against the object model, returning the same results we would expect from a relational model.

In the case of relationships captured by the object model, this is accomplished without requiring costly join operations at runtime. This is an important principle of the object databases. By taking the time up front to establish a pointer chain to precreate the relationship, the database will be able to traverse the relationship far faster than with a relational join.

Of course, the precreation of data relationships has a downside—they are simply not as flexible as relational databases. Whereas a new table can be immediately joined with other tables, a new object cannot become associated with other objects until the database has been restructured to pre-create the relationship.

Invoking Methods In SQL

As we know, the object data model coupled data with behavior in the class definition, and a data access language should be able to access any methods that are available to the object. For example, consider a table that contains employee_name and birth_date. We could create a method that would accept the birth_date and compute the age of the employee:

age(birth_date date) return int;

This method should be addressable as a data item from within the SQL. For example:

SELECT employee_name, age
FROM EMPLOYEE
WHERE
   age > 30;

To be fully functional, methods should be able to be called from SQL just as if they were native to the language. For example, assume that we create a method that computes the total amount for an order based on the individual prices of the items in the order. But take a close look at the sample method: It selects data from three tables and returns data from two tables. Given that no one-to-one correspondence exists between the tables and the method, where does this method belong? Should we associate it with the ORDER, LINE_ITEM, or ITEM class? For the moment, let’s assume that the method is defined as a ORDER method, namely ORDER.total_price():

example ORDER.total_price method

    SELECT sum(ITEM.price*LINE_ITEM.quantity)
    FROM ORDER, LINE_ITEM, ITEM
    WHERE
    order_number = :invar
    AND
    ORDER.order_number = LINE_ITEM.order_number
    AND
    LINE_ITEM.item_number = ITEM.item_number;

Now we should be able to reference total_price just as we would reference any other data item within the order object. For example, we could state:

SELECT     order_number,
    total_price
FROM ORDER
WHERE
    total_price > 300;

While access to object databases via SQL is a lofty goal, it remains to be seen if the SQL language will allow itself to become extended for object-orientation. It is possible that a totally new access method such as Object Query Language (OQL) will evolve from the vendors. For the time being, the answer resides in the marketplace, and the major relational database vendors will exert a strong influence in the future direction of SQL. For now, we can only speculate on the future and direction of objects and SQL.

In summary, the following examples of SQL are used to illustrate each of the object-oriented concepts.