CHAPTER 3:

THE ENHANCED E-R MODEL

Modern Database Management

12th Edition, International Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi

© 2013 Pearson Education

1
 OBJECTIVES
 Define terms
 Understand the use of supertype/subtype relationships
 Understand the use of specialization and generalization
techniques
 Specify completeness and disjointness constraints
 Develop supertype/subtype hierarchies for realistic business
situations
 Develop entity clusters
 Explain universal (packaged) data model
 Describe special features of data modeling project using
packaged data model

Chapter 3 © 2013 Pearson Education 2

 SUPERTYPE/SUBTYPE RELATIONSHIPS
 Supertype/Subtype Relationships
 Enable us to model a general entity type (called
supertype) and then subdivide it into several specialized
entity types (called subtypes)
 A new modeling construct for extending the original ER
model. The extended result model is the Enhanced ER
Model
 Example 1:
 Supertype : Car
 Subtypes : sedan, sports car, coupe, and so on.
 Example 2:
 Supertype : Vechicle
 Subtypes : Car, Motorcycle, Bike, Ship, and so on.
Chapter 3 © 2013 Pearson Education 3
 SUPERTYPES AND SUBTYPES
 Subtype
A subgrouping of the entities in an entity type that has
attributes distinct from those in other subgroupings
 Supertype
A generic entity type that has a relationship with one or more
subtypes
 Example
Supertype : Student
Subtypes : Undergraduate student, Graduate students
 Attribute Inheritance
An instance of a subtype is also an instance of the supertype
A subtype entity inherit all attributes of the supertype

Chapter 3 © 2013 Pearson Education 4

 Figure 3-1 Basic notation for supertype/subtype notation

a) EER

notation

Chapter 3 © 2013 Pearson Education

5
 Figure 3-1 Basic notation for supertype/subtype notation (cont.)

b) Microsoft Visio
Notation

Different modeling tools may have different notation for the same
modeling constructs.

Chapter 3 © 2013 Pearson Education

6
 DATA MODELING WITH MANY SUBTYPES
 Case : An organization with three basic types of employees
 Hourly employees Attributes
 Salaried employees of three
 Contract employees basic types
 Choices for developing a conceptual data model
 Define a single entity type
 Advantage : simple
 Disadvantage : an entity instance contain all the attributes for the
three types of employees. Some of the attributes are null.
 Define a separate entity type for each of the entities
 Advantage : simple
 Disadvantage : fails to exploit common properties of employees.
 Define a supertype with three subtypes

Chapter 3 © 2013 Pearson Education 7

 Figure 3-2 Employee supertype with three subtypes
(An example of data model with many subtypes)
All EMPLOYEE subtypes
will have employee
number, name, address,
and date hired

Each EMPLOYEE subtype

will also have its own
attributes

Chapter 3 © 2013 Pearson Education

8
 RELATIONSHIPS AND SUBTYPES
 Relationship at the supertype level
indicate that all subtypes will participate in the
relationship
 Relationship at the subtype level
The instances of a subtype may participate in a
relationship unique to that subtype.

Chapter 3 © 2013 Pearson Education 9

Figure 3-3 Supertype/subtype relationships in a hospital
relationship at supertype level

relationship at subtype level

Chapter 3 © 2013 Pearson Education

10
GENERALIZATION AND SPECIALIZATION
 Generalization
The process of defining a more general entity type from a set of
more specialized entity types.
(BOTTOM-UP process for creating a supertype/subtype relationship)
The common attributes are put in the supertype.
 Specialization
The process of defining one or more subtypes from the supertype
and forming supertype/subtype relationship.
(TOP-DOWN process for creating a supertype/subtype relationship)
Each subtype is formed with some distinguished attributes.

Chapter 3 © 2013 Pearson Education 11

 Figure 3-4 Example of generalization process
a) Three entity types: CAR, TRUCK, and MOTORCYCLE

1. What are the common attributes for all these types of vehicles ?
2. What is the supertype/subtype relationship ?

Chapter 3 © 2013 Pearson Education

12
 Figure 3-4 Example of generalization process
b) Generalization to VEHICLE supertype

So we put
the shared
attributes in
a supertype

Note
 No subtype for motorcycle, since it has no unique attributes
 The only attributes of MOTOTCYCLE are those common to all vehicles
Chapter 3 © 2013 Pearson Education
13
 Figure 3-5 Example of specialization process
a) Entity type PART

Only applies to
manufactured parts

Applies only to purchased parts

(a multivalued composite attribute)

1. What are the attributes specific to each of subtypes?

2. What is the supertype/subtype relationship ?
Chapter 3 © 2013 Pearson Education
14
 Figure 3-5 Example of specialization process
b) Specialization to MANUFACTURED PART and PURCHASED PART

Created 2
subtypes

Note : the multivalued composite attribute was replaced

by an associative entity relationship to another entity
Chapter 3 © 2013 Pearson Education
15
 CONSTRAINTS IN
SUPERTYPE/SUBTYPE RELATIONSHIPS
 Completeness Constraints
Whether an instance of a supertype must also be a
member of at least one subtype
 Total Specialization Rule: Yes (double line)

Each entity instance of the supertype must be a member of

some subtype in the relationship.
 Partial Specialization Rule: No (single line)
An entity instance of the supertype is allowed not to belong
to any subtype.

Chapter 3 © 2013 Pearson Education 16

 Figure 3-6 Examples of completeness constraints
a) Total specialization rule

Chapter 3 © 2013 Pearson Education

17
 Figure 3-6 Examples of completeness constraints (cont.)
b) Partial specialization rule

A vehicle can be a motorcycle

Chapter 3 © 2013 Pearson Education

18
CONSTRAINTS IN
SUPERTYPE/SUBTYPE RELATIONSHIPS
 Disjointness Constraints
Whether an instance of a supertype may
simultaneously be a member of two (or more) subtypes
 Disjoint Rule:
An instance of the supertype can be only a member of ONE
of the subtypes
 Overlap Rule:
An instance of the supertype could be a member of more
than one of the subtypes

Chapter 3 © 2013 Pearson Education 19

 Figure 3-7 Examples of disjointness constraints
a) Disjoint rule

PATIENT

RESIDENCE
OUTPATIENT PATIENT

Chapter 3 © 2013 Pearson Education

20
 Figure 3-7 Examples of disjointness constraints (cont.)
b) Overlap rule

PART

M PART P PART

PART
When does this situation happen ?
M PART P PART
Chapter 3 © 2013 Pearson Education
21
 CONSTRAINTS IN
SUPERTYPE/SUBTYPE RELATIONSHIPS
 Subtype Discriminator
An attribute of the supertype whose values determine
the target subtype(s)
 For disjoint subtypes

a simple attribute with different values to indicate the possible

subtypes
 For overlapping subtypes
a composite attribute whose subparts pertain to different
subtypes. Each subpart contains a Boolean value to indicate
whether or not the instance belongs to the associated subtype

Chapter 3 © 2013 Pearson Education 22

Figure 3-8 Introducing a subtype discriminator (disjoint rule)

Chapter 3 © 2013 Pearson Education

23
 Figure 3-9 Subtype discriminator (overlap rule)

1. Three cases:
• Y, Y
• Y, N
• N, Y
2. Why 3
cases?

Chapter 3 © 2013 Pearson Education

24
 Figure 3-10 Example of supertype/subtype hierarchy

What is the situation for each case?

• completeness constraint
• disjointness constraint
• subtype discriminator design
Case 1

Case 2 Case 3

Chapter 3 © 2013 Pearson Education

25
Example of supertype/subtype hierarchy

26
Example of supertype/subtype hierarchy

27
 ENTITY CLUSTERS
 EER diagrams are difficult to read when
there are too many entities and
relationships.
 Solution:
 Group entities and relationships into entity clusters.
 Entity cluster
A set of one or more entity types and associated relationships
grouped into a single abstract (virtual) entity type

Chapter 3 © 2013 Pearson Education 28

Figure 3-13a
Possible entity
clusters for Pine
Valley Furniture in
Microsoft Visio

Related groups
of entities could
become entity
clusters

Chapter 3 © 2013 Pearson Education 29

 Figure 3-13b EER diagram of entity clusters
for Pine Valley Furniture

More readable,
isn’t it?

Chapter 3 © 2013 Pearson Education

30
 Figure 3-14 Manufacturing entity cluster

Detail for a single cluster

Chapter 3 © 2013 Pearson Education

31
Copyright © 2013 Pearson Education

32