Object-Relational Databases:Querying with Complex Types

Querying with Complex Types

In this section, we present extensions of the SQL query language to deal with complex types. Let us start with a simple example: Find the title and the name of the publisher of each book. This query carries out the task:

select title, publisher.name

from books

Notice that the field name of the composite attribute publisher is referred to by a dot notation.

Path Expressions

References are dereferenced in SQL:1999 by the −> symbol. Consider the departments table defined earlier. We can use this query to find the names and addresses of the heads of all departments:

select head−>name, head−>address

from departments

An expression such as “head−>name” is called a path expression.

Since head is a reference to a tuple in the people table, the attribute name in the preceding query is the name attribute of the tuple from the people table. References can be used to hide join operations; in the preceding example, without the references, the head field of department would be declared a foreign key of the table people. To find the name and address of the head of a department, we would require an explicit join of the relations departments and people. The use of references simplifies the query considerably.

Collection-Valued Attributes

We now consider how to handle collection-valued attributes. Arrays are the only collection type supported by SQL:1999, but we use the same syntax for relation-valued attributes also. An expression evaluating to a collection can appear anywhere that a relation name may appear, such as in a from clause, as the following paragraphs illustrate. We use the table books which we defined earlier.

If we want to find all books that have the word “database” as one of their key- words, we can use this query:

Note that we have used unnest(keyword-set) in a position where SQL without nested relations would have required a select-from-where subexpression.

If we know that a particular book has three authors, we could write:

Now, suppose that we want a relation containing pairs of the form “title, author- name” for each book and each author of the book. We can use this query:

Since the author-array attribute of books is a collection-valued field, it can be used in a from clause, where a relation is expected.

Nesting and Unnesting

The transformation of a nested relation into a form with fewer (or no) relation-valued attributes is called unnesting. The books relation has two attributes, author-array and keyword-set, that are collections, and two attributes, title and publisher, that are not. Suppose that we want to convert the relation into a single flat relation, with no nested relations or structured types as attributes. We can use the following query to carry out the task:

The variable B in the from clause is declared to range over books. The variable A is declared to range over the authors in author-array for the book B, and K is declared to range over the keywords in the keyword-set of the book B. Figure 9.1 (in Section 9.1) shows an instance books relation, and Figure 9.2 shows the 1NF relation that is the result of the preceding query.

The reverse process of transforming a 1NF relation into a nested relation is called nesting. Nesting can be carried out by an extension of grouping in SQL. In the normal use of grouping in SQL, a temporary multiset relation is (logically) created for each group, and an aggregate function is applied on the temporary relation. By returning the multiset instead of applying the aggregate function, we can create a nested relation. Suppose that we are given a 1NF relation flat-books, as in Figure 9.2. The following query nests the relation on the attribute keyword:

The result of the query on the books relation from Figure 9.2 appears in Figure 9.4. If we want to nest the author attribute as well, and thereby to convert the 1NF table

The system executes the nested subqueries in the select clause for each tuple generated by the from and where clauses of the outer query. Observe that the attribute O.title from the outer query is used in the nested queries, to ensure that only the correct sets of authors and keywords are generated for each title. An advantage of this approach is that an orderby clause can be used in the nested query, to generate results in a desired order. An array or a list could be constructed from the result of the nested query. Without such an ordering, arrays and lists would not be uniquely determined.

We note that while unnesting of array-valued attributes can be carried out in SQL:1999 as shown above, the reverse process of nesting is not supported in SQL:1999.

The extensions we have shown for nesting illustrate features from some proposals for extending SQL, but are not part of any standard currently.