Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-21
❖ ANSWERS TO EXERCISES
Answer the following questions using IE Crow’s Foot notation.
5.56 Examine the subscription form shown in Figure 5-53. Using the structure of this form, do
the following:
A Create a model with one entity. Specify the identifier and attributes.
SUBSCRIPTION
SubNumber
StartDate
EndDate
AmtDue
Chapter Five – Data Modeling with the Entity-Relationship Model
B Create a model with two entities, one for customer and a second for subscription.
Specify identifiers, attributes, relationship name, type, and cardinalities.
Note that subscription is a weak (indicated by the corners) but NOT ID-dependent (indicated
by the dotted line) entity. The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
C Under what conditions do you prefer the model in A to that in B?
Model A would be the best model if a Customer can only have one subscription.
D Under what conditions do you prefer the model in B to that in A?
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-23
5.57 Examine the list of e-mail messages in Figure 5-54. Using the structure and example
data items in this list, do the following:
Figure 5-54 – E-mail List
A Create a single-entity data model for this list. Specify the identifier and attributes.
No attribute is unique, even a combination of all the shown attributes is not necessarily
unique. Therefore, a surrogate identifier of EmailMessageID was created.
EMAIL_MESSAGE
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-24
B Modify your answer to A to include entities SENDER and SUBJECT. Specify the
identifiers and attributes of entities and the types and cardinalities of the
relationships. Explain which cardinalities can be inferred from Figure 5-54 and which
need to be checked out with users.
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
SENDER
EMAIL_MESSAGE
Strong
1:N
M-O
SUBJECT
EMAIL_MESSAGE
Strong
1:N
M-O
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-25
5.58 Examine the list of stock quotes in Figure 5-55. Using the structure and example data
items in this list, do the following:
Figure 5-55 – Stock Quotations
A Create a single-entity data model for this list. Specify the identifier and attributes.
Chapter Five – Data Modeling with the Entity-Relationship Model
B Modify your answer to A to include the entities COMPANY and INDEX. Specify the
identifier and attributes of the entities and the types and cardinalities of the
relationships. Explain which cardinalities can be inferred from Figure 5-55 and which
need to be checked out with users.
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
INDEX
STOCK_QUOTE
ID-Dependent
1:N
M-M
COMPANY
STOCK_QUOTE
ID-Dependent
1:N
M-O
We can infer that:
• the one-to-many relationship between INDEX and STOCK_QUOTE can be implied
because there are multiple quotes in the single INDEX shown in the figure.
We need to determine if:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-27
C The list in Figure 5-55 is for a quote on a particular day at a particular time of day.
Suppose that the list were changed to show closing daily prices for each of these
stocks and that it includes a new column: QuoteDate. Modify your model in B to
reflect this change.
The E-R Crow’s Foot model above is based on the data in the table on the next page.
Note that both the SYMBOL and STOCK_QUOTE entities are necessary. This is because a
Chapter Five – Data Modeling with the Entity-Relationship Model
RELATIONSHIP
CARDINALITY
[Blue =
Inferable]
PARENT
CHILD
TYPE
MAX
MIN
INDEX
SYMBOL
ID–
Dependent
1:N
M-M
We need to determine if:
• the one-to-many relationship between COMPANY and STOCK_QUOTE is correct.
It is only correct if a COMPANY can be listed on multiple INDEXes.
• the M-M relationship between INDEX and SYMBOL is correct. We have assumed
that we are only recording stocks included in some stock INDEX.
• the M-O relationship between COMPANY and STOCK_QUOTE is correct. We can
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-29
D Change your model in C to include the tracking of a portfolio. Assume the portfolio
has an owner name, a phone number, an email address, and a list of stocks held.
The list includes the identity of the stock and the number of shares held. Specify all
additional entities, their identifiers and attributes, and the type and cardinality of each
relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
RELATIONSHIP
CARDINALITY
[Blue =
Inferable]
PARENT
CHILD
TYPE
MAX
MIN
INDEX
SYMBOL
ID–
Dependent
1:N
M-M
COMPANY
SYMBOL
1:N
M-O
We can infer that:
• the one-to-many relationship between INDEX and STOCK_QUOTE can be implied
because there are multiple quotes in the single INDEX shown in the figure.
• the one-to-many relationship between STOCK_QUOTE and SYMBOL can be
because a single stock can be an item in many different portfolios.
We need to determine if:
• the one-to-many relationship between COMPANY and STOCK_QUOTE is correct.
It is only correct if a COMPANY can be listed on multiple INDEXes.
• the M-M relationship between INDEX and SYMBOL is correct. We have assumed
that we are only with stocks listed on an INDEX.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-31
• the M-O relationship between PORTFOLIO and PORT_ITEM is correct. We have
assumed that a PORTFOLIO can be created before any PORT_ITEMS are added.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-32
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-33
The E-R Crow’s Foot model on the previous page is identical to the design in part D except
for the addition of two subtypes STOCK_PURCHASE and STOCK_SALE to the entity
PORT_ITEM. An additional attribute, TransactionType, is added to PORT_ITEM as a
discriminator.
The information about the supertype/subtype relationships is in the following table:
RELATIONSHIP
CARDINALITY
[Blue =
Inferable]
PARENT
CHILD
TYPE
MAX
MIN
We can infer that:
• the one-to-many relationships between (PORT_ITEM and STOCK_PURCHASE)
and (PORT_ITEM and STOCK_SALE) are implied because of the supertype/subtype
relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-34
5.59 Figure 5-56 shows the specifications for single-stage air compressor products. Note that
two product categories are based on Air Performance: The A models are at 125 pounds
per square inch of pressure, and the C models are at 150 pounds per square inch of
pressure. Using the structure and example data items in this list, do the following:
Figure 5-56 – Air Compressor Specifications
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-35
A. Create a set of exclusive subtypes to represent these compressors. The supertype will
have attributes for all single-stage compressors, and the subtypes will have attributes for
products with the two different types of Air Performance. Assume that there might be
additional products with different types of Air Performance. Specify the entities,
identifiers, attributes, relationships, exclusive/inclusive and total/partial properties, and
possible discriminator.
The entity in this example is COMPRESSOR, with identifier Model.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-36
B. Figure 5-57 shows a different model for the compressor data. Explain the entities, their
types, the relationship, its type, and its cardinality. How well do you think this model fits
the data shown in Figure 5-56?
Figure 5-57 – Alternative Model for Compressor Data
In the model in Figure 5-57, SS_COMPRESSOR is a strong entity and
AIR_PERFORMANCE_TYPE is an ID-Dependent weak entity. The relationship is one-to–
many since an SS_COMPRESSOR may have more than one AIR_PERFORMANCE_TYPE.
However, the maximum cardinality of the one-to-many relationship is currently two (2) since
there are currently only two AIR_PERFORMANCE_TYPEs. The relationship is ID-
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-37
Further, each combination will have at least one additional attribute associated with it—
namely Price. Therefore a better model would be an ID-Dependent associative model, with
two strong entities—COMPRESSOR and PERF_TYPE. That model would look like this:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-38
C. Compare your answer in question A with the model in Figure 5-57. What are the
essential differences between the two models? Which do you think is better?
Model A models the ModelType as a type hierarchy currently with two subtypes. The model
D. Suppose you had the job of explaining the differences in these two models to a
highly motivated, intelligent end user. How would you accomplish this?
I would focus the discussion on:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-39
5.60 Figure 5-58 shows a listing of movie times at theaters in Seattle, Washington. Using the
data in this figure as an example, do the following:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-40
A Create a model to represent this report using the entities MOVIE, THEATER, and
SHOW_TIME. Assume that theaters may show multiple movies. Although this report
is for a particular day, your data model should allow for movie times on different days
as well. Specify the identifiers of the entities and their attributes. Name the
relationships and the type and cardinality of each relationship. Explain which
cardinalities you can logically deduce from Figure 5-58 and which need to be
checked out with users. Assume that distance is an attribute of THEATER.
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
We can infer that:
• the one-to-many relationship between MOVIE and SHOW_TIME can be inferred
because there are multiple SHOW_TIMEs listed.