7.2 See the text in the book.
Answer:
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.
Answer:
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.
Answer:
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 a®
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.
Answer:
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.
Answer:
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.
Answer:
A ®® B
A ®® C
C ®® B
C ®® A
7.23 See the text in the book.
Answer:
The relational schema R = (A, B, C, D, E) and the dependencies of Exercise
7.21 constitutes a BCNF decomposition, however it is not in 4NF. It is
in BCNF because all FDs are trivial.
7.24 See the text in the book.
Answer:
4NF is more desirable than BCNF because it reduces the repetition of
information. If we consider a BCNF schema not in 4NF we observe that decomposition
into 4NF does not lose information provided that a lossless join decomposition
is used, yet redundancy is reduced.