Third Normal Form

 

3RD Normal Form Table Design

NormalizationDefinition 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:

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:

The two tables are now in 3rd normal form

 

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016