Second Normal Form

29.03.2017 |

Episode #7 of the course Relational database design by Ben Brumm

 

Welcome to lesson seven.

In the last lesson, we learned about relationships and how tables should be related to each other.

In this lesson, we’ll explain second normal form and take our example database through the process of getting to second normal form.

 

What Is Second Normal Form?

Second normal form is the second stage of normalization. It improves the quality of the database design by adhering to several rules.

The rules for a database being in second normal form are:

1. It must fulfil the requirements of first normal form.

2. Each non-key attribute must be functionally dependent on the primary key.

We’ve covered the first rule in an earlier lesson.

What about the second rule? What does that mean?

A non-key attribute is an attribute that is not the primary key. This is most of the attributes in each table.

Functionally dependent means the attribute is determined by that primary key. It’s specific to that record.

To move to second normal form, we need to use a concept called a “foreign key.”

 

What Is a Foreign Key?

We’ve learned about primary keys and relationships so far.

A foreign key is a field in a table that is the primary key in another table.

It allows for two tables to be related to each other and allows for records to be linked.

We can tell which teacher has which subject, and which students are enrolled in which subject, by using foreign keys.

 

Applying Second Normal Form

So, let’s apply second normal form to each of our tables.

Student:

Student (student ID, first name, last name, date of birth, address)

This looks OK. All of our fields (first name, last name, date of birth, and address) are dependent on the primary key. They are all specific to that student.

How about the subject table?

Subject (subject ID, subject name, category)

The subject name is OK, it is dependent on the subject ID.

However, category is not. It could be duplicated in this table (e.g., “Science”, “Language”, “Art”), and it could change.

What’s the solution? We move it into a new table.

Subject (subject ID, subject name)

Category (category ID, category name)

How do we link these two tables together? How do we know what category applies to a subject?

We use a foreign key. We add the primary key from one table into the other table as a foreign key.

But how do we know which one to add to the other table? Subject ID or category ID? It depends on the relationship we need to capture. I like to ask myself a question about the two tables. Does a table1 have many table2s, or does a table2 have many table1s?

Substitute table1 and table2 with your table names and you have, “Does a subject have many categories , or does a category have many subjects?”

In this example, the second part is true. A category has many subjects. This means the primary key for the second table goes into the first table as a foreign key (in italics):

Subject (subject ID, subject name, category ID)

Category (category ID, category name)

Our database looks like this so far:

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)

And this is our diagram. The line between the subject and category tables indicates that there is a relationship.

Now, we’re part of the way to second normal form. We need to apply the same rules to our teacher and university tables and work out how they are related to each other.

We’ll cover that in the next lesson: second normal form, part 2.

Until next time,
Ben

 

Recommended book

“Beginning Database Design: From Novice to Professional” by Clare Churcher

 

Share with friends