9. Third Normal Form

For a database to be in third normal form, the following rules have to be met

  • It is already in 2NF
  • There are no non-key attributes that depend on another non-key attribute

What this is trying to do is to spot yet another source of redundant data. If the value of an attribute can be obtained by simply making use of another attribute in the table, then it does not need to be there. Loading that attribute into another table and linking to it will make the database smaller.

To clarify, consider the table below

CONCERT

Venue Artist Date Attendance Profit City Country
Wembley Girls Aloud 1/10/08 53000 12334 London UK
NEC Leona Lewis 1/10/08 45000 66433 Birmingham UK
Carnegie Hall Girls Aloud 7/11/08 76090 53789 New York USA

Notice that the country could be obtained by referring to the City - if the concert was in London then you know it is also in the UK - no need to look at the primary key!

So to make this database into third normal form, split the table as follows

CONCERT

Venue Artist Date Attendance Profit City*
Wembley Girls Aloud 1/10/08 53000 12334 London
NEC Leona Lewis 1/10/08 45000 66433 Birmingham
Carnegie Hall Girls Aloud 7/11/08 76090 53789 New York

CITIES

City Country
London UK
Bimingham UK
New York USA

The new table called CITIES has City as the primary key and country as an attribute. The Concert table has City as a foreign key. So now you can obtain the country in which any particular concert took place and there is no redundant data.

 

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

Click on this link: Third normal form

 

Copyright © www.teach-ict.com