Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Contact  Privacy  Links
Relational Table Design
Primary Key Fields
Linking Database Tables
3RD Normal Form
 





Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
3rd Normal Form
Table Design Summary
Table Design Tutorial

Article:
Repair Corrupted Table

 

Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


3RD Normal Form Table Design

Normalization Definition Examples and Recommendations

3rd Normal Form Definition (simplified)

A table whose non-primary key fields are dependent only on the primary key and therefore have no dependence (relationship to) any other non-primary key field in the table is consider in third normal form (3NF). And, additionally the table must first be in 2nd normal form.

Example 1

Here is the classic employee table example:

Emp_SSN
Emp_Name
Street
City
State
Zip


Here we have a transitive dependency between Zip field and City and State fields - what ever that means.  I guess it means they are a little dependent but no big deal.

Since a zip will tell you what state and what city an address is in.  Therefore, to make this table 3NF we need to split out city state and zip fields into a table of their own and then just have the zip in the employee table:

Emp_SSN
Emp_Name
Street
Zip

The zip tables is as follows:

Zip
City
State

The two tables are now in third normal form.  Some people may argue that there is some transitive relationship between City and State - but we've already gone further in normalizing the Employee table than is customary in an Access database.

This problem might be more obvious if we add mailman to the list:

Emp_SSN
Emp_Name
Street
Zip
Mail_Man

Here you can see that we need a table of Zips defining each Mail_Man.  Here is the resulting tables assuming a mail man only works in one zip

Zip
Mail_Man

Emp_SSN
Emp_Name
Street
Zip

Example 2

Here's a more straightforward example:

3rd Normal Form Table Design

Above we have a Students table with SSN, Student's Advisor and the Advisor's Phone number.
Clearly, the advisor's phone is dependent upon the advisor and the either candidate primary key for this table (Student_Name and Student_SSN).  To change this table to third normal form we need to break out the Student Advisor and Advisor Phone fields into a separate table.  There resulting 3rd Normal Form tables look like:

Access Table Normalization

Access 3NF

The two tables are now in 3rd normal form.








A Blue Claw Database Design Article:

Why Choose Microsoft Access
 





Blue Claw Database Design Downloadable Tutorial:
TransferText & OutputTo Microsoft Access Download.  





A Blue Claw Database Design Template:

Preventive Maintenance (PM) Access Template
 




Contact Information

Copyright 2000-2014 Blue Claw Database Design