Transcript Chapter 5

Data Modeling and Database
Design
Chapter 5:
Modeling Complex Relationships
Myths About Data Modeling Commonly
Mistaken as Fact
Myth 1 Relationship types beyond degree 2 (binary)
are rarely found in real-world applications, so they
are not crucial for data modeling & database design
The Fact Designers in the real world often are not afforded
an opportunity to consider relationship types beyond
degree 2 during data modeling; the inadequacy of CASE
tools renders the designer’s data modeling vision myopic!
Myth 2 Even complex real-world phenomena can be
simplified and captured accurately using binary
relationship types
The Fact Costly errors occur in database design when
genuinely ternary (n-ary) relationships are inadvertently
modeled as a set of binary relationships and vice versa
Chapter 5 – Modeling Complex Relationships
2
Myths About Data Modeling Commonly
Mistaken as Fact (continued)
Myth 3 Complex business rules cannot be modeled
using simple data modeling constructs like
binary/unary relationships
The Fact Intricate modeling ideas can be employed to
develop elegant and accurate database designs using
simple data modeling constructs
Chapter 5 – Modeling Complex Relationships
3
Vignette 1
• A college often offers many courses, and a college term may be
divided into four quarters (fall, winter, spring, and summer)
during which one or more of these courses may be offered
– Let us even say that in every quarter at, least 23 courses are
offered
• The college also has several instructors, but not all instructors
teach during all quarters
– Further, instructors are capable of teaching a variety of
courses that the college offers
– If we wish, we can also state finer specifications such as the
minimum number of instructors teaching in a quarter, the
minimum number of quarters in which an instructor teaches,
etc.
• Tentatively, let us just say that at least one instructor teaches
per quarter and that each instructor must teach at least one
quarter
Chapter 5 – Modeling Complex Relationships
4
Which Instructor Teaches What Course in
Which Quarter?
Rank
Name
Course#
Qualification
Cname
Credits
m
INSTRUCTOR
Can_teach
n
COURSE
n
Offered_
during
4
Year
n
Teaches_
during
4
QUARTER
Qtr_ref
Qtr_prefix
Figure 5.1 An initial ER diagram for Vignette 1
Chapter 5 – Modeling Complex Relationships
Quarter
5
Figure 5.1: Sample Data Sets For Vignette 1
Can_teach
Teaches_during
Offered_during
Name
Course#
Name
Quarter
Course#
Quarter
Pezman
EE812
Pezman
Fall
EE330
Fall
Pezman
EE832
Pezman
Winter
EE330
Spring
Pezman
EE330
Pezman
Spring
EE330
Summer
Pezman
EE430
Fite
Fall
EE340
Winter
Fite
EE812
Fite
Spring
EE430
Fall
Fite
EE430
Hall
Winter
EE430
Spring
Fite
EE821
Hall
Spring
EE430
Summer
Hall
EE821
Hall
Summer
EE812
Fall
Hall
EE430
Cords
Fall
EE812
Spring
Hall
EE812
Cords
Winter
EE821
Winter
Stansbury
EE430
EE832
Winter
Cords
EE340
Cords
EE435
Chapter 5 – Modeling Complex Relationships
6
An Instructor Need Not Actually Teach What
He or She Can Teach
Rank
Name
Course#
Qualification
Cname
Credits
m
INSTRUCTOR
Can_teach
m
n
COURSE
n
Teaches
n
Offered_
during
4
Year
n
Teaches_
during
4
QUARTER
Qtr_ref
Qtr_prefix
Quarter
Figure 5.2 A second ER diagram for Vignette 1
Chapter 5 – Modeling Complex Relationships
7
Figure 5.2: Second Sample Data Sets For Vignette 1
Can_teach
Teaches_during
Offered_during
Teaches
Name
Course#
Name
Quarter
Course#
Quarter
Name
Course#
Pezman
EE812
Pezman
Fall
EE330
Fall
Pezman
EE812
Pezman
EE832
Pezman
Winter
EE330
Spring
Pezman
EE832
Pezman
EE330
Pezman
Spring
EE330
Summer
Pezman
EE330
Pezman
EE430
Fite
Fall
EE340
Winter
Fite
EE812
Fite
EE812
Fite
Spring
EE430
Fall
Fite
EE430
Fite
EE430
Hall
Winter
EE430
Spring
Hall
EE821
Fite
EE821
Hall
Spring
EE430
Summer
Hall
EE430
Hall
EE821
Hall
Summer
EE812
Fall
Cords
EE340
Hall
EE430
Cords
Fall
EE812
Spring
Cords
EE435
Hall
EE812
Cords
Winter
EE821
Winter
Stansbury
EE430
EE832
Winter
Cords
EE340
Cords
EE435
Chapter 5 – Modeling Complex Relationships
8
The Course(s) an Instructor Can Teach and the Course(s) an Instructor is
Scheduled to Teach in Various Quarters Can Be Different
Rank
Credits
Name
Can_teach
Qualification
(1,r)
(1,q)
INSTRUCTOR
(0,n)
Schedule
Teaches_by
(0,m)
COURSE
According_to
Included_in
Course#
(1,p)
Year
Cname
Quarter
QUARTER
Qtr_ref
Qtr_prefix
Note: A ternary relationship type is of degree three and is signified by
three edges emanating from the relationship diamond to the
participating entity types
Figure 5.3 The ternary relationship type Schedule
Chapter 5 – Modeling Complex Relationships
9
Figure 5.3: Sample Data Set
Can_teach
Name
Pezman
Pezman
Pezman
Pezman
Fite
Fite
Fite
Hall
Hall
Hall
Stansbury
Cords
Cords
Course#
EE812
EE832
EE330
EE430
EE812
EE430
EE821
EE821
EE430
EE812
EE430
EE340
EE435
Chapter 5 – Modeling Complex Relationships
Schedule
Name
Pezman
Pezman
Pezman
Pezman
Fite
Fite
Fite
Hall
Hall
Hall
Course#
EE812
EE832
EE330
EE330
EE812
EE430
EE430
EE821
EE430
EE430
Quarter
Spring
Winter
Fall
Spring
Fall
Fall
Spring
Winter
Spring
Summer
10
A Ternary Relationship Type May Lend Itself to be
Modeled as a Base Entity Type
Credits
Rank
INSTRUCTOR
Room
Call#
Qualification
No_of_students
Teaches_by
(1,n)
(0,1)
SCHEDULE
(1,1)
According_to
(0,m)
COURSE
(1,1)
Course#
Included_in
Cname
(1,p)
Year
Quarter
QUARTER
Qtr_ref
Qtr_prefix
Note: In this conceptualization it is possible to show that a course can be scheduled for a quarter without
any instructor assignment (see the bolded 0 in the Teaches_by relationship connector to SCHEDULE). It is not
possible to do the same in the design shown in Figure 5.3.
Figure 5.4 Modeling the ternary relationship type Schedule of Figure 5.3 as a base
entity type
Chapter 5 – Modeling Complex Relationships
11
A Ternary Relationship Type That Does Not Lend
Itself to be Modeled as a Base Entity Type
Rank
Credits
Name
Qualification
INSTRUCTOR
(0,n)
Uses
(0,m)
COURSE
Course#
(1,p)
Isbn#
Cname
Title
BOOK
Author
Figure 5.5 The ternary relationship type Uses
Chapter 5 – Modeling Complex Relationships
12
Vignette 2 – Get Well Pharmacists, Inc.
Get Well Pharmacists, Inc. has numerous pharmacies across the state
of Ohio. A pharmacy dispenses medication to patients. It is imperative
that the records at Get Well, Inc. always have the data on which of its
pharmacies dispensed what medication to which patient.
In addition, every pharmacy stocks numerous different medicines and
the same medicine is carried in several pharmacies. A patient often
takes one or more medicines, and the fact that the same medicine
may be used by at least one and often many patients can also be a
meaningful relationship.
Finally, a pharmacy typically has one or more patients as customers,
and some patients use one or more pharmacies. To make the story a
little more interesting, let us impose another business rule (V2R1) that
a particular physician prescribes a certain medication to a specific
patient.
Chapter 5 – Modeling Complex Relationships
13
An ER Diagram For Vignette 2: Alternative 1
Day
From
Pharm#
Location
Time
To
Working_hrs
PHARMACY
Pat_name
Med_code
Occupation
Med_name
Patient_id
Insurance
(1,p)
Price
(1,q)
Expiration_dt
(0,n)
Dispenses
PATIENT
MEDICATION
(0,r)
(0,s)
List_price
Prescribes
Dosage
Frequency
(1,m)
Speciality
PHYSICIAN
Experience
Physician#
Name
Phone
Figure 5.6 Two ternary relationship types Dispenses and Prescribes
Chapter 5 – Modeling Complex Relationships
14
An ER Diagram For Vignette 2: Alternative 2
Day
From
Pharm#
Location
Time
To
Working_hrs
PHARMACY
Pat_name
Med_code
Occupation
Med_name
Patient_id
Insurance
(1,q)
PATIENT
(1,p)
Price
Expiration_dt
(0,n)
Dispenses
MEDICATION
(0,s)
(0,r)
Prescription#
Date
Receives
Age
Is_for
( 1 ,1
)
List_price
)
(1,1
PRESCRIPTION
(1,1)
Dosage
Frequency
Writes
(1,m)
Experience
PHYSICIAN
Physician#
Name
Speciality
Phone
Figure 5.7 Representing the Prescribes relationship type as the PRESCRIPTION base
entity type
Chapter 5 – Modeling Complex Relationships
15
Vignette 3 – More on Madeira College
Madeira College, originally introduced in Vignette 1, has
students as well. Students must declare a major field of study
and in fact can enroll in up to three different majors. Every
major has some students.
The advising office of the college has staff specially trained in
advising in each major. An advisor is restricted to two majors
by training. Every major has exactly two trained advisors. A
student can have multiple advisors for each major he or she
enrolls in, but must have at least one advisor per major.
Therefore, it is imperative that information is recorded about
which advisor advises which student for what major.
Chapter 5 – Modeling Complex Relationships
16
Figure 5.8a: The Relationship Types for Vignette 3
Major_name
College
Department
(2,2)
Trained_in
Trainer
MAJOR
(1,q)
Enrolling
Enroller
Enrollee
Trainee
Name
(1,2)
Phone#
Stu_id
(1,n)
(1,3)
Stu_name
Major
Experience
ADVISOR
(1,m)
Advisor
Advising
(1,p)
Advisee
STUDENT
(a)
ER diagram for the scenario described in Vignette 3
Note: The “Advisee” edge of the Advising relationship type indicates that a student must be related to at least 1 {major, advisor} pair and may be
related to up to p {major, advisor} pairs. (e.g., SID 123 in Figure 5.8b has three different major/advisor combinations and SID 345 also has three
different major/advisor combinations but only two different majors and two different advisors).
Chapter 5 – Modeling Complex Relationships
17
Sample Data For the Advising Relationship in Figure 5.8a
Advising
Stu_id Major_name
123
Physics
123
Music
123
Chemistry
456
Literature
789
Music
678
Physics
456
Music
345
Physics
345
Physics
345
Chemistry
Chapter 5 – Modeling Complex Relationships
Name
Hawking
Mahler
Kouri
Michener
Bach
Hawking
Bach
Kouri
Hawking
Kouri
18
Vignette 3 – Two New Business Rules
A New Business Rule (V3R1)
An advisor may advise a student for only one major
Another New Business Rule (V3R2)
No more than one advisor may advise the same student
for the same major
Chapter 5 – Modeling Complex Relationships
19
A Student May Have as Many as 3 Majors; But, Has Exactly One
Advisor and That Too For No More Than One Major
Major_name
College
Department
(2,2)
Trained_in
Trainer
MAJOR
(1,q)
Enrolling
Enroller
Enrollee
Trainee
Name
(1,2)
(1,n)
Phone#
Stu_id
(1,3)
Stu_name
Advised_for
Experience
Major
ADVISOR
(1,m)
Advising
Advisor
(1,1)
STUDENT
Advisee
(a)
Figure 5.9 Changing the maximum cardinality of the “Advisee” edge in
Figure 5.8a from p to 1
Chapter 5 – Modeling Complex Relationships
20
The Effect of Restricting a Student to
One {Major, Advisor} Pair
Stu_id
123
123
123
456
789
678
456
345
345
345
Advising*
Major_name
Physics
Music
Chemistry
Literature
Music
Physics
Music
Physics
Physics
Chemistry
Name
Hawking
Mahler
Kouri
Michener
Bach
Hawking
Bach
Kouri
Hawking
Kouri
Note: A student is restricted to one major/advisor combination.
*
Removal of shaded rows from the data set enforces the restriction of one major/advisor pair per
student
(b)
Chapter 5 – Modeling Complex Relationships
21
Vignette 3 – More on Madeira College
(continued)
Table 5.1 Revised Advising data set
Stu_id
123
123
123
456
789
678
456
345
345
345
Advising*
Major_name
Physics
Music
Chemistry
Literature
Music
Physics
Music
Physics
Physics
Chemistry
Name
Hawking
Mahler
Kouri
Michener
Bach
Hawking
Bach
Kouri
Hawking
Kouri
Note: An advisor is limited to advising a student in only one major.
*
Removal of shaded row enables a constraint that limits an advisor to advising a student in only
(b)
one major.
Chapter 5 – Modeling Complex Relationships
22
Vignette 3 – More on Madeira College
(continued)
Major_name
MAJOR
(1,n)
College
Is
Cluster Entity Type
Department
In_charge_of
Takes_charge
(1,1)
Stu_id
Name
ADVISOR
STUDENT
Couns
(1,m)
elor
Couns
Counseling
eled
)
(1,p
COUNSELING
Figure 5.10 The relationship type In-charge_of with the cluster entity type COUNSELING
Chapter 5 – Modeling Complex Relationships
23
Vignette 3 – More on Madeira College
(continued)
Major_name
MAJOR
(1,n)
Colleg
e
Is
Cluster Entity Type
Department
In_charge_of
Takes_charge
(1,1)
Stu_id
Name
ADVISOR
STUDENT
Counsel
or
(1,m)
Cou
Counseling
nseled
)
(1,p
COUNSELING
Figure 5.10
The relationship type In_charge_of with the cluster entity type COUNSELING
Business Rule V3R1: An advisor may advise a student for only one major
A student may have multiple advisors;
A student may also have multiple majors (max = 3)
But, a student may not have the same advisor for more than one major
A {Student, Advisor} pair can be related to only one major, while a major can
be related to many such pairs
In other words:
An advisor may advise a student for only one major – V3R1;
But, a student can have multiple advisors for the same major
Chapter 5 – Modeling Complex Relationships
24
Vignette 3 – More on Madeira College
(continued)
Table 5.2 Advising data: Next revision
Advising Data Set*
SID
123
123
123
456
789
678
456
345
345
345
Major
Physics
Music
Chemistry
Literature
Music
Physics
Music
Physics
Physics
Chemistry
Advisor
Hawking
Mahler
Kouri
Michener
Bach
Hawking
Bach
Kouri
Hawking
Kouri
Note: A student is limited to only one advisor in one major.
*
Row shaded allows the restriction that limits a student to only one advisor in each major to be
enforced.
(b)
Chapter 5 – Modeling Complex Relationships
25
Vignette 3 – More on Madeira College
(continued)
Business Rule (V3R2)
Major_name
No more than one advisor may
advise the same student for the
same major
ENROLLMENT
College
Department
MAJOR
(1,q)
Enrolling
Enroller
Name
(1,r)
Advis
e
As
s
ig
ne
d_
(1
to
,1
)
A student may enroll in up to three
majors and a major can enroll many
students; however, no more than one
advisor can be assigned to the same
student for the same major
Assignment
(1,3) Enrollee
Stu_id
Emp_id
Stu_name
Major
ADVISOR
STUDENT
Phone#
Figure 5.11 The relationship type Assignment with the cluster entity type ENROLLMENT
Chapter 5 – Modeling Complex Relationships
26
Vignette 3 – More on Madeira College
(continued)
Exactly one advisor per enrollment
and exactly one major per
counseling
Major_name
Department
(2,2)
Trained_in
ENROLLMENT
College
(1,q)
MAJOR
Trainer
Enrolling
Enroller
(1,n)
ig
ne
d_
(1
to
,1
)
Is
Takes_charge
(1,3) Enrollee
(1,1)
(1,r)
Advis
e
Assignment
Name
In_charge_of
As
s
Trainee (1,2)
Stu_id
Stu_name
Major
Emp_id
ADVISOR
STUDENT
Couns
(1,m)
Phone#
elor
Couns
Counseling
eled
(1,p
)
COUNSELING
Figure 5.12 Two cluster entity types: COUNSELING and ENROLLMENT
Chapter 5 – Modeling Complex Relationships
27
Entity Clustering
• Entity clustering is a useful abstraction to present an
ER diagram at a broader level of conceptualization
• The technique enables a bottom-up consolidation of
natural groupings of entity types
• A cluster entity type literally emerges as a result of a
grouping operation on a collection of entity types and
relationship(s) among them
• Some database design applications lend themselves
to a repeated roll-up to higher levels of abstractions
through clustering and provide an opportunity to
conceptualize a layered set of ER diagrams
Chapter 5 – Modeling Complex Relationships
28
Entity Clustering (continued)
• A layered set of ERDs can be especially useful for
large database design projects where the different
layers within the Presentation Layer ER diagram can
be used to inform the end-user hierarchy (e.g.,
executives, managers, staff, etc.)
• Even for an office staff working at the detail level of a
business, a layered presentation can facilitate a
quicker understanding of the semantics captured by
the design when presented through a cascading set
of ER diagrams
Chapter 5 – Modeling Complex Relationships
29
Vignette 4
Vignette 3 demonstrated the use of a new ER modeling grammar
construct, namely, the cluster entity type using a simple example.
In the vignette that follows a slightly more complex entity clustering
is demonstrated.
Surgeons perform surgeries on patients to treat illnesses. Also, a
surgery event pertains to a certain surgery type, and there can be
numerous surgeries of a certain surgery type. Suppose there is a
need to keep track of which surgeons perform what surgery(ies) on
which patient(s) to treat what illness(es) for insurance purposes.
In fact, the primary insurance covers such treatments. However, all
treatments are not necessarily covered by insurance.
Policy_no
(0,1)
TREATMENT
Covers
(1,n)
Figure 5.13(b)
TREATMENT is a Roll-up of a cluster entity type
Chapter 5 – Modeling Complex Relationships
PRIMARY_
INSURANCE
Company_name
Face_value
30
Vignette 4 (continued)
Base entity type TREATMENT exploded as a cluster entity type
Name
TREATMENT
Code
SURGERY_
TYPE
Ill_name
Ill_code
(0,n)
Anotamy
Symptoms
ILLNESS
Pat_name
Includes
Occupation
Patient_id
(0,p)
Theatre
(1,1)
Support_staff
(0,q)
(1,n)
Performs
PATIENT
SURGERY
Event_seq
----- --(1,m)
Gender
Seq#
Age
Date
Speciality
SURGEON
Experience
Physician#
Name
Phone
(0,1)
Covers
Special_need
Policy_no
(1,n)
PRIMARY_
INSURANCE
Company_name
Face_value
(a)
A cluster entity type, TREATMENT containing more than one relationship types
Figure 5.13 Modeling Vignette 4 with a cluster entity type
Chapter 5 – Modeling Complex Relationships
31
Vignette 4 (continued)
SECTION
Start
Period
TIME_SLOT
End
Credits
Employee_id Name
Qualification
(0,q)
Room#
INSTRUCTOR
(0,n)
Offering
(0,m)
COURSE
Course#
(1,p)
Year
Name
Qtr_name
QUARTER
Quarter
Qtr_prefix
Figure 5.14 An example of the cluster entity type SECTION emerging as
a product of a quarternary relationship type
Chapter 5 – Modeling Complex Relationships
32
Vignette 4 (continued)
SECTION
Start
Employee#
End
Credits
Name
Room#
Qualification
Timeslot
INSTRUCTOR
(0,n)
Offering
(0,m)
COURSE
Course#
(1,p)
Year
Name
Qtr_name
QUARTER
Quarter
Qtr_prefix
Figure 5.15 An alternate representation of the cluster entity type SECTION
(TIME_SLOT reduced from a base entity type to a mandatory multi-valued attribute of Offering)
Note: The semantics of the cluster entity type has changed.
Chapter 5 – Modeling Complex Relationships
33
Vignette 4 (continued)
A richer conceptualization of a course SECTION
Capacity
SECTION
Room#
Start
Location
Bldg#
Period
CLASS_ROOM
Employee_id
End
TIME_SLOT
Credits
Name
Qualification
INSTRUCTOR
(0,r)
(0,q)
(0,n)
Offering
(0,m)
COURSE
Course#
(1,p)
Year
Name
Qtr_name
QUARTER
Quarter
Qtr_prefix
Figure 5.16 The quintary relationship type Offering
Chapter 5 – Modeling Complex Relationships
34
Vignette 4 (continued)
SECTION
From
To
Room#
Employee_id
Bldg#
Credits
Name
Time_slot
Qualification
Location
Section
INSTRUCTOR
(0,n)
Offering
(0,m)
COURSE
Course#
(1,p)
Year
Name
Qtr_name
QUARTER
Quarter
Qtr_prefix
Figure 5.17 Reducing Offering in Figure 5.16 to a ternary relationship type
with a mandatory multi-valued attribute Section
Note: The semantics of the relationship type has changed
Chapter 5 – Modeling Complex Relationships
35
Vignette 4 (continued)
A course section is taught by only one instructor – i.e., no team-teaching of course sections
SECTION
Capacity
Room#
From
Location
To
Bldg#
CLASS_ROOM
Time_slot
Employee_id
Credits
Section#
-----------
Name
Qualification
(0,q)
Section
INSTRUCTOR
(0,n)
Teaching
(1,1)
(0,m)
Offering
COURSE
Taught_by
Teaches
Qtr_name
Course#
(1,p)
Year
Name
QUARTER
Quarter
Qtr_prefix
(a)
Incorrect representation of Teaching relationship type
SECTION
Capacity
Room#
From
To
Location
Bldg#
CLASS_ROOM
Time_slot
Employee_id
Credits
Section#
----------Name
Qualification
(0,q)
Section
INSTRUCTOR
(0,n)
Teaches
Teaching
(1,1)
Offering
(0,m)
COURSE
Taught_by
Qtr_name
Course#
(1,p)
Year
Name
QUARTER
Quarter
Qtr_prefix
(b)
Correct representation of Teaching relationship type
Figure 5.18 Prohibition of “team-teaching” modeled in the Teaching relationship type
Chapter 5 – Modeling Complex Relationships
36
Vignette 4 (continued)
Capacity
Room#
From
To
Location
Bldg#
CLASS_ROOM
Time_slot
Stu_name Major
Credits
Stu_id
Section#
-----------
(0,r)
Section
(1,q)
STUDENT
Offering
(0,m)
COURSE
Title
(0,n)
Isbn#
(1,p)
Qtr_name
Course#
Year
Author
TEXTBOOK
Name
QUARTER
Quarter
Qtr_prefix
Note: This ER diagram allows a course section in a quarter when held in one classroom to use one book for one student and to
use a different book for another student. This does not reflect the intended semantics of the story.
Figure 5.20 STUDENT enrolled in a course Offering using a TEXTBOOK
modeled as a quintary relationship type
Chapter 5 – Modeling Complex Relationships
37
Vignette 4 (continued)
Isbn#
TEXTBOOK
(0,n)
Use
Author
Title
(0,m)
SECTION
Capacity
Room#
From
To
Location
CLASS_ROOM
Time_slot
Stu_name
Stu_id
Bldg#
Credits
Section#
-----------
Major
(0,q)
Section
STUDENT
(1,n)
Enroll
(1,m)
Offering
Qtr_name
(0,m)
COURSE
Course#
(1,p)
Year
Name
QUARTER
Quarter
Qtr_prefix
Figure 5.21 A more sensible rendition of the design intended in Figure 5.20
Chapter 5 – Modeling Complex Relationships
38
Vignette 4 (continued)
Capacity
Room#
SECTION
Course#
Location
CLASS_ROOM (0,m)
Name
Bldg#
Credits
(1,1)
Offering
COURSE
From
Employee_id
To
Name
Qualification
(0,4)
INSTRUCTOR
(0,m)
Teaching
(1,1)
Time_slot
Section
Section#
-----------
Offering
(0,m)
Use
(0,n)
(1,p)
Isbn#
Qtr_name
Year
TEXTBOOK
QUARTER
Quarter
Qtr_prefix
Stu_name
Stu_id
Major
Author
Title
(1,m)
STUDENT
(1,n)
Enroll
Figure 5.22 ER diagram for Madeira College - The rest of the story
Note: A course section can be held in only one classroom and can be taught by only one instructor
Chapter 5 – Modeling Complex Relationships
39
Vignette 4 (continued)
Companies often use consultants on a contract basis to
do some work and often have different types of
boilerplate contracts that can be simply reviewed on a
year-to-year basis
Name
Contract#
Contract
Location
Name
Size
Location
Size
Year
COMPANY
(0,n)
Client
Contract
(1,m)
Contractor
CONSULTANT
Figure 5.23 Contract as a binary relationship type
Note: COMPANY and CONSULTANT are identical entity types (same set of attributes)
Chapter 5 – Modeling Complex Relationships
40
Vignette 4 (continued)
Since COMPANY and CONSULTANT in Figure 5.23 are identical entity types meaning a consultant
is also a company, the relationship type Contract can be modeled as a recursive relationship type.
Name
Contract#
Contract
Location
Name
Size
Location
Size
Year
(0,n)
Client
COMPANY
Contract
(1,m)
Contractor
CONSULTANT
Figure 5.23 Contract as a binary relationship type
Name
Location
Contract#
Size
Contract
Client
(0,n)
COMPANY
Year
Contract
(1,m)
Contractor
Figure 5.24 Contract as a recursive relationship type
Chapter 5 – Modeling Complex Relationships
41
Vignette 4 (continued)
Contract#
Name
Year
Location
Size
Contract
Client
(0,n)
COMPANY
Contract
(1,m)
Contractor
(1,1)
Linked_with
Pno
(0,n)
Pname
Location
PROJECT
Note: ER modeling grammar does not permit an edge connecting two relationship types
Figure 5.25a Incorrect representation of the Linked_with relationship type
Chapter 5 – Modeling Complex Relationships
42
Vignette 4 (continued)
Contract#
Name
Year
Location
Size
Contract
Client
(0,n)
Contract
COMPANY
(1,m)
Contractor
(1,1)
Linked_with
Pno
Pname
(0,n)
Location
PROJECT
CONTRACT
Figure 5.25b Involving the cluster entity type CONTRACT in the Linked_with relationship type
Chapter 5 – Modeling Complex Relationships
43
Vignette 4 (continued)
A flight may connect to several other flights in an airport.
Thus, flight connection information is crucial to the airline
and its passengers.
Note that a particular flight connection must happen in an
airport (not in mid-air), and that too in only one airport.
Chapter 5 – Modeling Complex Relationships
44
Vignette 4 (continued)
Day
-----Flight#
Flight_gate
Connect_gate
Origin
Connect_time
Destination
Connection
Flight
(0,n)
FLIGHT
Connect
(1,1)
Is_at
(0,m)
Connects_flight
(0,n)
AIRPORT
Note: ER modeling grammar does not permit an edge connecting two
relationship types
Ap_name
Ap_code
City
Figure 5.26a Syntactically incorrect representation of the Is_at relationship type
Chapter 5 – Modeling Complex Relationships
45
Vignette 4 (continued)
Day
-----Flight#
Flight_gate
Connect_gate
Origin
Destination
Connect_time
Connection
Flight
(0,n)
(1,1)
FLIGHT
Is_at
Connect
(0,m)
Connects_flight
(0,n)
AIRPORT
FLIGHT_CONNECTION
Ap_name
Ap_code
City
Figure 5.26b Syntactically correct representation of the Is_at relationship type
Chapter 5 – Modeling Complex Relationships
46
Vignette 4 (continued)
Lname
Address
Works_in
( 10
1)
(1,
Name
rk
Wo
Emp#
Gender
Pnumber
Salary
Minit
Fname
Pl_name
Name_tag
er
EMPLOYEE
Em
p
Mgr_start_dt
(0,
Date_hired
Ma
na
ge
0,
loy
Building
n)
er
No_of_employees
PLANT
Budget
1)
r
No_of_dependents
Manages
1)
y
(1,
_b
ed
g
na
Ma
Figure 5.27 The Works_in and Manages relationship types from Figure 3.5
What if we have a business rule that stipulates: In order for an employee to manage a plant the
same employee must work for the same plant?
Chapter 5 – Modeling Complex Relationships
47
Weak Relationship Types
• An inter-relationship constraint arises in many realworld situations when two relationship types are
linked by:
– An event precedence sequence or
– A condition precedence sequence
• A weak relationship type in the ER modeling grammar
facilitates modeling such inter-relationship constraints
• A weak relationship can be inclusion-dependent on
another relationship
Or
• Two weak relationships can be exclusion-dependent
on each other
Chapter 5 – Modeling Complex Relationships
48
Dependencies in Weak Relationship Types
• Inclusion Dependency vs. Exclusion Dependency
• While inclusion dependency indicates a subset
relationship between a regular relationship type and a
weak relationship type, exclusion dependency can be
used to model scenarios of mutual exclusion between
relationship types
Chapter 5 – Modeling Complex Relationships
49
Revisit - Bearcat
Recall that in Bearcat Incorporated, business rules
indicate that a plant has employees and some plant
employees hold the position of managers of these
plants. Let us now change this rule as follows.
All employees need not be working at the plants
because some are in the corporate office and others are
in the regional offices of Bearcat Incorporated.
However, we are not presently interested in modeling
activities anywhere other than in the plants. Also, a
plant manager should be a plant employee.
Chapter 5 – Modeling Complex Relationships
50
Revisit – Bearcat (continued)
Business rule: In order for an employee to manage a plant, the
same employee must work for the same plant
Lname
Address
Works_in
1)
(0,
Name
Wo
Emp#
Gender
Pnumber
Salary
Minit
Fname
Pl_name
Name_tag
( 10
r
r ke
Em
p
EMPLOYEE
0,
loy
er
No_of_employees
(0,
Date_hired
Ma
na
ge
Building
n)
PLANT
Mgr_start_dt
Budget
1)
(1,
r
No_of_dependents
Manages
M
1)
a
an
ge
by
d_
Figure 5.28 Manages as a (condition-precedent) weak relationship type
“Works_in” precedes “Manages”
Manages is said to be “inclusion-dependent” on Works_in since in order for a relationship
Manages to exist between a plant and an employee, a Works_in relationship must exist
between the same employee and the same plant
Chapter 5 – Modeling Complex Relationships
51
Let’s Revisit…
Let’s revisit Vignette 1: In order for an instructor to
teach a course, he or she should be capable of
teaching that course
Employee_id Name
Course#
Qualification
INSTRUCTOR
(1,n)
Name
Credits
Can_teach
(2,
q)
(1,m)
r
(1,
COURSE
)
Teaches
Figure 5.29 Teaches as a (condition-precedent) weak relationship type
Teaches is “inclusion-dependent” on Can_teach
Chapter 5 – Modeling Complex Relationships
52
Let’s Revisit… (continued)
Let’s revisit Vignette 2: A medication must be stocked by a
pharmacy before it can be dispensed to a patient
Day
From
Time
To
Location
On_order
On_hand
Working_hrs
Lead_time
Pharm#
PHARMACY
Pat_name
Patient_id
Stocks
Occupation
Price
Insurance
(1,q)
(1,p)
Dispenses
PATIENT
Expiration_dt
Gender
(2000,r)
(0,m)
(0,n)
MEDICATION
List_price
Age
Med_code
Med_name
Note: It is also possible to view this as a condition-precedent weak relationship based on the storyline.
Figure 5.30 Dispenses as an (event-precedent) weak relationship type
Dispenses is “inclusion-dependent” on Stocks
Chapter 5 – Modeling Complex Relationships
53
Let’s Revisit… (continued)
In order to return a vehicle, a customer should have rented the same vehicle
Drv_License#
Name
Vehicle_id
Gender
Type
(1,m)
CUSTOMER
Make
Rents
(1,
m)
(0,n)
VEHICLE
n)
(0,
Returns
Figure 5.31 Returns as an (event-precedent) weak relationship type
Returns is “inclusion-dependent” on Rents
Chapter 5 – Modeling Complex Relationships
54
Let’s Revisit… (continued)
EMPLOYEE
(0,n)
Dependent_of
(0,m)
(1,1)
Held_by_E
(0,m)
(0,1)
DEPENDENT
Held_by_D
(0,1)
Business Rule: A joint-account between
an employee and a dependent is
prohibited
BCU_ACCOUNT
Note: The exclusive arc indicates that the relationship types Held_by_E and Held_by_D are mutually
exclusive – i.e., Held_by_E and Heled_by_D are mandatorily exclusive.
Figure 5.32 An excerpt from Bearcat Incorporated demonstrating the use of exclusive arc
Chapter 5 – Modeling Complex Relationships
55
Let’s Revisit… (continued)
EMPLOYEE
(0,n)
Dependent_of
(0,m)
(1,1)
(0,m)
Held_by_E
(0,1)
DEPENDENT
Held_by_D
(0,1)
Business Rule: A joint-account between
an employee and a dependent is
prohibited
BCU_ACCOUNT
Figure 5.33 The exclusive arc of Figure 5.32 portrayed using exclusion
dependency between weak relationship types
Held_by_E and Held_by_D are exclusion-dependent on each other – hence no directional arrow
Chapter 5 – Modeling Complex Relationships
56
Inclusion Dependency in a Composite
RelationshipType
Business rule: In order for a nurse to be assigned to a surgery type, that nurse should
have the surgery skills that the surgery type requires
Emp_id
Grade
NURSE
Skill_code
Name
Name
Name
Anat_location
Level
(1,m)
Nurse_skill
(0,n)
SURGERY_SKILL
(0,p)
Req_skill
(1,q)
SURGERY_TYPE
S_code
(0,r)
Assigned_to
(1,s)
Figure 5.34 A weak relationship type inclusion-dependent on a composite relationship type
Assigned_to is inclusion-dependent on the composite (intersection) of Nurse_skill and Req_skill
Chapter 5 – Modeling Complex Relationships
57
Inclusion Dependency in a Composite
Relationship Type – 2nd Example
Business rule: A restaurant can cater to a banquet only if that restaurant caters food items that are on the
banquet’s menu
Name
Cost
Address
Food_type
Rating
Invoice_no
Cuisine
Address
Bill_to
Id
RESTAURANT
(0,m)
Caters
(1,n)
FOOD_ITEM
(1,n)
Contains
(0,p)
BANQUET
Product_code
(50,q)
Can_cater
(7,r)
Figure 5.35 A weak relationship type inclusion-dependent on a composite
relationship type: A second example
Can_cater is inclusion-dependent on the composite (intersection) of Caters and Contains
Chapter 5 – Modeling Complex Relationships
58
Inclusion Dependency in a Composite
Relationship Type – 3rd Example
Business rule: A restaurant can cater to a banquet only if that restaurant is capable of
preparing food items that are on the banquet’s menu
Name
Cost
Address
Food_type
Rating
Invoice_no
Cuisine
Address Bill_to
Id
RESTAURANT
(0,m)
Caters
(1,n)
BANQUET
(1,n)
Contains
(0,p)
FOOD_ITEM
Product_code
(100,q)
Capable_of_preparing
(17,r)
(b)
A composite of two weak relationship types inclusion-dependent on a relationship Type
The composite (intersection) of Caters and Contains inclusion-dependent on capable_of_preparing
Chapter 5 – Modeling Complex Relationships
59
Let’s Revisit… (continued)
Name
Ssno
University
PROFESSOR
O
AUTHOR
Specialty_area
U
U
Book_title
(1,p)
Writes
Manuscript#
(1,q)
Title
PAPER
No_pages
(2,5)
Referees
(1,r)
REVIEWER
Figure 5.36 An ER diagram depicting a professor as an author and reviewer of papers
Chapter 5 – Modeling Complex Relationships
60
Exclusion Dependency in a Composite
Relationship Type
Name
Ssno
Address
PROFESSOR
Business rule: In order for a reviewer to referee
a paper written by an author, a conflict of
interest relationship cannot exist between the
reviewer and the author and vice versa
AUTHOR
(1,p)
Writes
(1,q)
(0,n)
PAPER
Conflict_of_
interest
U
Manuscript# Title
U
Book_title
O
Speciality_area
No_pages
(2,5)
Referees
(1,r)
REVIEWER
(0,m)
Adapted From: Dey, Storey and Barron (1999)
Note: Conflict_of_interest and (Writes
Referees) are mutually exclusive.
Figure 5.37 An example of exclusion dependency in an ER diagram
Chapter 5 – Modeling Complex Relationships
61
Decomposing n-ary Relationships
Employee_id
Credits
Name
Qualification
INSTRUCTOR
(0,n)
Uses
(0,m)
COURSE
Course#
(1,p)
Isbn#
Name
Title
BOOK
Author
Figure 5.38 The ternary relationship type Uses
Chapter 5 – Modeling Complex Relationships
62
Decomposing n-ary Relationship Types
Employee_id
Credits
Name
Qualification
INSTRUCTOR
(0,n)
Selects
(1,1)
USE
(1,1)
Adopts
(0,m)
COURSE
(1,1)
Course#
Name
Finds
(1,p)
Isbn#
BOOK
Title
Author
Figure 5.39 Decomposition of the ternary relationship type Uses to the gerund entity type USE
Chapter 5 – Modeling Complex Relationships
63
Decomposing n-ary Relationship Types
(continued)
Contract#
Name
Contract
Location
Name
Size
Location
Size
Year
COMPANY
(0,n)
Client
Contract
(1,m)
Contractor
CONSULTANT
(a)
Contract as a binary relationship type with a multi-valued attribute
Name
Location
Name
Size
Location
Size
COMPANY
(0,n)
Client
Execute
(1,m)
Contractor
CONSULTANT
Year
Contract#
Contract
------------
(1,1)
Document
CONTRACT
(b)
Decomposition of the relationship type Contract in Figure 5.40a to a gerund entity type CONTRACT
Figure 5.40 Decomposition of a relationship type with a multi-valued attribute
Chapter 5 – Modeling Complex Relationships
64
Decomposing n-ary Relationship Types
(continued)
Retainer
Contract_yr
---------------
Contract#
Year
Name
Location
Size
(0,n)
Client
COMPANY
Name
Contract
Location
Size
Contract
(1,m)
Contractor
CONSULTANT
(a)
'Retainer' as an additional attribute in the composite multi-valued attribute 'Contract'
Name
Location
Name
Size
(0,n)
Client
COMPANY
Location
Size
Execute
(1,m)
Contractor
CONSULTANT
Year
(1,1)
Contract#
Document
Contract_yr
---------------
CONTRACT
Retainer
(b)
Decomposition of the relationship type Contract in Figure 5.41a to a gerund entity type CONTRACT
Figure 5.41 Decomposition of a relationship type with a partial key in a multi-valued attribute
Chapter 5 – Modeling Complex Relationships
65
Decomposing n-ary Relationship Types
(continued)
Contract_yr
---------------
Contract#
Year
Name
Location
Name
Contract
Size
Location
Size
Retainer
(0,n)
Client
COMPANY
Contract
(1,m)
Contractor
CONSULTANT
(a)
'Retainer' as an attribute of Contracts independent of the multi-valued attribute Contract
Name
Retainer
Location
Name
Size
COMPANY
Location
Size
(0,n)
Client
Execute
(1,m)
Contractor
CONSULTANT
Year
Contract#
Contract_yr
---------------
(1,1)
Document
CONTRACT
(b)
Incorrect* fine-granular decomposition of the ERD in Figure 5.42a
* Retainer as an attribute of the identifying relationship type does not convey the semantics
implied in Figure 5.42a.
Figure 5.42 ERD with a single-valued as well as a multi-valued attribute of a relationship type
Chapter 5 – Modeling Complex Relationships
66
Decomposing n-ary Relationship Types
(continued)
Name
Retainer
Location
Name
Size
Size
(0,n)
Client
COMPANY
Location
Retains
(1,m)
Contractor
CONSULTANT
Agreement
(0,p)
Execute
Year
Contract#
(1,1)
Contract_yr
---------------
CONTRACT
(a)
'Retainer' as an attribute of Retains independent of CONTRACT, a mapping of the
multi-valued attribute Contract in Figure 5.42a
Retainer
Name
Location
COMPANY
Name
Size
(0,n)
Location
Size
Has
(1,1)
AGREEMENT
(1,1)
Reciprocates
(1,m)
CONSULTANT
(0,p)
Execute
Year
Contract#
(1,1)
Contract_yr
---------------
CONTRACT
(b)
A fine-granular decomposition of the design in Figure 5.43a
Figure 5.43 A two step decomposition of the design shown in Figure 5.42a
Chapter 5 – Modeling Complex Relationships
67
Decomposing n-ary Relationship Types
(continued)
Retainer
Name
Location
COMPANY
Name
Size
Location
Size
(0,n)
Has
(1,1)
AGREEMENT
(1,1)
(1,m)
Reciprocates
(0,p)
CONSULTANT
(0,q)
Signs
Honors
(1,1)
(1,1)
Contract#
CONTRACT
Contract_yr
---------------
Year
Note: CONTRACT is a weak entity type with two ‘independent’ identifying parents - viz., COMPANY
and CONSULTANT – not a gerund entity type emerging from the relationship between the two.
Figure 5.44 An alternative design for the ER diagram in Figure 5.43b
Chapter 5 – Modeling Complex Relationships
68
Decomposing a Weak Relationship Type
Employee_id Name
Course#
Qualification
(1,n)
INSTRUCTOR
(2,
Name
Credits
Can_teach
q)
(1,m)
COURSE
r)
(1,
Teaches
U
Note: Weak relationship type Teaches depicts inclusion dependency Teaches _ Can_teach.
Figure 5.45 Teaches as a condition-precedent weak relationship type
Chapter 5 – Modeling Complex Relationships
69
Decomposing a Weak Relationship Type
(continued)
TEACHING has existence dependency on the gerund entity type CAN_TEACH
Employee_id
Name
Course#
Qualification
Name
Credits
INSTRUCTOR
(1,n)
Able_to
(1,1)
CAN_TEACH
(1,1)
Reciprocates
(1,m)
)
,q
(2
,r
(1
U
)
Entails
Lined_up
(1,1
)
COURSE
1)
(1,
TEACHING
U
Note: Partial specialization of CAN_TEACH as TEACHING enforces the inclusion dependency
Teaches _ Can_teach shown in Figure 5.45.
Figure 5.46 Decomposition of the weak relationship type Teaches using the EER construct, Specialization
Chapter 5 – Modeling Complex Relationships
70
Decomposing a Weak Relationship Type
(continued)
Employee_id
Name
Course#
Qualification
Name
CAN_TEACH
Credits
INSTRUCTOR
(1,n)
(1,1)
Able_to
(1,1)
(0,1)
Reciprocates
(1,m)
)
,q
(2
,r
(1
COURSE
)
Only-if
Entails
Lined_up
(1,1)
(1,1
)
1)
(1,
TEACHING
U
Note: The inclusion dependency (Teaches _ Can_teach) of Figure 5.45 decomposed using
a basic ER construct - a (1:1) relationship type.
Figure 5.47 Decomposition of the weak relationship type Teaches: An alternative design
Chapter 5 – Modeling Complex Relationships
71
Decomposing a Weak Relationship Type
(continued)
Membership#
Phone#
Title
Library_id
Name
(1,1)
PATRON
Name
Location
Member_of
(1,n)
LIBRARY
(1,m)
Call#
Author
(1,1)
Available_in
BOOK
Note: Relationship types Member_of and Available_in fanning-out from LIBRARY
(a)
An ER diagram exemplifying a relationship fan
PATRON
Member_of
p1
m1
p2
m2
p3
LIBRARY
Available_in
BOOK
a1
k1
l1
a2
k2
m3
l2
a3
k3
p4
m4
l3
a4
k4
p5
m5
a5
k5
a6
k6
(b)
An instance diagram for the design shown in Figure 5.48a
Figure 5.48 An example of a fan trap
Chapter 5 – Modeling Complex Relationships
72
Decomposing a Weak Relationship Type
(continued)
Membership#
Title
Phone#
(1,n)
PATRON
Call#
Library_id
Author
Name
Borrowed_by
(1,1)
Name
Location
BOOK
(1,1)
Available_in
(1,m)
LIBRARY
Note: Relationship types Borrowed_by and Available_in fanning-in to BOOK
(a)
An ER diagram exemplifying a relationship fan
LIBRARY
PATRON
Borrowed_by
BOOK
Available_in
p1
b1
k1
a1
p2
b2
k2
a2
l1
p3
b3
k3
a3
l2
p4
b4
k4
a4
l3
p5
b5
k5
a5
b6
k6
a6
(b)
An instance diagram for the design shown in Figure 5.49a
Figure 5.49 An alternative solution for the design shown in Figure 5.48
Chapter 5 – Modeling Complex Relationships
73
Decomposing a Weak Relationship Type
(continued)
Library_id
Membership#
Name
(1,m)
Member_of
(1,1)
PATRON
Call#
Author
Name
Location
LIBRARY
Title
Phone#
(1,n)
Borrowed_by
(1,1)
BOOK
Note: Relationship types Member_of and Borrowed_by neither fanning-in to nor fanning-out of PATRON
(a)
An ER diagram exemplifying a relationship hierarchy
Member_of
PATRON
Borrowed_by
BOOK
m1
p1
b1
k1
l1
m2
p2
b2
k2
l2
m3
p3
b3
k3
l3
m4
p4
b4
k4
m5
p5
b5
k5
b6
k6
LIBRARY
(b)
An instance diagram for the design shown in Figure 5.50a
Figure 5.50 Resolution of the fan trap present in Figures 5.48 and 5.49
Chapter 5 – Modeling Complex Relationships
74
Decomposing a Weak Relationship Type
(continued)
Lic-plate#
Type
Model
Name
Year
VEHICLE
(1,1)
Name
Age
Experience
Owned_by
(1,n)
MUSICIAN
(1,m)
Played_by
(1,1)
INSTRUMENT
Figure 5.51 A relationship fan semantically irrelevant as a fan trap
Chapter 5 – Modeling Complex Relationships
75
Decomposing a Weak Relationship Type
(continued)
Library_id
Membership#
Name
(1,m)
(1,1)
Member_of
PATRON
Call#
Author
Name
Location
LIBRARY
Title
Phone#
(1,n)
Borrowed_by
(0,1)
BOOK
(a)
An ER diagram exemplifying a relationship hierarchy with a partial participation
Member_of
PATRON
Borrowed_by
m1
p1
b1
k1
l1
m2
p2
b2
k2
l2
m3
p3
b3
k3
l3
m4
p4
b4
k4
m5
p5
b5
k5
LIBRARY
(b)
An instance diagram for the design shown in Figure 5.52a
BOOK
k6
Figure 5.52 An example of a chasm trap
Chapter 5 – Modeling Complex Relationships
76
Decomposing a Weak Relationship Type
(continued)
Library_id
Membership#
Name
LIBRARY
(1,m)
Title
Phone#
Member_of
(1,1)
PATRON
(1,n)
(1,n)
Call#
Author
Name
Location
(0,1)
Borrowed_by
BOOK
(1,1)
Available_in
(a)
ER diagram in Figure 5.52a augmented by the relationship type Available_in
LIBRARY
Member_of
PATRON
Borrowed_by
BOOK
p1
b1
k1
m2
p2
b2
k2
l2
m3
p3
b3
k3
l3
m4
p4
b4
k4
m5
p5
b5
k5
m1
l1
k6
Available_in
a6
a5
a4
a3
a2
a1
(b)
An instance diagram for the design shown in Figure 5.53a
Figure 5.53 Final design free of connection traps for the scenario specified
Chapter 5 – Modeling Complex Relationships
77
Decomposing a Weak Relationship Type
(continued)
Specification
Budget
Frequency
Part#
Price
(1,p)
PRODUCT
(1,m)
Supplies
Address
(1,n)
Name
PROJECT
Proj#
Qualification
Location
VENDOR
(a)
A ternary relationship type Supplies in a semantic trap
Specification
Budget
Frequency
Part#
Price
PRODUCT
(1,p)
(1,m)
Uses
PROJECT
Proj#
INVENTORY
Location
(1,1)
Supplies
Address
Qualification
Name
(1,n)
VENDOR
(b)
A revised design of Figure 5.54a eliminating the semantic trap
Figure 5.54 Demonstration of a semantic trap
Chapter 5 – Modeling Complex Relationships
78
Decomposing a Weak Relationship Type
(continued)
Name
Address
Cost
Food_type
Rating
Invoice_no
Cuisine
Address
Bill_to
Id
RESTAURANT
(0,m)
Caters
(1,n)
FOOD_ITEM
(1,n)
Contains
(0,p)
BANQUET
Product_code
(50,q)
Can_cater
(7,r)
(a)
A weak relationship type inclusion-dependent on a composite relationship type
Figure 5.55 (a) Inclusion dependency in composite relationship types: Example 1
Chapter 5 – Modeling Complex Relationships
79
Decomposing a Weak Relationship Type
(continued)
Cost
Address
Food_type
Rating
Invoice_no
Cuisine
Address Bill_to
Id
RESTAURANT
(0,m)
Caters
(1,1)
BANQUET
(1,n)
Contains
(0,p)
FOOD_ITEM
Product_code
(100,q)
Capable_of_preparing
(17,r)
(b)
A composite of two weak relationship types inclusion-dependent on a relationship Type
Figure 5.55 (b) Inclusion dependency in a composite relationship types: Example 2
Chapter 5 – Modeling Complex Relationships
80
Cougar Medical Associates
• See book
Chapter 5 – Modeling Complex Relationships
81
Cougar Medical Associates (continued)
Emp#
CLINIC_PERSONNEL
Name
Gender Address
Phone#
U
d
PERSON
Ssno
U
U
Salary
O
SALARIED_EMPLOYEE
Speciality
d
SURGEON
U
Con_type
Con_years
U
PATIENT
U
NURSE
PHYSICIAN
Grade
Yrs_experience
Speciality
Figure 5.56 Presentation Layer ER diagram for Cougar Medical Associates - Stage 1
Chapter 5 – Modeling Complex Relationships
82
Cougar Medical Associates (continued)
Name
Gender Address
Phone#
Emp#
PERSON
Ssno
U
CLINIC_PERSONNEL
O
U
U
d
U
PATIENT
U
U
NURSE
Speciality
SURGEON
PHYSICIAN
SUPPORT_STAFF
Salary
Grade
Yrs_experience
Con_type
Con_years
Speciality
Salary
Salary
Figure 5.57 Presentation Layer ER diagram for Cougar Medical Associates Stage 1 (An alternative design)
Chapter 5 – Modeling Complex Relationships
83
Cougar Medical Associates (continued)
Name
Gender Address
Phone#
Emp#
PERSON
Ssno
U
CLINIC_PERSONNEL
O
U
d
U
U
PATIENT
U
U
U
SUPPORT_STAFF
A
Salary
NURSE
Speciality
1
PHYSICIAN
SURGEON
1
Ch_ratio
Blood_sugar
MEDICAL_INFO
Grade
Salary
Yrs_experience
PERSONAL_INFO
Salary
Con_type
Heart_risk
Blood_type
Allergy
Con_years
Speciality
Description
Patient#
Birthdate
Total_
cholesterol
Code
HDL
Cholesterol
LDL
Triglyceride
Figure 5.58 Presentation Layer ER diagram for Cougar Medical Associates - Stage 2
Chapter 5 – Modeling Complex Relationships
84
Cougar Medical Associates (continued)
Name
Gender Address
Phone#
Emp#
PERSON
Ssno
U
CLINIC_PERSONNEL
Code
Description
O
ILLNESS
Percent_own
Headquarters
CLINIC_
OWNER
U
MEDICAL_
CORPORATION
Corp_name
Severity
U
U
U
SUPPORT_STAFF
d
Interacts
U
Salary
m
n
m
Suffers
PHYSICIAN
n
Q_on_hand
U
Speciality
1
Speciality
Salary
U
Skill_code
PATIENT
S_code
Surg_sch
Name
Surg_date
n
Nurse_skill
m
n
SURGERY_
SKILL
MEDICATION
Prescribes
Q_on_order
Con_years
Description
Salary
NURSE
20
Req_skill
m
SURGERY_
TYPE
n
U
A
m
Name
Takes
Special_Needs
1
Category
Unit_cost
Dosage
Frequency
U
Yrs_experience
Theatre
SURGEON
Con_type
PCP*
Ytd_usage
Med_code
1
Ch_ratio
Blood_sugar
Grade
Anat_location
MEDICAL_INFO
PERSONAL_INFO
1
n
Blood_type
Assigned_to
Allergy
1
Attends
Patient#
n
IN_PATIENT
Birthdate
Location
* PCP stands for Primary Care Physician.
Admit_date
Nursing_unit
Description
Heart_risk
Total_
cholesterol
Code
HDL
Cholesterol
LDL
Triglyceride
Bed#
Room#
Figure 5.59 Presentation Layer ER diagram for Cougar Medical Associates – The genesis
Chapter 5 – Modeling Complex Relationships
85
Cougar Medical Associates (continued)
Gender Address
Name
Phone#
Emp#
PERSON
Ssno
Code
U
CLINIC_PERSONNEL
Description
O
ILLNESS
Percent_own
Headquarters
U
U
d
Severity
U
U
1
SUPPORT_STAFF
CLINIC_
OWNER
U
MEDICAL_
CORPORATION
Corp_name
Writes
PHYSICIAN
Interacts
m
m
Suffers
n
1
U
Speciality
SURGEON
Con_type
U
PRESCRIPTION
20
n
PATIENT
Theatre
S_code
Surg_sch
Name
Nurse_skill
m
MEDICATION
Q_on_order
n
SURGERY_
SKILL
Req_skill
m
n
U
SURGERY_
TYPE
A
m
Name
Takes
Special_Needs
1
Category
Unit_cost
Dosage
Frequency
Surg_date
n
NURSE
m
Prescribed
Con_years
Description
Salary
U
Skill_code
Q_on_hand
PCP*
Salary
Speciality
Salary
n
m
Yfd_usage
Med_code
1
Ch_ratio
Blood_sugar
Grade
Anat_location
Yrs_experience
n
PERSONAL_INFO
MEDICAL_INFO
Blood_type
1
Allergy
Assigned_to
1
Attends
Patient#
n
IN_PATIENT
Description
Birthdate
Total_
cholesterol
Code
Location
* PCP stands for Primary Care Physician.
Heart_risk
Admit_date
Nursing_unit
HDL
Cholesterol
LDL
Triglyceride
Bed#
Room#
Figure 5.60 Presentation Layer ER diagram For Cougar Medical Associates – Final
Chapter 5 – Modeling Complex Relationships
86
Cougar Medical Associates (continued)
[A,30]
Name
[N,6]
Salary
SUPPORT_STAFF
[X,50]
Address
[A,1]
Gender
[X,9]
Ssno
[X,10]
Phone#
PERSON
U
Possessed_as
U
(1,1)
(1,1)
[N,6]
Salary
U
Has
Speciality
[A,20]
NURSE_SKILL
(0,1)
[A,20]
Description
[A,20]
Speciality
[A,20]
Con_type
(0,m)
SURGERY
_SKILL
[A,3]
Skill_code
May_have
[N,1.1]
Con_years
SUFFERIING
(1,1)
Source_of
(0,m)
C
Writes
PCP*
(1,m)
(1,1)
PATIENT
D
(0,n)
Issued_for
Issued_to
U
(0,n)
SURG_SCH
(1,1
(1,1)
(1,1)
(1,1)
(1,1)
Slated_for
Yfd_usage
[N,5]
U
[A,15]
Surg_date
Theatre
[Dt,8]
Alloted_to
REQ_SKILL
Depends_on
(1,m)
Special_Needs
[A,20]
Assignment
(0,p)
Category
[A,1]
(1,n)
(2,n)
Has
Attends
1
1
[N,3]
Blood_sugar
PERSONAL_INFO
Name
[A,30]
N
IN_PATIENT
(0,1)
Location
*PCP Stands for Primary Care Physician.
Ch_ratio
[N,1.2]
[X,3]
S_code
SURGERY_TYPE
Name
[X,30]
Med_code
[A,3]
A
Anat_location
[A,20]
Requires
(1,1)
(1,1)
MED_TAKEN
(0,1)
(1,1)
Is
(1,1)
(1,n)
(5,n)
(0,n)
By
)
[N,4]
Q_on_order
MEDICATION
U
Works_by
Part_of
[N,4]
Q_on_hand
(0,n)
(0,m)
(0,m)
Subject_to
(0,m)
(0,n)
(1,1)
(1,1)
Subject_to
[N,3.2]
Unit_cost
PRESCRIPTION
(1,1)
(7,20)
R
(1,1)
Subject_to
(1,m)
SURGEON
INTERACTION
(1,1)
U
PHYSICIAN
(0,1)
(1,1)
CLINIC_
OWNER
U
(1,m)
ILLNESS
U
MEDICAL_
CORPORATION
d
[A,20]
Description
O
[A,15]
[A,30]
Headquarters Percent_own
Corp_name
[N,2.1]
U
NURSE
[A,3]
Code
(0,n)
Causes
CLINIC_PERSONNEL
[N,2]
Yrs_experience
U
[A,20]
Grade
[N,6]
Salary
[X,6]
Emp#
Admit_date
[Dt,8]
Bed#
[A,1]
Nursing_unit
[N,1]
[X,10]
Patient#
Birthdate
[Dt,8]
Blood_type
[X,2]
(0,n)
Room_num
Includes
(1,1)
Room#
[N,2]
Wing
[A,1]
[A,3]
Code
---------
MEDICAL_INFO
Heart_risk
[A,1]
HDL
[N,3]
LDL
[N,3]
Total_
cholesterol
[N,3]
Cholesterol
Triglyceride
[N,3]
ALLERGY
Description
[A,20]
Figure 5.61 Fine-granular Design-Specific ER diagram for Cougar Medical Associates
Chapter 5 – Modeling Complex Relationships
87
Exercise: Schnaps Distillery
• A small rural distillery called “Kurt” is thinking about
entering the e-commerce world by creating an
Internet presence
• Since Kurt is low on budget, he wants to keep his
Web site as basic as possible, but at the same time
as effective as possible
• Kurt is very open to any suggestions regarding his
Web site, but he has two stipulations: the Web site
has to show off his products (and provide a
functionality for customers to buy his products), and it
has to have a guest book
• Further information available
Chapter 5 – Modeling Complex Relationships
88
Assignment
• A famous Greek shipping magnate, Stell, owns many
container ships. Containers are collected at one port
and delivered to another port. Customers pay a
negotiated fee for the delivery of each container.
Each ship has a sailing schedule that lists the ports
the ship will visit over the next six months. The
schedule shows the expected arrival and departure
dates. The daily charge for use of each port is also
recorded.
Chapter 5 – Modeling Complex Relationships
89