Consider the EER diagram for a car dealer in the figure below. Map the EER schema into a set of relations. For the VEHICLE to CAR/ TRUCK/ SUV generalization, consider the four options presented in Section 9.2.1 and show the relational schema design under each of those options.
Mapping of Specialization or Generalization
There are several options for mapping a number of subclasses that together form a specialization (or alternatively, that are generalized into a superclass), such as the {SECRETARY, TECHNICIAN, ENGINEER} subclasses of EMPLOYEE in Figure 8.4. We can add a further step to our ER-to-relational mapping algorithm from, which has seven steps, to handle the mapping of specialization. Step 8, which follows, gives the most common options; other mappings are also possible. We dis- cuss the conditions under which each option should be used. We use Attrs(R) to denote the attributes of relation R, and PK(R) to denote the primary key of R. First we describe the mapping formally, then we illustrate it with examples.
Step 8: Options for Mapping Specialization or Generalization. Convert each specialization with m subclasses {S1, S2, ..., Sm} and (generalized) superclass C, where the attributes of C are {k, a1, ...an} and k is the (primary) key, into relation schemas using one of the following options:
? Option 8A: Multiple relations-superclass and subclasses. Create a rela- tion L for C with attributes Attrs(L) = {k, a1, ..., an} and PK(L) = k. Create a relation Li for each subclass Si, 1 ² i ² m, with the attributes Attrs(Li) = {k} ∪ {attributes of Si} and PK(Li) = k. This option works for any specialization (total or partial, disjoint or overlapping).
? Option 8B: Multiple relations-subclass relations only. Create a relation Li for each subclass Si, 1 ² i ² m, with the attributes Attrs(Li) = {attributes of Si} ∪ {k, a1, ..., an} and PK(Li) = k. This option only works for a specialization whose subclasses are total (every entity in the superclass must belong to (at least) one of the subclasses). Additionally, it is only recommended if the spe- cialization has the disjointedness constraint (see Section 8.3.1).If the special- ization is overlapping, the same entity may be duplicated in several relations.
? Option 8C: Single relation with one type attribute. Create a single relation L with attributes Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attrib- utes of Sm} ∪ {t} and PK(L) = k. The attribute t is called a type (or
Mapping EER Model Constructs to Relations 295
discriminating) attribute whose value indicates the subclass to which each tuple belongs, if any. This option works only for a specialization whose sub- classes are disjoint, and has the potential for generating many NULL values if many specific attributes exist in the subclasses.
¦ Option 8D: Single relation with multiple type attributes. Create a single relation schema L with attributes Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attributes of Sm} ∪ {t1, t2, ..., tm} and PK(L) = k. Each ti, 1 ² i ² m, is a Boolean type attribute indicating whether a tuple belongs to subclass Si. This option is used for a specialization whose subclasses are overlapping (but will also work for a disjoint specialization).