Book

Question

What should be the decomposition for the following relation ? Any one please ?

R(A,B,C,D,E,F)

FD'S ARE :

AB -> CDEF
BC -> EF
E -> F

According to me th realtion is in 2nd NF but not in 3NF for
3NF the decomposition should be
R1 (A,B,C,D),R2(A,B,E),R3(A,B,F),R4(B,C,E),R5(B,C,F),R5(E,F).

Answer 1

R(A,B,C,D,E,F)

FD'S ARE :

AB -> CDEF
BC -> EF
E -> F

 

Here the candidate key is AB

so there will be transitive dependency

BC -> EF
E -> F

to remove the transitive dependency the relation can be decomposed as

R1 (A,B,C,D),R2(B,C,E,F)

Now from R1 and R2 ...R can be brought back as BC is a key in R2

Now R2 has transitive depenency E -> F

Now R2 can be decomposed to make it inot 3NF

R1 (A,B,C,D),R2(B,C,E) and R3(E,F)

all the FD's are preserved and it is a lossless decomposition

Comments

All dependencies are not preserved sir

AB->E and AB->F are lost for that can we create R3(A,B,E) and R4(A,B,F)

---

They are satisfied in the closure property...

see

AB -> CD

BC -> E
E -> F

 

these FD's are enough...from these you can derive the other FD's

simply the minimal FD's are enough for FD preserving

Answer 2

Ck=AB

There does not exist any partial dependency that's why it is by default in 1NF and 2NF

Since BC->EF

and E->F will be transitive dependency

therefore find out closure of 

closure of(E)=EF

Closure of(BC)=BCEF

tables R1(EF)  R2(BCE)   R3(ABCD) it is in 3NF.