Normalization Process in DBMS

Database normalization is a stepwise formal process that allows us to decompose database tables in such a way that both data dependency and update anomalies are minimized. It makes use of functional dependency that exists in the table and primary key or candidate key in analyzing the tables. Normal forms were initially proposed called First Normal Form (INF), Second Normal Form (2NF), and Third Normal Form (3NF).

Subsequently, R, Boyce, and E. F. Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form. With the exception of 1NF, all these normal forms are based on functional dependency among the attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However, these later normal forms deal with situations that are very rare.



Summary of Normalization in a Nutshell :

Normal Form Test Remedy (Normalization)
1NF Relation should have no non-atomic attributes or nested relations. Form name relation for each non-atomic attribute or nested relation.
2NF For relations where primary key conatins multiple attributes, no non-key attributes should be functionally dependent on a part of the primary key. Decompose and set up a new relation for each partial key with its dependent attributes. Make sure to keep a relation with the original primary key and any attributes that are fully functionally dependent on it.
3NF Relation should not have a non-key attribute functionally determined by another non-key attribute (or by a sets of non-key attributes) i.e., there should be no transitive dependency of a non-key attribute of the primary key. Decompose and set up a relation that includes the non-key attribute(s) that functionally determine(s) other non-key attribute(s).
BCNF Relation should not have any attribute in Functional Dependency which is non-prime, the attribute that doesn’t occur in any candidate key. Make sure that the left side of every functional dependency is a candidate key.
4NF The relation should not have a multi-value dependency means it occur when two attributes of a table are independent of each other but both depend on a third attribute. Decompose the table into two subtables.
5NF The relation should not have join dependency means if a table can be recreated by joining multiple tables and each of the tables has a subset of the attributes of the table, then the table is in
Join Dependency.
Decompose all the tables into as many as possible numbers in order to avoid dependency.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.