Database Design – 2NF Decomposition and Normalization

database-designnormalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't been able to find a really good example online. I am studying for a test and would like to know if my reasoning on how to accomplish a 2NF decomposition is correct

R = {a, b, c, d, e, f, g}
F = {AB –> C, A –> DE, B –> F, F –> GH, D –> IJ }

The first thing I did was to find the super key which was easy to see (AB)+ = R, However I am not sure if this is what 2NF definition means when it uses the term "key"

2nd I used A.A. and combined some terms in F (just to make it more managable)

F = {AB –> C, A –> DEIJ, B –> FGH}

3rd I remove and partial functional dependencies I am not really sure ( I did look it up to no eval) what a PFD is I think its when you have a FD where the LHS is a proper subset of the superkey in this case

A –> DEIJ and B –> FGH

4th I break it into relations that remove the violations from step 3

R1 = AB –> C

R2 = A –> DEIJ

R3 = B –> FGH

I'm really looking for someone to give me feedback on if I am understanding the concept correctly any help would be greatly appreciated

Thanks

Best Answer

There's a little aphorism that goes:

  • The key (1NF)
  • The whole key (2NF)
  • And nothing but the key (3NF)

. . . So help me Codd

In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3 etc.).

R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH, D --> IJ }

2NF deals with 'the whole key' - if you have a composite key and some members of the relation are dependent on a part of that key then they should be split off into their own relations. In your case:

  • R1 = AB --> C is in 2NF as 'AB' is 'the whole key'

  • R2 = A --> DEIJ is 2NF but not 3NF as A --> DE, but A --> IJ is not 3NF as the real dependency of I and J is D --> IJ. D is not a key of this relation (A --> DEIJ), so A --> IJ violates the 'nothing but the key' princple of 3NF.

  • R3 = B --> FGH is in 2NF but not 3NF as B --> F is a correct relation and F is dependent on the entirety of the key 'B'. However, B --> GH is not in 3NF as the correct relation is F --> GH. B --> GH violates the 'nothing but the key' principle, as G ahd H are correctly dependent on the non-key attribute F.

Your decomposition to 2NF is correct. Decomposition to 3NF requires taking the non-key attributes that have their own dependencies into separate relations.

The relation in 3NF would look like:

  • R1 = AB --> C

  • R2 = A --> DE (I and J are dependent on the non-key attribute D)

  • R3 = B --> F (G and H are dependent on the non-key attribute F)

  • R4 = D --> IJ

  • R5 = F --> GH

Related Question