What is Normalization in SQL? 1NF, 2NF, 3NF and BCNF in DBMS

What is Normalization in SQL and what are its types?

The amount of data saved in a database is huge. If the data is not arranged properly, retrieving specific information will be a time-consuming operation. With the aid of Normalization, we can organize this data and also decrease the duplicated data. I'll give you a thorough understanding of SQL normalization through the medium of this post.

In a database, what is normalization?

It is a set of procedures for minimizing data redundancy and increasing data integrity in a table. So, why is this necessary? We may encounter a slew of problems if we don't use SQL normalization.

• Insertion anomaly: This occurs when we are unable to enter data into a table because another attribute is present.

• Update anomaly: Data inconsistency caused by data redundancy and partial data updates is known as an update anomaly.

• Deletion Anomaly: When certain traits are lost as a result of the deletion of other attributes, this is known as a deletion anomaly.

In a nutshell, normalization is a method of arranging data in a database. The term "normalization" refers to the process of structuring a database's columns and tables so that their dependencies are adequately enforced by database integrity constraints. It frequently separates a huge table into smaller ones to save space. Edgar F Codd established the First Normal Form in 1970, and later on, further Normal Forms were defined.

What does SQL have to do with Normalization, for example, is a question that occurs in the middle. SQL, on the other hand, is the language that is used to interface with databases. The data in the database must be in Normalized Form to commence any interaction. Otherwise, we won't be able to move on because anomalies will occur.

The distribution of data will be improved via SQL normalization. Let's take a look at each of the Normal Forms one by one.

1st Normal Form (1NF)

The problem of atomicity is addressed in this Normal Form. The term atomicity refers to the fact that the table's values should not be divided further. A single cell cannot carry multiple values, to put it simply. The First Normal Form is broken when a table contains a composite or multi-valued attribute.

2nd Normal Form (2NF)

The table must be in the first NF as the first condition in the second NF. There should also be no partial dependencies in the table. In this case, partial dependency means that a non-prime property is determined by the right subset of candidate keys. Take a look at the sample below for a better understanding.

3rd Normal Form (3NF)

The same criterion as before applies: the table must be in 2NF before moving on to 3NF. The other stipulation is that non-prime characteristics should not be transitively dependent. Non-prime attributes (those that do not constitute a candidate key) should not be reliant on other non-prime attributes in a table. A transitive dependency is a functional relationship in which X Z (X determines Z) is determined indirectly by X Y and Y Z (where Y X is not the case).

Boyce Codd Normal Form (BCNF)

This is also referred to as 3.5 NF. It was created by Raymond F. Boyce and Edgar F. Codd as a higher version of 3NF to address specific types of anomalies that were not addressed by 3NF.

The table must fulfill the 3rd Normal Form before moving to BCNF.

In BCNF if every functional dependency A → B, then A has to be the Super Key of that particular table.