Search code examples
databasedatabase-normalization

Database and the 2nd Normal Form

I have a table that satisfies the following relations: 

R(A, B, C, D, E, F, G, H, I, J, K, L, M, N)
{A, B, C} is the primary key. 
{D, E} forms a candidate key. 

The following functional dependencies exist among the attributes of the relation:
{A, B} -> {H,I}
{D} -> {F,G}
{J} -> {K,L}
{E} -> {M,N}

I need to decompose this to the 2nd normal form, so I separate {AB->HI}, {D->FG}, {E->MN} into independent tables. But how about {J->KL}? How should I normalize this one?

Solution

  • A table is in 2nf if and only if all nonprime attributes are functionally dependent on all the attributes of every candidate key, not just on a subset of attributes of any candidate key.

    According to your comment, you're given two keys, {ABC} and {DE}.

    In the case of {AB}->{HI}, the attributes H and I are dependent on part of a key. ({AB} is part of the key {ABC}.) So you're correct in projecting {HI} from R. Ditto for {D}->{FG} and {E}->{MN}.

    • R1 = {ABCDEJKL}
    • R2 = {ABHI}
    • R3 = {DFG}
    • R4 = {EMN}

    When it comes to the attributes J, K, and L, you have to ask the same question.

    • Is J functionally dependent on any combination of the attributes A, B, C, D, and E, excluding the given keys {ABC} and {DE}?
    • Is K functionally dependent on any combination of the attributes A, B, C, D, and E, excluding the given keys {ABC} and {DE}?
    • Is L functionally dependent on any combination of the attributes A, B, C, D, and E, excluding the given keys {ABC} and {DE}?

    What do you think about J, K, and L?