Database Normalization

what is Normalization?

Normalization is a database design technique that reduces data inessential and eliminates undesirable anomalies like Update, Insertion and Deletion. The purpose of Normalization is to avoid repetitive data and store the data logically. And it is being done by dividing larger tables into smaller ones and creating relations between the tables.

It follows certain normal forms to achieve Normalization.They are 1NF(first normal form), 2NF(second normal form), 3NF(third normal form) etc.

Database Normalization with examples:

Let us consider an movie rented store who keeps track of information of person who rents movies from the store, First let’s see the tabular format without normalization then only we can know the importance of normalization.

Information about the Rented Movies

Here we see that “Movies Rented” columns have multiple values, Now let us see the first normal form.

1NF(First Normal Form) Rule:

Each table should contain only one value.

Each record should be unique.

The above table in 1NF:

Following 1NF Form

Before we get into other forms let’s discuss some more things in order to get clarity of other forms.

What is Key?

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns.Columns in a table that are NOT used to identify a record uniquely are called non-key columns.

What is a Primary Key?

A primary key is a minimal set of attributes columns in a table that uniquely identifies rows in that table.

It has following attributes

• A primary key cannot be NULL
• A primary key value must be unique
• The primary key values should rarely be changed
• The primary key must be given a value when a new record is inserted.

What is composite key?

A composite key is a primary key composed of multiple columns used to identify a record uniquely.

Here we can see that different person having same name so we require both names and address for uniquely identify records. That is a composite Key.

2NF(Second Normal Form)

Follow 1NF Rule

Single Column Primary Key

From the above table information we can not form 2NF so we have to make partition of our column.

Members information Table

Movies Rented Information

From the above two tables we can clearly see that in the first table there are information about members only and in the second table we have movies rented information available.

Here we have introduced one more key that is Membership ID which is the primary key and using this we can uniquely identify records.

3NF(Third Normal Form)

Rule-1:Be in 2NF

Rule-2: Has no transitive dependencies

what is transitive dependencies:

It can be defined as changing a non-key column can affect the other non-key column to change.

lets take previous table add something to understand.

here one more column has been added that is salutation column.

Now if we want to change something from “Names” column it has dependencies issues with respect to “Salutation” column.

To overcome this issue we follow the 3NF rule.

Table-1

Table-2

We have again divided our table to create a new table to store salutation details. We have eliminate any kind of transitive dependencies and now our table is in 3NF.

In the first table above Salutation ID is the primary key of that table but in second table it acts as foreign to primary key of the Table-2.

Summary:

• Database designing is critical to the successful implementation of a database management system that meets the data requirements of an enterprise system.
• Normalization in DBMS helps produce database systems that are cost-effective and have better security models.
• Functional dependencies are a very important component of the normalize data process
• Most database systems are normalized database up to the third normal forms.
• A primary key uniquely identifies are record in a Table and cannot be null
• A foreign key helps connect table and references a primary key