Search code examples
databasenormalizationdatabase-normalizationthird-normal-form

When will it be in 3NF?

I am having a hard time understanding the 3 Normal form.

3 NF: 2 NF + No transitions

So, for eg: If I have, 

A -> B
B -> C

Then the above is sort of a transition relation and hence won't be in 3 NF? Am I understanding it correctly?

But in this answer What exactly does database normalization do? , by paxdiablo, it says,

Third normal form (3NF) - 2NF and every non-key column in a table depends on nothing but the key.According to this, it will be in 3 NF. Where am I going wrong?

Solution

  • A relation is in 3NF if it is in 2NF and:

    1. either each attribute depends on a key,
    2. or, if an attribute depends on a non-key, then it is prime.

    (being prime means that it belongs to a key).

    See for instance Wikipedia.

    A relation is in Boyce-Codd normal form if only the first condition hold, that is:

    1. each attribute depends on a key

    So, in your example, if the relation has only three attributes A, B and C and the two dependencies, it is not in 3NF, since C is not prime, and depends on B, which is a not a key. On the other hand, if there are other attributes, and C is a key or part of a key, then it could be in 3NF (but this depends on the other functional dependencies, that should satisfy the above conditions).

    The 2NF says that each non-prime attribute depends on each whole candidate key, and not by part of it. For instance, if a relation has attributes A, B and C, the only key is AB, and B -> C, then this relation is not in 2NF.