Given a relational schema R with attributes A, B, C and D where the functional d
ID: 3669168 • Letter: G
Question
Given a relational schema R with attributes A, B, C and D where the functional dependencies AB -> C, C -> D, and D -> A are supposed to hold.
(20 points) Given a relational schema R with attributes A, B, C and D where the functional dependencies AB >C,CD, and D>A are supposed to hold. (a) Is R in BCNF? If yes, explain why. If not, list all violations. (b) If R is NOT in BCNF, give it a lossless BCNF decomposition. (c) Does your decomposition in (b) preserve the given functional dependencies? 6. Explain. (d Give a 3NF decomposition for R (e) Does your decomposition in (d) preserve the given functional dependencies? Explain.Explanation / Answer
AB -> C
C -> D
D -> A
Decompse the above
A -> C
B -> C
C -> D
D -> A
a) R is not in BCNF
Logically, since C and D are the only attributes that can be determined via other attributes, we can deduce that the keys will contain the other attributes, thus we prefrom a smaller attribute closure
AB -> ABCD
CD -> ABCD
Relation R is in BCNF iff whenever there is a non trivial FD A1 A2 … An -> B1 B2 … Bn for R { A1 A2 … An } is a superkey.
AB->C and AB->D: The set closure of AB+ is { A, B, C, D } therefore the left hand side of this FD is a superkey.
C->D and C->A: The set closure of C+ is { C, D, A } therefore the left hand side is not a superkey. BCNF Violation
D-> A: C->D: The set closure of D+ is { D, A } therefore the left hand side is not a superkey. BCNF Violation
DB->C: The set closure of DB+ is { D, B, C, A } therefore the left hand side of this FD is a superkey.
No 3NF violations. No decomposition needed.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.