Normalization

Today let's have a discussion on the Normalization of Databases. Why is it needed?

Before we begin let's be familiar with certain terminologies.

1. Schema- The overall design of the database is called the schema.
2. Instances- Collection of information stored in the database at a particular time.
3. Attributes- Columns in the database are often termed attributes.
4. Tuple- Each record in the database is a tuple ie each row in the database is a tuple.

Let's look at the below table and observe each entity.

The tuple with Student_id 101,102,110,111 has redundancy.This redundancy can lead to various anomalies.

Anomalies

1. Deletion anomaly- The unintended loss of data due to deletion of other data. For example, if the student with Student_Id 109 has been deleted from the database then M.E department would cease to exist.

2. Updation anomaly- An update anomaly is a data inconsistency that results from a partial update. For example, the CSE department building is changed to K Block then there might be a chance that the database is not updated for every entry.

3.Insertion anamaly- An insertion anomaly is the inability to add data to the database due to the absence of other data.

Normalization

To avoid redundancy and various anomalies a relation schema having many attributes is divided into one or more schema having fewer attributes. We can divide the schema randomly, it has to be divided in such a way that it assumes the form of various forms.

First Normal Form

A relation is in first normal form if it does not contain any composite or multi-valued attribute. For example, the student name can’t be First_name, Last_name.

Second Normal Form

A relation is considered in the second normal form if it is in the first normal form and that every non-key column is fully dependent upon the primary key ie it should not contain any partial dependency.

Third Normal Form

A relation is considered in the third normal form if it is in the second normal form and all columns in the table are dependent only upon the primary key.

Boyce-Codd Normal Form - BCNF

This is a more robust version of 3NF that occurs only under specific circumstances. For functional dependency, A -> B, A should be a Super key.