Internet Archaeol 15. Nick Ryan. Object-relational models

2.3 Object-relational models

Early relational DBMS were designed to manage simple character and numeric data, some with additional types to handle dates and monetary values. This was adequate when most display terminals were character based and the main database applications were in business administrative areas such as stock control and payroll systems. However, once graphical displays and the use of spatial and multimedia data became widespread, this restricted range of supported data types came to be seen as a serious limitation to the range of applications that database systems could support.

At the same time, OO design approaches and programming languages were gaining in popularity and were widely seen as offering better ways of handling these more complex forms of data. The result of these demands and influences has been that most of the major DBMS designed for use in multi-user servers have been extended not only to handle a much wider range of data types, but also to incorporate many ideas derived from the OO approach. Unfortunately, vendors of DBMS designed for small-scale or desktop systems have been slow to adopt these developments, preferring instead to concentrate on developing superficial appearances, so that today even the most popular of these is functionally very antiquated.

Extended relational systems are now widely referred to as Object-Relational DBMS. The main differences between relational and OR systems lie in their approach to data types. These differences are outlined below.

Large objects. Whereas earlier systems placed quite low limits on the maximum size of records and the length of character strings that could be stored as attributes and few permitted binary data to be stored, both character and binary large objects are now supported. For example, an attribute value may now contain an entire chapter of a book, an image or a video clip.

Simple types. SQL:1999 standardises some new types already available in many systems such as Boolean true/false values and introduces what it calls 'distinct types'. Attributes such as dimensions and weights might normally be stored as real numbers and so it would be possible, but meaningless, to use conditions such as 'weight > length' in an SQL query. It is now possible to define weight and length as distinct types based on real numbers so that such comparisons are treated as illegal.

Composite types. Although the requirement for atomic attribute values is fundamental to the relational model, many types such as character strings and dates are, in fact, composite objects that can be further decomposed. Most DBMS already provide functions to extract substrings and day/month/year elements from values of these types. It is apparent, therefore, that an attribute could contain an object of arbitrary complexity without compromising the relational model, provided that it is treated as a single item for the purposes of relational operations. SQL:1999 adds array and row types that permit grouping of related values into a single attribute For example, one way of encapsulating dimensions in an artefact class (but without OO encapsulation and behaviour) might be to define the dimensions as a row type:

CREATE TABLE artefact (
   id INTEGER NOT NULL,
   ...
   dimensions ROW (
   length real,
   width real,
   depth real ),
   ...
   );

Individual dimensions could then be retrieved as follows:

SELECT dimensions.length 
   FROM artefact
   WHERE id=123;

Structured types. SQL:1999 provides an OO-inspired approach to data types through its structured types. These may be composed of any other types, have their behaviour implemented as user-defined methods and participate in inheritance hierarchies. For example, the Point type from the previous section might be specified as:

CREATE TYPE point AS (
   x real,
   y real
   )
   INSTANCE METHOD distance (from_p point) RETURNS REAL;

The child Point3D would then be defined as:

CREATE TYPE point3d UNDER point AS (
   z real 
   )
   INSTANCE METHOD distance (from_p point3d) RETURNS REAL;

Details of how the instance methods are implemented are not shown here, but they can be written in SQL or in conventional programming languages such as C or Java.

In addition to an enhanced type system, SQL:1999 supports the concept of an 'active database' through a mechanism known as a trigger. Triggers are a form of rule that instructs the DBMS to perform certain actions whenever a particular event occurs. For example, the following statement creates a trigger that inserts a record into a log table whenever there is an update to the description column of a context table. The log table receives records containing the identity of the user making the changes together with the old and new values of the description field.

CREATE TRIGGER log_context_description
   BEFORE UPDATE OF description ON context
      REFERENCING OLD ROW as oldrow
                  NEW ROW as newrow
      FOR EACH ROW
         INSERT INTO context_description_log
         VALUES ( CURRENT_USER,
                  oldrow.description,
                  newrow.description)

Triggers can be defined to fire on any database operation — select, insert, update or delete — and can perform arbitrarily complex operations. Typically the operations are relatively simple SQL statements as in the example, but it is also possible to execute 'stored procedures', program fragments similar to those used as methods for user-defined data types.

One of the major benefits to arise from the development of OO and OR DBMS lies in the simplification of application programs that manipulate the stored data. In fact, other than the extension of the range of 'built-in' data types to include arbitrary length binary data and both binary and textual large objects, these modern systems do little that could not have been achieved with an early Relational system and, possibly quite complex, application programs. However, by permitting the semantics or behaviour of 'user-defined' types to be incorporated in the DBMS, they enable complex objects to be manipulated directly by query language statements. Previously, the individual elements of such objects needed to be retrieved from the database and manipulated by purpose-written program code. This both increases the range of operations that can be performed without specialised programming, and reduces program size and complexity where it is necessary to develop special-purpose code.

Some of these capabilities have existed in various DBMS for over a decade, so it is perhaps surprising that they are not already more widely used (Ryan 1992). Until quite recently, database developers have been reluctant to use OO design methods and to employ many of these newer capabilities. One reason for this has been the, often considerable, differences in the syntax of the SQL extensions used by various DBMS vendors. This has always limited the portability of code between systems and made the task of learning and understanding how to use them quite difficult. The functionality of the above examples can presently be achieved in several different systems but, in most cases, the exact syntax of the commands would need to be changed. However, as development of the SQL:1999 standard has approached completion, manufacturers have rapidly moved towards compliance with the standard. During the next few years a much greater degree of standardisation in these enhanced capabilities can be expected.