# How to reconstruct functional dependencies from 3NF decomposition

database-designnormalization

I have the following decomposition for the relation R(A,B,C,D):

• R1 = (A,B),
R2 = (B,C),
R3 = (A,D).

It is given that AD is the only candidate key for the original relation and that R is not in BCNF. The decomposition above preserves the functional dependencies as well as is lossless-join decomposition (that is R1 natural join R2 natural join R3 = R).

What could be a set of functional dependencies for the decomposition?

3NF is always lossless decomposition as well as one that preserves functional dependencies. Therefore, I think the decomposition given is in 3NF. If we were to reverse engineer 3NF decomposition algorithm we could say that the original set of functional dependencies was {a→b, c→b, a→d} indeed R3 contains all of the attributes of the candidate key. But the problem is that AD is not the candidate key of the above set. In order to for AD to be the only candidate key I'd have to either add another dependency or add more attributes to the existing dependencies. In either case the ensuing 3NF decomposition would not result in one like in the OP.

Is there something I'm missing to find an appropriate dependency set?

#### Best Answer

If AD is the only candidate key of R, then we know that in the original relation the dependency AD → BC holds.

But, since the relation is not in BCNF, we know that there are also other dependencies for which the determinant is not a superkey. Given the fact that the decomposition preserves the dependencies, we know that the other dependencies are constituted by attributes that are in the decomposed relations.

The simplest possible case is that there are dependencies A→B and B→C. So the original relation can be:

R<(ABCD), {AD→BC, A→B, B→C}>


In fact, if we apply both the classical BCNF decomposition algorithm as well as the 3NF synthesis algorithm we obtain the specified decomposition.