CODEWITHSUNDEEP
databasedatabase-normalization
Some Noob Student
Some Noob Student

Reputation: 14574

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?

Upvotes: 1

Views: 621

Answers (2)

Ashish Singh Parihar
Ashish Singh Parihar

Reputation: 79

According to your FD's :

I think your selection of PK/CK is not good here.

Only CK / PK would be ={ABCDEJ} Because closure of {ABCDEJ} ={ABCDEFGHILKLMN}All Attributes , Hence Prime attributes (6)={A,B,C,D,E,J}

In 2NF , we don't allow partial dependencies. Here Partial Functional Dependencies: {A, B} -> {H,I} {D} -> {F,G} {J} -> {K,L} {E} -> {M,N}

So after removing these Partial Dependencies your table will be decomposed like this(Removal of Partial Dependency in 2NF):

R1 = {ABHI}

R2 = {DFG}

R3 = {JKL}

R4 = {EMN}

R5 = {ABCDEJ}

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

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?

Upvotes: 1

Related Questions