Database Normalization is a database schema design technique, by which an existing schema is modified to minimize Redundancy and dependency of data.
Normalization split a large table into smaller table and define relationships between them to increase the clarity in organizing data
Why Normalization Needed ?
- To avoid redundancy
- to avoid Minimize anomalies and other issues
Problems that occurs in poorly planned unnormalized databases where are the data is stored in the table
Types of Anomalies
Insert Anomalies occurs when data of certain attributes cannot be inserted into the database without the present of other attributes.
Delete Anomalies exists when data of certain attributes are lost because of the deletion of the attributes
Update Anomalies exists when one or more instances of duplicated data in update, but not all.
Each Column is unique in 1NF.
Sample Students Table, it displays Students are working with multiple Departments.
Students Table following 1NF:
Second Normal Form (2NF)
The entity should be considered already in 1NF and all attributes within the entity should depend solely on the unique identifier of the entity.
Sample Products Table:
Product table following 2NF:
Products Category table
Products Brand table:
Third Normal Form (3NF)
The entity should be considered already in 2NF, and no column entry should be dependent on any other entry (value) other than the key for the table.
If such an entity exists, move it outside into a new table.
3NF is achieved, considered as the database is normalized.
Boyce-Codd Normal Form (BCNF)
3NF and all tables in the database should be only one primary key.
Fourth Normal Form (4NF)
Tables cannot have multi-valued dependencies on a Primary Key.
Fifth Normal Form (5NF)
A composite key shouldn’t have any cyclic dependencies.
Well, this is a highly simplified explanation for Database Normalization. One can study this process extensively, though. After working with databases for some time, you’ll automatically create Normalized databases, as it’s logical and practical.
Tamil Video Tutorial: