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

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)..!