Skip to content
Related Articles
Open in App
Not now

Related Articles

Normalization Process in DBMS

Improve Article
Save Article
  • Difficulty Level : Basic
  • Last Updated : 13 Sep, 2022
Improve Article
Save Article

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 FormTestRemedy (Normalization)
1NFRelation should have no non-atomic attributes or nested relations.Form name relation for each non-atomic attribute or nested relation.
2NFFor relations where primary key contains 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.
3NFRelation 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).
BCNFRelation 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.
4NFThe 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.
5NFThe 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.
My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!