B.4.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.

Example:

The library model maps to the following relational design:

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

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

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

• • • 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.

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