6. Second Normal Form 2NF

Most tables tend to have a single-attribute (i.e. simple) primary key. Like this

CUSTOMER

ID Firstname Surname Telephone email
2 Tom Smith 22323 ts@aol.com

But sometimes a table has a primary key made up of more than one attribute i.e. it has a compound primary key.

CONCERT

Venue Artist Attendance Profit Style
Wembley Girls Aloud 53000 12334 Girl band
NEC Leona Lewis 45000 66433 Female soloist

The table above is using both the venue and artist as the compound primary key.

It is in this situation that the extra rule for second normal form comes in handy. The rule states

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

So inherently, any table that is already in 1NF and has a simple primary key is automatically in second normal form as well.

Consider the Concert example above - this is NOT in second normal form. Notice the attribute called Style. This is describing the style of artist - it has nothing to do with where the concert took place! And so its value does not depend on EVERY part of the primary key, so the rule for second normal form is not being met.

The reason for this rule is to ensure there is no redundant data being stored.

For example, let's add another Girls Aloud concert to the table

Venue Artist Attendance Profit Style
Wembley Girls Aloud 53000 12334 Girl band
NEC Leona Lewis 45000 66433 Female soloist
NEC Girls Aloud 76090 53789 Girl band

Notice that the 'girl band' value is being repeated and so is causing the database to be bigger than it needs to be.

 

 

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