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|
|Decompose all the tables into as many as possible numbers in order to avoid dependency.|
Attention reader! Don’t stop learning now. Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.
Learn all GATE CS concepts with Free Live Classes on our youtube channel.