Normalization — SQL

Introduction

Normalization is the process of organizing the data in the database. Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.

The Need for Normalization

It is used to remove the duplicate data and database anomalies from the relational table. Normalization helps to reduce redundancy and complexity by examining new data types used in the table. It is helpful to divide the large database table into smaller tables and link them using relationship.

Now let’s understand each and every Normal Form with examples:

1st Normal Form (1NF)

In this Normal Form, we tackle the problem of atomicity. Here atomicity means values in the table should not be further divided. In simple terms, a single cell cannot hold multiple values. If a table contains a composite or multi-valued attribute, it violates the First Normal Form.

In the above table, we can clearly see that the Phone Number column has two values. Thus it violated the 1st NF. Now if we apply the 1st NF to the above table we get the below table as the result.

By this, we have achieved atomicity and also each and every column have unique values.

2nd Normal Form (2NF)

The first condition in the 2nd NF is that the table has to be in 1st NF. The table also should not contain partial dependency. Here partial dependency means the proper subset of candidate key determines a non-prime attribute. To understand in a better way lets look below.

This table has a composite primary key Employee Id, Department Id. The non-key attribute is Office Location. In this case, Office Location only depends on Department Id, which is only part of the primary key. Therefore, this table does not satisfy the second Normal Form.

To bring this table to Second Normal Form, we need to break the table into two parts. Which will give us the below tables:

As you can see we have removed the partial functional dependency that we initially had. Now, in the table, the column Office Location is fully dependent on the primary key of that table, which is Department Id.

3rd Normal Form (3NF)

The same rule applies as before i.e, the table has to be in 2NF before proceeding to 3NF. The other condition is there should be no transitive dependency for non-prime attributes. That means non-prime attributes (which doesn’t form a candidate key) should not be dependent on other non-prime attributes in a given table.

In the above table, Student Id determines Subject Id and Subject Id determines Subject. Therefore, Student Id determines Subject via Subject Id. This implies that we have a transitive functional dependency, and this structure does not satisfy the third normal form.

Now in order to achieve third normal form, we need to divide the table as shown below:

As you can see from the above tables all the non-key attributes are now fully functional dependent only on the primary key. In the first table, columns Student Name, Subject Id and Address are only dependent on Student Id. In the second table, Subject is only dependent on Subject Id.

Boyce Codd Normal Form (BCNF)

Before proceeding to BCNF the table has to satisfy 3rd Normal Form. In BCNF if every functional dependency A -> B then A has to be the Super Key of that particular table.

Consider the below table:

• One student can enrol for multiple subjects.
• There can be multiple professors teaching one subject
• And, For each subject, a professor is assigned to the student

In this table, all the normal forms are satisfied except BCNF.

As you can see Student Id and Subject form the primary key, which means the Subject column is a prime attribute. But, there is one more dependency, Professor -> Subject.

And while Subject is a prime attribute, Professor is a non-prime attribute which is not allowed by BCNF.

Now in order to satisfy the BCNF, we will be dividing the table into two parts. One table will hold Student Id which already exists and newly created column Professor Id.

And in the second table, we will have the columns Professor Id, Professor and Subject.

By doing this we are satisfied the Boyce Codd Normal Form.

Thus this brings us to the end of Normalization in SQL article.

I hope now you have a clear idea about Normalization concepts.