Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-61
5.63 Consider the data model in Figure 5-43. How should this model be altered if the users
want to keep track of how many of each part are used? Suppose, for example, that the
wheel assembly requires four washers and the handle assembly requires just one, and
the database must store these quantities. (Hint: adding Quantity to this N:M relationship
is analogous to adding Price to the N:M relationship in Figure 5-22.)
Figure 5-43 – N:M Recursive Model for the Bill of Materials in Figure 5-42
However, if an additional attribute is added to the intersection table, we now have an association
pattern as discussed in this chapter, and the additional entity must be added to the data model.
The design is:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-62
5.64 The data model in Figure 5-52 uses the attribute Room in COLLEGE and
DEPARTMENT but uses OfficeNumber in PROFESSOR. These attributes have the
same kind of data, even though they have different names. Examine Figure 5-46 and
explain how this situation came to be. Do you think having different names for the same
attribute types is rare? Do you think it is a problem? Why or why not?
The use of different attribute names occurred because Figure 5-46 uses the term “Room” for the
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-63
❖ WRITER’S PATROL CASE QUESTIONS
Consider the Writer’s Patrol traffic citation shown in Figure 5-61. The rounded corners on this
form provide graphical hints about the boundaries of the entities represented.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-64
A Create the entities for an E-R data model based on the traffic citation form. Use five
entities, and use the data items on the form to specify identifiers and attributes for
those entities. In which of these entities should you place the unique Notice Number
that is the unique identifier for this notice?
The Notice Number will to in the NOTICE entity class. The ER diagram below answers both
Parts A and B of this question.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-65
B Complete the E-R data model by specifying relationships among the entities. Use IE
Crow’s Foot E-R symbols as shown in Figure 5-8. Name the relationships and
specify the relationship types and cardinalities. Justify the decisions you make about
minimum and maximum cardinalities, indicating which cardinalities can be inferred
from data on the form and which need to be checked out with systems users.
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
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.
We need to check:
• the logic that one DRIVER may receive more than one CORRECTION_NOTICE.
OFFICER
CORRECTION_NOTICE
Strong
1:N
M-O
VEHICLE
CORRECTION_NOTICE
Strong
1:N
M-O
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-66
❖ HIGHLINE UNIVERSITY MENTOR PROGRAM CASE QUESTIONS
Highline University is a four-year undergraduate school located in the Puget Sound
region of Washington State. A discussion of the design of a college information system
for Highline University appears in this chapter on pages 245-251 as an example of
creating data models, and a variant of that discussion is used in Appendix B.
Highline University, like many colleges and universities in the Pacific Northwest (see the
Wikipedia article on Pacific_Northwest), is accredited by the Northwest Commission on
Colleges and Universities (NWCCU – see the NWCCU Web site). Like all the colleges
and universities accredited by the NWCCU, Highline University must be reaccredited at
approximately five-year intervals. Additionally, the NWCCU requires annual status-
update reports.
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 cardinalities.
Your model should track students, advisers, and mentors. Additionally, Highline
University needs to track alumni because the program administrators view alumni as
potential mentors.
1. Create separate entities for students, alumni, faculty advisers, and mentors.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-67
• At Highline University, all students are required to live on campus and are
assigned Highline University ID numbers and email accounts in the format
• At Highline University, all faculty advisers have on-campus offices and are
assigned Highline University ID numbers and email accounts in the format
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-68
• 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
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-69
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
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
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-70
Crow’s Foot E-R model for your E-R diagrams. Justify the decisions you make regarding
minimum and maximum cardinalities. Note that:
• A person may be a current student, an alumnus, or both, because Highline
University does have alumni return for further study.
The minimum and maximum cardinalities are the same as in Part A, with the same justifications
(refer to description of relationships in Part A problem statement: they were quite explicit).
Note that this solution does not capture the constraints that faculty and students cannot be
mentors. That information should be carried forward to the data design phase and when tables
are created in a relational database, CHECK constraints can be used to enforce those constraints.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-71
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 cardinalities. The
MPIS needs to record:
• The date a student enrolled at Highline University, the date the student
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-72
All three of these changes involve transforming N:1 relationships into M:N relationships with attributes
using the associative entity patter. To enforce the constraints that a student only has one advisor at a time,
etc., requires additional logic using triggers (see Chapter 7).
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?
Model A shows a set of four 1:N relationships between four strong entities. Model B introduces