Third Normal Form
Episode #9 of the course Relational database design by Ben Brumm
Welcome to lesson nine!
In the last lesson, we finished taking our database through second normal form.
In this lesson, we’ll look at the last step of normalization, which is third normal form.
What Is Third Normal Form?
Third normal form is the desired state of most relational databases. It allows for all of the benefits of normalization that we’ve mentioned so far.
So how do we get to third normal form?
The rules of third normal form are:
1. All the requirements of second normal form are met.
2. There are no transitive functional dependencies.
We’ve met rule number 1.
What does rule number 2 mean? What is a “transitive functional dependency”?
It means that every attribute that is not the primary key must depend on the primary key and the primary key only.
For example, let’s say we have a table with three columns: A, B, and C. Column B depends on column A. Also, column C depends on column B. Therefore, column A determines column C.
This is a “transitive functional dependency,” and it should be removed by putting column C in a separate table.
Let’s apply these rules to our example. The student table looks like this:
Student (student ID, first name, last name, date of birth, address)
We could say that the address does not depend on the student ID, as there may be teachers living at that address as well. The details of an address (street name, suburb, etc.) are not dependent on a student.
So, we can split this out into another table.
Student (student ID, first name, last name, date of birth, address ID)
Address (address ID, address)
We can leave the address as a single field, or we can split it out. We’ll split it into separate columns.
Address (address ID, unit number, street number, street name, suburb, city, state, postal code, country)
How about the subject enrollment table?
Subject Enrollment (enrollment ID, student ID, subject ID)
This table is OK the way it is. All fields are dependent on the subject ID.
How about the subject and category tables?
Subject (subject ID, subject name, category ID, university ID)
Category (category ID, category name)
These tables are also OK. What about the teacher table?
Teacher (teacher ID, first name, last name, date of birth, address, subject ID)
This has the same address field as a student. To be consistent, it should match to the address table using the ID.
Teacher (teacher ID, first name, last name, date of birth, address ID, subject ID)
Finally, the university table:
University (university ID, name, address)
We need to link to the address table instead of using the address field here.
University (university ID, name, address ID)
So, our tables now look like this, now that they meet third normal form:
Student (student ID, first name, last name, date of birth, address ID)
Address (address ID, unit number, street number, street name, suburb, city, state, postal code, country)
Subject Enrollment (enrollment ID, student ID, subject ID)
Subject (subject ID, subject name, category ID, university ID)
Category (category ID, category name)
Teacher (teacher ID, first name, last name, date of birth, address ID, subject ID)
University (university ID, name, address)
Our diagram now looks like this:
So, that’s our student enrollment database in third normal form. It has been normalized and is now what you’d call a well-designed database.
Improvements can be made to this, depending on future requirements. But, based on the requirements we have, this database design is sufficient.
In the next and final lesson, we’ll determine some data types, naming conventions, and next steps to get this database created.
Until next time,
Ben
Recommended book
“SQL in 10 Minutes, Sams Teach Yourself” by Ben Forta
Share with friends