##### Database Normalization

Database Normalization

**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.