Nested Relations

Nested Relations

Relations extended by point (1) of "The Object-Relational Model" are sometimes called "nested relations". In the nested-relational model, we allow attributes of relations to have a type that is not atomic: particularly, a type can be a relation schema. Thus, there is a convenient, recursive definition of the types of attributes and the types (schemas) of relations:

BASIS: An atomic type (integer, real, string, etc.) can be the type of an attribute.

INDUCTION: A relation's type can be any schema consisting of names for one or more attributes, and any legal type for each attribute. Furthermore, a schema can also be the type of any attribute.

In our discussion of the relational model, we did not specify the particular atomic type associated with each attribute, because the distinctions among integers, reals, strings, and so on had little to do with the issues discussed, such as functional dependencies and normalization. We shall continue to avoid this distinction, but when describing the schema of a nested relation, we must indicate which attributes have relation schemas as types. To do so, we shall treat these attributes as if they were the names of relations and follow them by a parenthesized list of their attributes. Those attributes, in turn, may have associated lists of attributes, down for as many levels as we wish.

Example (a): Let us design a nested relation schema for stars that integrates within the relation an attribute movies, which will be a relation representing all the movies in which the star has appeared. The relation schema for attribute movies will contain the title, year, and length of the movie. The relation schema for the relation Stars will contain the name, address, and birthdate, as well as the information found in movies. Furthermore, the address attribute will have a relation type with attributes street and city. We can record in this relation many addresses for the star. The schema for Stars can be written:

Stars(name, address(street, city), birthdate,
     movies(title, year, length))

An example of a possible relation for nested relation Stars is shown in Figure (a). We see in this relation two tuples, one for Carrie Fisher and one for Mark Hamill. The values of components are abbreviated to conserve space, and the dashed lines separating tuples are only for convenience and have no notational significance.

A nested relation for stars and their movies

In the Carrie Fisher tuple, we see her name, an atomic value, followed by a relation for the value of the address component. That relation has two attributes, street and city, and there are two tuples, corresponding to her two houses. Next comes the birthdate, another atomic value. Finally, there is a component for the movies attribute; this attribute has a relation schema as its type, with components for the title, year, and length of a movie. The relation for the movies component of the Carrie Fisher tuple has tuples for her three best-known movies.

The second tuple, for Mark Hamill, has the same components. His relation for address has only one tuple, because in our imaginary data, he has only one house. His relation for movies looks just like Carrie Fisher's because their best-known movies happen, by coincidence, to be the same. Note that these two relations are two different tuple-components. These components happen to be the same, just like two components that happened to have the same integer value, e.g., 124.