database-normalizationfunctional-dependencies3nfbcnf

# Understanding BCNF Functional Dependency

I was following this tutorial for BCNF decomposition. The functional dependencies given are:

``````A->BCD
D->B
``````

These are concerned with the relation R(A,B,C,D). The conditions for BCNF include:

The relation must be in 3NF and when X->Y, X must be a superkey

The given relation doesn't have a transitive FD but D->B is a partial FD--or is it that the three FDs represent 3 separate relations?

If they represent 3 separate relations, why is it that D is not a key and if they are all in the same relation then D->B is a partial functional dependency.

Solution

• If we write the given set of FDs with singleton right-hand side, we have -

A->B
A->C
A->D
BC->A
BC->D
D->B

We can see at once 2 transitive dependencies. We have A->D and D->B so we don't need A->B and also we have BC->A and A->D so we don't need BC->D. So now we have -

A->C
A->D
BC->A
D->B

or

A->CD
BC->A
D->B

The keys here are A, BC and CD. Since each attribute of the relation R comes at least once in each of the keys, all the attributes in your relation R are prime attributes.

Note that if a relation has all prime attributes then it is already in 3NF.

Hence the given relation R is in 3NF. I hope you get why you are completely wrong here - "The given relation though doesn't have a transitive FD but D->B is a partial FD ". I just proved that the relation is in 3NF which is a higher normal form then 2NF and hence in turns proves that the relation is in 2NF and hence no partial dependency.

To be in BCNF, for each functional dependency X->Y, X should be a key. We see that the last functional dependency D->B violates this since D is not a key. Therefore to convert into BCNF we can break our relationship R into R1 and R2 as -

R1(A,C,D)

R2(B,D)