Converting Subclass Structures to Relations

Converting Subclass Structures to Relations

When we have an isa-hierarchy of entity sets, we are presented with numerous choices of strategy for conversion to relations. Recall we assume that:

●  There is a root entity set for the hierarchy,

●  This entity set has a key that serves to identify every entity represented by the hierarchy, and

●  A given entity may have elements that belong to the entity sets of any subtree of the hierarchy, as long as that subtree includes the root.

The principal conversion strategies are:

1. Follow the E/R viewpoint. For each entity set E in the hierarchy, create a relation that includes the key attributes from the root and any attributes belonging to E.

2. Treat entities as objects belonging to a single class. For each possible subtree including the root, create one relation, whose schema includes all the attributes of all the entity sets in the subtree.

3. Use null values. Create one relation with all the attributes of all the entity sets in the hierarchy. Each entity is represented by one tuple, and that tuple has a null value for whatever attributes the entity does not have.

We shall examine each approach in turn.

E/R-Style Conversion

Our first approach is to create a relation for each entity set, as usual. If the entity set E is not the root of the hierarchy, then the relation for E will contain the key attributes at the root, to identify the entity represented by each tuple, plus all the attributes of E. In addition, if E is involved in a relationship, then we use these key attributes to recognize entities of E in the relation corresponding to that relationship.

Note, however, that although we spoke of "isa" as a relationship, it is unlike other relationships, in that it connects components of a single entity, not separate entities. Thus, we do not create a relation for "isa".
The movie hierarchy

Example : Consider the hierarchy of "Subclasses in the E/R Model" figure, which we reproduce here as Figure 1. The relations needed to represent the four different kinds of entities in this hierarchy are:

1. Movies (title, year, length, filmType). This relation was discussed in "From Entity Sets to Relations" example, and every movie is represented by a tuple here.

2. MurderMysteries(title, year, weapon). The first two attributes are the key for all movies, and the last is the lone attribute for the corresponding entity set. Those movies that are murder mysteries have a tuple here as well as in Movies.

3. Cartoons(title, year). This relation is the set of cartoons. It has no attributes other than the key for movies, since the extra information about cartoons is contained in the relationship Voices. Movies that are cartoons have a tuple here as well as in Movies.

Note that the fourth kind of movie - those that are both cartoons and murder mysteries - have tuples in all three relations.

In addition, we shall need the relation Voices(title, year, starName) that corresponds to the relationship Voices between Stars and Cartoons. The last attribute is the key for Stars and the first two form the key for Cartoons. For example, the movie Roger Rabbit would have tuples in all four relations. Its basic information would be in Movies, the murder weapon would appear in MurderMysteries, and the stars that provided voices for the movie would appear in Voices.

Notice that the relation Cartoons has a schema that is a subset of the schema for the relation Voices. In many situations, we would be content to remove a relation such as Cartoons, since it appears not to contain any information beyond what is in Voices. However, there may be silent cartoons in our database. Those cartoons would have no voices, and we would therefore lose the fact that these movies were cartoons.