Database Normalization

Database Normalization

Database

A database is a collection of information that can be stored, accessed, retrieved and updated easily with the help of tables. Tables consist of rows (corresponds to a single record) and columns (contains different types of attributes).

Normalization

Normalization can be simply defined as the process of efficient organization of data in a database. The guidelines developed by database community for normalization are referred as normalization forms and they are numbered from one (first normal form or 1NF) to five (fifth normal form or 5NF). These forms are guidelines only. Occasionally, we may need to strive from it to meet your business requirements. However, it is very important to evaluate any possible ramifications they could have on our system and check for possible inconsistencies. A table is said to be normalized if it meets the requirements of first normal form. The other normal forms deal with eliminating redundancies. Commonly we see 1NF, 2NF, 3NF and 4NF in most of the practical applications and 5NF is very rarely seen

First Normal Form (1NF)

This NF sets the basic rules for an organized database: –

• Ensure atomicity: If any duplicative column is present in the same table, eliminate it. I.e., we should not duplicate data in the same row of a table.
• Keep separate tables for related data and identify the rows with a unique identifier (the primary key).

Second Normal Form (2NF)

This NF further point to the concept of removing redundant data:-

• Meet the requirements of 1NF.
• Place subsets of data that apply to multiple rows of a table in other separate tables and remove those subsets from the original table.
• By using the foreign keys, create relationship between these new tables and its predecessors.

Third Normal Form (3NF)

This NF goes one more step further: –

• Here also, our first requirement is that the table should satisfy the requirements of 1NF and 2NF.
• Remove non- dependent columns (that do not depend on primary key).

Fourth Normal Form (4NF)

Finally, 4NF has one additional requirement: –

• Meet the requirements of 3NF.
• If a relation has no multi valued dependencies, then it is in 4NF

Benefits of Normalization:

1. Redundant data elimination: For example, same data stored in more than one table are eliminated.
2. Ensures sensible data dependencies: Only related data are stored in a table.