Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-41
We need to determine if:
• the M-O relationship between MOVIE and SHOW_TIME is correct. We have
assumed that there may be MOVIEs in the database that are not currently scheduled
to be shown.
B This report was prepared for a user who is located near downtown Seattle. Suppose
that it is necessary to produce this same report for these theaters, but for a user
located in the greater Seattle area, such as Bellevue, Renton, Redmond, or Tacoma.
In this case, distance cannot be an attribute of THEATER. Change your answer in A
for this situation. Specify the entity identifiers and attributes. Name the relationships,
and identify the type and cardinality of each relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-42
The E-R Crow’s Foot model above is based on the model in question A, but adds the entities
AREA and DISTANCE. Note that this must be modeled as an association pattern rather than
an N:M relationship because of the attribute DistanceToCenterOfArea. The data for the new
parts of the model are contained in the following table:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
AREA
DISTANCE
ID-Dependent
1:N
M-O
We can infer that:
• the one-to-many relationship between AREA and DISTANCE can be inferred
because there are multiple distances to the AREA listed in the figure.
We need to determine if:
• the M-O relationship between AREA and DISTANCE is correct. We have assumed
that there may be AREAs in the database that do not currently have THEATER
THEATER
DISTANCE
ID-Dependent
1:N
M-O
Chapter Five – Data Modeling with the Entity-Relationship Model
C Suppose that you want to make this data model national. Change your answer to B
so that it can be used for other metropolitan areas. Specify the entity identifiers and
attributes. Name the relationships, and identify the type and cardinality of each
relationship.
This is the same data model shown in the answer to question B adjusted for multiple states.
The attributes State and ZIP where added to THEATER and the identifier has changed to
(TheaterName, City, State) to allow for non-unique TheaterNames. The Attribute AreaState
was added to AREA, and made part of the composite identifier (AreaName, AreaState) to
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-44
D Modify your answer to C to include the leading cast members. Assume that the role
of a cast member is not to be modeled. Specify the identifiers of new entities and
their attributes. Name the relationships, and identify the type and cardinality of each
relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-45
The E-R Crow’s Foot model above is based on the model in question C, but adds the entity
ACTOR. Since there are no additional attributes needed, this can be modeled as an N:M
relationship. The data for the new parts of the model are contained in the following table:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
We can infer that:
• the many-to-many relationship between ACTOR and MOVIE can be inferred based
on general knowledge of movies—One ACTOR can and does appear in more than
one MOVIE, and one MOVIE can and does have more than one ACTOR in the cast.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-46
E Modify your answer to C to include the leading cast members. Assume that the role
of a cast member is specified. Specify the identifiers of new entities and their
attributes. Name the relationships, and identify the type and cardinality of each
relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-47
The E-R Crow’s Foot model above is based on the model in question D, but adds the attribute
of Role. Because of this extra attribute, we now have to model the relationship between
ACTOR and MOVIE using the association pattern. The data for the new parts of the model
are contained in the following table:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
We can infer that:
• the one-to-many relationship between ACTOR and MOVIE_CAST can be inferred
based on general knowledge of movies—One ACTOR can and does appear in more
than one MOVIE.
We need to determine if:
• the M-O relationship between ACTOR and MOVIE _CAST is correct. We have
assumed that there may be ACTORs in the database that are not currently in any
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-48
5.61 Consider the three reports in Figure 5-59. The data are samples of data that would
appear in reports like these.
Figure 5-59 – Cereal Product Reports
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-49
A Make a list of as many potential entities as these reports suggest.
COMPANY
PRODUCT
INGREDIENT
B Examine your list to determine whether any entities are synonyms. If so, consolidate
your list.
COMPANY and ISSUER are synonyms. The company issues the reports to the FDA. We
will remove Issuer from the entity list.
==================================================================
Crow’s Foot Model for Question C [Shown here to save space]:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-50
C Construct a crow’s foot model showing relationships among your entities. Name
each relationship, and specify cardinalities. Indicate which cardinalities you can
justify on the basis of these reports and which you will need to check out with the
users.
The E-R Crow’s Foot model is shown on the previous page to save space. It is based on the
following data:
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
COMPANY
PRODUCT
Strong
1:N
M-O
COMPANY
FDA_REPORT
Weak
1:N
M-O
PRODUCT
FDA_REPORT
Weak
1:N
M-O
PRODUCT
PROD_VIT_IRON
ID-Dependent
Associative
1:N
M-O
PROD_NUTR
ID-Dependent
Associative
NUTRIENT
PROD_NUTR
ID-Dependent
Associative
1:N
M-O
INGREDIENT
PRODUCT
Strong
N:M
M-O
INGREDIENT
FDA_REPORT
Weak
1:N
M-O
INGREDIENT
ING_SUPPLIER
ID-Dependent
Associative
1:N
M-O
SUPPLIER
ING_SUPPLIER
ID-Dependent
1:N
M-O
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-51
Cardinalities that we can infer from the reports are shown in blue in the table. In particular,
we can infer that:
• a COMPANY can produce several PRODUCTS because of the multiple lines in the
FDA Report. We will assume that only COMPANYs that produce PRODUCTS
listed in the database are to be listed in COMPANY. We will further assume that
each PRODUCT is produced by only one COMPANY.
We can also infer:
• the mandatory one-to-many relationships between:
1. PRODUCT, NUTRIENT and the ID-dependent associative entity PROD_NUTR.
2. PRODUCT, VITAMIN_IRON and the ID-dependent associate entity
PROD_VIT_IRON.
We can do this because Nutrition Information is required to be printed on PRODUCT
packaging, and since that information must include information about NUTRIENTs and
VITAMIN_IRON.
We need to check:
• the assumption stated above that only COMPANYs that produce PRODUCTs listed
in the database are to be listed in COMPANY.
Although it’s not a cardinality, we also need to check:
• how to handle COMPANY and SUPPLIER. These could be designed as subtypes
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-52
5.62 Consider the CD cover in Figure 5-60.
Figure 5-60 – CD Cover
A Specify identifiers and attributes for the entities CD, ARTIST, ROLE, and SONG.
We need to be careful here. West Side Story was a Broadway production in New York City,
New York. Much of the data listed on the CD is about the Broadway production, not the CD
itself. For example, West Side Story was produced, directed, and choreographed by Jerome
Robbins, but nowhere are we told who actually produced the CD.
The only data that really applies to the CD itself are:
where the CDTitle is “West Side Story” and the CDComments is “Highlights From The
Complete Recording.”
To accommodate the other West Side Story material, let’s use a CDCoverNotes column.
Note that this is not a general-purpose remarks column – Many CDs have a set of cover notes
describing the CD artist(s) and the material on the CD. So, now we have:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-53
There is a problem with ROLE. Without a PRODUCTION entity, the identifier of a “role “ is
incomplete. ROLE should be an ID-dependent entity dependent on PRODUCTION.
Consider the ROLE of “Maria” —are we talking about Maria in West Side Story or Maria in
The Sound of Music? We’ll add a Production attribute to ROLE to take care of this, and use
the composite identifier (Production, Role).
Finally, another problem will be handling the attribute TrackNumber. The songs on the CD
are ordered by track number. But consider that this CD has the highlights from the complete
recording. Could the same SONG appear on another CD with a different TrackNumber? It
seems likely. A CD_TRACK entity, similar to a SALES_ITEM entity is needed.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-54
B Construct a crow’s foot model showing relationships among these four entities.
Name each relationship and specify cardinalities. Indicate which cardinalities you can
justify on the basis of the CD cover and which you will need to check out with the
users.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-55
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
CD
CD_TRACK
ID-Dependent
1:N
M-M
We can infer that:
• the one-to-many relationship between CD and CD_TRACK can be inferred because
there are multiple CD_TRACKs listed.
We need to determine if:
• the one-to-many relationship between SONG and CD_TRACK is correct. We have
assumed that one SONG can appear as a CD_TRACK on more than one CD. This
seems reasonable based on our general knowledge of CDs, however, we only have
data on one CD actually given to us.
ROLE
SONG
Strong
ARTIST
ROLE
Strong
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-56
C Consider a CD that does not involve a musical, so there is no need for ROLE.
However, the entity SONG_WRITER is needed. Create a crow’s foot model for CD,
ARTIST, SONG, and SONG_WRITER. Assume that an ARTIST can either be a
group or an individual. Assume that some artists record individually and as part of a
group.
The E-R Crow’s Foot model above is based on the answer to question B, but eliminates the
ROLE entity and recognizes that:
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-57
SONGs are recorded by ARTISTs.
ARTISTs may be either individuals (INDIVIDUAL) or groups (GROUP) but not both
(Exclusive subtypes).
The new entities and relationships are shown in the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
ARTIST
SONG
Strong
N:M
M-O
ARTIST
INDIVIDUAL
Subtype
1:N
M-O
ARTIST
GROUP
Subtype
1:N
M-O
INDIVIDUAL
SONGWRITER
Subtype
1:N
M-O
INDIVIDUAL
SINGER
Subtype
1:N
M-O
SONG
SONGWRITER
Strong
N:M
M-O
INDIVIDUAL
GROUP
Strong
N:M
M-O
We can infer that:
• the many-to-many relationship between ARTIST and SONG can be inferred because
ARTIST replaces ROLE in this model, but otherwise has the same relationship to
SONG that ROLE had.
• the one-to-many relationship between ARTIST and INDIVIDUAL can be inferred
from the definition of a supertype/subtype relationship.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-58
We need to determine if:
• the many-to-many relationship between SONG and SONGWRITER is correct. We
have assumed that one SONG can be written by many SONGWRITERs, and that one
SONGWRITER can write many SONGs. This seems reasonable based on our
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-59
D Combine the models you developed in your answers to B and C. Create new entities
if necessary, but strive to keep your model as simple as possible. Specify identifiers
and attributes of new entities, name new relationships, and indicate their
cardinalities.
Chapter Five – Data Modeling with the Entity-Relationship Model
Page 5-60
The E-R Crow’s Foot model above is based on the answer to question C, but adds back in the
ROLE entity. Now however, we must figure out how to deal with ROLE. We could create
an entity named PERFORMER and make ARTIST and ROLE its subtypes. But a simpler
key such as “ArtistID” to replace “ArtistName” when creating the actual database.
The new entity and relationships are shown in the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
We can infer that:
• the one-to-many relationship between ARTIST and ROLE can be inferred from the
definition of a supertype/subtype relationship.
We need to determine if:
• the M-O relationship between INDIVIDUAL and ROLE is correct. We have
assumed that a ROLE must be played or filled by an INDIVIDUAL, but that we may
have INDIVIDUALs in the database who have not played or filled any ROLE.