db-3-solution

Database System Concept the 3rd Assignment

Possible Answers

Problem 1：

a) AB is the only key. AB+ includes C because of AB->C, then we can add E because of C->E, and we can add D because of B->D.

It is easy to check that A+ = A, and B+ = BD, so AB is minimal.

b) B->D or C->E are the obvious choices.

c) Using B->D, we get S = BD and T = ABCE.

Using C->E, we get S = CE amd T = ABCD.

d) The key for BD is B; for CE it is C, and for ABCD and ABCE it is AB.

e) and f) BD and CE are in BCNF. ABCD and ABCE are not.

B->D is a problem for ABCD, causing us to decompose into BD and ABC. C->E is a problem for ABCE, causing us to decompose into CE and ABC. Either way, we wind up decomposing into the three BCNF relations ABC, BD, and CE.

Problem 2

(1)Minimal Key for R: (A, B, D)

(2)Minimal Basis for R1: AB->C

* Take off 1pt. if not minimal (e.g., more stuff added).

(3)Minimal Basis for R2: AD->E, ABE->F

* Take off 2pt. for missing one FD.

* Take off 1pt. if not minimal (e.g., more stuff added).

(4)Key(s) for R2: (A, B, D).

(5)BCNF violation in R2: AD->E

Decomposition: S(A, D, E) and T(A, B, D, F)

(6) Both S and T are in BCNF.

S has one FD: AD->E, and AD is the key. No BCNF violation.

T has one FD: ABD->F, and ABD is the key. No BCNF violation.

Problem 3

Correct: c) Only AB and AC are keys

There are four FD's: A -> D, B -> C, D -> E and CE -> B. A is not on the right hand side of any FD. So A must be part of any key for R. This leaves us with four possible keys: A, AB, AC, and ABC. It is easy

to see that:

A -> ADE

AB -> ABCDE

AC -> ABCDE

ABC -> ABCDE

Thus, the keys for R are AB and AC.

ABC is not a key because it properly contains a key.

It is important to note that when considering the projected relation R, we do not "project out" FD's. All of them need be considered when reasoning about FD's in R.

Problem 4-1: (d)

Many people thought that T was redundant. I assume they imagined that T was necessarily the composition of R and S. However, there is nothing in the E/R model that requires it, nor should there be. T can in general map an A-entity to a completely different C entity from what you would get by following R then S. Put another way, if we didn't tell you what pairs were in relationship T, but let you see all the other entity sets and relationships, you couldn't figure out anything about what T was. Others thought R was redundant. I assume that was because you remembered that when converting to relations, R goes away. However, the reason it goes away is because we introduce a relational structure, namely the relation for A, that includes the information of R.

Problem 4-2: (b)

The database schema is A(a,b,d), B(b,e), C(c,f), S(b,c), and T(a,b,c). Problem 5-1: (d)

The fact that R is many-one and onto (i.e., each A is associated with exactly one B does not constrain B, except that it must have at least one entity. All the A's could map to 1 B or 100 different B's, or anything in between, and there could be any number of B's that are not associated with any A.

Problem 5-2: (b)

The schema for R is (a,b,c), including the full key for B.

Problem 5-3: (a)

Many many people got this wrong. The problem is that people confuse the idea of where the key comes from in a weak entity set with what are the entities themselves. For example, if B were ``crews'' as in the text, and A was movies, while R indicated which crew worked on which movie, a movie would be related to a crew, i.e., a group of people. The fact that we couldn't uniquely refer to that crew without knowing the studio (entity set C) they worked for is irrelevant; R still connects movies to crews and not to studios.

Problem 6:

Correct: c) 1000

R has two functional dependancies: AB -> C and AC -> B.

Consider tuples in R with the first attribute A = a.

If (a, b, c) is a tuple,

- there cannot be another tuple (a, b', c) with b <> b'

- there cannot be anotehr tuple (a, b, c') with c <> c' Effectively, there is a "pairing" of b and c.

Since B has 20 entities while C has 200, there can be at most 20 such pairs (minimum of 20 and 200 is 20).

Thus, any particular value of A = a can be associated with only 20

pairs of the form (b, c). Since there are 50 distinct values of A, the total size of R can be at most 50 x 20 = 1000 tuples.

Problem 7:

I had expected that people would use subclasses to represent the condition that express trains don't stop at local stops. However, that solution is hard, and few got it completely correct. There is a simpler solution that ignores this constraint and just treats the problem as two entity sets and a relationship. I had to take off a point, because this solution omits a part of the problem statement. However, since simplicity is part of good design, I feel guilty. Here are the Solution with subclasses and the Solution without subclasses.

For part (b), suitable relations are:

Train(number, engineer)

Station(name, address)

ExpressStop(number, name, time)

Stop(number, name, time)

If you don't like these, I don't blame you. You have to guess whether a train is local or express before you can find when it stops in Palo Alto. However, that is what the E/R-to-relations crank gives you.

Note that the subclasses don't have any attributes, so there is no need to create relations. If you do, you get relations whose schemas are contained in another schema. That's not illegal. However, in this case there is no justification. For example, listing all the express trains is not useful. E.g., we can find out which trains are express by looking at the number component of ExpressStop.

Error Codes

A. Fails to represent the condition that an express train can only stop at an express stop (‐1).

B. Wrong key (-2).

C. Created a subclass that is not distinguished in any way from its superclass (-2).

D. Not following the E/R-to-Relations rules (-4).

E. Making Stop a connecting (weak) entity set (-3).

F. Creating a relation whose schema is contained in another's (-1).

G. Making the Stop relationship more than 2-way (-2). Remember that a relationship relates all the entity sets it connects. There is no ``or'' option.

H. Failure to represent a subclass fact, e.g., an express train is a kind of train, and the attributes of trains belong with Train, not Express Train (-3).

Problem 8: