In this tutorial we are going to learn about normalization in DBMS.
Data duplication may occur in a big database defined as a single relation. This data repetition may result in:
Increasing the size of relationships.
It is difficult to manage and update data because it requires searching through several records in relation.
Wastage and inefficient use of disk space and resources.
Errors and inconsistencies become more likely.
To address these issues, we should examine and breakdown the redundant data relations into smaller, simpler, and well-structured relations that satisfy desirable qualities. Normalization is the practice of breaking down relationships into those with fewer properties.
Working of Normalization in DBMS
Normalization in DBMS is a strategy for designing the schema of a database by altering the existing schema, which also eliminates data redundancy and reliance. As a result, using Normalization, the undesirable duplication in data is removed, as are the abnormalities. Values such as null are not permitted to be input for a column in insert anomalous.
The data cannot be updated correctly in an update anomaly because the same values appear numerous times in a column, and in a delete anomaly, the deletion of a record causes inconsistency since it is erased from more than one row. As a result, the goal of normalization is to eliminate redundant data while still storing only related data in the database. This reduces the database size and logically stores the data in the database.
Types of Normal Forms in DBMS
Normalization occurs through a sequence of steps known as Normal forms. Individual relationships are subject to the normal forms. If a relation fulfills constraints, it is in particular normal form.
- 1NF – If it has an atomic value, then the relation is in 1NF.
- 2NF – A relation is in 2NF if it is in 1NF and all non-key attributes are completely functional when the primary key is in use.
- 3NF – If a relation is in 2NF and there is no transition dependency, it is in 3NF.
- BCNF – Boyce Codd’s normal form is more stringent definition of 3NF.
- 4NF – A relation is in 4NF if it is in Boyce Codd’s normal form and does not have any multi-valued dependencies.
- 5NF – In 5NF, there is a relationship. Joining should be lossless if it is in 4NF and does not contain any join dependencies.