database-normalizationfunctional-dependencies3nfbcnf

Understanding BCNF Functional Dependency

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

A->BCD
BC->AD
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)