Database Systems Homework 4

?. You are to design a database that maintains information for producing a weekly television guide for
a given region (such as Northern California). The data should include information about television
shows, television networks, cities, channels, show times, etc. For starters, you may make the following
assumptions:
• A given channel in a given city is associated with one network.
• A given show is either owned by a network (and shown on a channel associated with that network)
or is a local show and may be shown on any channel.
• Not all shows are shown in all cities, and the days and times for a given show may di↵er from
city to city.
• You may ignore cable channels, which generally are not city-dependent.
Please feel free to make additional assumptions about the real world in your design, as long as the
assumptions are reasonably realistic and are stated clearly as part of your solution.
Specify an entity-relationship diagram for your database. Don’t forget to underline key attributes and
include arrowheads and double lines.
Note that this question is fairly open-ended and there is no single right answer, but some designs are
better than others.
?. This problem is based on an E/R design for a database used in a manufacturing company shown in
Figure 1. This database stores information about parts. Each part has a part number, which uniquely
identifies the part. A part may in fact be an assembly, which consists of some number of one or more
subparts. For example, a bicycle might be described as an assembly consisting of one frame and two
wheels; a frame is just a basic part; a wheel is an assembly consisting of one tire, one rim, and 48
spokes. Each assembly is also associated with the cost of assembling its subparts.
Convert the E/R diagram to relations. For the translation of subclasses, assume that we generate
multiple tables for specialization and that a subclass does not inherit non-key attributes from its
superclass.
Figure 1: E/R Diagram for Question 3
2
? ? ? $4?????%BUBCBTF?4ZTUFNT)PNFXPSL???
??. Suppose that we decompose the schema R(A, B, C, D, E, F) into (A, B, C, F) and (A, D, E). When
the following set of functional dependencies hold, is the decomposition lossless?
A ! BC, CD ! E,B ! D, E ! A
Explain your answer.
?. List non-trivial functional dependencies satisfied by the following relation. You do not need to find
all functional dependencies. It is enough to identify a set of functional dependencies that imply all
functional dependencies that is satisfied by the relation.
A B C
a1 b1 c2
a1 b1 c2
a2 b1 c1
a2 b1 c3
?? Assume the following set of functional dependencies hold for the relation R(A, B, C, D, E): A ! BC, CD ! E, B ! D, E!A
(a) Is E a key for R? Explain your answer.
(b) Is BC a key for R? Explain your answer.
?. Assume the following set of functional dependencies hold for the relation R(A, B, C, D, E, F ):
A ! BC, C ! E, B!D
Is it in BCNF? Explain your answer. If it is not, normalize it into a set of relations in BCNF.
?