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