Normalising Your Database - Third Normal Form (3NF)

Now we've looked at normalising a database to 1NF (First Normal Form) and to 2NF (Second Normal Form) we will continue to investigate normalising to Third Normal Form.
Concepts >> A table is in second normal form (2NF) and there are no transitive dependencies.
A transitive dependency is a type of functional dependency in which the value in a non-key field is determined by the value in another non-key field and that field is not a candidate key.
A Practical Approach

Again, look for repeated values in a non-key field as in the following example.

*ProjectNum	ProjectTitle	ProjectMgr	Phone
30-452-T3	STAR manual	Garrison	2756
30-457-T3	ISO procedures	Jacanda	2954
30-482-TC	Web site	Friedman	2846
31-124-T3	Employee handbook	Jones	3102
31-238-TC	STAR prototype	Garrison	2756
31-241-TC	New catalog	Jones	3102
35-152-TC	STAR pricing	Vance	3022
36-272-TC	Order system	Jacanda	2954

A table with a single field primary key and repeating values in non-key fields.

The phone number is repeated each time a manager name is repeated. This is because the phone number is only a second cousin to the project number. It's dependent on the manager, which is dependent on the project number (a transitive dependency).

The ProjectMgr field is not a candidate key because the same person manages more than one project. Again, the solution is to remove the field with repeating data to a separate table.

Complying with third normal form

As you've probably come to expect by now, you'll take the above table and create new tables to fix the problem.

1. Think about which fields belong together and create new tables to hold them.

2. Enter the sample data and check for unnecessarily (not part of primary key) repeated values.

3. Identify the primary key for each table and, if necessary, add foreign keys.

PROJECTS

*ProjectNum	ProjectTitle	ProjectMgr
30-452-T3	STAR manual	Garrison
30-457-T3	ISO procedures	Jacanda
30-482-TC	Web site	Friedman
31-124-T3	Employee handbook	Jones
31-238-TC	STAR prototype	Garrison
31-241-TC	New catalog	Jones
35-152-TC	STAR pricing	Vance
36-272-TC	Order system	Jacanda

MANAGERS

*ProjectMgr	Phone
Friedman	2846
Garrison	2756
Jacanda	2954
Jones	3102
Vance	3022

Re-examine your tables to make sure there are no unnecessarily repeating values in non-key fields and that the value in each non-key field is determined by the value(s) in the key field(s).

That wraps up this topic on normalising to Third Normal Form. In most cases 3NF should be sufficient to ensure that your database is properly normalised, however higher normal formscan be achieved.