database

Question

decompose relation into 2NF ,3NF,BCNF

R(ABCDE) {AB--->C, DE----->C , B------->D}

R(ABCD) {AB----->C,C-------->D,D-------->A}

Answer 1

For R(ABCDE) :
    Decomposition into  3NF = [{ABC}, {BD}, {DEC}, {ABE}]
    Decomposition into  BCNF = [{ABC}, {BD}, {ABE}] or [{ABE}, {BD}, {DEC}]

For R(ABCD) :
    Decomposition into  3NF = [{ABC}, {CD}, {AD}]
    Decomposition into  BCNF = [{ABC}, {CD}]

Comments

why u specify table {ABE} in R(ABCDE) as all dependency are being present in {ABC}, {BD}, {DEC}??

---

dependency D------>A  not present in relation .??

---

to make the decomposition Lossless.

because BCNF decomposition need not be dependency preserving.

---

thanku this thing was not know to me..

Answer 2

a) Candidate key = {ABE}

R(ABCDE) can be decomposed to R1(ABC),  R2(BD) and R3(BE). this relation is in 2nf, 3nf and bcnf

b) Candidate key = {AB, BC, BD}

Decomposition into R1(ABC) & R2(ACD) gives a relation in 2NF as candidate key for R1 is {AB} and R2 is {C}.

Now, further decomposition of the relation R1 into R3(CD) and R4(AD) gives decomposition into 3NF. Thus, relations in 3NF will be R3, R4 and R1. This relation is in BCNF too.

Comments

Error :  ADE is not a Candidate Key

---

actually ADE can never be key because u cant miss B attribute in key..even u cant miss A attribute

---

Thanks for pointing out. ADE is not a candidate key. I'll edit the answer. But still the decomposition remains same.

---

shefalishrma u r considering C------>D as partial dependency.??

---

Only when we are taking BC as candidate key, in that case, it is.

Can you please elaborate the reason for this doubt.

---

i was considering  dependency C----->D  as PD !!because here C is proper subset of candidate KEY {BC}  and in RHS of dependency is D which is NON PRIME attribute IN {BC}..
 but i was thinking in other keys {AB},  {BD} in these keys ..! key {BD} has D as PRIME ATTRIBUTE...so for that reason i was asking..???

---

Wrong : this decomposition for R(ABCDE) has failed to satisfy the Lossless Property.

---

plzz explain how lossless property is not satisfied.??

---

Here is how we check for Lossless property :

---

yes u r rt!! but ABC ⋂ BD gives B which is key for relation BD .????

---

that's correct, but later when we combine new relation R₁₂(ABCD) and R₃(BE); its lossy

---

 R12(ABCD) and R3(BE); its lossy because B being common ,but it is not the key for  R12(ABCD) and R3(BE)..!