The database design methodology described in this book includes the use of specialization/
generalization as an optional step (Step 1.6) in building an EER model. The choice to
use this step is dependent on the complexity of the enterprise (or part of the enterprise)
being modeled and whether using the additional concepts of the EER model will help the
process of database design.
In Chapter 11 we described the basic concepts necessary to build an ER model to represent
the Branch user views of the DreamHome case study. This model was shown as
an ER diagram in Figure 11.1. In this section, we show how specialization/generalization
may be used to convert the ER model of the Branch user views into an EER model.
As a starting point, we first consider the entities shown in Figure 11.1. We examine
the attributes and relationships associated with each entity to identify any similarities or
differences between the entities. In the Branch user views’ requirements specification
there are several instances where there is the potential to use specialization/generalization
as discussed below.
(a) For example, consider the Staff entity in Figure 11.1, which represents all members
of staff. However, in the data requirements specification for the Branch user views of
the DreamHome case study given in Appendix A, there are two key job roles mentioned
namely Manager and Supervisor. We have three options as to how we may
best model members of staff. The first option is to represent all members of staff as a
generalized Staff entity (as in Figure 11.1), the second option is to create three distinct
entities Staff, Manager, and Supervisor, and the third option is to represent the Manager
and Supervisor entities as subclasses of a Staff superclass. The option we select is based
on the commonality of attributes and relationships associated with each entity. For
example, all attributes of the Staff entity are represented in the Manager and Supervisor
entities, including the same primary key, namely staffNo. Furthermore, the Supervisor
entity does not have any additional attributes representing this job role. On the other
hand, the Manager entity has two additional attributes, namely mgrStartDate and bonus.
In addition, both the Manager and Supervisor entities are associated with distinct relationships,
namely Manager Manages Branch and Supervisor Supervises Staff. Based on this
information, we select the third option and create Manager and Supervisor subclasses
of the Staff superclass, as shown in Figure 12.5. Note that in this EER diagram, the
subclasses are shown above the superclass. The relative positioning of the subclasses
and superclass is not significant, however; what is important is that the specialization/
generalization triangle points toward the superclass.