Assume a relation schema, R, that has attributes A, B, C, D, and E. The only functional dependencies (FD’s) are AB ? CDE, D ? E, and CD ? B.

(a) What is a key of R?
(b) What does the FD D ? E mean?
(c) What does the FD D ? E imply about the redundant storage of information in an instance of R? Explain.
(d) The insertion of a new row in R might cause a violation of an FD. In order to prevent this for the FD D ? E we might try one of the following.
(e) Is R in 3rd Normal Form? Explain.
(f) What is the condition for losslessness? Explain why it is important for a decomposition to be lossless?
(g) Give a lossless decomposition of R into two 3rd Normal Form relations R1 and R2. Demonstrate that R1 and R2 are in 3rd Normal Form by showing that the FD’s that apply to each satisfy the conditions for 3rd NF. Show that the decomposition is lossless.

(a) What is a key of R?
Solution:
AB

(b) What does the FD D ? E mean?
Solution:
For each value of D there can be at most one value of D.

(c) What does the FD D?E imply about the redundant storage of information in an instance of R? Explain.
Solution:
If there are a number of rows in an instance of R with the same value of D they will all have the same value of E. This means that the value of E is stored multiple times unnecessarily.

(d) The insertion of a new row in R might cause a violation of an FD. In order to prevent this for the FD D ? E we might try one of the following.
i. We might add the constraint UNIQUE (D,E). Would this be an appropriate was to enforce the FD? Explain your answer.
Solution:
No. Since D is not a key there can be multiple rows with the same value of D and hence the same value of E. The constraint would prevent this although multiple rows with the same value of DE models the real world.
ii. Write an assertion that prevents a violation of the FD from taking place.
Solution:

CREATE ASSERTION X

CHECK ((SELECT MAX COUNT DISTINCT E

FROM   R

GROUP BY  D  ) <= 1)

or

CREATE ASSERTION X

CHECK NOT EXISTS

( SELECT

FROM   R R1, R R2

WHERE R1.D = R2.D AND R1.E <> R2.E)

(e) Is R in 3rd Normal Form? Explain.
Solution:
No. The FD D ? E violates the conditions for 3rd Normal Form.
i. {E} is not contained in {D}
ii. D is not a superkey
iii. E is not part of a key

(e) Is R in 3rd Normal Form? Explain.
Solution:
No. The FD D ? E violates the conditions for 3rd Normal Form.
i. {E} is not contained in {D}
ii. D is not a superkey
iii. E is not part of a key

(f) What is the condition for losslessness? Explain why it is important for a decomposition to be lossless?
Solution:
A decomposition of R into R1 and R2 is lossless if R = R1 œ R2 and either R1 T R2 ?

(g) Give a lossless decomposition of R into two 3rd Normal Form relations R1 and R2. Demonstrate that R1 and R2 are in 3rd Normal Form by showing that the FD’s that apply to each satisfy the conditions for 3rd NF. Show that the decomposition is lossless.
Solution:
R1: ABCD with FDs AB? CD, CD?B
R2: DE with FD D? E
R1 is in 3rd Normal Form since AB is a key and B is part of a key. R2 is in 3rd Normal Form since D is a key.
lossless: {ABCD} {DE} = {D} and D is a key of R2.