Question

What are the different options for converting a subtype-supertype hierarchy to relational tables?

Answer 1

Answer:

A supertype is a generic entity type that has a relationship with one or more subtypes.

A subtype is a sub-grouping of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distinct from other subgroups.

• Subtypes inherit all supertype attributes.

• Subtypes have attributes that are different from other subtypes.

There are three basic options a designer considers when mapping these structures to a relational tables:

1. Create a relation for each entity type in the hierarchy.
2. Create relations for only the bottom-most entity types.
3. Create one relation to represent the whole hierarchy.

1. Relations for All Entity Types

With this option each entity type in a hierarchy is represented by its own relation. Important points here are that

• All relations representing entity types in the same hierarchy have the same primary key.
• The primary key of a subtype relation will also be a foreign key that references its supertype relation.
• Attributes of a supertype (except for the primary key) appear only in the relation that represents the supertype.

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.

3. One Relation Representing the Whole Hierarchy

When this option is applied one relation is created for a complete hierarchy. All attributes appearing in the hierarchy are placed in one relation. Note that the value of a discriminator attribute will enable the user to know easily the subtype of a particular entity. For our example models, when we map a hierarchy to a single relation we obtain very simple relational designs.

Please give thumbsup, or do comment in case of any query. Thanks.