CODEWITHSUNDEEP
databasedatabase-normalizationfunctional-dependencies3nfbcnf
FAYA
FAYA

Reputation: 155

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?

Upvotes: -1

Views: 1014

Answers (2)

philipxy
philipxy

Reputation: 15118

You need to use a definition of a NF when you claim/show that a relation is in it.

You don't actually say what all the attributes are. I'll assume the attributes are A through E. Otherwise, the CKs (candidate keys) are not what you say.

You are right in your (loose) argument against BCNF. You are using the definition that all determinants of non-trivial FDs (functional dependencies) are out of superkeys. You found a counterexample FD B → E.

If it were an either-or question re BCNF vs 3NF you could stop there.

in the first functional dependency the left side is a candidate key and in the second E is contained in a candidate key

You don't show that the table meets the conditions of either of the following definitions (from Wikipedia that happen to be correct) that a table is in 3NF if and only if:

both of the following conditions hold:

  • The relation is in 2NF
  • Every non-prime attribute is non-transitively dependent on every [candidate] key

for each of its functional dependencies X → A, at least one of the following conditions holds:

  • X contains A
  • X is a superkey
  • each attribute in A-X is prime

You seem to using definition 2 (but not saying so). You show bullet 2 holds for AE → BCD. Pointing out that E is prime in B → E seems to be part of showing that E-B is all prime. But you need to show every FD satisfies a bullet. Note that more FDs hold than the given ones: assuming F is a cover for the FDs that hold, Armstrong's axioms tell you what all the FDs that hold are.

In practice it can be easier to show a schema is in 3NF by applying a 3NF algorithm.

Upvotes: 0

Renzo
Renzo

Reputation: 27424

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.

Upvotes: 1

Related Questions