CHAPTER 3:
THE ENHANCED E-R MODEL
Modern Database Management
11th Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
© 2013 Pearson Education, Inc. Publishing as Prentice Hall
1
OBJECTIVES
Define terms
Understand use of supertype/subtype relationships
Understand use of specialization and generalization techniques
Specify completeness and disjointness constraints
Develop supertype/subtype hierarchies for realistic business
situations
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 2
Basic E-R Model
pushing 40 years old
complex data relationships and new database technology have
outgrown it in some respects
Enhanced E-R Model
a response to the shortcomings of the basic E-R model
not universally agreed upon in some respects
introduced the supertype/subtype relationship
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall
Specialization Supertype
Based on
Object Inheritance
Oriented
Programming
Subtype
Generalization
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 4
SUPERTYPES AND SUBTYPES
Enhanced ER model: extends original ER model with
new modeling constructs
Subtype: A subgrouping of 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
Attribute Inheritance:
Subtype entities inherit values of all attributes of the
supertype
An instance of a subtype is also an instance of the supertype
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 5
Generalization:
The process of defining a more general entity type from a set
of more specialized entity types. BOTTOM-UP
Specialization:
The process of defining one or more subtypes of the supertype.
TOP-DOWN
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 6
Figure 3-1 Basic notation for supertype/subtype notation
a) EER
notation
specialization circle
(IsA relationship)
Inheritance symbol
(subset symbol)
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 7
Is qualified by
Completeness Constraints
Independent
Partial Constraints constraints
Completeness Constraints:
Total Specialization (double line)
Partial Specialization (single line)
Partial Constraints
Disjoint ( d )
Overlap ( o )
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 8
Partial
Completeness
Constraints
Constraints
Total Specialization
A superclass entity belongs to Overlap
a subclass in hierarchy An instant of superclass
belongs to more than one
subclass
Ex: student works in University
Partial
Specialization
A superclass entity
does not belong to any
subclass Disjoint
Ex: Stack Holder An instant of
superclass
belongs to one
subclass
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 9
RELATIONSHIPS AND SUBTYPES
1. Relationships at the supertype level indicate that all
subtypes will participate in the relationship
2. Relationship at the subtype level are unique to that
subtype.
Case 1
Is carried for relationship Case2
exists at super class
Patient, therefore all Is assigned relationship
subtypes participate in exists only for Resident
that relationship Patient subtype entity
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 10
Use this type of relationship when either (or both) of the
following are present:
1. When there are attributes that apply to some (but not all) of
the instances of an entity type
2. When instances of a subtype participate in a relationship
unique to that subtype
Case 1
All Patients have
attributes Name and
admission date
Case 2
While Outpatient and
Resident Patient Resident Patient has
have two different his own relationship
attributes with Entity Bed
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall
The property by which subtype entities inherit values of
all attributes of the supertype.
This important property makes it unnecessary to include
supertype attributes redundantly with the subtypes.
Faculty entity has attributes
•SSN, Name, Address, Gender,
Date Of Birth from Person entity
•And attributes Salary and Date
Hired from Employee Entity
•And attribute Rank from Faculty
Entity
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall
Subtype Discriminator: An attribute of
supertype whose values determine the target
subtype(s)
Disjoint – a simple attribute with alternative values
to indicate the possible subtypes
Overlapping – 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, Inc. Publishing as Prentice Hall 13
Subtype discriminator is an attribute of supertype
whose value determines to which subtype an
instance belongs
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall
Figure 3-8 Introducing a subtype discriminator (disjoint rule)
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 15
Figure 3-9 Subtype discriminator (overlap rule)
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 16
EER diagrams are difficult to read when there are
too many entities and relationships.
Solution: Group entities and relationships into
entity clusters.
Entity cluster: Set of one or more entity types and
associated relationships grouped into a single
abstract entity type
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 17
Figure 3-13a
Possible entity
clusters for Pine
Valley Furniture in
Microsoft Visio
Related
groups of
entities could
become
clusters
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 18
Figure 3-13b EER diagram of PVF entity clusters
More readable,
isn’t it?
Chapter 3 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 19
