Module 4 - Enhanced ER Model

Database Management Systems
Module 4
The Enhanced ER Model (EERD)
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
• Attribute Inheritance:
– Subtype entities inherit values of all attributes of
the supertype
– An instance of a subtype is also an instance of the
supertype
Basic notation for supertype/subtype notation
a) EER
notation
Basic notation for supertype/subtype notation (cont.)
b) Microsoft
Visio
Notation
Different modeling tools may have different notation for the same
modeling constructs
Employee supertype with three subtypes
All employee subtypes

will have emp nbr, name,
address, and date-hired
Each employee subtype

will also have its own
attributes
Relationships and Subtypes
• Relationships at the supertype level
indicate that all subtypes will participate in
the relationship
• The instances of a subtype may
participate in a relationship unique to that
subtype. In this situation, the relationship
is shown at the subtype level
Supertype/subtype relationships in a hospital
Both
outpatients
and resident
patients are
cared for by
a responsible
physician
Only resident patients are

assigned to a bed
Generalization and Specialization
• 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
and forming supertype/subtype
relationships. TOP-DOWN
Example of generalization
a) Three entity types: CAR, TRUCK, and MOTORCYCLE
All these types of vehicles have common attributes

Example of generalization (cont.)
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

Example of specialization
a) Entity type PART
Only applies to
manufactured parts
Applies only to purchased parts

Example of specialization (cont.)
b) Specialization to MANUFACTURED PART and PURCHASED PART
Created 2
subtypes
Note: multivalued attribute was replaced by an

associative entity relationship to another entity
Constraints in Supertype
• Completeness Constraints:
Whether an instance of a supertype
must also be a member of at least one
subtype
– Total Specialization Rule: Yes (double line)
– Partial Specialization Rule: No (single line)
Examples of completeness constraints
a) Total specialization rule
A patient must be either

an outpatient or a
resident patient
Figure 4-6 Examples of completeness constraints (cont.)
b) Partial specialization rule
A vehicle
could be a
car, a truck,
or neither
Constraints in Supertype
• 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 ONE of the subtypes
– Overlap Rule: An instance of the supertype
could be more than one of the subtypes
Examples of disjointness constraints
a) Disjoint rule
A patient can either be outpatient

or resident, but not both
Examples of disjointness constraints (cont.)
b) Overlap rule
A part may be both

purchased and
manufactured
Subtype Discriminators
• Subtype Discriminator: An attribute of the

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
Introducing a subtype discriminator (disjoint rule)
A simple attribute with

different possible values
indicating the subtype
Subtype discriminator (overlap rule)
A composite
attribute with
sub-attributes
indicating “yes”
or “no” to
determine
whether it is of
each subtype
Example of supertype/subtype hierarchy
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: Set of one or more entity
types and associated relationships
grouped into a single abstract entity type
Possible entity
clusters for Pine
Valley Furniture in
Microsoft Visio
Related
groups of
entities could
become
clusters
Possible entity clusters for Pine Valley Furniture
in Microsoft Visio
ENTITIES AND DESCRIPTION

SELLING UNIT
Represents the SALESPERSON and SALES TERRITORY entity
types and the Serves relationship.
CUSTOMER
Represents the CUSTOMER entity supertype, its subtypes and
the relationship between supertype and subtypes.
ITEM SALE
Represents the ORDER entity type and the ORDER LINE
associative entity as well as the relationship between them.
ITEM
Represents the PRODUCT LINE and PRODUCT entity types and
the Includes relationship
Possible entity clusters for Pine Valley Furniture
in Microsoft Visio
ENTITIES AND DESCRIPTION

MANUFACTURING
Represents the WORK CENTER and EMPLOYEE supertype
entity and its subtypes as well at the Works In associative
entity and Supervises relationships and the relationship
between the supertype and its subtypes.
MATERIAL
Represents the RAW MATERIAL and VENDOR entity types, the
SUPPLIER subtype, the Supplies associative entity, and the
supertype/subtype relationship between VENDOR and
SUPPLIER.
EER diagram of PVF entity clusters
More readable,
isn’t it?
Manufacturing entity cluster
Detail for a single cluster

Module 4 - Enhanced ER Model

Uploaded by

Copyright:

Available Formats

Module 4 - Enhanced ER Model

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Module 4 - Enhanced ER Model

Uploaded by

Copyright:

Available Formats

Database Management Systems

• Subtype: A subgrouping of the entities in an entity

All employee subtypes

Each employee subtype

Only resident patients are

• Generalization: The process of defining

All these types of vehicles have common attributes

Note: no subtype for motorcycle, since it has no unique attributes

Applies only to purchased parts

Note: multivalued attribute was replaced by an

a) Total specialization rule

A patient must be either

• Disjointness Constraints: Whether

A patient can either be outpatient

A part may be both

• Subtype Discriminator: An attribute of the

A simple attribute with

• EER diagrams are difficult to read when

ENTITIES AND DESCRIPTION

ENTITIES AND DESCRIPTION

Detail for a single cluster

You might also like