Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
    
Thanks a lot for the feedback and the addition of the 3NF decomposition. Your clear concise explanation was much appreciated –  cpowel2 Apr 9 '12 at 17:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.