IST 331/504 DB Design,

Implementation and
Mgmt.
CHAPTER 5: ADVANCED DATA MODELING
FALL 2025
The Extended Entity Relationship
Model

 Extended Entity Relationship Model (EERM): result of

adding more semantic constructs to the original ER model
 It is sometimes referred to as the enhanced entity relationship
model
 EER diagram (EERD): a diagram that uses the EERM
Entity Supertypes and Subtypes

Entity supertype
 Generic entity type related to one or more entity subtypes
 Contains common characteristics
Entity subtype
 Contains unique characteristics of each entity subtype
Criteria to determine usage
 There must be different, identifiable kinds of the entity in the user’s
environment
 The different kinds of instances should each have one or more
attributes that are unique to that kind of instance
Specialization Hierarchy

Entity supertypes and subtypes are organized in a specialization hierarchy:

 Depicts arrangement of higher-level entity supertypes and lower-level
entity subtypes
 Relationships are described in terms of “is-a” relationships
 Subtype exists within the context of a supertype
 Every subtype has one supertype to which it is directly related
 Supertype can have many subtypes

A specialization hierarchy provides the means to:

 Support attribute inheritance
 Define a special supertype attribute known as the subtype discriminator
 Define disjoint or overlapping constraints and complete or partial
constraints
Specialization
Hierarchy

Figure 5.2 A
Specialization
Hierarchy
Checkpoint #1

 What is an entity supertype, and why is

it used?
Inheritance

Inheritance: enables an entity subtype to inherit attributes

and relationships of the supertype
 All entity subtypes inherit their primary key attribute from
their supertype
 At the implementation level, supertype and its subtype(s)
maintain a 1:1 relationship
 Entity subtypes inherit all relationships in which
supertype entity participates
 Lower-level subtypes inherit all attributes and
relationships from its upper-level supertypes
Inheritance
Diagram

Figure 5.4 Specialization

Hierarchy with Overlapping
Subtypes
Subtype Discriminator

Subtype Discriminator: Attribute in the

supertype entity that determines to which entity
subtype the supertype occurrence is related
 Defaultcomparison condition is the equality
comparison
 Insome situations the subtype discriminator is
not necessarily based on an equality
comparison
Disjoint and Overlapping Constraints

Disjoint subtypes: contain a unique subset of the supertype

entity set
 Known as nonoverlapping subtypes
 Implementation is based on the value of the subtype
discriminator attribute in the supertype
Overlapping subtypes: contain nonunique subsets of the
supertype entity set
 Implementation requires the use of one discriminator
attribute for each subtype
Disjoint and Overlapping Constraints

Discriminator Attributes with Overlapping Subtypes

Table 5.1
Discriminator
Attributes
Professor Administrator Comment
Y N The employee is a member of the Professor subtype
N Y The Employee is a member of the Administrator
subtype
Y Y The Employee is both a Professor and an
Administrator
N N The Employee is not required to be a member of any
subtype. Only possible if the hierarchy exhibits the
 Completeness Constraint

Table 5.2
Type Disjoint Constraint Overlapping Constraint
Partial Supertype has optional subtypes. Supertype has optional subtypes.
Subtype discriminator can be null. Subtype discriminators can be null.
Subtype sets are unique. Subtype sets are not unique.
Total Every supertype occurrence is a member Every supertype occurrence is a member of
of only one subtype. at least one subtype.
Subtype discriminator cannot be null. Subtype discriminators cannot be null.
Subtype sets are unique. Subtype sets are not unique.
 Completeness constraint: specifies whether each supertype occurrence must
also be a member of at least one subtype
 Partial completeness: not every supertype occurrence is a member of a
subtype
 Total completeness: every supertype occurrence must be a member of at
Specialization and Generalization

Specialization
 Top-down process
 Identifies lower-level, more specific entity subtypes from a higher-level entity
supertype
 Based on grouping unique characteristics and relationships of the subtypes

Generalization
 Bottom-up process
 Identifies a higher-level, more generic entity supertype from lower-level
entity subtypes
 Based on grouping common characteristics and relationships of the subtypes
Entity
Clustering
“VIRTUAL” ENTITY TYPE USED TO
REPRESENT MULTIPLE ENTITIES AND
RELATIONSHIPS IN ERD

 Formed by combining multiple

interrelated entities into a
single, abstract entity object
 General rule: avoid the
display of attributes to
eliminate complications that
result when the inheritance
rules change
Entity Integrity: Selecting Primary and Natural
Keys

Primary keys: single attribute or a combination of attributes

 Uniquely identifies each entity instance
 Guarantees entity integrity
 Works with foreign keys to implement relationships
 The importance of properly selecting the primary key has a direct bearing on
the efficiency and effectiveness of database implementation

Natural key or natural identifier: real-world identifier used to uniquely identify

real-world objects
 Familiar to end users and forms part of their day-to-day business vocabulary
 Used as the primary key of the entity being modeled
Primary Key Guidelines

 The following are desirable primary key

characteristics:
 Unique values
 Non intelligent
 No change over time
 Preferably single-attribute
 Preferably numeric
 Security-compliant
When to Use Composite Primary
Keys

 Identifiers of composite entities: each primary key combination is

allowed once in M:N relationship
 Identifiers of weak entities
 Strong identifying relationship with the parent entity
 Represents a real-world object that is existence-dependent on another
real-world object
 Represented in the data model as two separate entities in a strong
identifying relationship
 When to
Use
Composite
Primary
Keys

Figure 5.7 The M:N Relationship

Between STUDENT and CLASS
When to Use Surrogate Primary Keys

 Surrogate key: a primary key created by the database designer to simplify

the identification of entity instances
 One advantage of a surrogate key is that because it has no intrinsic meaning, values
for it can be generated by the DBMS to ensure that unique values are always
provided

 Require ensuring that the candidate key of entity in question performs properly
 Use “unique index” and “not null” constraints

 Surrogate keys are useful in the following situations:

 When there is no natural key
 When the selected candidate key has embedded semantic contents

Checkpoint #2

 Underwhat circumstances are composite

primary keys appropriate?
Design Case 1: Implementing 1:1 Relationships

Foreign keys work with primary keys to properly

implement relationships in relational model
 Place
primary key of the parent entity on the
dependent entity as foreign key

Options for selecting and placing the foreign key

 Place a foreign key in both entities
 Place a foreign key in one of the entities
Design Case 1: Implementing 1:1 Relationships

Table 5.5
Case ER Relationship Constraints Action
I One side is mandatory and the Place the PK of the entity on the mandatory
other side is optional. side in the entity on the optional side as an
FK, and make the FK mandatory
II Both sides are optional. Select the FK that causes the fewest nulls, or
place the FK in the entity in which the
(relationship) role is played.
III Both sides are mandatory. See Case II, or consider revising your model
to ensure that the two entities do not belong
together in a single entity.
Design Case 1: Implementing 1:1
Relationships

Figure 5.8 The 1:1 Relationship Between DEPARTMENT and EMPLOYEE

Design Case 2: Maintaining History of Time-Variant Data

Time-variant data: data whose

values change over time and for
which a history of the data
changes must be retained

 Requires creating a new

entity in a 1:M relationship
with the original entity
 New entity contains the new
value, date of the change,
and any other pertinent
attribute
Design Case 2: Maintaining History of Time-Variant
Data

Figure 5.10
Maintaining
Manager History
Design Case 2: Maintaining History of Time-Variant
Data

Figure 5.11
Maintaining Job
History
Design Case 3: Fan Trap Design Error

 Design trap: occurs when a

relationship is improperly or
incompletely identified
 Represented in a way not
consistent with the real world
 Fan trap: occurs when one entity
is in two 1:M relationships to other
entities
 Produces an association among
other entities not expressed in
the model
Design Case 3: Fan Traps Correction

Figure 5.13 Corrected ERD after

Removal of the Fan Trap
Design Case 4: Redundant
Relationships
 Redundant relationships
occur when there are
multiple relationship paths
between related entities
 The main concern is that
they remain consistent
across the model
 Some designs use
redundant relationships
as a way to simplify the
design
Checkpoint #3

 Whatis the most common design trap,

and how does it occur?