Introduction to Normalization – SQL Tutorial

# Introduction to Normalization – SQL Tutorial

clickHere
Updated on Nov 22, 2022 19:30 IST

This article will cover what Normalization in SQL is, its forms, such as 1NF, 2NF, etc., and examples.

Databases store redundant data and retrieving data without duplicates can be a tedious task. Normalization in SQL can be used to retrieve data without redundancy. In this article you will get a detailed walkthrough on what normalization is and its various normal forms.

1. What is Normalization?
2. Normal Forms

## What is Normalization?

Normalization is the process of organizing the data to reduce duplicates in tables. It improves data integrity with the various normal forms used step by step. The 1st normal form was defined by Edgar F Codd in the 1970s.

Large tables are broken down into small tables and for every interaction to occur on the data, data present in the database has to be normalized. Incase the data is not present in normal forms, then the following anomalies occur:

1. INSERTION –  Occurs when users cannot insert data into the table without the presence of another attribute.
2. UPDATION  – Occurs during partial update of data.
3. DELETION – Occurs when a specific set of attributes are lost because of deleting another set of attributes.

Now that you know what normalization is, let us understand the various normal forms.

## Normal Forms

The following are the four normal forms used to enhance the distribution of data.

1. 1st Normal Form (1NF)
2. 2nd Normal Form (2NF)
3. 3rd Normal Form (3NF)
4. Boyce-Codd Normal Form(BCNF)

You must remember that to normalize any piece of data you will start normalizing from the 1st normal form and go to the higher normal forms [based on the requirement].

To understand normal forms better, let us consider the following table:

Patients table

### 1st Normal Form (1NF)

In this normal form each table cell must only have a single value. So, basically this normal form ensures that atomicity is maintained and all the records in a table hold a unique value.

Here, please note that this table is not further divided and if the table holds composite or multi-valued attributes then it violates 1NF form.

In our example the medicines taken and category have two values. So, currently it violates the 1st NF. So, the above table is normalized into 1NF as below:

Now if you look at the above table, although every record is unique, there is a lot of data redundancy present. So, to avoid the same, let’s further normalize the above table.

### 2nd Normal Form (2NF)

For data to be normalized into 2NF, the data must be in 1NF and should have only a single-column primary key. So, basically the table should not have partial dependency.

Here the above table can be normalized into two tables.

Table 1

Table 2

In the above two tables, though each row holds a unique value and a single primary key, there is still a lot of data redundancy. So, let’s further normalize the data into 3NF form.

### 3rd Normal Form (3NF)

For data to be normalized into 3NF, the data must be in 1NF and 2NF. Apart from this, data should not have any transitive dependencies for non-prime attributes.

So basically all attributes which do not belong to the candidate key set must not be dependent on other non-prime attributes.

In our above example, we see the Patient ID determines the MedicineID and the MedicineID determines the medicine. So basically the PatientID determines the medicine name through MedicineID, which implies the presence of transitive functional dependency.

So you can further normalize the above Table 2 in to the 3NF form as follows:

Table 1:

Table 2:

Here we can see that all the non-key attributes are fully dependent only on the primary key.

Next let us understand the Boyce Codd Normal Form.

### Boyce Codd Normal Form (BCNF)

Boyce Codd Normal Form, also known as the 3.5 NF was developed by Raymond F. Boyce and Edgar F. Codd . For this normal form, you must make sure that the data is in 3NF. Here you can divide tables further to make sure a single candidate key is present.

Refer to the below table to understand BCNF.

• Every patient can go to multiple doctors
• Multiple doctors can be specialized in various specialization
• For every specialization, a doctor can treat patients

Here, the PatientID and Specialization can form the primary key indicating Specialization is a prime attribute.  Every doctor is also related to specialization which is not allowed in BCNF.

So we can normalize the above table as follows:

Table 1:

Table 2:

With this, we end this article on Normalization in SQL. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.