Chapter Four – Data Modeling and the Entity-Relationship Model
Page 21 of 52
4.30 Extend your work for question 4.29 to include supplies that Heather uses on a project.
Assume that she wants to track the description, price, and amount used of each supply.
Supplies are used on multiple days of a project. Use the IE Crow’s Foot E-R model for
your E-R diagrams.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 22 of 52
4.31 Using recursive relationships, as appropriate, develop a data model of the boxcars on a
railway train. Use the IE Crow’s Foot E-R model for your E-R diagrams.
4.32 Develop a data model of a genealogical diagram. Model only biological parents; do not
model stepparents. Use the IE Crow’s Foot E-R model for your E-R diagrams.
Boxcar–Behind
Boxcar–Ahead
BOXCAR
BoxcarID
RailroadName
CarNumber
WeightEmpty
WeightMaxLoaded
ClearanceHeight
DateLastInspection
MARRIAGE is a Non-ID-Dependent
weak entity related to MAN and WOMAN
Gender
Son Daughter
Wife
Husband
PERSON
PersonID
LastName
FirstName
DateOfBirth
Gender
MarriageLicenseNumber
DateOfMarriage
DateOfDivorce
{OtherAttributes}
{OtherAttributes}
{OtherAttributes}
Chapter Four – Data Modeling and the Entity-Relationship Model
4.33 Develop a data model of a genealogical diagram. Model all parents, including
stepparents. Use the IE Crow’s Foot E-R model for your E-R diagrams.
NOTE: Last entry incomplete due to an erwin error (text cut off): MARRIAGE is a Non–ID–
Dependent weak entity related to MAN should be MARRIAGE is a Non-ID-Dependent weak
entity related to MAN and WOMAN.
MARRIAGE is a Non-ID-Dependent
weak entity related to MAN and
Gender
Wife
Husband
BoyParents
FatherMarriage MotherMarriage
GirlParents
PERSON
PersonID
LastName
FirstName
DateOfBirth
Gender
MARRIAGE
MarriageLicenseNumber
DateOfMarriage
DateOfDivorce
{OtherAttributes}
ManPersonID
WomanPersonID
WOMAN
PersonID
{OtherAttributes}
MAN
PersonID
{OtherAttributes}
MAN_MARRIAGE
MarriageLicenseNumber
ManPersonID
isSon
isStepson
{OtherAttributes}
WOMAN_MARRIAGE
MarriageLicenseNumber
WomanPersonID
isDaughter
isStepdaughter
{OtherAttributes}
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 24 of 52
ANSWERS TO HIGHLINE UNIVERSITY MENTOR PROGRAM CASE
QUESTIONS
Highline University is a 4-year undergraduate school located in the Puget Sound region
of Washington State.1 Highline University, like many colleges and universities in the
Pacific Northwest, is accredited by the Northwest Commission on Colleges and
Universities (NWCCU—see www.nwccu.org ). Like all the colleges and universities
accredited by the NWCCU, Highline University must be reaccredited at approximately 5–
year intervals. Additionally, the NWCCU requires annual status-update reports. Highline
University is made up of five colleges: The College of Business, The College of Social
Sciences and Humanities, the College of Performing Arts, the College of Sciences and
Technology, and the College of Environmental Sciences. Jan Smathers is the president
of Highland University, and Dennis Endersby is the provost (a provost is a vice president
of academics; the deans of the colleges report to the provost).
A. Draw an E-R data model for the Highline University Mentor Program Information System
(MPIS). Use the IE Crow’s Foot E-R model for your E-R diagrams. Justify the decisions
you make regarding minimum and maximum cardinality.
Your model should track students, advisers, and mentors. Additionally, Highline
University needs to track alumni because the program administrators view alumni as
potential mentors.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 25 of 52
1. Create separate entities for students, alumni, faculty advisers, and mentors.
• At Highline University, all students are required to live on campus and are
assigned Highline University ID numbers and email accounts in the format
FirstName.LastName@students.hu.edu. The student entity should track
student last name, student first name, student University ID number, student
email address, dorm name, dorm room number, and dorm phone number.
NOTE: Visio insists on using primary keys rather than identifiers as we normally use
in a data model, but we can live with that. Just remember that PK is showing a data
model identifier at this point, not a true primary key.
• At Highline University, all faculty advisers have on-campus offices and are
assigned Highline University ID numbers and email accounts in the format
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 26 of 52
• Highline University alumni live off campus and were previously assigned
Highline University ID numbers. Alumni have private email accounts in the
• Highline University mentors work for companies and use their company
address, phone, and email address for contact information. They do not have
Highline University ID numbers as mentors. Email addresses are in the format
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 27 of 52
2. Create relationships between entities based on the following facts:
• Each student is assigned one and only one faculty adviser and must have an
adviser. One faculty member may advise several students, but faculty
members are not required to advise students. Only the fact of this
assignment is to be recorded in the data model—not possible related data
(such as the date the adviser was assigned to the student).
• Each student may be assigned one and only one mentor, but students are
not required to have a mentor. One mentor may mentor several students,
and a person may be listed as a mentor before he or she is actually assigned
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 28 of 52
B. Revise the E-R data model you created in part A to create a new E-R data model
based on the fact that students, faculty, alumni, and mentors are all a PERSON. Use
the IE Crow’s Foot E-R model for your E-R diagrams. Justify the decisions you make
regarding minimum and maximum cardinality. Note that while the following
business rules need to be implemented in your design, the E-R model notation may
not be able to express them, in which case you will need to add text notes to your
diagram indicating which rules will be to be enforced in SQL structures or application
code (alternatively, write a memo to accompany your E-R diagram that contains
these notes). The relevant business rules are:
• A person may be a current student, an alumnus, or both, because Highline
University does have alumni return for further study.
• A person may be a faculty member or a mentor, but not both. (SQL or code
needed)
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 29 of 52
C. Extend and modify the E-R data model you created in part B to allow more data to
be recorded in the MPIS system. Use the IE Crow’s Foot E–R model for your E-R
diagrams. Justify the decisions you make regarding minimum and maximum
cardinality. The MPIS needs to record:
• The date a student enrolled at Highline University, the date the student
graduated, and the degree the student received
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 30 of 52
D. Write a short discussion of the difference between the three data models you have
created. How does data model B differ from data model A, and how does data model
C differ from data model B? What additional features of the E-R data model were
introduced when you created data models B and C?
ANSWERS TO WRITER’S PATROL CASE QUESTIONS
Consider the Writer’s Patrol traffic citation shown in Figure 4-22. The rounded corners on
this form provide visual hints about the boundaries of the entities represented.
Chapter Four – Data Modeling and the Entity-Relationship Model
B. Specify relationships among the entities. Name the relationships, and specify the
relationship types and cardinalities. Justify the decisions you make regarding minimum
and maximum cardinalities, indicating which cardinalities can be inferred from the data
on the form and which need to be checked out with the system users.
Is Cited By /
Cites
Is Given /
Is Given To
Specifies /
Is Specified In
Writes /
Is Written By
DRIVER
LicenseState
DriversLicense
DriverLastName
DriverFirstName
DriverInitial
Address
City
State
Zip
Sex
BirthDate
Height
Weight
EyeColor
VEHICLE
VIN
State
Make
Model
Year
Color
OwnerLastName
OwnerFirstName
OwnerInitial
Address
CORRECTION_NOTICE
NoticeNumber
Month
Date
Year
Time
District
Detach
Distance
Direction
City
Highway
ActionRequired
VIOLATION
Violation
OFFICER
PersonnelNumber
OfficerLastName
OfficerFirstName
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 32 of 52
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue =
Inferable]
PARENT
CHILD
TYPE
MAX
MIN
DRIVER
CORRECTION_NOTICE
Strong
1:N
M-O
OFFICER
CORRECTION_NOTICE
Strong
1:N
M-O
VEHICLE
CORRECTION_NOTICE
Strong
1:N
M-O
CORRECTION_NOTICE
VIOLATION
ID–
Dependent
Multi-
valued
1:N
M-O
We can infer that:
• only one DRIVER is cited because there is only room for one set of DRIVER
information. Logically, however, one DRIVER may receive more than one
CORRECTION_NOTICE.
• only one OFFICER issues the CORRECTION_NOTICE because there is only one
OFFICER Signature. Logically, however, one OFFICER may issue many
CORRECTION_NOTICES.
We need to check:
• the logic that one DRIVER may receive more than one CORRECTION_NOTICE.
• the logic that one OFFICER may issue many CORRECTION_NOTICES.
Although it’s not a cardinality, we also need to check:
• How to handle VIOLATIONs. If VIOLATIONs are standardized, we should have put a
ViolationCode in the VIOLATION entity instead of Violation. ViolationCode will have
a 1:1 relationship to a standardized text description in an additional entity named
VIOLATION_DEFINITION.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 33 of 52
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
Garden Glory wants to expand its database applications beyond the recording of
property services. The company still wants to maintain data on owners, properties,
employees, and services, but it wants to include other data as well. Specifically, Garden
Glory wants to track equipment, how it is used during services, and equipment repairs.
In addition, employees need to be trained before they use certain equipment, and
management wants to be able to determine who has obtained training on which
equipment.
A. Draw an E-R data model for the Garden Glory database schema shown in Chapter 3’s
“Garden Glory Project Questions.” Use the IE Crow’s Foot E-R model for your E-R
diagrams. Justify the decisions you make regarding minimum and maximum cardinality.
Note that foreign keys are not shown in data models; they are added in the database design
phase. Therefore, the foreign keys shown in the Garden Glory database schema in Chapter 3
are not in the E-R diagram! The only exception is where the foreign key must be part of the
identifier of an ID-dependent weak entity.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 34 of 52
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 35 of 52
The E-R Crow’s Foot model above has the following cardinalities:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
OWNER
OWNED_PROPERTY
Non-
identifying
1:N
M-O
OWNED_PROPERTY
PROPERTY_SERVICE
Non-
identifying to
a weak but
not ID–
Dependent
entity
1:N
M-O
EMPLOYEE
PROPERTY_SERVICE
Non-
identifying to
a weak but
not ID–
Dependent
entity
1:N
M-O
GG_SERVICE
PROPERTY_SERVICE
Non-
identifying to
a weak but
not ID–
Dependent
entity
1:N
M-O
We have decided that:
• One owner can own many properties, and OWNER data may be entered into the database
before any associated property is identified or data for that property is entered.
• One property can have many services performed at it, and OWNED_PROPERTY data may be
entered into the database before any service is performed at that property.
Chapter Four – Data Modeling and the Entity-Relationship Model
Page 36 of 52
B. Extend and modify the E-R data model to meet Garden Glory’s new requirements. Use
the IE Crow’s Foot E-R model for your E-R diagrams. Create appropriate identifiers and
attributes for each entity. Justify the decisions you make regarding minimum and
maximum cardinality.
One possible E-R diagram is shown on the next page. Note that we have added a surrogate key
to SERVICE—the addition of SUB_PROPERTY made the non-surrogate composite key much too
cumbersome! This also allows for more than one SERVICE each day. The same decisions
regarding minimum and maximum cardinalities made in part A apply, plus the following:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
PROPERTY
SUBPROPERTY
ID-Dependent
1:N
M-M
SUB_PROPERTY
PROPERTY_SERVICE
ID-Dependent
1:N
M-O
EQUIPMENT
PROPERTY_SERVICE
Non-ID–
Dependent
N:M
O-O
EQUIPMENT
TRAINING_COURSE
Non-ID–
Dependent
1:N
M-O
EMPLOYEE
TRAINING_COURSE
Non-ID–
Dependent
N:M
O-O
EQUIPMENT
EQUIPMENT_REPAIR
ID-Dependent
1:N
M-O
We have decided that:
• One property must have one or more sub-properties, and the date for one sub-property
must be entered into the database at the same time the PROPERTY data is entered.
• One sub-property can have many services performed at that sub-property, and
SUBPROPERTY data may be entered into the database before any service is performed at
that sub-property.