Using Null Values to Combine Relations - Comparison of Approaches

Using Null Values to Combine Relations - Comparison of Approaches

Using Null Values to Combine Relations :

There is another way to representing information regarding a hierarchy of entity sets. If we are allowed to use NULL (the null value as in SQL) as a value in tuples, we can handle a hierarchy of entity sets with a single relation. This relation has all the attributes belonging to any entity set of the hierarchy. An entity is then represented by a single tuple. This tuple has NULL in each attribute that is not defined for that entity.

Example :  If we applied this approach to the diagram of figure in "Converting Subclass Structures to Relations". We would create a single relation whose schema is:

Movie(title, year, length, filmType, weapon)

Those movies that are not murder mysteries would have NULL in the weapon component of their tuple. It would also be required to have a relation Voices to connect those movies that are cartoons to the stars performing the voices, as in "An Object-Oriented Approach" example.

Comparison of Approaches :

Each of the three approaches, which we shall refer to as "straight-E/R",  "object-oriented", and "nulls", respectively, have advantages and disadvantages. Here is a list of the principal issues.

1.  It is expensive to answer queries involving several relations, so we would prefer to find all the attributes we needed to answer a query in one relation. The nulls approach uses only one relation for all the attributes, so it has an advantage in this regard. The other two approaches have advantages for different kinds of queries. For example:

(a)  A query like "what films of 1999 were longer than 150 minutes?" can be answered directly from the relation Movies in the  straight-E/R approach of "Converting Subclass Structures to Relations" example . However, in the object-oriented approach of "An Object-Oriented Approach" example, we need to examine Movies, MoviesC, MoviesMM, and MoviesCMM, since a long movie may be in any of these four relations'.

(b)  On the other hand, a query like "what weapons were used in cartoons of over 150 minutes in length?" gives us trouble in the straight- E/R approach. We must access Movies to find those movies of over 150 minutes. We must access Cartoons to confirm that a movie is a cartoon, and we must access MurderMysteries to find the murder weapon. In the object-oriented technique, we have only to access the relation MoviesCMM, where all the information we need will be found.

2.  We would like not to use too many relations. Here again, the nulls method shines, since it requires only one relation. However, there is a difference between the other two methods, since in the straight-E/R approach, we use only one relation per entity set in the hierarchy. In the object-oriented approach, if we have a root and n children (n + 1 entity sets in all), then there are 2n different classes of entities, and we need that many relations.

3.  We would like to minimize space and avoid repeating information. Since the object-oriented method uses only one tuple per entity, and that tuple has elements for only those attributes that make sense for the entity, this approach offers the minimum possible space usage. The nulls approach also has only one tuple per entity, but these tuples are "long"; i.e., they have components for all attributes, whether or not they are appropriate for a given entity. If there are many entity sets in the hierarchy, and there are many attributes among those entity sets, then a large fraction of the space could wind up not being used in the nulls approach. The straight-E/R method has numerous tuples for each entity, but only the key attributes are repeated. Therefore, this method could use either more or less space than the nulls method.