Normalization serves as a valuable design technique that plays a crucial role in eliminating undesired attributes like insert, update, and delete anomalies. Additionally, it effectively reduces data redundancy, leading to enhanced data integrity and streamlined operations for data querying and manipulation.
Redundancies refer to the existence of duplicate data or the repetition of identical values in certain columns across multiple records.
Normalization comprises multiple levels, each accompanied by specific rules in order to progressively improve the design of the database and also to address different types of data anomalies. Each level builds upon the previous one, offering a higher degree of data organization and integrity.A table is considered to meet a particular level of normalization when it adheres to the corresponding set of rules associated with that level.
This technique has 7 distinct forms called normal forms which goes from First Normal Form(1NF) to Sixth Normal Form and additional one called Boyce-Codd Normal Form (BCNF)
First Normal Form(1NF)
- A primary key must be present in order to uniquely identify each record within the table.
- Each column should possess only one distinct value for each row in the table. Atomicity requires that each cell within a table holds a single value, preventing multiple values from being stored within a single cell.
In the case of multi-valued attributes, to ensure adherence to the first normal form (1NF), the values should be organized as separate rows
Second Normal Form(2NF)
The Second Normal Form focuses on the relationship between a table’s non-key columns and its primary key.
- The table should satisfy the requirements of the first normal form (1NF).
- Full dependence: All non-key attributes should be fully dependent on the primary key. If a non-key column exhibits partial dependence on the primary key, it should be split into a separate table.
Every table should have primary key and their relationship between tables is established only using foreign key
Third Normal Form(3NF)
The main emphasis of the third normal form (3NF) is to remove transitive dependencies from a database table.
Transitive dependency arises when a non-key attribute relies on another non-key attribute, rather than directly depending on the primary key.
3NF forbids the dependency of non-key attribute to another non-key attribute
- The table needs to meet the prerequisites outlined by the second normal form (2NF)
- The presence of transitive partial dependencies should be eliminated within the table, guaranteeing a more robust and normalized data structure.
In BCNF (Boyce-Codd Normal Form), a non-prime attribute should not determine any prime attribute. BCNF is a more advanced form of normalization than 3NF. It ensures that all functional dependencies are preserved and eliminates certain types of anomalies
To summarize the concepts
- First Normal Form (1NF) requires that each attribute in a relation should hold atomic values.
- In Second Normal Form (2NF), we eliminate partial dependencies.
- In Third Normal Form (2NF), we removed transitive dependencies.
- BCNF, being a higher level of normalization compared to 3NF (Third Normal Form), enforces a stricter rule: it guarantees that a non-prime attribute should not functionally determine any prime attribute within a relation.
