8. 2NF continued

Of course there could be more than one attribute related to different parts of the primary key. Consider a table like this:

CONCERT

Venue Artist Date Attendance Profit City No1Hits Style
Wembley Girls Aloud 1/10/09 53000 12334 London 5 Girl band
NEC Leona Lewis 1/10/09 45000 66433 Birmingham 2 Female soloist
NEC Girls Aloud 7/11/09 76090 53789 Birmingham 5 Girl band

As before the Style attribute only depends on Artist, but now No1Hits also only depends on the Artist. This table also includes City and this only depends on the Venue.

So to make this database into second normal form, four tables need to be created

CONCERT

VenueID Artist Date Attendance Profit
005 0112 1/10/09 53000 12334
006 0115 1/10/09 45000 66433
006 0112 7/11/09 76090 53789

STYLE

Style ID Style
001 Girl band
002 Solo artist
003 Rap

ARTIST

ArtistID Artist No1Hits StyleID
0112 Girls Aloud 20 001
0115 Leona Lewis 3 002

VENUE

Venue ID Venue City
005 Wembly London
006 NEC Birmingham

Summary

The rules for second normal form are

  • Non-key attributes must depend on every part of the primary key
  • The table must already be in first normal form

challenge see if you can find out one extra fact on this topic that we haven't already told you

Click on this link: Second normal form