databasedev.co.uk - database solutions and downloads for microsoft access

Designer for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available

Third Normal Form (3NF) - Normalising Your Database:

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.

A table with a single field primary key and repeating values in non-key fields.
*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

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 forms can be achieved.