1. 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 differ 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.
2. 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
3.. 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. (hint: what are the attributes that are FD on A?)
4. List non-trivial functional dependencies satis ed by the following relation. You do not need to nd
all functional dependencies. It is enough to identify a set of functional dependencies that imply all functional dependencies that is satis ed 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
Is E a key for R? Explain your answer.
Is BC a key for R? Explain your answer.
6. 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.
3
