# Solutions for Exercise no. 8

From "Database System Concepts"

7.2 See the text in the book.
A decomposition {R1, R2}is a lossless-join decomposition if R1 Ç R2 ® R1 or R1 Ç R2® R2. Let R1 = (A, B, C), R2 = (A, D, E), and R1 Ç R2 = A. Since A is a candidate key (see exercise 6.15), therefore R1Ç R2 ® R1.

7.3 See the text in the book.
Following the hint, use the following example of r: R1 = (A, B, C), R2 = (C, D, E)

 A B C D E a1 b1 c1 d1 e1 a2 b2 c1 d2 e2

pR1(r) would be :

 A B C a1 b1 c1 a2 b2 c1

pR2(r) would be :

 C D E c1 d1 e1 c1 d2 e2

pR1(r) npR2(r) would be:

 A B C D E a1 b1 c1 d1 e1 a1 b1 c1 d2 e2 a2 b2 c1 d1 e1 a2 b2 c1 d2 e2

Clearly pR1(r) npR2(r) is not equal to r. Therefore this is a lossy join.

7.5 See the text in the book.
The simple answer: F1 contains no dependencies with D on the right side of the arrow. F2 contains no dependencies with B on the left side of the arrow. Therefore for B ® D to be preserved there must be an FD B®a in F1+ and and D in F2+, so B ® D would follow by transitivity. Since the intersection of the two schemas is A, a = A. Observe that B ® A is not in F1+ since B+ = BD.

The complex answer: The dependency B ® D is not preserved. F1, the restriction of F to (A, B, C) is A ® ABC, A ® AB, A ® AC, A ® BC, A ® B, A ® C, A ® A, B ® B, C ® C, AB ® AC, AB ® ABC, AB ® BC, AB ® AB, AB ® A, AB ® B, AB ® C, AC (same as AB), BC (same as AB), ABC (same as AB). F2, the restriction of F to (C, D, E) is A ® ADE, A ® AD A ® AE, A ® DE, A ® A, A ® D, A ® E, D ® D, E (same as A), AD, AE, DE, ADE (same as A). (F1 È F2)+ is seen not to contain B ® D since the only FD F1 È F2 with B as the left side is B ® B, a trivial FD. Note that CD ® ABCDE is also not preserved.

7.8 See the text in the book.
From Exercise 6.15 we know that B ® D is nontrivial and the left hand side is not a super key. By the algorithm of Figure 7.6. we derive the relations {(A,B,C,E), (B,D)} This is in BCNF.

7.11 See the text in the book.
First we note that the dependencies given in Exercise 7.2 form a canonical cover. Generating the schema from the algorithm of Figure 7.7 we get:
R' = {(A,B,C), (C, D, E), (B, D), (E, A)}
Schema (A,B,C) contains a candidate key. Therefore R' is a 3NF dependency preserving lossless-join decomposition.

Note that the original schema R = (A, B, C, D, E) is already in 3NF. Thus, it was not necessary to apply the algorithm as we have done above. The single original schema is trivially a lossless join, dependency preserving decomposition.

7.16 See the text in the book.
A ®® B
A ®® C
C ®® B
C ®® A

7.23 See the text in the book.