Second Normal Form, Part 2
Episode #8 of the course Relational database design by Ben Brumm
Welcome to lesson eight.
In the last lesson, we looked at applying the rules of second normal form to our database and the relationships between tables.
In this lesson, we will continue with applying the rules of second normal form to our database.
Our database looks like this at the moment:
Student (student ID, first name, last name, date of birth, address)
Subject (subject ID, subject name, category ID)
Category (category ID, category name)
Teacher (teacher ID, first name, last name, date of birth, address, subjects taught)
University (university ID, name, address)
We have joined the subject and category table. But how is the student and subject table related? A subject can have many students, and a student can have many subjects.
This is a many-to-many relationship, which we learned about in lesson 6.
To capture this, we need a joining table. This is a table where the ID fields of each table and the relationship between these IDs are captured.
Normally, we can call the joining table a concatenation of the two tables it refers to, but it’s preferable to call it something that it represents. We could call the act of a student linking to a subject as a subject_enrollment.
So, we have a table like this:
Subject Enrollment (enrollment ID, student ID, subject ID)
The enrollment ID is the primary key that uniquely identifies the row. The student ID and subject ID link to the student and subject tables, respectively.
Now, we’ve covered the student, subject, and category tables.
What about the teacher table?
Teacher (teacher ID, first name, last name, date of birth, address, subjects taught)
The rule of second normal form states that “each non-key attribute must be functionally dependent on the primary key.”
It means that each field needs to be determined from the ID and unique to that row.
Most of the fields are OK: first and last name, date of birth, and address.
Subject taught does not meet this rule. It refers to the name of the subject the teacher teaches. However, it’s not the primary key in this table or the subject table, and it could change.
Let’s confirm the relationship. Does a teacher have many subjects, or does a subject have many teachers? The first statement is true. So, let’s add the subject ID to the teacher table instead of the subject name.
Teacher (teacher ID, first name, last name, date of birth, address, subject ID)
Now, let’s look at the university table.
University (university ID, name, address)
All of the fields look OK. The name and address are unique to the row. How are universities related to other tables? Subjects are taught at a university. So, does a subject have many universities, or does a university have many subjects?
For our purposes, it’s the second statement.
So, we add the primary key of the university table (university ID) to the subject table.
Subject (subject ID, subject name, category ID, university ID)
Our database now looks like this, and it is in second normal form:
Student (student ID, first name, last name, date of birth, address)
Subject (subject ID, subject name, category ID, university ID)
Subject Enrollment (enrollment ID, student ID, subject ID)
Category (category ID, category name)
Teacher (teacher ID, first name, last name, date of birth, address, subject ID)
University (university ID, name, address)
And our diagram of the database looks like this:
Our database is now in second normal form!
The next step is to move the database to third normal form, which we will look at in the next lesson.
Until next time,
Ben
Recommended book
“The Language of SQL: How to Access Data in Relational Databases” by Larry Rockoff
Share with friends