Normalisation

Normalisation

Normalization is the process of organizing the data in the database. Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.

Normal Forms:

Normalization rules are divided into the following normal forms:

1.      First Normal Form

2.      Second Normal Form

3.      Third Normal Form

4.      BCNF

First Normal Form (1NF):

Relational schema R is in 1NF if every attribute of R is single valued (Atomic). [No multi-valued attribute in R]

Above relation is not in 1NF because it has multi valued attribute (Cn). So arrange the relation as

Second Normal Form (2NF):

Relational schema R is in 2NF if it is in 1NF and there are no partial dependencies in R.

Example: Consider the relation

Stuid	Subjectid	Marks	Teacher
1	10	65	Java teacher
1	20	78	C++ teacher
2	10	70	Java teacher

In above relation Stuid + Subjectid is the candidate key and it is the primary key. It uniquely identifies any tuple in relation.

If observed carefully there is a dependency between Subjectid and Teacher. And Subjectid is part of primary key, so there is a partial dependency in the relation.

Partial dependency can be eliminated by decomposing the relation as follows:

Score

Stuid	Subjectid	Marks
1	10	65
1	20	78
2	10	70

Subject:

Subjectid	Teacher
10	Java teacher
20	C++ teacher
10	Java teacher

Third Normal Form(3NF):

A relation is in 3NF if it is in 2NF and there are no transitive dependencies in the relation

Example: Consider the relation

 

ID	Name	Subject	State	Country
29	Lalita	English	Gujrat	India
33	Ramesh	Geography	Punjab	India
49	Sarita	Mathematics	Maharashtra	India

The candidate key in the above table is ID.

The functional dependency set can be defined as Id->Name, Id->Subject, Id->State,

State->Country.

For the above relation, Id->State, State->Country is true. So we deduce that Country is transitively dependent upon Id. This does not satisfy the conditions of the Third Normal Form. The realtion can be decomposed to eliminate transitive dependency as

Teacher:

ID	Name	Subject
29	Lalita	English
33	Ramesh	Geography
49	Sarita	Mathematics

 State_country:

State	Country
Gujrat	India
Punjab	India
Maharashtra	India

Boyce-Codd Normal Form(BCNF):

A relation is in BCNF if it is in 3NF and for every dependency X → Y, X should be a super key.

It means that for every dependency X → Y, X cannot be a non prime attribute if Y is a prime attribute.

Example:

Stuid	Subject	Teacher
101	Java	Javateacher
101	C++	C++teacher
102	Java	Javateacher
103	C#	C#teacher

In above relation Studid + Subject is primary key. There is a dependency between Subject and Teacher (Teacher → Subject). Teacher is a non prime attribute and Subject is prime attribute. This relation violates BCNF.

Decomposing the realation as:

Professor:

Pid	Subject	Teacher
1	Java	Javateacher
2	C++	C++teacher
3	C#	C#teacher

Student:

Stuid	Pid
101	1
101	2
102	1
103	3