Normal forms are used to eliminate or reduce redundancy in database tables.

First Normal Form

A relation is in first normal form if every attribute in that relation is singled valued attribute.

Example :

ID Name Courses

------------------

1 A c1, c2

2 E c3

3 M C2, c3

In the above table Course is a multi valued attribute so it is not in 1NF.

Below Table is in 1NF as there is no multi valued attribute

ID Name Course

------------------

1 A c1

1 A c2

2 E c3

3 M c1

3 M c2

Second Normal Form

A relation is in 2NF iff it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

For example consider following functional dependencies in relation R (A, B , C, D )

AB -> C [A and B together determine C]

BC -> D [B and C together determine D]

In the above relation, AB is the only candidate key and there is no partial dependency, i.e., any proper subset of AB doesn’t determine any non-prime attribute.

Third Normal Form

A relation is in 3NF iff at least one of the following condition holds in every non-trivial function dependency X –> Y

a) x is a super key.

b) Y is a prime attribute (each element of Y is part of some candidate key).

For example consider relation R(A, B, C, D, E)

A -> BC,

CD -> E,

B -> D,

E -> A

All possible candidate keys in above relation are {A, E, CD, BC}

All attribute are on right sides of all functional dependencies are prime.

BCNF

A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.

For example consider relation R(A, B, C)

A -> BC,

B -> A

A and B both are super keys so above relation is in BCNF.

Key Points

BCNF is free from redundancy.

If a relation is in BCNF, then 3NF is also also satisfied.

If all attributes of relation are prime attribute, then the relation is always in 3NF.

A relation in a Relational Database is always and at least in 1NF form.

Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.

If a Relation has only singleton candidate keys( i.e. every candidate key consists of only 1 attribute), then the Relation is always in 2NF( because no Partial functional dependency possible).

Sometimes going for BCNF form may not preserve functional dependency. In that case go for BCNF only if the lost FD(s) is not required, else normalize till 3NF only.

There are many more Normal forms that exist after BCNF, like 4NF and more. But in real world database systems it’s generally not required to go beyond BCNF.

Exercise 1: Find the highest normal form in R (A, B, C, D, E) under following functional dependencies.

ABC --> D

CD --> AE

Important Points for solving above type of question.

1) It is always a good idea to start checking from BCNF, then 3 NF and so on.

2) If any functional dependency satisfied a normal form then there is no need to check for lower normal form. For example, ABC –> D is in BCNF (Note that ABC is a super key), so no need to check this dependency for lower normal forms.

Candidate keys in given relation are {ABC, BCD}

BCNF: ABC -> D is in BCNF. Let us check CD -> AE, CD is not a super key so this dependency is not in BCNF. So, R is not in BCNF.

3NF: ABC -> D we don’t need to check for this dependency as it already satisfied BCNF. Let us consider CD -> AE. Since E is not a prime attribute, so relation is not in 3NF.

2NF: In 2NF, we need to check for partial dependency. CD which is a proper subset of a candidate key and it determine E, which is non prime attribute. So, given relation is also not in 2 NF.

So, the highest normal form is 1 NF.

**ALL THE BEST **