Database Normalization - Page 6

March 22, 2002

The definition of 3rd normal form
It's in 2nd normal form
It contains no transitive dependencies (where a non-key attribute is dependent on another non-key attribute).

We can narrow our search down to the Employee table, which is the only one with more than one non-key attribute. Employee name is not dependent on either Rate category or Hourly rate, the same applies to Rate category, but Hourly rate is dependent on Rate category. So, as before, we remove it, placing it in it's own table, with the attribute it was dependent on as key, as follows:

Employee project table

Project number - primary key
Employee number - primary key

Employee table

Employee number - primary key
Employee name
Rate Category

Rate table

Rate category - primary key
Hourly rate

Project table

Project number - primary key
Project name

These tables are all now in 3rd normal form, and ready to be implemented. There are other normal forms - Boyce-Codd normal form, and 4th normal form, but these are very rarely used for business applications. In most cases, tables in 3rd normal form are already in these normal forms anyway.

 » See All Articles by Columnist Ian Gilfillan