Object-Relational Databases:Complex Types

Complex Types

Nested relations are just one example of extensions to the basic relational model; other nonatomic data types, such as nested records, have also proved useful. The object-oriented data model has caused a need for features such as inheritance and references to objects. With complex type systems and object orientation, we can rep- resent E-R model concepts, such as identity of entities, multivalued attributes, and generalization and specialization directly, without a complex translation to the relational model.

In this section, we describe extensions to SQL to allow complex types, including nested relations, and object-oriented features. Our presentation is based on the SQL:1999 standard, but we also outline features that are not currently in the standard but may be introduced in future versions of SQL standards.

Collection and Large Object Types

Consider this fragment of code.

This table definition differs from table definitions in ordinary relational databases, since it allows attributes that are sets, thereby permitting multivalued attributes of E-R diagrams to be represented directly.

Sets are an instance of collection types. Other instances of collection types include arrays and multisets (that is, unordered collections, where an element may occur multiple times). The following attribute definitions illustrate the declaration of an array:

author-array varchar(20) array [10]

Here, author-array is an array of up to 10 author names. We can access elements of an array by specifying the array index, for example author-array[1].

Arrays are the only collection type supported by SQL:1999; the syntax used is as in the preceding declaration. SQL:1999 does not support unordered sets or multisets, although they may appear in future versions of SQL.1

Many current-generation database applications need to store attributes that can be large (of the order of many kilobytes), such as a photograph of a person, or very large (of the order of many megabytes or even gigabytes), such as a high-resolution medical image or video clip. SQL:1999 therefore provides new large-object data types for character data (clob) and binary data (blob). The letters “lob” in these data types stand for “Large OBject”. For example, we may declare attributes

book-review clob(10KB)

image blob(10MB)

movie blob(2GB))

Large objects are typically used in external applications, and it makes little sense to retrieve them in their entirety by SQL. Instead, an application would usually retrieve a “locator” for a large object and then use the locator to manipulate the object from the host language. For instance, JDBC permits the programmer to fetch a large object in small pieces, rather than all at once, much like fetching data from an operating system file.

Structured Types

Structured types can be declared and used in SQL:1999 as in the following example:

The first statement defines a type called Publisher, which has two components: a name and a branch. The second statement defines a structured type Book, which contains a title, an author-array, which is an array of authors, a publication date, a publisher (of type Publisher), and a set of keywords. (The declaration of keyword-set as a set uses our extended syntax, and is not supported by the SQL:1999 standard.) The types illustrated above are called structured types in SQL:1999.

Finally, a table books containing tuples of type Book is created. The table is similar to the nested relation books in Figure 9.1, except we have decided to create an array of author names instead of a set of author names. The array permits us to record the order of author names.

Structured types allow composite attributes of E-R diagrams to be represented directly. Unnamed row types can also be used in SQL:1999 to define composite at- tributes. For instance, we could have defined an attribute publisher1 as

The variable self refers to the structured type instance on which the method is invoked. The body of the method can contain procedural statements, which we shall study in Section 9.6.

Creation of Values of Complex Types

In SQL:1999 constructor functions are used to create values of structured types. A function with the same name as a structured type is a constructor function for the structured type. For instance, we could declare a constructor for the type Publisher like this:

We can then use Publisher(’McGraw-Hill’, ’New York’) to create a value of the type Publisher.

SQL:1999 also supports functions other than constructors, as we shall see in Section 9.6; the names of such functions must be different from the name of any structured type.

Note that in SQL:1999, unlike in object-oriented databases, a constructor creates a value of the type, not an object of the type. That is, the value the constructor creates has no object identity. In SQL:1999 objects correspond to tuples of a relation, and are created by inserting a tuple in a relation.

By default every structured type has a constructor with no arguments, which sets the attributes to their default values. Any other constructors have to be created explicitly. There can be more than one constructor for the same structured type; although they have the same name, they must be distinguishable by the number of arguments and types of their arguments.

An array of values can be created in SQL:1999 in this way:

array[’Silberschatz’, ’Korth’, ’Sudarshan’]

We can construct a row value by listing its attributes within parentheses. For instance, if we declare an attribute publisher1 as a row type (as in Section 9.2.2), we can construct this value for it:

(’McGraw-Hill’, ’New York’)

without using a constructor.

We create set-valued attributes, such as keyword-set, by enumerating their elements within parentheses following the keyword set. We can create multiset values just like set values, by replacing set by multiset.3

Thus, we can create a tuple of the type defined by the books relation as:

(’Compilers’, array[’Smith’, ’Jones’], Publisher(’McGraw-Hill’, ’New York’), set(’parsing’, ’analysis’))

Here we have created a value for the attribute Publisher by invoking a constructor function for Publisher with appropriate arguments.

If we want to insert the preceding tuple into the relatio n books, we could execute the statement insert into books values(’Compilers’, array[’Smith’, ’Jones’], Publisher(’McGraw-Hill’, ’New York’), set(’parsing’, ’analysis’))