Understanding Database Normalization in SQL with Example

Normalization in SQL: An Overview

Normalization in SQL or database normalization is a process of organizing the data into database tables. To make a good database design, you have to follow Normalization practices. Without normalization, a database system might be slow, inefficient, and might not produce the expected result. Normalization reduces data redundancy and inconsistent data dependency. This SQL Server tutorial discusses all the normal forms deeply.

Normal Forms: Types of Normalization in SQL

The world's database community has developed a certain guideline for ensuring that the databases in our application should be normalized in a specific way. These are referred to as normal forms and are numbered from one where the lowest form of normalization is referred to as the first normal form or 1NF through five which is the fifth normal form or 5NF along with the BCNF which is the Boyce Code Normal Form.

While working with the real-time application or scenarios, we will often see different forms of normalization such as 1NF, 2NF, and 3NF, along with the occasional 4NF and BCNF. but the fifth normal form (5NF) is very rarely seen and used in enterprise applications due to its complexity.

Read More: Basics of SQL Commands

We organize the data into database tables by using normal forms of rules or conditions. Normal forms help us to make a good database design. Generally, we organize the data up to the third normal form. We rarely use the fourth and fifth normal forms.

To understand normal forms consider the following unnormalized database table. Now we will normalize the data in the below table using normal forms.

Read More - DBMS Interview Questions For Freshers

1. First Normal Form (1NF)

A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:

1. Separate the repeating fields into new database tables along with the key from the unnormalized database table.

2. The primary key of new database tables may be the composite key.

1NF of the above UNF table is as follows:

2. Second Normal Form (2NF)

A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(which means the value of the field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.

The process of converting the database table into 2NF is as follows:

1. Remove the partial dependencies (A type of functional dependency where a field is only functionally dependent on the part of the primary key) of any non-key field.
2. If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B into a new database table where B will be the primary key and also marked as a foreign key in a parent table.

2NF of the above 1NF tables is as follows:

As you can see in the above example, We have divided our 1NF table into two tables viz. Table 1 and Table2. Where Table 1 contains project information. and Table 2 contains information on the employees assigned to the specific projects.

We have introduced a new column called project_code which is the primary key for table 1. Records can be uniquely identified in Table 1 using the project code column itself.

3. Third Normal Form (3NF)

A database table is said to be in 3NF if it is in 2NF and all non-keys fields should be dependent on the primary key We can also say a table to be in 3NF if it is in 2NF and no fields of the table are transitively functionally dependent on the primary key. The process of converting the table into 3NF is as follows:

1. Remove the transitive dependencies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key. Hence its value is determined, indirectly by the primary key )

2. Make a separate table for transitive-dependent Fields.

3NF of the above 2NF tables is as follows:

4. Boyce Code Normal Form (BCNF)

A database table is said to be in BCNF if it is in 3NF and contains every determinant as a candidate key. The process of converting the table into BCNF is as follows:

1. Remove the nontrivial functional dependency.
2. Make a separate table for the determinants.

BCNF of the below table is as follows:

5. Fourth Normal Form (4NF)

A database table is said to be in 4NF if it is in BCNF and the primary key has a one-to-one relationship to all non-keys fields We can also say a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies. The process of converting the table into 4NF is as follows:

1. Remove the multivalued dependency.

2. Make a separate table for multivalued Fields.

4NF of the below table is as follows:

6. Fifth Normal Form (5NF)

A database table is said to be in 5NF if it is in 4NF and contains no redundant values. We can also say a table to be in 5NF if it is in 4NF and contains no join dependencies. The process of converting the table into 5NF is as follows:

1. Remove the join dependency.

2. Break the database table into smaller and smaller tables to remove all data redundancy.

5NF of the below table is as follows:

Summary

Database designing is one of the critical tasks for the successful implementation of a database management system of the application that meets the data requirements of an enterprise application to be developed. Database normalization in DBMS is a crucial process that helps to create concrete database systems that are cost-effective while having better security models attached to them. The complete functional database dependencies are a very important component of the normalized data process

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE