SQL and Object-Oriented Databases Oracle Database Tips by Donald Burleson

SQL and Objects

While these are relatively straightforward operations, they do not provide for many of the features that have become associated with the object-oriented databases, especially when dealing with abstract data types (ADTs), and pointers.  Many of the characteristics of SQL for relational databases is in contradiction with some of the new features of the object/relational implementations of SQL:

One of the most confounding problems is the database arena today is the reconciliation of objects with SQL.  Industry experts, such as Christopher Stone, President of the Object Management Group, agree that, "So what the object database community needs - - excuse me, what the object community needs - - is agreement on a data model and how you pinpoint it for design, how you build applications that are free from specific Data Manipulation Languages (DMLs).  Does that mean that you extend SQL - - and that's going on all over the place - - to be object-oriented?  Does it mean you develop an entirely new object query language?  Probably not.  Does it mean you just extend C++ and pray the marriage of a programming language and a database is really going to happen?  I don't think that is going to happen.  The writing is on the wall that it'll pretty much be an evolution of SQL.  Object database technology, those extensions to SQL supporting abstract data types, and things like that will become much more prevalent over the next two to three years."  For the past several years there has been a increasing effort among application developers to interface their C++ systems with relational databases.

When attempting to reconcile the object-oriented approach and relational databases, it is very important to recognize that the object-oriented approach deals with data at a much higher level than a relational database.  Whereas a relational database deals with data at the level of columns and rows, an object-oriented system deals with objects, which may be any number of collections of data items.  An object may be an order, an inventory list, or any real-world representation of a physical object.  For example, consider an object called ORDER.  ORDER is a logical object to the object-oriented system, and each ORDER will have associated data items and behaviors.  Behaviors might include PLACE_ORDER, CHANGE_ORDER, an so on. 

At the relational database level, an ORDER is really a consolidation of many different columns from many different tables.  The customer name comes from the CUSTOMER table, order date comes from the ORDER table, quantity from the LINE_ITEM table and item description from the ITEM table.  Hence, a single behavior for an object may cause changes to many tables within the relational database. 

Figure 9.1 - The mapping of objects to relational tables.

One of the major shortcomings of the Relational database model is its inability to represent aggregate objects.  All data must be decomposed into tables, and the display of an aggregate object requires a joins of the component tables at runtime.  Codd suggested the use of relational "views" to represent this higher level of abstraction.  For example, an SQL statement could be created an SQL view called ORDER_FORM.

CREATE OR REPLACE VIEW
order_form
AS
SELECT
   customer_name,
   customer_address,
   customer_phone,
   order_nbr,
   order_date,
   item_name,
   qty_ordered
FROM
   customer,
   order,
   line_item,
   item
WHERE
  order_nbr = :hostvar;

The view could then be used to produce an order form in a single SQL statement without requiring the SQL syntax for joining the tables together:

SELECT *
FROM
order_form
WHERE
order_nbr = 999;

The relational view still misses the basic point of aggregate objects.  The whole idea about data aggregation is that higher-level objects will have an independent existence instead of being rebuilt each time that the view is used.  Also relational views cannot be used for update operations.  In a relational view, the row ID (the RID), cannot be maintained within the subordinate tables, and consequently, UPDATE and INSERT operations are not allowed.  Object behaviors such as PLACE_ORDER and CHANGE_ORDER cannot use relational views.  Some researchers have suggested methods for creating "updatable" views within the relational database model, but no commercial databases have implemented support for updatable views. 

SQL and the impedance mismatch

One of the early vendors to address this market is Persistence Software (see Fi in Addendum), whose object-to-relational mapping product is distinguished by it's strong in-memory object caching that offsets the performance setbacks associated with translation between object and relational models (known as "impedance mismatch").

In an online object-oriented application such as a C++ program, the "impedance mismatch" between object and relational models requires encapsulated data to be mapped into a relational table for persistent storage, and then reassembled at run-time.  Most implementation of this type of mapping of in-memory objects to rows in relational tables results in substantial overhead and performance degradation.

Some products alleviate this problem with its large object caches which retains highly used business objects (along with their encapsulated data) in memory, thereby avoiding the overhead associated with re-assembly of objects from the underlying relational tables (as well as the delays associated with repeatedly re-reading from disk).

 

---