Database normalization
Database normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity. Normalization is also the process of simplifying the design of a database so that it achieves the optimal structure composed of atomic elements.
Normalization involves arranging attributes in relations based on dependencies between attributes, ensuring that the dependencies are properly enforced by database integrity constraints.
Objectives
The objectives of normalization beyond 1NF (first normal form) were stated as follows by Codd:Boyce and Codd Normal Form (BCNF)is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF.
- First Normal Form (1NF)As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row.The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key.
- Second Normal Form (2NF)As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key. If any column depends only on one part of the concatenated key, then the table fails Second normal form.
- Third Normal Form (3NF)applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.
When an attempt is made to modify (update, insert into, or delete from) a relation, the following undesirable side-effects may arise in relations that have not been sufficiently normalized:
- Update anomaly. The same information can be expressed on multiple rows; therefore updates to the relation may result in logical inconsistencies.
- Insertion anomaly. There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" relation might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code.
- Deletion anomaly. Under certain circumstances, deletion of data representing certain facts necessitates deletion of data representing completely different facts. The "Faculty and Their Courses" relation described in the previous example suffers from this type of anomaly,
