madeeasy

Question

how many foreign keys required for the following relation R (ABCDEF) with FD ( AB ⇒C,BC ⇒A , AC ⇒B ,D ⇒E) into BCNF and lossless join, dependency preserving decomposition?

Answer 1

First we find key of the given relation.

(AC)+ = (ABCD) and hence AC is the candidate key of the relation and hence A and C which are determinants are not superkeys.So we need to separate them.

(AB)+ = (ABC)

(BC)+ = (ABC)

(AC)+ = ( ABC)

So we take R1(A,B,C) in  which will have AB--> C , AC --> B and BC --> A.

D+  = (DE) .So we take R2(D,E) which will have D --> E.

Hence all original FDs are mapped into subrelations so dependency preservation is done.Also R1 and R2 are in BCNF.But still lossless property is not satisfied since we have nothing common between R1 and R2.So we take another table R3 which will have attributes (F,D) so on taking join with R2 we will get D which is superkey for R2 and then take intersection of R1 and joined result(which contains D,E and F as attributes), which is now NULL.So we need to include more attributes in R₃ from R₁ such that we get common attributes such that it is a key for either R₁ or the joined result.So we can include either AB, BC or AC in the joined result of R₂ and R₃ and hence in R_3.Here in R_3, the attribute {D} will reference to R₂ and AB (or) AC (or) BC will reference to R₁. 

So minimum no. of relations = 3 and minimum no. of foreign keys = 2 [Here D and (AB) or (BC) or (AC) ]

Comments

if you are making the third relation as ABCD then all have to hold and thats what I am saying else that wont be lossless. I hope you are decomposing like this.

---

No if you decompose like having ABCD in one relation then D is not involved in any of the dependencies of subrelation ABCD since ,

D⁺ = (D,E) according to original FD set.So u have to include D in ABCD for key formation.Hence keys in (ABCD) will be ABD,BCD,ACD.But the determinants are AB,BC and AC so neither of them will remain superkey and (ABCD) is not in BCNF as every determinant must be a superkey in BCNF.Hence the decomposition done by you is wrong.

Hence the correct decomposition is :

R(A,B,C) , S(D,E) AND T(A,B,D,F) OR T(B,C,D,F) OR T(A,C,D,F)

Hope you have now understood now.

---

Got you. Thanks.

Answer 2

2 fk

Comments

please explain

---

Plz dont write the answer @ritu shekhawat only, provide valid explanation as well.