Define Normalization? Explain 1NF, 2NF and 3NF with example

67views

written 6.3 years ago by

meghalikalyankar • 2.3k

First Normal Form

• A relation is in 1NF, if every row contains exactly one value for each attribute.

Example

Faculty code	Faculty Name	Date of Birth	Subject	Hours
100	Yogesh	17/07/64	DSA	16
			SS	8
			IS	12
101	Amit	24/12/72	MIS	16
			PM	8
			IS	12

Consider a table “Faculty”. It does not have any atomic values in the subject column. Hence it is called un-normalized table. Inserting, updating and deletion would be a problem is such a table.

Hence it is to be normalized.

Faculty code	Faculty Name	Date of Birth	Subject	Hours
100	Yogesh	17/07/64	DSA	16
100	Yogesh	17/07/64	SS	8
100	Yogesh	17/07/64	IS	12
101	Amit	24/12/72	MIS	16
101	Amit	24/12/72	PM	8
101	Amit	24/12/72	IS	12

This table shows the same data as previous table but we have eliminated the repeating groups. Hence the table is now said to be in First Normal Form(1NF).

Second Normal Form:

• A relation is in 2NF , if it is in 1NF and every non-key attribute is fully functionally dependent on the whole and not just part of primary key of relation.

• It should be in 1NF.

• There should not be any partial dependencies.

Example:

Faculty code	Faculty Name	Date of Birth	Subject	Hours
100	Yogesh	17/07/64	DSA	16
100	Yogesh	17/07/64	SS	8
100	Yogesh	17/07/64	IS	12
101	Amit	24/12/72	MIS	16
101	Amit	24/12/72	PM	8
101	Amit	24/12/72	IS	12

Consider the table we obtained after 1NF.

While eliminating the repeating groups, we have introduced redundancy into table. Faculty code, Name and Date of birth are repeated since faculty is multi skilled.

To eliminate this we have to split the tables into 2 parts, one with the non-repeating groups and the other for repeating groups.

Faculty:

Faculty code	Faculty Name	Date of Birth
100	Yogesh	17/07/64
101	Amit	24/12/72

Subject :

Faculty code	Faculty Name	Subject	Hours
100	Yogesh	DSA	16
100	Yogesh	SS	8
100	Yogesh	IS	12
101	Amit	MIS	16
101	Amit	PM	8
101	Amit	IS	12

The Faculty Code is the only key to identify the faculty name and the date of birth. Hence, Faculty code is the primary key in the first table and foreign key in the second table.

Hence, the relation is now in Second Normal Form.

Third Normal Form:

• A relation is in 3NF, if it is in 2NF and no non-key attribute of the relation is transitively dependent on the primary key.

• It should be in 2NF.

• There should not be any transitive dependency.

Faculty code	Faculty Name	Subject	Hours
100	Yogesh	DSA	16
100	Yogesh	SS	8
100	Yogesh	IS	12
101	Amit	MIS	16
101	Amit	PM	8
101	Amit	IS	12

In this table hours depend on the subject and subject depends on the faculty code and Sr.no.

But, hours is neither dependent on the faculty code nor the Sr.No. Hence there exits a transitive dependency between Sr.no., Subject and Hours.

If a faculty code is deleted, due to transitive dependency, information regarding the subject and hours allotted to it will be lost.

For a table to be in 3r normal form, transitive dependencies must be eliminated.

Fac_Sub

Sr.no	Faculty code	Subject
1	100	DSA
2	100	SS
3	100	IS
4	101	MIS
5	101	PM
6	101	IS

Sub_Hrs

Subject	Hours
DSA	16
SS	8
IS	12
MIS	16
PM	8
IS	12

After decomposing the “Subject”table we now have “Fac_Sub” and “Sub_Hrs”table respectively

ADD COMMENT EDIT