1. 3rd Normal Form
It is now time to discuss the third and final step in the process of normalizing databases that will be taught in this course.
2. Defining 3rd Normal Form
To satisfy 3rd Normal Form, we must first satisfy the requirements of 2nd Normal Form.
3rd Normal Form has an additional requirement that no transitive dependencies are present in the table.
This means that non-key columns are solely dependent on the table PRIMARY KEY.
3. Transitive dependencies
Transitive dependencies are relationships within a database table that involve three columns.
Imagine a table exists which has multiple columns: X, Y, and Z.
Column Y is determined by column X. So, if we know the value of column X, column Y is also known.
In this scenario, the same relationship structure exists between column Y and column Z. Knowing the value of column Y removes all ambiguity about the value of column Z.
The transitive dependency, in this case, is between column X and column Z. Knowing the value of column X leaves no ambiguity about the value of column Z due to both columns' relationship to column Y. This is the case, even though, column X and column Z are not directly related.
4. Example: course room assignments
An example will help to clarify this concept.
Returning to our school database, imagine that we have a table storing data about course room assignments including room numbers.
In this school, teachers teach in the same classroom throughout the day.
If we know the name of the course, we automatically know the teacher.
Knowing the name of the teacher is all we need to know to get the teacher's room number. Therefore, the course name is sufficient to know the room number for the class.
5. Example: course room assignments
This relationship creates a transitive dependency because knowing the course name also implies knowledge of the room number. You may be wondering what problems such a table structure presents. There are a few.
6. Example: course room assignments
This table design requires changing multiple rows to update a teacher's room number.
7. Example: course room assignments
Structuring the table in this way does not allow new teachers to be added to our database unless an associated course is assigned to that teacher.
8. Example: course room assignments
If all of the teacher's courses are deleted from the table, we no longer have information on the teacher (specifically her name and room number).
9. Example: course room assignments
So, how do we change the structure of our data in order to alleviate these potential problems? One change that will be helpful is to create a table that stores teacher-specific data.
10. Example: course room assignments
One potential implementation of the teacher table is shown here. We have assigned each teacher a unique identifier using the PRIMARY KEY of the table and can associate the room number with the teacher in a single location.
11. Example: course room assignments
This allows us to simplify the structure of our course table. Here the table name is changed to course underscore assignment to better reflect the data in the table.
We remove the "teacher" and "room underscore number" columns and add a "teacher underscore id" FOREIGN KEY column. We still have access to the same information as before. The course is associated with the teacher name and room number through the "teacher underscore id" FOREIGN KEY which can be accessed through the use of a JOIN statement which you have seen in previous courses.
12. Let's practice!
That wasn't so bad, was it!? Satisfying 3rd Normal Form is typically the last step in the normalization process. Now that we have seen how to satisfy 3rd Normal Form, let's practice what we learned to hammer home this concept.