What is Normalization? Explain its Types?

“NORMALIZATION” is the analysis of Functional dependency between Attributes/ data items., of user views. It reduces the complex user view to a set of small individual and stable sub-groups of relations/ Fields. This process helps to design a Logical data model, known as Conceptual Data Model.

Normalization helps to split the relations of each user view into small and stable subgroups of data items. Thus, Normalization is the technique/ process used to reduce the redundancy of data, which means, there is no duplicate data in the tables.

There are different levels of Normalized Forms as,

    1.      Un-Normalized Form  (UNF)

    2.      First Normal Form ( 1 NF)

    3.      Second Normal Form ( 2 NF)

    4.      Third Normal Form ( 3 NF)

    5.      Boyce-Codd Normal Form ( BCNF)

    6.      Fourth Normal Form ( 4 NF)

    7.      Fifth Normal Form ( 5 NF)

UnNormalized Form: This is a relation which satisfied all the properties of a relation.

First Normal Form (1NF):A relation is said to be in the First Normal Form if it is already in Un-Normalized Form and it has no repeating groups.

Second Normal Form (2NF):A relation is said to be in the Second Normal Form if it is already in First Normal Form and it has no partial dependency.

PARTIAL DEPENDENCY:  In a relation having more than one key field, a sub set of non-key fields may depend on all the key fields, but another subset or a particular non-key field(s) may depend on only one of the key fields (ie. May not depend on all the key fields) then such a dependency is called Partial Dependency.

Third Normal Form (3 NF):A relation is said to be in the 3NF if it is already in Second Normal Form and it has no Transitive dependency.

TRANSITIVE DEPENDENCY:  In a relation, there may be dependency among the Non-Key fields, such a dependency is called as Transitive dependency.

Boyce-Codd Normal Form (BCNF):A relation is said to be in BCNF if it is already in the Third Normal Form and every determinant is a Candidate Key.

Candidate Key:  A candidate key has an ability to become a Primary key. (It is an Alternative Key).

DETERMINANT:  A Determinant is any key field (Simple key field or Composite key field) on which some other key field is fully functionally dependent.

Fourth Normal Form (4NF):A relation is said to be in the Fourth Normal Form if it is already in Boyce-Codd Normalized Form and it has no multi-valued dependency.

MULTI VALUED DEPENDENCY:  Consider 3 fields x, y and z in a relation. If for each value of X, there is a well defined set of values of Y and well defined set of values of Z. The set of values of Y is independent of the set of values of Z, then there exist multi-valued dependency.

Firth Normal Form (5 NF):A relation is said to be in the Firth Normal Form, if it already in 4 NF and it has no Join dependency.

JOIN DEPENDENCY:  A relation which has a Join dependency cannot be decomposed by its Projection into their relations without any difficulty and undesirable results. The occurrence of this type of dependency is very rare.

Note that, in practice, it is sufficient to Normalize each and every relation/ report up to 3NF.