NORMALIZATION IN DBMS

3 min readNov 23, 2020

Normalization rules divide larger tables into smaller tables and link them using relationships. The purpose of Normalization in SQL is to eliminate repetitive data and ensure data is stored logically.

It 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.

Anomalies

Anomalies are problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table.

There are three types of anomalies:

Update Anomalies.
Deletion Anomalies.
Insertion Anomalies.

Deletion Anomalies.

A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted.To avoid these kinds of update or deletion problems, we need to decompose the original table into several smaller tables where each table has minimal overlap with other tables.

Insert Anomalies.

An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. For example, this is the converse of delete anomaly — we can’t add a new course unless we have at least one student enrolled on the course.

Update Anomalies.

Update Anomalies happen when the person charged with the task of keeping all the records current and accurate, is asked, for example, to change an employee’s title due to a promotion.

If the data is stored redundantly in the same table, and the person misses any of them, then there will be multiple titles associated with the employee. The end user has no way of knowing which is the correct title.

Database Normal Forms

Here is a list of Normal Forms

1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)

First Normal Form (1NF)

First normal form disallows the multi-valued attribute, composite attribute, and their combinations. It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attributes.

Second Normal Form(2NF)

A relation is in the second normal form if it fulfills the following two requirements:

->It is in first normal form.
->It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation.