Transcript Chapter 3: The Enhanced ER Model and Business Rules

Chapter 4:
The Enhanced ER Model and
Business Rules
Modern Database Management
8th Edition
Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden
By: Aatif Kamal
Dated: March 2008
1
Objectives









Definition of terms
Use of supertype/subtype relationships
Use of generalization and specialization techniques
Specification of completeness and disjointness constraints
Develop supertype/subtype hierarchies for realistic business
situations
Develop entity clusters
Explain universal data model
Name categories of business rules
Define operational constraints graphically and in English
2
Enhanced-ER (EER) Model Concepts





Includes all modeling concepts of basic ER
Additional concepts: subclasses/superclasses,
specialization/generalization, categories, attribute
inheritance
The resulting model is called the enhanced-ER or
Extended ER (E2R or EER) model
It is used to model applications more completely and
accurately if needed
It includes some object-oriented concepts, such as
inheritance
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
4
Subclasses and Superclasses (1)

An entity type may have additional meaningful subgroupings of its entities

Example:






EMPLOYEE may be further grouped into SECRETARY,
ENGINEER, MANAGER, TECHNICIAN, SALARIED_EMPLOYEE,
HOURLY_EMPLOYEE,…
Each of these groupings is a subset of EMPLOYEE entities
Each is called a subclass of EMPLOYEE
EMPLOYEE is the superclass for each of these subclasses
These are called superclass/subclass relationships.
Example: EMPLOYEE/SECRETARY,
EMPLOYEE/TECHNICIAN
Instances of a specialization.
Subclasses and Superclasses (2)

These are also called IS-A relationships

Note: An entity that is member of a subclass represents the
same real-world entity as some member of the superclass
 The Subclass member is the same entity in a distinct specific
role
 An entity cannot exist in the database merely by being a
member of a subclass; it must also be a member of the
superclass
 A member of the superclass can be optionally included as a
member of any number of its subclasses
Example: A salaried employee who is also an engineer belongs to
the two subclasses ENGINEER and SALARIED_EMPLOYEE
 It is not necessary that every entity in a superclass be a
member of some subclass

(SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, …).
Attribute Inheritance in Superclass
/ Subclass Relationships


An entity that is member of a subclass inherits all
attributes of the entity as a member of the superclass
It also inherits all relationships
Figure 4-1 Basic notation for supertype/subtype notation
a) EER
notation
9
Figure 4-1 Basic notation for supertype/subtype notation (cont.)
b) Microsoft
Visio
Notation
Different modeling tools may have different notation for the same modeling
constructs
10
Figure 4-2 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
11
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
12
Figure 4-3 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
13
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
14
Figure 4-4 Example of generalization
a) Three entity types: CAR, TRUCK, and MOTORCYCLE
All these types of vehicles have common attributes
15
Figure 4-4 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
16
Generalization.
(a) Two entity types, CAR and TRUCK.
(b) Generalizing CAR and TRUCK into the superclass VEHICLE.
Specialization




Is the process of defining a set of subclasses of a superclass
The set of subclasses is based upon some distinguishing
characteristics of the entities in the superclass
Example: {SECRETARY, ENGINEER, TECHNICIAN} is a
specialization of EMPLOYEE based upon job type.
 May have several specializations of the same superclass
Example: Another specialization of EMPLOYEE based in method of
pay is {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}.
 Superclass/subclass relationships and specialization can be
diagrammatically represented in EER diagrams
 Attributes of a subclass are called specific attributes. For
example, TypingSpeed of SECRETARY
 The subclass can participate in specific relationship types. For
example, BELONGS_TO of HOURLY_EMPLOYEE
Example of a Specialization
Figure 4-5 Example of specialization
a) Entity type PART
Only applies to
manufactured parts
Applies only to purchased parts
20
Figure 4-5 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
21
Notation for Specialization
Wor
k
For
Employee
d
Secretary
Engineer
Technician
Belon
gs To
Professional
Organization
Department
Constraints on Specialization &
Generalization




Several specializations can be defined on an entity type.
Entities may belong to subclasses in each of the
specializations.
The specialization may also consist of a single subclass,
such as the manager specialization, in this case we don’t
use the circle notation.
Types of Specializations



Predicate-defined or Condition-defined specialization
Attribute-defined specialization
User-defined specialization
Types of Specializations:
Predicate-defined


If we can determine exactly those entities that will become
members of each subclass by a condition, the subclasses are
called predicate-defined (or condition-defined) subclasses
Example: Income > 0



The condition is called the defining predicate of the sub class.
The condition is a constraint specifying exactly those entities of
the Employee entity type whose attribute value for Job Type is
Secretary belong to the subclass.
Predicate defined subclasses are displayed by writing the
predicate condition next to the line that connects the subclass
to the specialization circle
Types of Specializations:
Attribute-defined

If all subclasses in a specialization have membership
condition on same attribute of the superclass,
specialization is called an attribute definedspecialization



Attribute is called the defining attribute of the specialization
Example: JobType is the defining attribute of the specialization
{SECRETARY, TECHNICIAN, ENGINEER} of EMPLOYEE
Attribute-defined specializations are displayed by placing the
defining attribute name next to the arc from the circle to the
super class.
EER diagram notation for an attributedefined specialization on JobType.
Types of Specializations:
User-defined

If no condition determines membership, the subclass
is called user-defined


Membership in a subclass is determined by the database users
by applying an operation to add an entity to the subclass
Membership in the subclass is specified individually for each
entity in the superclass by the user
Constraints on Specialization and
Generalization
Two other conditions apply to a
specialization/generalization:
 Disjointness Constraint:





Specifies that the subclasses of the specialization must
be disjointed (an entity can be a member of at
most one of the subclasses of the specialization)
Specified by ‘d’ in EER diagram
If not disjointed, overlap; that is the same entity may
be a member of more than one subclass of the
specialization
Specified by ‘o’ in EER diagram
EER diagram notation for an overlapping
(nondisjoint) specialization.
O
Constraints on Specialization and
Generalization

Completeness Constraint:




Total specifies that every entity in the superclass
must be a member of some subclass in the
specialization/ generalization
Shown in EER diagrams by a double line
Partial allows an entity not to (may or may
not) belong to any of the subclasses
Shown in EER diagrams by a single line
Constraints on Specialization and
Generalization

Hence, we have four types of specialization /
generalization constraints:





Disjoint, total
Disjoint, partial
Overlapping, total
Overlapping, partial
Note: Generalization usually is total because the
superclass is derived from the subclasses.
Disjointness vs. Completeness

Disjoint constraints and completeness constraints are
independent.

The following possible constraints on specializations are possible:
Disjointness vs. Completeness

Disjoint, total
Department
d
Academic

Administrative
Disjoint, partial
Employee
d
Secretary
Analyst
Engineer
Disjointness vs. Completeness

Overlapping, total
Part
o
Manufactured

Overlapping, partial
Purchased
Movie
o
Children
Comedy
Drama
Constraints in Supertype/
Completeness Constraint (ONCE AGAIN)

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)
34
Figure 4-6 Examples of completeness constraints
a) Total specialization rule
A patient must be either
an outpatient or a
resident patient
35
Figure 4-6 Examples of completeness constraints (cont.)
b) Partial specialization rule
A vehicle
could be a
car, a truck,
or neither
36
Constraints in Supertype/ Disjointness
constraint (ONCE AGAIN)
 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
37
Figure 4-7 Examples of disjointness constraints
a) Disjoint rule
A patient can either be outpatient
or resident, but not both
38
Figure 4-7 Examples of disjointness constraints (cont.)
b) Overlap rule
A part may be both
purchased and
manufactured
39
Insertion & Deletion rules




Deleting an entity from a superclass implies that it is
automatically deleted from all the subclasses to which it
belongs.
Inserting an entity in a superclass means that the entity is
mandatorily inserted in all predicate-defined (attribute defined)
subclasses for which the entity satisfies the defining predicate.
Inserting an entity in a superclass of total specialization implies
that the entity is mandatorily inserted in at least one of the
subclasses of the specialization.
Number of other rules can be inferred for insertions &
deletion can be derived from various types of specializations.
Example of Disjoint
__________
Partial Specialization
Specialization / Generalization Hierarchies,
Lattices and Shared Subclasses (01)
 A subclass may itself have further subclasses specified on
it
 Forms a hierarchy or a lattice
 Hierarchy has a constraint that every subclass has only
one superclass (called single inheritance)
 Lattice, a subclass can be subclass of more than one
superclass (called multiple inheritance)
 In a lattice or hierarchy, a subclass inherits attributes not
only of its direct superclass, but also of all its
predecessor superclasses
 A subclass with more than one superclass is called a
shared subclass
Specialization / Generalization Hierarchies,
Lattices and Shared Subclasses (02)




Can have specialization hierarchies or lattices, or
generalization hierarchies or lattices
In specialization, start with an entity type and then
define subclasses of the entity type by successive
specialization (top down conceptual refinement
process)
In generalization, start with many entity types and
generalize those that have common properties
(bottom up conceptual synthesis process)
In practice, the combination of two processes is
employed
Specialization/Generalization
Lattices and Hierarchies (Example)
Employee
d
Secretary
Technician
d
Engineer
Manager
Salaried_Emp
Hourly Emp
Engineering Manager

In a lattice, when a superclass inherits attributes from more
than one superclass, and some attributes are inherited more than
once via different paths (i.e. Engineer, Manager and Salaried
Employee all inherit from Employee, that are then inherited by
Engineering Manager.

In this situation, the attributes are included only once in the subclass
Figure 4-8 Introducing a subtype discriminator (disjoint rule)
A simple attribute with
different possible values
indicating the subtype
attribute definedspecialization
45
Figure 4-9 Subtype discriminator (overlap rule)
A composite
attribute with
sub-attributes
indicating “yes”
or “no” to
determine
whether it is of
each subtype
Predicatedefined
46
Figure 4-10 Example of supertype/subtype hierarchy
47
Specialization / Generalization
Lattice Example (UNIVERSITY)
Categories (UNION TYPES)





All of the superclass/subclass relationships we have seen
thus far have a single superclass
A shared subclass is subclass in more than one distinct
superclass/subclass relationships, where each
relationships has a single superclass (multiple
inheritance)
In some cases, need to model a single
superclass/subclass relationship with more than one
superclass
Superclasses represent different entity types
Such a subclass is called a category or UNION TYPE
Example of categories (UNION TYPES)

Database for vehicle
registration, vehicle owner can
be a person, a bank (holding a
lien on a vehicle) or a company.



Category (subclass) OWNER is
a subset of the union of the
three superclasses COMPANY,
BANK, and PERSON
A category member must exist
in at least one of its superclasses
Note: The difference from
shared subclass, which is subset
of the intersection of its
superclasses (shared subclass
member must exist in all of its
superclasses).
Formal Definitions of EER Model (1)



Class C: A set of entities; could be entity type, subclass,
superclass, category.
Subclass S: A class whose entities must always be subset of
the entities in another class, called the superclass C of the
superclass/subclass (or IS-A) relationship C/S:
S⊆C
Specialization Z: Z = {S1, S2,…, Sn} a set of subclasses with
same superclass G; hence, G/Si a superclass relationship for
i = 1, …., n.



G is called a generalization of the subclasses {S1, S2,…, Sn}
Z is total if we always have:
S1 ∪ S2 ∪ … ∪ Sn = G;
Otherwise, Z is partial.
Z is disjoint if we always have:
Si ∩ S2 empty-set for i ≠ j;
Otherwise, Z is overlapping.
Formal Definitions of EER Model (2)



Subclass S of C is predicate defined if predicate p on attributes of C is used
to specify membership in S; that is, S = C[p], where C[p] is the set of entities
in C that satisfy p
A subclass not defined by a predicate is called user-defined
Attribute-defined specialization: if a predicate A = ci (where A is an
attribute of G and ci is a constant value from the domain of A) is used to
specify membership in each subclass Si in Z


Note: If ci ≠ cj for i ≠ j, and A is single-valued, then the attribute-defined
specialization will be disjoint.
Category or UNION type T





A class that is a subset of the union of n defining superclasses
D1, D2,…Dn, where n>1:
T ⊆ (D1 ∪ D2 ∪ … ∪ Dn)
A predicate pi on the attributes of T.
If a predicate pi on the attributes of Di can specify entities of Di that are members
of T.
If a predicate is specified on every Di then : T = (D1[p1] ∪ D2[p2] ∪…∪ Dn[pn]
Note: The definition of relationship type should have 'entity type' replaced with
'class'.
A
specialization
lattice with
multiple
inheritance for
a UNIVERSITY
database.
An EER
conceptual
schema for a
UNIVERSITY
database.
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
55
Figure 413a
Possible
entity
clusters for
Pine Valley
Furniture in
Microsoft
Visio
Related
groups of
entities
could
become
clusters
56
Figure 413a
Possible
entity
clusters for
Pine Valley
Furniture in
Microsoft
Visio
Related
groups of
entities
could
become
clusters
57
Figure 4-13b EER diagram of PVF entity clusters
More readable,
isn’t it?
58
Figure 4-14 Manufacturing entity cluster
Detail for a single cluster
59