Search code examples
database-normalizationfunctional-dependencies3nf

Is this 3NF decomposition done right?


The relation R(ABCDEF) is given with the following functional dependencies:

A->B
B->C
A->D
A->E,F
E->F

State the strongest normal form this is in and then convert it into third normal form.

I think the relation is in first normal form since there exists a partial dependency while the candidate key is A. Therefore to convert it into second/third normal form I decomposed R(ABCDEF) into

R1(A,B,D,E,F) 
R2(B,C) 
R3(E,F)

Aren't all these in third normal form? Have I done the decomposition right?


Solution

  • A schema is not in 2NF when a non-prime attribute is depending from a proper subset of a candidate key (the so-called partial dependency) (see for instance wikipedia). Since in your schema the only candidate key is A, this is not possible, so the schema is in 2NF.

    Your decomposition in 3NF is not correct, since in the relation R1(A B D E F) the dependency E->F holds, with the attribute F not prime and non-trivially depending on an determinant which is not superkey.

    A correct decomposition is:

    R1(A B D E)
    R2(B C)
    R3(E F)
    

    Note that in the case of your decomposition, R3 is contained in R1, which is not possible.