Normalization is a database schema design technique by which an existing schema is modified to minimize redundancy & dependency of data.
Normalization split a large table into smaller tables & defines relationships between them to increase the clarity in organizing data.
- Normalization is the process of organizing the data in the database.
- Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
- The normal form is used to reduce redundancy from the database table.
Data modification anomalies can be categorized into three types:
Insert Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a relationship due to lack of data.
Delete Anomaly: The delete anomaly refers to the situation where the deletion of data results in the unintended loss of some other important data.
Update Anomaly: The update anomaly is when an update of a single data value requires multiple rows of data to be updated.
Types of Normal Forms:
Normalization works through a series of stages called Normal forms. The normal forms apply to individual relations. The relation is said to be in particular normal form if it satisfies constraints.
Following are the various types of Normal forms:
| 1NF | 2NF | 3NF | BCNF (Boyce-Codd Normal Form) | 4NF | 5NF |
|---|---|---|---|---|---|
| Eliminate Repeating Groups | Eliminate Partial Functional Dependency | Eliminate Transitive Dependency | Eliminate Overlapping Candidate Keys | Eliminate Multi-values dependency | Eliminate Join Dependency |
| A relation is in 1NF if it contains an atomic value. | A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key. | A relation will be in 3NF if it is in 2NF and no transition dependency exists. | A stronger definition of 3NF is known as Boyce Codd’s normal form. | A relation will be in 4NF if it is in Boyce Codd’s normal form and has no multi-valued dependency. | A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless. |
Advantages of Normalization
- Normalization helps to minimize data redundancy.
- Greater overall database organization.
- Data consistency within the database.
- Much more flexible database design.
- Enforces the concept of relational integrity.
Disadvantages of Normalization
#1NF #2NF #3NF #4NF #5NF #BCNF #database normalization #dbms #normalization
- You cannot start building the database before knowing what the user needs.
- The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
- It is very time-consuming and difficult to normalize relations of a higher degree.
- Careless decomposition may lead to a bad database design, leading to serious problems.