B.4.2: Relations for Bottom-Most Entity Types

In this case relations are created for only entity types that are at the “bottom” of the hierarchy. There are no relations created for a supertype. Important points here are that:

• • • All relations derived from entity types in the same hierarchy will have the same primary key.
    • No primary key value can be repeated (We have not seen how to handle this in MS Access. Further study of relational systems can include techniques that automate the checking for this kind of integrity constraint.)
    • Attributes of a supertype must be included in each of its subtype relations.

Example:

For the library model, since there is total participation in subtyping this option works well. Every item will be stored in a relation, and each item is stored exactly once. The resulting design:

Note the foreign keys:

• • • Because there is no Item relation, each of Video, Book, and Magazine have foreign keys referencing Member.
    • Video is the only relation with a foreign key referencing Producer.

An issue the designer should be aware of is that callNumbers across the three relations must be unique (call number is the primary key of Item). Further study of database systems is needed to know how this rule can be enforced.

It is left as an exercise for the student to create a database with sample data.

Example:

Consider the university model. This approach (creating relations for bottom- most entity types) is not suitable for the university model because of the overlapping subtypes and because the participation in subtyping is not total. Applying the option, we have:

If an entity exists in more than one subtype, then such an entity will have data stored redundantly in the database. In the design above if a person is both an employee and a student then that person’s first and last names would be stored twice (in two different relations).

The Employee and Student relations are not sufficient to store Person data. The participation is optional and so a person may exist who is neither an employee nor a student; in such a case the data for the person cannot be stored!

It is left as an exercise for the student to create a database with sample data.