12.4: Mapping Supertypes and Subtypes to a Relational Database

In chapter 8 we covered rules to be used when an ERD is mapped to a relational database. In this section we add rules for mapping supertypes and subtypes to relations. There are three basic options a designer considers when mapping these structures to a database:

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.

We use two examples to exhibit the mapping options; one where total participation is specified for the supertype and the other where participation is optional.
The previous library model is modified to show that an item can be out on loan to a member, and that one of the subtypes, video, is produced by a producer:

Figure \(\PageIndex{1}\): Previous library model.

A university model where a person may be a student and/or an employee, and where students declare a major subject area:

Figure \(\PageIndex{2}\): University model.

Regardless of the option selected for hierarchies, the rules for mapping an ERD to a relational database discussed previously (Chapter 8) still apply. We must apply rules regarding relationships and attributes consistently. For example, if any entity type in a hierarchy is involved in a one-to-many relationship we must ensure the proper use of foreign keys.

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.

Example \(\PageIndex{1}\)

The library model maps to the following relational design:

Figure \(\PageIndex{3}\): Library model mapping.

Note the foreign keys:

• Item has a foreign key referencing Member
• Video has a foreign key referencing Producer
• Each of Video, Book, and Magazine has a foreign key referencing Item. If a row exists in Video, Book, or Magazine then there must be a corresponding row in Item.

A sample database is presented on the next page.

The tables are shown here with sample data. Note that

• each row of Video, Book, and Magazine has a related row in Item
• some items are out on loan to a member
• each video has a producer

Figure \(\PageIndex{4}\): Tables with sample data.

In the relationships diagram note the one-to-one relationships between the supertype relation and each of its subtype relations:

Figure \(\PageIndex{5}\): Relationships diagram.

Example \(\PageIndex{2}\)

Now consider the university model. The relational design for this mapping option:

Figure \(\PageIndex{6}\): University model mapping.

Since subtyping is optional in the university model there can be a row in Person with no corresponding row in Employee or Student. A person does not have to exist as one of the subtypes.

Note the foreign keys:

• Student has a foreign key referencing SubjectArea
• Employee and Student have foreign keys referencing Person. If a row exists in Employee or Student then a corresponding row must exist in Person.

On the following page we show tables with some sample data and the relationships diagram.

A sample database is presented below. Note that person 2 is both a student and an employee, and that person 4 is neither a student nor an employee.

Figure \(\PageIndex{7}\): Sample database.

In the relationships diagram note the relationships are one-to-one between the supertype relation and each of its subtype relations:

Figure \(\PageIndex{8}\): Relationships diagram.

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 \(\PageIndex{3}\)

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:

Figure \(\PageIndex{9}\): Library model 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 \(\PageIndex{4}\)

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:

Figure \(\PageIndex{10}\): University model mapping.

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.

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.

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

Example \(\PageIndex{5}\)

The library model maps to the following design

Figure \(\PageIndex{11}\): Library model mapping.

In the Item relation the itemType attribute indicates if the row represents a video, a magazine, or a book. The memberId may have a value if the item is out on loan. producerId can only have a value if itemType is “video”.

Example \(\PageIndex{6}\)

When mapping a hierarchy to a single relation for the university model the designer should include discriminator attributes that are boolean-valued with one discriminator attribute per subtype. Applying this option to the university model we have:

Figure \(\PageIndex{12}\): University model mapping with discriminator attributes.

With this database each person is stored at most once in the database; there is no duplicated data as with the previous mapping option.

If a person is neither an employee nor a student then the only attributes that can have values are: personId, employeeFlag, studentFlag, first and last – the others must be null. The values of employeeFlag and studentFlag would be false.

---

Exercises

Exercise \(\PageIndex{1}\)

Consider the database designs illustrated in this appendix. Implement one or more of these and populate with data.

Exercise \(\PageIndex{2}\)

Consider the two designs used in the examples of this appendix. Combine these two designs by replacing Member with the Person hierarchy. Illustrate the relational structures when the model is mapped to a database. Choose mapping options for the hierarchies.

Exercise \(\PageIndex{3}\)

Consider the design you created in exercise 2 but modify the one-to-many borrows relationship to be a many-to-many with attributes dateBorrowed and dateReturned where dateBorrowed is a discriminator for the relationship. Recall this discriminator is not the same as the discriminators suggested for mapping supertypes and subtypes.

Note that this modification to the library example will allow history to be recorded for the borrowing of items.

Exercise \(\PageIndex{4}\)

For exercise \(\PageIndex{3}\) create the database and populate the database with sample data.

Exercise \(\PageIndex{5}\)

Create an ERD for a service station business that provides goods and services to its customers. Typically a customer comes in with their vehicle and requests certain work to be performed. For example a customer may request an oil change and for a new set of four tires to be provided and installed.

The work items that can be performed or supplied can be of two types: a service (such as the oil change) and actual physical items (such as litres of oil). There will be several services that can be performed such as tire installation, changing oil, or fixing a flat tire. Each of these will have some cost to be charged to a customer. There are many concrete items that are supplied and charged to a customer such as fan belts, litres of oil, or tires – these are things that are kept in inventory. Consider creating a hierarchy for products (goods / services); make up reasonable attributes.

This service station has customers that fall into two groups: some are private individuals and others are businesses. Individuals will have a first name, last name, address and phone number. A business will have a business name, address, phone number and a contact person who has a first name and last name. Consider creating a hierarchy for customers.

The service station needs to keep track of all the goods and services it provides to its customers so that it has a historical record and knows what it has charged to each customer. Each visit to the service station by a customer will generate a work order that keeps track of the work that was done for the customer’s vehicle. Vehicles have license plate numbers, and other attributes to describe them (make, model, colour, ...). For each visit of a customer to the station the system needs to know the date the visit occurred, the details of the work performed and goods provided, and the total charge to the customer.