Solutions
Chapter 4
4.1.1
4.1.2
a)
b)
c)
In c we assume that a phone and address can only belong to a single customer (1–
m relationship represented by arrow into customer).
d)
In d we assume that an address can only belong to one customer and a phone can
exist at only one address.
If the multiplicity of above relationships were m-to-n, the entity set becomes
4.1.3
4.1.4
a)
b)
c)
The relationship “played” between Teams and Players is similar to relationship
“plays” between Teams and Players.
4.1.5
4.1.6 The information about children can be ascertained from motherOf and
fatherOf relationships. Attribute ssNo is required since names are not unique.
4.1.7
4.1.8
a)
(b)
4.1.9
Assumptions
cs157–3 spring 09).
4.1.10
Given that for each movie, a unique studio exists that produces the movie. Each
star is contracted to at most one studio.
But stars could be unemployed at a given time. Thus the four-way relationship in
fig 4.6 can be easily into converted equivalent relationships.
4.2.1
Redundancy: The owner address is repeated in AccSets and Addresses entity sets.
Simplicity: AccSets does not serve any useful purpose and the design can be more
4.2.2
Studios and Presidents can be combined into one entity set Studios with
Presidents becoming an attribute of Studios under following circumstances:
4.2.3
4.2.4 The entity sets should have single attribute.
a) Stars: starName
b) Movies: movieName
c) Studios: studioName. However there exists a many-to-many relationship between
4.2.5
From Augmentation rule of Functional Dependency,
given
B -> M (B=Baby, M=Mother)
then
BND -> M (N=Nurse, D=Doctor)
Hence we can just put an arrow entering mother.
b)
c)
Again from Augmentation rule of Functional Dependency,
given
BM -> D
4.2.6
a)
b) Transitivity and Augmentation rules of Functional Dependency allow arrow
entering Mothers from Births. However, a new relationship in below figure
represents more accurate information.
c)
4.2.7
In below figure there exists a many-to-one relationship between Babies and
Births and another many-to-one relationship between Births and Mothers. From
4.3.1
a)
b)
A captain cannot exist without a team. However a player can (free agent). A
recently formed (or defunct) team can exist without players or colors.
c)
Children can exist without mother and father (unknown).
4.3.2
a)
The keys of both E1 and E2 are required for uniquely identifying tuples in R
4.3.3
Special Case: All entity sets have arrows going into them i.e. all relationships
4.4.1
No, grade is not part of the key for enrollments. The keys of Students and
Courses become keys of the weak entity set Enrollments.
4.4.2
It is possible to make assignment number a weak key of Enrollments but this is
not good design (redundancy since multiple assignments correspond to a course).