Using SQL to access nonrelational architectures is not limited to the object databases. In the early 1980s when SQL became popular, many database vendors wrote SQL “umbrellas” over their hierarchical and network database products. Some products such as CA’s CA-IDMS were even renamed to IDMS/R, proudly proclaiming that they were “relational” because they supported SQL queries. Of course, it takes much more than SQL to make a database relational, and a great debate rages today about how relational an object database becomes when it allows SQL access.

The basic operators for SQL are Select, Insert, Update, and Delete. Mapping these to an object database requires first matching the context of the data involved, then the semantics of the operation. The SQL engine maps relational tables to object classes, rows to objects, row identifiers to object identifiers, and columns to object data members. Table and attribute information to identify accessible table and column names are also available through SQL select operations.

While originally specified in the ANSI X3H2 SQL syntax, SQL3 included object extensions for invoking object methods, navigating relationships between objects, and accessing nested structures. Furthermore, extents and virtual object identifiers were supported. Since SQL3 has failed to create a usable standard, the community will have to come up with a de facto standard for accessing object databases with SQL.

This section will explore how client/server developers can adapt their SQL programming to deal with objects. It will include comments from actual users of SQL++, and discuss how programmers can prepare for this new SQL paradigm.

---

NOTE:  For details about Oracle’s object layer, refer to the section in Chapter 2, Towards Oracle8.

---

If you make a careful comparison of the relational model and the object model, you will find several constructs within object technology for which SQL must be extended to address:

•  Inheritance—Some or all objects within a class hierarchy can be selected.

•  Abstraction—Selecting from “composite” objects.

•  Pointer-Based Navigation—Non-key-based data retrieval.

•  Invoking Methods in SQL—Addressing data behaviors and functions.

Inheritance

In an inheritance hierarchy, objects are “typed” with general objects and data items high in the hierarchy, while more specific objects appear lower in the tree. While subtyping is found very commonly in the real world, it is surprising that it was not addressed in the relational model. The idea of “class hierarchies” and inheritance is a very useful concept, and one that can be relatively easily incorporated into SQL, as illustrated in Figure 4.1.

Figure 4.1  A sample class hierarchy for a vehicle entity.

Note that not all classes within a generalization hierarchy have objects associated with them. The object-oriented paradigm allows for abstraction, which means that a class may exist only for the purpose of passing inherited data and behaviors. The classes VEHICLE and CAR would probably not have any concrete objects, while objects within the VAN class would inherit from the abstract VEHICLE and CAR classes.

Now, let’s assume that we have built this vehicle hierarchy in our database. How will we be able to traverse the inheritance hierarchy using SQL? One suggestion is to add an ALL operator to the SQL, such that all objects beneath the specified object will be included in the query. For example, we could state:

SELECT
vehicle_name, vehicle_ID, date_of_sale
FROM
ALL VEHICLE;

Here is the output:

name                  ID        date
van                  1234    01/01/95
van                  5643    01/01/95
luxury sedan         6453    03/03/95
economy sedan        7342    03/01/95
economy sedan        9822    03/01/95
economy sedan        7254    03/01/95
amphibian car        5334    03/04/95
sailboat             4353    03/04/95
power boat           6452    03/01/95

Here we are saying that ALL VEHICLE includes all subclasses—starting at VEHICLE and working down the hierarchy. To limit the query to only boats, the query could state:

SELECT
vehicle_name, vehicle_ID, date_of_sale
FROM
ALL BOAT;

The result might look like this:

name                  ID            date
amphibian car        5334        03/04/95
sailboat             4353        03/04/95
power boat           6452        03/01/95

Here we see that the ALL BOAT limited the results to only those objects beneath the BOAT class. Note that the AMPHIBIAN CAR appears in both queries. This is an example of multiple inheritance.

Abstraction

Unlike the Oracle server, in which every data item is required to be atomic, object databases allow for abstract data types to be represented. In a relational database, information is “assembled” from independent tables at the time of the query. Object-oriented advocates state that this atomic approach does not properly represent the real world—analogous to assembling your automobile when you want to travel and then disassembling the car after you have completed your trip. If the goal of the database is to model the real world, then a mechanism should be in place to allow aggregate objects to exist independently from their components. (See Figure 4.2.)

Figure 4.2  The relationships between database objects and aggregate entities.

In simple terms, an aggregate object contains “references” to atomic data that resides in other object classes—in addition to its own unique data. As seen below, a report card consists of its own data (date of report) and subobjects that have their own unique identity within the database.

If the aggregate object will have its own identity within the database, how can we represent these identities? In an object database, a student object (table) might have the following fields. Note that the report_card_list field is a pointer to an array of pointers to report cards:

first_name                char(20);
last_name                 char(20);
age                       number;
report_card_list          pointer to an array of report cards