Search code examples
database-normalization3nf

The right way to normalize database into 3NF

I have this database:

R(A, B, C, D, E)
Keys: A
F = {A -> B, D -> E, C -> D}

I normalize it into 3NF like this:

R(A, B, C, D, E)
Keys: AD
F = {AD -> B, AD -> E, C -> D}

What I do is when I check D -> E, D is not a superkey and E is not a key attribute, so I treat D and A as a superkey {AD}. When I check C -> D, C is not a key but D is a key attribute so it's OK.

Is my normalization correctly?

Solution

  • There is a problem in your input data. If the relation R has the dependencies F = {A -> B, D -> E, C -> D}, then A cannot be a key. In fact, a key is a set of attributes whose closure determines all the attributes of the relation, which is not the case here, since:

    A+ = AB

    From F, the (only) possible key is AC, in fact

    AC+ = ABCD

    Normalizing means to reduce the redundancy by decomposing a relation in other relations in which the functional dependencies do not violate the normal form, and such that joining the decomposed relations, one can obtain the original one.

    In you solution, you do not decompose the relation, but only change the set of dependencies with other dependencies not implied by the first set.

    A correct decomposition would be instead the following:

    R1 < (A B) ,
{ A → B } >

R2 < (C D) ,
{ C → D } >

R3 < (D E) ,
{ D → E } >

R4 < (A C) ,
{ } >

    The algorithm to decompose a relation into 3NF can be found on any good book on databases.