0
2.4kviews
Define Normalization? Explain 1NF, 2NF and 3NF with example
1 Answer
1
12views

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

Please log in to add an answer.

Continue reading...

The best way to discover useful content is by searching it.

Search