Transcript Chapter 4

Data Modeling and Database
Design
Chapter 4:
Enhanced Entity-Relationship (EER)
Modeling
Remember!
• Presentation Layer ER Diagram
 Contains hashes and oval
• Design-specific ER Diagram (Coarse-granularity)
 Uses (min, max) notation
 Maps deletion rules
• Design-specific ER Diagram (Fine-granularity)
 Maps attribute characteristics into ER diagram
 Decomposes multi-valued attributes
 Decomposes m:n relationships
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
2
Enhanced Entity-Relationship (EER)
Model
• Enhanced Entity-Relationship (EER) modeling is an
extension to the ER modeling that incorporates
additional constructs
• Central construct
Superclass/subclass (SC/sc) relationship
• More specifically:
– Specialization/generalization
– Categorization
– Aggregation
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
3
Superclass/Subclass Relationship (SC/sc)
Inter-Entity Class Relationship
(Has-a Relationship)
A STORE has a relationship with FURNITURE
STORE
FURNITURE
Sells
Intra-Entity Class Relationship
(Is-a Relationship)
There are 3 SC/sc relationships shown here.
A CHAIR is a FURNITURE.
A CHAIR is a FURNITURE.
A TABLE is a FURNITURE.
A SOFA is a FURNITURE.
CHAIR
TABLE
SOFA
CHAIR, TABLE, and SOFA are entity types that
belong to the entity class FURNITURE.
Figure 4.4 Inter-entity and intra-entity class relationships
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
4
An Instance Diagram
CHAIR
e1
FURNITURE
e4
e5
.
.
e1
e2
TABLE
e3
e2
e4
e7
e5
.
.
e6
e7
SOFA
e8
.
.
e3
e8
.
.
.
Figure 4.5 Superclass/subclass entity instances
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
5
Think About It…
• Theoretically, one has three choices of modeling
furniture/chair/table/sofa
– Model three separate entity types for chair, table,
and sofa, and create three separate relationship
types with the entity type store
– Model furniture as an entity type with an attribute
called furniture_type; then chair, table, and sofa
would be values of that attribute
– Model furniture as a superclass/subclass
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
6
SC/sc Relationships
• There are two basic kinds of Sc/sc relationships
– Specialization/Generalization: One superclass
(SC) is related to one or more subclasses (sc)
– Categorization: One subclass (sc) is related to
one or more superclasses (SC)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
7
Vignette 1
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
8
Modeling Vignette 1: Alternative 1
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
9
Modeling Vignette 1: Alternative 2
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
10
Modeling Vignette 1: Alternative 3
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
11
Properties of SC/sc Relationships
• An entity that exists in a subclass can be associated
with only one superclass entity
• An entity cannot exist in the database merely by being
a member of a subclass; it must also be a member of
an associated superclass
• An entity that is a member of a superclass can be
optionally included as a member of any number of its
subclasses
• It is not required that every member of a superclass
be a member of a subclass
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
12
Properties of SC/sc Relationships
(continued)
• Type Inheritance Property: A subclass inherits all the
attributes of the superclass to which it is related
– In addition, it will also inherit all the relationship
types in which the superclass participates
• A subclass may also have its own specific attributes
in addition to the attributes inherited
– Likewise, a subclass may have its own specific
relationship(s) with other entity types (i.e., interentity class relationships)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
13
Note That…
• The cardinality ratio of any SC/sc relationship is
always 1:1
• The participation of the subclass in a SC/sc
relationship is always total
• A subclass inherits all attributes as well as all
relationship types that a superclass possesses (type
inheritance property)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
14
Specialization and Generalization
• Specialization is the process of generating subgroups
(‘sc’s) of a generic entity class (SC) by specifying the
distinguishing properties (attributes) of the subgroups
(= top-down approach)
• Generalization, on the other hand, crystallizes the
common properties (attributes) shared by a set of
entity types (‘sc’s) into a generic entity type (SC)
(= bottom-up approach)
• Notation: circle + fork (indicating subset)
• Read: “is-a”
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
15
Specialization and Generalization
(continued)
• Completeness Constraint
The participation of the superclass in a specialization/
generalization is referred to as the completeness
constraint and can assume one of two values: total or
partial
– Total specialization means that every entity of the
superclass must participate in this specialization/
generalization relationship (indicated by a solid line
from the superclass to the specialization/generalization
symbol (i.e., the circle))
– Partial specialization means that there may be entities
present in the superclass that do not participate in this
specialization/generalization (indicated by a dotted
line)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
16
Specialization and Generalization
(continued)
• Disjointness Constraint
Used to specify that the subclasses of a
specialization must be:
– Disjointed (indicated by ‘D’), i.e., an entity of the
superclass cannot be a member of more than one
subclass
– Overlapped across subclasses (indicated by ‘O’)
• Please note that you have to specify both:
completeness and disjointness constraints!
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
17
Notation
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
18
An EER Model of Vignette 1
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
19
Sample Data Sets For EER Model of Vignette 1
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
20
An Extension of Vignette 1: Multiple Specializations
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
21
Sample Data Sets For Vignette 1 Extension
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
22
An EER Model of Vignette 2
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
23
Specialization/Generalization
Hierarchy
• Example of a Hierarchy:
[STUDENT -> STUDENT_ATHLETE ->
FOOTBALL_PLAYER -> {DEFENSIVE_PLAYER,
OFFENSIVE_PLAYER}] shows a 3-level hierarchy
• Inheritance
A subclass inherits the attributes and relationship
types of not just the immediate parent, but also of the
predecessor superclasses in the hierarchy all the way
up to the root of the specialization hierarchy
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
24
An EER Model of Vignette 3
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
25
Specialization/Generalization
Lattice
• In a specialization lattice, an entity type can
participate as a subclass in more than one
specialization (i.e., a child can have more than one
parent)
• Inheritance
The subclass will inherit all the attributes and
relationship types from the superclasses of all the
specializations participating in the lattice and the
predecessor hierarchy of all these superclasses
– This is called multiple type inheritance, and the
subclass in the lattice is referred to as a shared
subclass
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
26
The Categorization Construct
• A categorization occurs when a subclass is
associated with more than one superclass of different
entity types
– The subclass is called category
• An entity that is a member of the category (subclass)
must exist in ONLY ONE of the superclasses in the
categorization relationship
• Example: A financial donor can be an individual, a
company, or a foundation  no “is-a” relationship!
• Notation: ‘U’ (= union, the subclass is a subset of the
union of the superclasses)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
27
Categorization Example
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
28
Characteristics of a Categorization
• There is only one subclass in each categorization
• The cardinality ratio is 1:1 within and across the
SC/sc relationship
• The participation of the subclass in the categorization
is always total
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
29
Characteristics of a Categorization (continued)
• Each superclass may exhibit either total or partial
participation
• A category can either be:
– A total category (i.e., the category is the union of all the
superclass entities)
– A partial category (i.e., the category is a true subset of
the union of all the superclass entities)
• A category possesses the property of selective type
inheritance (i.e., it inherits attributes of one entity type
only)
• Often a unique identifier for a category must be
manufactured (which is called a surrogate key)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
30
Sample Data Sets For Categorization Example
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
31
Choosing the Appropriate EER Construct
Exercise:
• Part1: Consider an automobile dealership in the state
of Texas; the dealership typically stocks cars, trucks,
vans, and sport utility vehicles (SUVs)
• Part 2: Not all vehicles in the dealership are
registered vehicles though
– How would we model REGISTERED_VEHICLE?
• One exception exists in which a categorization and a
generalization/specialization are mutually
substitutable constructs: in the case of a total
category!
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
32
Choosing the Appropriate EER Construct (continued)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
33
Choosing the Appropriate EER Construct (continued)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
34
Choosing the Appropriate EER Construct (continued)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
35
Choosing the Appropriate EER Construct (continued)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
36
The Aggregation Construct
• An aggregation allows us to model a “whole/part”
relationship as an “is-a-part-of” relationship between
a subclass and a superclass
• An entity in the aggregate contains superclass
entities from ALL SC/sc relationships in which it
participates
• Inheritance: Selective type inheritance connotes the
inheritance of attributes and relationships from ALL
superclass entities contained in the specific
aggregation
• Notation: ‘A’
• Read: “is-part-of”
• Note that an aggregate can never be partial
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
37
An Example of Aggregation
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
38
An Aggregation Hierarchy
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
39
Aggregation Versus Categorization
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
40
A Fine-granular Design-Specific EER Diagram for Vignette 3
[N,6]
Student#
[A,30]
Name
STUDENT
[A,25]
Major
[N,1.2]
Gpa
U
[A,1]
F_b_b
[N,2]
Height
STUDENT_
ATHLETE
[N,3]
Weight
F_b_b_value
d3
d1
[N,2]
Uniform#
FOOTBALL_
PLAYER
BASKETBALL_
PLAYER
"Basketball"
U d2
Batting_avg
[N,1.3]
Home_runs
[N,2]
Errors
[N,2]
"Baseball"
Bpid
U
League
BASEBALL_
PLAYER
TEAM_
CAPTAIN
o
Uniform#
[N,2]
U
Position
[A,15]
U
(1,n)
OFFENSIVE_
PLAYER
No_of_interceptions No_of_tackles Receptions
[N,2]
[N,3]
[N,3]
Rebounds_per_game
[N,2.1]
Yards_gained
[N,4]
POSITION_BK
[A,15]
Position
-----------
INTRAMURAL_
PLAYER
Auto
(0,1)
Scholarship
[A,1]
U
Plays_
position
(1,1)
C
DEFENSIVE_
PLAYER
[A,15]
Team
(1,n)
Plays_
Plays_
position
position
VARSITY_
PLAYER
N
Pts_per_game
[N,2.1]
Assists_per_game
[N,2.1]
U
U
"Football"
U
U
[N,2.2]
Speed
[N,2]
Touchdowns
Redshirt
[A,1]
Sponsored_by
(1,1)
(1,n)
C
POSITION_BA
[N,3]
Walks
C
(0,1)
Plays_pitcher
[A,15]
Position
-----------
(1,1)
PITCHER
[N,3]
Strikeouts
ORGANIZATION
[N,3.1]
Innings_pitched
[N,2.2]
Era
Name
[A,20]
Pitching_speed
[N,3]
Type
[A,2]
Figure 4.20 A Fine-granular Design-Specific ER Diagram
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
41
A Presentation Layer EER Diagram For Bearcat, Inc.
Lname
Pl_name
Name_tag
Minit
Name
No_of_employees
Address
Fname
Pnumber
n
Salary
1
Works_in
Emp#
worker
employer
EMPLOYEE
PLANT
Gender
manager
Managed_by
1
Date_hired
Responsible
1
1
Supervisee
Building
Supervisor
No_of_dependents
1
Pnumber
n
Controlled
Pr_name
Assignee
20
PROJECT
1
1
Having
Held_by_E
Plocation
m
Dependent_of
m
Assigned
Dname
IN_HOUSE_PROJECT
n
Depends_on
U
7
Related_how
Belongs to
Undertaken_by
Mg_start_dt
Supervised_by
Holder of
Budget
managed by
d
Assignment
U
Dependent
--------------
Hours
DEPENDENT
Account#
Gender
OUTSOURCED_PROJECT
Description
Participant
Bank#
m
Birthdate
1
Account Holder
Annual_cost
Account_id
Held_by_D
Acc_type
1
Account of
BANK_ACCOUNT
Description
Participates
n
Hb_name
n
Hrs_per_wk
Contracted_to
n
m
Supports
n
Usage
HOBBY
Status
VENDOR
lo_activity
Gi_activity
SPONSOR
U
V_name
U
U
V_address
Pastor
Budget
V_phone
Name
NOT_FOR_PROFIT_
ORGANIZATION
CHURCH
SCHOOL
INDIVIDUAL
Ssn
Denomination
U
Name
PUBLIC_
SCHOOL
Exempt_id
District
Name
U
Type
Phone#
Size
Address
Principal
Tax_base
Figure 4.21 Presentation Layer ER diagram for Bearcat, Incorporated
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
42
A Fine-granular Design-Specific EER Diagram For Bearcat, Inc.
[A,20]
Lname
[N,3]
No_of_employees
Name
[X,50]
Address
[A,20]
Fname
[A,1]
Emp_a
[A,30]
Pl_name
[N,1]
Name_tag
[A,1]
Minit
[N,5]
Emp_n
[N,2]
Pnumber
[N,6]
Salary
Emp#
(100,n)
Works_in
(1,1)
R
EMPLOYEE
[A,1]
Gender
(1,1)
(0,1)
D
R
(0,m)
R
(3,n)
Managed_by
[Dt,8]
Date_hired
[N,7]
Budget
PLANT
R
R
[N,2]
No_of_dependents
(0,20)
(0,1)
Houses
[Dt,8]
Mg_start_dt
(0,n)
(0,1)
N
Supervised_by
(1,m)
(1,1)
[A,20]
Building
----------
[A,12]
Related_how
[A,15]
Plocation
BUILDING
(1,1)
d
(1,m)
Belongs_to
U
[A,15]
Dname
IN_HOUSE_PROJECT
(1,1)
(0,1)
[X,6]
Account#
[N,2]
Bank#
C
DEPENDENT
[A,1]
Gender
ASSIGNMENT
OUTSOURCED_PROJECT
[X,50]
Description
(0,n)
[Dt,8]
Birthdate
U
[N,3]
Hours
(1,1)
C
Dependent
--------------
[N,6]
Annual_cost
(0,n)
Held_by_D
[A,1]
Acc_type
Account_id
(1,n)
(1,1)
Includes_D
C
[A,20]
Pr_name
PROJECT
Uses
Held_by_E
Dependent_of
[N,2]
Pnumber
Undertaken_by
(0,7)
C
[X,50]
Description
PARTICIPATION
(0,1)
(1,1)
BANK_ACCOUNT
C
C
SUPPORT
[A,20]
Hb_name
(1,1)
(0,n)
Receives
HOBBY
Contracted_to
[N,2,1]
Hrs_per_wk
R (0,m)
Includes_H
(0,1)
C
N
[A,1]
Status
(1,1)
[A,1]
lo_activity
(1,m)
Provides
VENDOR
[A,1]
Gi_activity
SPONSOR
U
[A,30]
V_name
U
U
[N,10.0]
Budget
[X,50]
V_address
[A,30]
Pastor
[X,10]
V_phone#
[A,30]
Name
NOT_FOR_PROFIT_
ORGANIZATION
U
Denomination
[A,20]c
PUBLIC_
SCHOOL
Ssn
[N,9]
INDIVIDUAL
[A,30]
Name
Name
[A,30]
Size
[N,4]
Exempt_id
[X,6]
District
[A,30]
SCHOOL
U
Type
[A,1]
CHURCH
[X,10]
Phone#
[X,50]
Address
Principal
[A,30]
Tax_base
[N,10.0]
Figure 4.22 Fine-granular Design-Specific ER diagram for Bearcat, Incorporated
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling
43