databasedatabase-normalizationfunctional-dependencies3nfbcnf

Is this in BCNF, 3NF or neither?


Set of functional dependencies F is { AE -> BCD, B -> E }. I say it is in 3NF, but my teacher says it's in neither 3NF nor BCNF.

I obtained candidate keys AE and AB.

In the first functional dependency the left side is a candidate key and in the second E is contained in a candidate key, so it is in 3NF.

How do I show this in BCNF, 3NF or neither?


Solution

  • Assuming that all the attributes of the relations are A B C D and E, and that the only dependencies given are the two described (F), you are correct. Since the (only) candidate keys are correctly A E and A B, and since the functional dependency B → E has a determinant which is not a superkey, the relation is not in BCNF. Given one of the definitions of BNCF: “for all the non-trivial dependencies X → Y of F+, X is a superkey”, there is a theorem that shows that a necessary and sufficient condition for this is that the property of being a superkey holds for all the dependencies in F.

    On the other hand, since E is a prime attribute, i.e. an attribute of a candidate key, the dependency B → E does not violate the 3NF, so that the relation is in 3NF. This, again, given one of the definitions of 3NF: “for all the non-trivial dependencies X → A in F+, then X is a superkey or A is a prime attribute”, is due to a theorem that says that this condition is equivalent to check, “for each functional dependency X → A1,...,An in F, and for each i in {1..n}, either Ai belongs to X, or X is a superkey or Ai is prime”. And this is satified by the two dependencies of F.