Entity-Relationship Model – Part 3
Enhanced Modeling Concepts
Enhanced Entity-Relationship (EER) Model
The basic modelling concepts are only sufficient for some database
applications.
To reflect data properties and constraints more precisely, a number of
enhanced ER models (EERs) were proposed.
Each EER model includes all the basic modeling concepts of the ER model
we discussed before.
We will further discuss the following concepts in EERs:
Subclass/superclass
Specialisation/generalisation
Constraints on specialisation/generalisation
Subclass and Superclass
Subclass of an entity type: subgrouping of entities.
In many cases subclasses need to be represented explicitly because
of their application significance.
Superclass/subclass, Supertype/subtype and Class/subclass are different
names for the same concept.
Subclass inherits attributes and relationships of superclass.
Subclass can have additional attributes and relationships.
This type of relationship between subclass and superclass is often
described as an ISA relationship type.
Specialisation and Generalisation
Specialization is the process of defining a set of subclasses of an entity
type (top-down).
Defined on distinguishing features of entities in the superclass, e.g.,
based on the job type of each employee:
DepartmentEmployee Works_for
DepartmentEmployee Works_for
M
DepartmentEmployee Works_for
DepartmentEmployee Works_for
N
M 1
1 1
Employee
AddressName
SalarySSN
DepartmentEmployee Works_for
Employee
Name
SalarySSN
Supervise
Supervisor Subordinate
Address
Employee
Name
SalarySSN
has
Address
ChildCname Age
ProjectSupplier Supply
Part
Quantity Pid
Part_no
Sname
DepartmentEmployee Works_for
(1,1) (1,N)
Weak
Entity
Entity
Identifying
Relationship
Relationship
Attribute
Multivalued
Attribute
Key
Attribute
Key
Attribute
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Secretary
Address
Name Salary
SSN Level Engineer
Address
Name Salary
SSN Type
Technician
Address
Name Salary
SSN Grade
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Employee
AddressName
SalarySSN
Specialisation and Generalisation
Generalization is a reverse process of specialization (bottom-up).
Common features of entities in subclasses may be generalized into
single superclass (including primary key).
DepartmentEmployee Works_for
DepartmentEmployee Works_for
M
DepartmentEmployee Works_for
DepartmentEmployee Works_for
N
M 1
1 1
Employee
AddressName
SalarySSN
DepartmentEmployee Works_for
Employee
Name
SalarySSN
Supervise
Supervisor Subordinate
Address
Employee
Name
SalarySSN
has
Address
ChildCname Age
ProjectSupplier Supply
Part
Quantity Pid
Part_no
Sname
DepartmentEmployee Works_for
(1,1) (1,N)
Weak
Entity
Entity
Identifying
Relationship
Relationship
Attribute
Multivalued
Attribute
Key
Attribute
Key
Attribute
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Secretary
Address
Name Salary
SSN Level Engineer
Address
Name Salary
SSN Type
Technician
Address
Name Salary
SSN Grade
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Constraints on Specialisation and Generalisation
Disjointness constraint
Specifies that the subclasses of the specialization must be disjoint.
If not constrained, then entities in the subclasses may overlap.
DepartmentEmployee Works_for
DepartmentEmployee Works_for
M
DepartmentEmployee Works_for
DepartmentEmployee Works_for
N
M 1
1 1
Employee
AddressName
SalarySSN
DepartmentEmployee Works_for
Employee
Name
SalarySSN
Supervise
Supervisor Subordinate
Address
Employee
Name
SalarySSN
has
Address
ChildCname Age
ProjectSupplier Supply
Part
Quantity Pid
Part_no
Sname
DepartmentEmployee Works_for
(1,1) (1,N)
Weak
Entity
Entity
Identifying
Relationship
Relationship
Attribute
Multivalued
Attribute
Key
Attribute
Key
Attribute
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Secretary
Address
Name Salary
SSN Level Engineer
Address
Name Salary
SSN Type
Technician
Address
Name Salary
SSN Grade
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Employee
AddressName
SalarySSN
ISA
d
Disjoint
o
Overlap
(default)
d
Employee
o
Employee
Total
d
Employee
o
Employee
Partial
(default)
ISA d
Disjoint
o
Overlap
(default)
Total
Partial
(default)
CourseLecturer Teaches
M 1
Department
Employee
Works_for
M 1
Location
Has
M
1
Constraints on Specialisation and Generalisation
Completeness constraint
total – every entity in the superclass must be a member of at least one
subclass.
partial – an entity may not belong to any of the subclasses.
DepartmentEmployee Works_for
DepartmentEmployee Works_for
M
DepartmentEmployee Works_for
DepartmentEmployee Works_for
N
M 1
1 1
Employee
AddressName
SalarySSN
DepartmentEmployee Works_for
Employee
Name
SalarySSN
Supervise
Supervisor Subordinate
Address
Employee
Name
SalarySSN
has
Address
ChildCname Age
ProjectSupplier Supply
Part
Quantity Pid
Part_no
Sname
DepartmentEmployee Works_for
(1,1) (1,N)
Weak
Entity
Entity
Identifying
Relationship
Relationship
Attribute
Multivalued
Attribute
Key
Attribute
Key
Attribute
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Secretary
Address
Name Salary
SSN Level Engineer
Address
Name Salary
SSN Type
Technician
Address
Name Salary
SSN Grade
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Employee
AddressName
SalarySSN
ISA
d
Disjointness
o
Overlapping
(default)
d
Employee
o
Employee
Total
d
Employee
o
Employee
Partial
(default)
Design Choices for the EER Model
Specializations and generalisation can be defined to make the conceptual
model accurate.
If the subclasses has few specific attributes and no specific relationships,
then
can be merged into the superclass,
replace with one or more type attributes specifying the subclass that
each entity belongs to.
Choices of disjoint/overlapping and total/partial constraints are driven by
rules in the miniworld being modeled.
Informal Method for Constructing an ER or EER Model
Draw an ER or EER diagram to represent the following design:
(1) Identify the entity types (including weak entity types)
(2) Identify the relationship types (including ISA and identifying
relationship types)
(3) Identify the attributes of entity and relationship types (and their
underlying domains)
(4) Identify a primary key for each entity type
(5) Classify each binary relationship type identified in step 2 (i.e.
one-to-one, many-to-one or many-to-many)
(6) Determine the participation constraints for each entity type in each
binary relationship type
(7) Determine the disjointness and completeness constraints for each ISA
Summary of Notation for ER and EER Diagrams
DepartmentEmployee Works_for
DepartmentEmployee Works_for
M
DepartmentEmployee Works_for
DepartmentEmployee Works_for
N
M 1
1 1
Employee
AddressName
SalarySSN
DepartmentEmployee Works_for
Employee
Name
SalarySSN
Supervise
Supervisor Subordinate
Address
Employee
Name
SalarySSN
has
Address
ChildCname Age
ProjectSupplier Supply
Part
Quantity Pid
Part_no
Sname
DepartmentEmployee Works_for
(1,1) (1,N)
Weak
Entity
Entity
Identifying
Relationship
Relationship
Attribute
Multivalued
Attribute
Key
Attribute
Partial Key
Attribute
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Secretary
Address
Name Salary
SSN Level Engineer
Address
Name Salary
SSN Type
Technician
Address
Name Salary
SSN Grade
Employee
AddressName
SalarySSN
ISA
Secretary Technician Engineer
TypeLevel
Grade
Employee
AddressName
SalarySSN
ISA
d
Disjoint
o
Overlap
(default)
d
Employee
o
Employee
Total
d
Employee
o
Employee
Partial
(default)
ISA d
Disjoint
o
Overlap
(default)
Total
Partial
(default)
CourseLecturer Teaches
M 1
Department
Employee
Works_for
M 1
Location
Has
M
1
Entity-Relationship Model