Question 2: Decomposition [7 marks Consider a relation S on attributes (A, B,C,

Question

Question 2: Decomposition [7 marks Consider a relation S on attributes (A, B,C, D, E) and the following functional dependencies. AB D BCD EA The relation S meets the conditions for 3NF but not BCNF (a) Give a decomposition of S into BCNF (use S1, S2,. to name the new relations) (b) Does your decomposition from part (a) preserve functional dependencies? Justify your answer. (c) Which (if any) of the following decompositions have a lossless join under the FDs above? Decomposition 1 RI(A, C, D), R2(A, B,C), R3(B,C, D, E) Decomposition 2 Ri(A, D, E), R2(A, B,C, D), R3(A, B, E) Decomposition 3 R1(A, C, D), R2(B, C, D), R3(A, B, C, E)

Explanation / Answer

Given functional dependencies are:
AB->D
AC->D
BCD->EA
E->AB

a)Now let us construct attribute closures:
A+ --> A
E+ --> EABD
AB+ -->ABD
AC+ ---> ACD
ABC+ --> ABCDE
BCD+ --> BCDEA
ECD+ ---> ECDAB  

Therefore super keys are: {ABC, BCD, ECD}.Super keys are the keys which contains all elements of a relation.
Candidate keys are nothing but minimal degree keys of super keys. i.e {ABC, BCD, ECD}

Now AB->D is a not a key of relation R. So that it violates BCNF. So that decompose the given relation as (ABD)(ACE)
Now AC->D is a not a key of relation R. So that it violates BCNF. So that decompose the given relation as (ACD)(BCD)(ABCE)
Now BCD->EA is a key of R. It dosn't violates BCNF. So that no decomposition needed.
Now E->AB is a not a key of relation R. So that it violates BCNF. Here (ACD)(BCD)(ABCE) satisfies FD. So that final loss less decomposition:
(ACD)(BCD)(ABCE).

b) Yes, this decomposition preserve all functional dependencies and ofcource performed decomposition based on FDs.

c) Decomposituion 3: (ACD)(BCD)(ABCE).

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.