Normalization
Definition Examples and Recommendations
Third 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. And, the table is 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 third
normal form 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 third
normal form tables
look like:
The two tables are now in third normal form.
