Normalization is a systematic approach which is applied on relations to reduce the degree of redundancy. It is defined as a systematic approach because it always gives guarantee for the following properties:
1) Lossless decomposition and
2) Dependency preservation
Normal form: It is the property of relation which indicates the amount of redundancy existing in relation. Normal form and degree of redundancy are inversely proportional.
When the normalization process gets applied on a relation, it performs the following activities:
1) It finds out the highest normal form of the relation
2) Then the normalization process will start decomposing relation from its existing normal form to the higher normal form.
Procedure for finding the highest normal form of the relation:
1) Find all the possible candidate keys of the given relation
2)Divide the attributes of the relation into two groups, they are prime or key attributes and non-prime or non-key attributes.
3)Identify all the existing full dependencies, partial dependencies, transitive dependencies and overlapping candidate key dependencies.
4) Refer to the definitions of normal forms and hierarchy of normal form before evaluating highest normal form of the relation.
Now, lets see some simple definitions which will help in solving the problems faster:
Prime or key attributes: The attribute of the relation is said to be either prime or key attributes iff it exist in atleast one of the possible candidate keys.
Non-prime or non-key attributes: If the attribute of the relation does not exist in any of the possible candidate keys, then it is a non-prime attribute.
The simplified definitions of types of dependencies(for problem solving):
Full dependencies: While identifying full dependencies, make sure that determinant(left side of a function dependency) is either a candidate key or a super key.
Partial dependency: While identifying partial dependency, make sure that dependent(right side of relation) is a non-prime attribute and determinant is a part of candidate key.
Transitive dependency: While identifying transitive dependency, make sure both dependent and determinant are non-prime attributes or determinant can be combination of part of C.K. along with a non-prime attribute.
Overlapping candidate key dependency: While identifying these type of dependency, make sure dependent is part of C.K. and determinant can be prime or non-prime attributes, but not the C.K.
Finding the normal form:
1) According to Codd's rules, every relation should be in minimum of First normal form.
2) A relation is in Second normal form, if it is in First normal form and does not have any partial dependency.
3)A relation is said to be in Third normal form if it is in Second normal form and does not have any transitive dependencies.
4)A relation is said to be in BCNF if all the dependencies of relation are full dependencies.