Consider the relation schema R = (A, B, C, D, E) and the set F of functional dep
ID: 3874258 • Letter: C
Question
Consider the relation schema R = (A, B, C, D, E) and the set F of functional dependencies:
A --> BC
CD --> E
B --> D
E --> A
(a) Suppose schema R is decomposed into R1 = (A, B, C) and R2 = (A, D, E). Is this decomposition a lossless-join decomposition? Why? Is this decomposition a dependency preserving decomposition? Why?
(b) Suppose schema R is decomposed into R1 = (A, B, C) and R2 = (C, D, E). Is this decomposition a lossless-join decomposition? Why? Is this decomposition a dependency preserving decomposition? Why?
(c) Find all the candidate keys for R.
(d) Using the above functional dependencies, compute the canonical cover Fc.
(e) List the three design goals for relational databases, and explain why each is desirable.
(f) Decompose schema R into BCNF.
(g) Decompose schema R into 3NF.
(h) In designing a relational database, why might we choose a non-BCNF design?
Explanation / Answer
a) yes this decomposition is a lossless-join decomposition. because 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
Therefore R1 R2 R1.
b) No this decomposition is a not lossless-join decomposition.
Let R1 = (A, B, C),
R2 = (C, D, E),
and R1 R2 Not equal to C. Since C is not a candidate key
c) A -> BC, B -> D so A -> D so A -> DC -> E
therefore A -> ABCDE
E -> A, A -> ABCDE, so E -> ABCDE
CD -> E, so CD -> ABCDE
B -> D, BC -> CD, so BC -> ABCDE
All candidate keys are A, E, CD, and BC
d) The left side of each FD in F is unique. Also none of the attributes in the left side or right side of any of the FDs is extraneous. Therefore the canonical cover Fc is equal to F.
e)The design goals of relational database are :
They are desirable so we can maintain an accurate database, check correctness of updates quickly, and use the smallest amount of space possible.
f) All Decomposition In BCNF is:
R1 = {(A, B), (C, D), (B, C)}
R2 = {(A, B), (C, D), (A, C)}
R3 = {(B, C), (A, D), (A, B)}
g) R = (A, B, C, D, E) is already in 3NF.