Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples

Introduction

A database contains enormous amounts of data. If the data is not organized properly, retrieving and accessing certain data might be time-consuming.  Here, the role of normalization in SQL comes into the picture. Normalization in SQL helps in reducing the redundant content and organizing the data properly. 

 With the help of this article, we give you a complete insight of Database Normalization in SQL Server specially 1NF 2NF 3NF with examples. To strengthen your preparation on SQL, consider enrolling in a professional online SQL training.

What Is Normalization In SQL?

Redundancy in the data is eliminated during SQL normalization to improve data integrity. In this procedure, we normalize the database such that a key may be used to access the columns of data. It aids in the database's data organization. This procedure converts the data into tabular representations while removing duplicate data from relational tables.

It simply means to return anything to its usual state in order to better understand this concept. The way the columns and tables are set up makes sure that the normalization SQL requirements and data integrity restrictions are carried out correctly.

What Is Normalization In A Database?

It is the procedure for minimizing data redundancy in the table and enhancing data integrity. Why then is this necessary? Without normalization in SQL, we could run into a lot of problems, like

• Insertion anomaly: This is when we are unable to insert data into the table without another attribute being present.
• Update irregularity: It is a data discrepancy brought on by redundant data and incomplete data updates.
• Deletion Anomaly: When some qualities are lost as a result of the deletion of other attributes, this phenomenon is known as a deletion anomaly.

In a nutshell, SQL normalization is a method of database data organization. A database's columns and tables must be organized as part of normalization SQL in order for database integrity requirements to properly enforce their dependencies.

It is more effective because it typically breaks up a big table into smaller ones. Edgar F. Codd established the First Normal Form in 1970, and later, further Normal Forms were defined.

In between, the issue of what SQL has to do with normalization comes up. Well, the language used to communicate with the database is called SQL. The data that is currently in the database must be in normalized form in order to start any interaction. Otherwise, we will be unable to continue because it will

It is more effective because it typically breaks up a big table into smaller ones. Edgar F. Codd established the First Normal Form in 1970, and later, further Normal Forms were defined.

In between, the issue of what SQL has to do with SQL normalization comes up. Well, the language used to communicate with the database is called SQL. The data that is currently in the database must be in normalized form in order to start any interaction. Otherwise, it leads to abnormalities, which prevents us from moving forward. Before we move further, let’s quickly check out the different types of SQL keys.

Database Normalization and its Benefits

Data normalization is more than simply restructuring the data in a database as data has increasing value for all kinds of businesses. Here are a few of its main advantages:

Benefits:

• Store data efficiently
• Reduce duplicates
• Protect data integrity

A business can gather all the information it needs from any source. Yet, without data normalization, the majority of it would just be wasted and not be of any real use to the organization. Removing Duplicates is called Normalization. In this blog for Database normalization, we will discuss 1nf 2nf 3nf bcnf with examples.

Example: The above table is not normalized.

EName	Gender	Salary	DName	DHead	DLocation
Chris	Male	4500	Finance	Lu	Michigan
Mary	Female	2000	HR	Pete	Florida
Den	Male	1500	Finance	Lu	Michigan
Kate	Female	3000	HR	Pete	Florida
Mathew	Male	6500	Finance	Lu	Michigan

Problems with Duplicate data:

• Wastage of disk space.
• Inconsistent data.
• DML(Insert/Update/Delete) queries can become slow.

To solve this, we normalize the tables. We have broken the above table into 2 tables, repeating columns we have moved into a separate table and we don’t have to repeat DName, DHead, DLocation in the Employees Table all we do here refer to Did. And we have given the primary key in SQL to Did to uniquely identify each department in the Department table.

Department Table

DId	DName	DHead	DLocation
1	Finance	LU	Michigan
2	HR	Pete	Florida

Employee Table

EID	EName	Gender	Salary	DId
1	Chris	Male	4500	1
2	Mary	Female	2000	2
3	Den	Male	1500	1
4	Kate	Female	3000	2
5	Mathew	Male	6500	1

There are 6 normal forms: 1NF,2NF,3NF,4NF,5NF,6NF. Most databases are in 3NF. There are certain rules that each normal form follows. You can also download 1nf 2nf 3nf with an example PDF from the web for more details. Learn more about SQL and get a better understanding, here is a complete guide on different types of SQL joins. 

Normalization in SQL - Example of 1NF 2NF 3NF BCNF

Register for a Demo class to practice Database normalization in SQL with examples.

In this section, we will discuss example of 1nf 2nf 3nf bcnf in detail

First Normal Form (1NF)

A table is said to me 1NF, if

• No Multiple values separated by comma.

Example:

DName	EName
Finance	Chris, Mary, Den
HR	Mathew

Because of multiple values in the above example Insert, Update, Delete is not possible for one Employee.

• No repeating columns.

Example:

DName	EName1	EName2	EName3
Finance	Chris	Den	Kate
HR	Mary

Suppose HR department has only one employee, EName2 and EName3 columns have null values in that case, wastage of the disk space.

• By using Primary key, each record should identify Uniquely.

Example: Table1:

DId	DName
1	Finance
2	HR

Table2:

DId	EName
1	Chris
1	Den
1	Kate
2	Mary

Here, DId is the Primary key in the Table1 and DId is the Foreign key in the Table2, which satisfies 1NF.

When you are looking to make your career in SQL, think of getting in-depth understanding on the same. Here is a complete guide on  different types of SQL database functions. Signup for SQL training classes online now to know more about Database Normalization.

Signup for SQL training classes online now to know more about Database Normalization.

Second Normal Form (2NF)

• Should meet all the requirements of 1NF.
• Move unwanted data into separate table.
• Create relationship between these tables by using Foreign key.

Department Table

DId	DName	DHead	DLocation
1	Finance	LU	Michigan
2	HR	Pete	Florida

Employee Table

EID	EName	Gender	Salary	DId
1	Chris	Male	4500	1
2	Mary	Female	2000	2
3	Den	Male	1500	1
4	Kate	Female	3000	2
5	Mathew	Male	6500	1

DId is Primary key in Department table and DId in Employee table is Foreign key (Added additional column to make sure the two tables have relationship).

Read More: Different Types of SQL Injection

Third Normal Form (3NF)

• Should meet all the requirements of 1NF and 2NF.
• Remove columns which are not fully dependent on Primary key.

 Example: Employee Table

EID	EName	Gender	Salary	Annual Salary	DId
1	Chris	Male	4500	54000	1
2	Mary	Female	2000	24000	2
3	Den	Male	1500	18000	1
4	Kate	Female	3000	36000	2
5	Mathew	Male	6500	78000	1

Annual Salary is not fully dependent on Primary key because Annual Salary Column depends on Salary column.

Fourth Normal Form(4NF)

• Should meet all the requirements of 1NF, 2NF, 3NF
• NO Multivalued dependencies, other than Candidate key

Example: Course, Lecture, Books

Learn what candidate key SQL with this guide post. Example: Course, Lecture, Books

With this discussion, you have a depth idea of normalization 1nf 2nf 3nf example. Let us have a look at a quick comparison between normalization and denormalization in the next section.

Difference between Normalization and Denormalization

Normalization	Denormalization
Divides data into multiple tables so, data retrieval is slow	Data from multiple tables are divided into single table so, data retrieval is fast
Reduce duplicate data	Create duplicate data
Because of more tables, we use more joins	Less tables less joins

Final Words

Thus, this brings us to the end of Normalization in SQL articles. I hope now you have a clear idea about SQL Normalization concepts.

The successful installation of a database management system that satisfies the data requirements of an enterprise system depends on database designing. If you wish to learn more about normalization in SQL with examples then check the online SQL course offered by the JanBask Training that comes with instructor-led LIVE classes and real-life project experiences. Our SQL training will help you to understand SQL concepts in-depth and help you to achieve mastery over the subject.

Frequently Asked Questions

Q1. Describe normalization NF1 NF2 NF3

The repetitive groups are removed from the table in this form, and a relationship is only in 1NF if it includes an atomic value.

The partial functional dependency should be eliminated in Second Normal Form, making all non-key characteristics completely functional and dependent on the primary key. To be in 2NF, the relationship must also meet the requirements of 1NF.

3NF: The table must not have any transitive functional dependencies in order to be in third normal form. Before the relationship enters 2NF, it must also satisfy the rules of 2NF.

Q2. What three stages of normalization in SQL are there?

First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form are the three steps of normalizing the data in the database (3NF). In each stage, the data is chosen with the understanding that the data grouping shouldn't contain any anomalies. These abnormalities include redundant data, erroneous data relationships, and lost data.

Q3. Why is database normalization done?

Databases are normalized to cut down on data duplication. The prevention of problems with the addition, deletion, or updating of data in the database is another benefit of normalization SQL. Additionally, it makes sure that each table has only the relevant data. The basic justification for normalizing databases is this.

Q4. What does normalization in SQL aim to achieve?

The primary objective of normalization in SQL is to efficiently organize the data in the database. Eliminating unnecessary data and assuring the table's data dependencies are the normalization's other two key goals.

Q5. What in SQL is a KEY?

In SQL, a KEY is a value that is used to uniquely identify records in a table. A single column or a group of columns known as a SQL KEY is used to identify rows or tuples in a table in a unique way. In addition to helping build a relationship between various database tables, SQL Key is used to discover instances of duplicate data.

Non-key columns are those in a table that are not utilized to uniquely identify a record.

Q6. What is a primary key?

A primary is a single column value that a database record uses to be uniquely identified. It is a key component when it comes to normalization in SQL.

Q7. What in SQL is a KEY?

In SQL, a KEY is a value that is used to uniquely identify records in a table. A single column or a group of columns known as a SQL KEY is used to identify rows or tuples in a table in a unique way. In addition to helping build a relationship between various database tables, SQL Key is used to discover instances of duplicate data.

Q8. Describe Composite Key.

In order to uniquely identify a record, a composite key, which is a primary key made up of several columns, is employed.

Q9. What are transitive functional dependencies?

When changing one non-key column could result in any of the other non-key columns also changing, this is known as a transitive functional dependency.

Q10. What is Normalization? Describe the purpose of normalization in SQL.

The practice of normalization in SQL improves data integrity in the table by removing redundant data and duplication. Additionally, normalization aids in database data organization. Setting the data into tabular form and removing duplicate data from relational tables involve several steps.

Q11. What is the importance of normalization in SQL server?

By avoiding data from being duplicated in two tables at once or from being grouped together in one database with unrelated products, normalization SQL is a crucial component of product information management. Moreover, normalization aids in streamlining data, streamlining and condensing your database.

Q12. What is normalization in machine learning?

The process of normalization is frequently used to prepare data for machine learning. The purpose of normalization in SQL is to convert the values of the dataset's numeric columns to a standard scale without losing information or distorting the ranges of values.

FaceBook Twitter Google+ LinkedIn Pinterest Email