INSTRUCTOR’S MANUAL
TO ACCOMPANY
40th Anniversary Edition
DATABASE PROCESSING
Fundamentals, Design, and Implementation
15th Edition
Chapter 5
Data Modeling with the Entity–Relationship Model
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-2
❖ CHAPTER OBJECTIVES
• To understand the two-phase data modeling/database design process
• To understand the purpose of the data modeling process
• To understand and be able to use strong entity patterns
• To understand and be able to use the ID-dependent association pattern
• To understand and be able to use the ID-dependent multivalued attribute relationship
pattern
• To understand and be able to use the ID-dependent archetype/instance relationship
pattern
• To be able to apply the data modeling process
❖ ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
❖ TEACHING SUGGESTIONS
• A good way to introduce the chapter is by discussing the relationship between
systems analysis and design and database design. If a systems analysis and design
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-3
people who are going to use the system?” Convince the students that the model
needs to fit the user requirements, even if the developer “knows better”.
• Although it is not covered in the chapter, you might want to cover the concept of
domain in Appendix C – E-R Diagrams and the IDEF1X and UML Standards. This
idea becomes more important as projects get larger and last for longer periods of
time.
• Software for drawing E-R diagrams: there is no software package that exactly
matches the IE crow’s-foot notation as used in this text. Many faculty accept hand-
drawn (neatly!) E-R diagrams or diagrams drawn on a computer. Some students will
o erwin: uses solid vs. dashed lines for M:N relationships; minimum cardinalities
can only be specified on “children” in a relationship. These features mean that
nearly any E-R diagram created using erwin will be incorrect for this text. Figure
IM 5-1 on the next page summarizes the differences between the notation used
in the text and the notation used by erwin. Note that erwin does not display
minimum cardinalities or nonidentifying relationships on N:M diagrams, nor does
it distinguish between ID-dependent and non–ID–dependent weak entities. These
must be indicated with a text note.
notation. It is easy to use but the entity boxes cannot be resized, leading to text
length limitations.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-4
ERWin Symbol Meaning
One – Mandatory
Symbol Used In
Database Concepts
Exclusive Subtype
Figure IM 5-1 Comparison of erwin Symbols to Crow’s Foot Symbols in Text
Most of the E-R diagrams in this IM chapter have been created using ER-Assistant, as it
comes closest to the book’s notation for nearly all E-R features. Here are details on how to
access these four software packages:
o Parallax Capital Partners’ erwin (formerly Erwin) is available in several editions.
Chapter Five – Data Modeling with the Entity-Relationship Model
o Note that the MySQL Workbench (see Appendix E – Getting Started with the
MySQL Workbench Data Modeling Tools) only creates database designs as
discussed in Chapter 6, not data models as discussed in this chapter. While this
• As mentioned in the previous teaching tip, most of the E-R diagrams in this IM
chapter were drawn with ER-Assistant. This software follows the book’s notation for
nearly all aspects of E-R diagrams with the exception of subtyping. Here we
summarize the differences between the book’s notation for subtyping and the ER-
Assistant notation:
o To model subtyping in general, ER-Assistant uses bold lines with an arrow
pointing to the supertype; the text uses standard-width lines with a circle.
o To model exclusive subtypes, ER-Assistant uses a “D” (for “disjoint”); the text
places an “X” inside the circle.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-6
o Here is Figure 5-13(b), with a “total” requirement added, and how it looks in ER–
Assistant:
• The Queen Anne Curiosity Shop and Morgan Importing projects in this chapter and
the next two chapters will give a consecutive, progressive introduction to data
modeling, database design, and database creation. The solutions for this chapter are
the data models, the solutions for Chapter 6 are the database designs, and the
databases are implemented as specific tables in Chapter 7. Use at least one of
these projects for an integrated set of assignments.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-7
❖ ANSWERS TO REVIEW QUESTIONS
5.1 Describe the two phases in designing databases that arise from the development of new
information systems.
5.2 In general terms, explain how a data model could be used to design a database for a
small library’s lending system.
The two steps of designing a database are:
(1) creating a data model, in which we work out the complexities of the database design,
and
5.3 Explain how a data model is like a building blueprint. What is the advantage of making
changes during the data modeling stage?
Before a building is actually constructed, it is carefully planned and designed. That work is
5.4 Who is the author of the entity-relationship data model?
Peter P. Chen, in his paper “The Entity-Relationship Model—Towards a Unified View of Data,”
Chapter Five – Data Modeling with the Entity-Relationship Model
5.5 Define entity. Give an example of an entity (other than one presented in this chapter).
5.6 Explain the difference between an entity class and an entity instance.
An entity class is a collection of entities and is described by the structure or format of the entities
5.7 Define attribute. Give an example attribute for the entity in your answer to Review
Question 5.5.
Attributes describe the entity’s characteristics. For example, in the Real Estate Agency example
5.8 Define identifier. Give an example identifier for the entity in your answer to Review
Question 5.5.
5.9 Give an example of a composite identifier.
5.10 Define relationship. Give an example of a relationship (other than one presented in this
chapter). Name your relationship.
A relationship is an association between two or more entity classes. For example, assume you
5.11 Explain the difference between a relationship class and a relationship instance.
Relationship classes are associations among entity classes, and relationship instances are
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-9
5.12 What is the degree of a relationship? Give an example of a relationship of degree three
(other than one presented in this chapter).
The number of entity classes in the relationship is the degree of the relationship. For example, in
5.13 What is a binary relationship?
5.14 Explain the difference between an entity and a table. Why is this difference important?
Formally, an entity is a database design concept while a table is the implementation of that entity
5.15 What does cardinality mean?
5.16 Define the terms maximum cardinality and minimum cardinality.
5.17 Give examples of 1:1, 1:N, and N:M relationships (other than those presented in this
chapter). Draw two E-R diagrams for each of your examples: one using the traditional
diamond notation and one using IE Crow’s Foot notation.
AGENT CAR1:1
AGENT_VEHICLE
Chapter Five – Data Modeling with the Entity-Relationship Model
5.18 Give an example for which the maximum cardinality must be an exact number (other
than those presented in this chapter).
In the Real Estate Agency example in question 5.5, each AGENT is required to work out of two
Chapter Five – Data Modeling with the Entity-Relationship Model
5.19 Give examples of M-M, M-O, O-M, and O-O relationships (other than those presented in
this chapter). Draw two E-R diagrams for each of your examples: one using the
traditional diamond notation and one using IE Crow’s Foot notation.
In the Real Estate Agency example in question 5.5, each AGENT must use an agency car when
on agency business. Further, to keep costs down the agency keeps exactly enough cars for the
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-12
5.20 Explain in general terms how the traditional E-R model, the IE Crow’s Foot version, the
IDEF1X version, and the UML version differ. Which version is used primarily in this text?
The traditional E-R model uses the notation shown in RQ 5-17 and 5-19—rectangles for entities,
5.21 Explain how the notations shown in Figure 5-7 differ.
The notations in Figure 5-7 show the difference between the original E-R model and the IE
5.22 Explain how the notations shown in Figure 5-9 differ.
The notations in Figure 5-9 show the difference between the original E-R model and the IE
5.23 What is an ID–dependent entity? Give an example of an ID-dependent entity (other than
one presented in this chapter).
Chapter Five – Data Modeling with the Entity-Relationship Model
An ID-dependent entity is one in which the identifier of one entity includes the identifier of
another entity.
5.24 Explain how to determine the minimum cardinality of both sides of an ID-dependent
relationship.
The ID-dependent entity (the “child”) cannot exist without the entity upon which it is dependent
(the “parent”). Therefore, the minimum cardinality from the ID-dependent entity to the parent is
always one (1).
5.25 What rules exist when creating an instance of an ID-dependent entity? What rules exist
when deleting the parent of an ID-dependent entity?
In order to create an instance of an ID-dependent entity, the parent entity upon which it depends
5.26 What is an identifying relationship? How is it used?
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-14
An identifying relationship is a special type of relationship. It is used to represent ID–
5.27 Explain why the relationship between BUILDING and APARTMENT discussed on page
222 is an identifying relationship.
The relationship between BUILDING and APARTMENT is an identifying relationship because
5.28 What is a weak entity? How do weak entities relate to ID-dependent entities?
5.29 What distinguishes a weak entity from a strong entity that has a required relationship to
another entity?
A strong entity that has a required relationship with another entity can and will exist outside the
5.30 Define subtype and supertype. Give an example of a subtype–supertype relationship
(other than one presented in this chapter).
A supertype is an entity class that contains a set of attributes common to what would otherwise be
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-15
5.31 Explain the difference between exclusive subtypes and inclusive subtypes. Give an
example of each.
A group of subtypes may be considered as either a set of exclusive subtypes or inclusive
5.32 What is a discriminator?
A discriminator is an attribute of the supertype entity that identifies the associated subtype entity.
5.33 Explain the difference between IS-A and HAS-A relationships.
The relationship between a supertype and its subtypes is sometimes called an IS-A relationship.
5.34 What is the most important reason for using subtypes in a data model?
The most important reason for using subtypes in a data model is to avoid value-inappropriate null
5.35 Describe the relationship between the structure of forms and reports and the data model.
5.36 Explain two ways forms and reports are used for data modeling.
Forms and reports are used to:
5.37 Explain why the form and report in Figure 5-15 indicate that the underlying relationship is
1:1.
The form shows that each MEMBER is associated with just one LOCKER. The report shows
Chapter Five – Data Modeling with the Entity-Relationship Model
5.38 Why is it not possible to infer minimum cardinality from the form and report in Figure 5–
15?
5.39 Describe two tests for determining if an entity is a strong entity.
The two tests are:
5.40 Why does the form in Figure 5-17 not indicate that the underlying relationship is 1:N?
What additional information is required to make that assertion?
The form in Figure 5-17 only shows that the relationship from MEMBER to UNIFORM is 1:N.
5.41 Explain why two forms or reports are usually needed to infer maximum cardinality.
5.42 How can you assess minimum cardinality for the entities in the form in Figure 5-17?
You cannot assess minimum cardinality from the form in Figure 5-17. In general, you cannot
determine minimum cardinality from forms and reports.
5.43 Explain why the form and report in Figure 5-19 indicate that the underlying relationship is
N:M.
5.44 Name three patterns that use ID-dependent relationships.
5.45 Explain how the association pattern differs from the N:M strong entity pattern. What
characteristic of the report in Figure 5-21 indicates that an association pattern is
needed?
Chapter Five – Data Modeling with the Entity-Relationship Model
The association pattern differs from the N:M strong entity pattern in that a new, third entity is
5.46 In general terms, explain how to differentiate an N:M strong entity pattern from an
association pattern.
In general, if there are one or more additional attributes associated with the relationship between
5.47 Explain why two entities are needed to model multivalued attributes.
In the E-R model, all attributes must have a single value. Therefore, multivalued attributes must
5.48 How do the forms in Figures 5-26 and 5-28 differ? How does this difference affect the
data model?
In Figure 5-26, CONTACT and PHONE are independent—any phone number can be used to
5.49 Describe in general terms the archetype/instance pattern. Why is an ID-dependent
relationship needed for this pattern? Use the CLASS/SECTION example shown in
Figure 5-30 in your answer.
The archetype/instance pattern generally has one entity that is a manifestation (or “instance”) of
5.50 Explain what caused the entities in Figure 5-31 to change from ID-dependent entities.
The entities in Figure 5-31 changed from ID-dependent entities to simply weak entities when a
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-18
5.51 Summarize the two sides in the argument about the importance of weak but not ID–
dependent entities.
Side one – Weak, non-ID-dependent entities are NOT important: Although they exist, they are
5.52 Give an example of the line-item pattern as it could be used to describe the contents of a
shipment. Assume that the shipment includes the names and quantities of various items
as well as each item’s insured value. Place the insurance value per item in an ITEM
entity.
This question actually looks ahead to the Morgan Importing project, where exactly such a
5.53 What entity type should come to mind when you see the words “For use by” in a form?
5.54 Give examples of 1:1, 1:N, and N:M recursive relationships (other than those presented
in this chapter).
Chapter Five – Data Modeling with the Entity-Relationship Model
1:1 Recursive: To be a member of the New City Club, you must have a sponsor, who
can sponsor at most one other member. Therefore each member is associated with exactly one
5.55 Explain why the data modeling process must be iterative. Use the Highline University
example.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-20
The data modeling process must be iterative because each new step may reveal new entities that