Normalization

It is the step by step process in which we convert the complex data structure (not well
structured relation) into simpler data structures (well structured relations). In this process we
apply different rules in each step and after every step the relation assumes a new shape/ form,
known as NORMAL FORM.
Unnormalize data
Student Name Address Town Post Gend Course Course Teacher Grade
No Code er Code Name

1 Ali Alam 243-Gulburg Karachi 497 M L24 DB 11-H Hayat B

1 Ali Alam 243-Gulburg Karachi 497 M M90 Math 3- Q Nazar C
1 Ali Alam 243-Gulburg Karachi 497 M G13 IT 7-A Asgar B
2 Anjum 143-Gulshan Karachi 467 F M90 Math 3- Q Nazar A
Tariq

 In the above example, there is repeating group, as the (Student no, Name, Address, Town,
Post Code and Gender ) repeats for the 3 registered courses
1st Normal Form(1NF)
1. There are no repeating groups
2. All data values are atomic (Single value)
3. Each field has a unique name
4. It has a primary key
Removing Repeating groups

Student Name Address Town Post Code Gender

no
1 Ali Alam 243-Gulburg Karachi 497 M
2 Anjum Tariq 143-Gulshan Karachi 467 F

Student Course Code Course Name Teacher Grade

no
1 L24 DB 11-H Hayat B
1 M90 Math 3- Q Nazar C
1 G13 IT 7-A Asgar B
2 M90 Math 3- Q Nazar A
All data values are atomic (Single value)

Student First Name Second Name Address Town Post Code Gender
no
1 Ali Alam 243-Gulburg Karachi 497 M
2 Anjum Tariq 143-Gulshan Karachi 467 F

Student Course Course Name Teacher Teacher First Teacher Second Grade
no Code ID Name Name

1 L24 DB 11 Hamza Hayat B

1 M90 Math 3 Qasim Nazar C
1 G13 IT 7 Anum Asgar B
2 M90 Math 3 Qasim Nazar A

Each field has a unique name

It has a primary key
Student First Name Second Address Town Post Code Gender
no Name

1 Ali Alam 243-Gulburg Karachi 497 M

2 Anjum Tariq 143-Gulshan Karachi 467 F

Studen Course Course Name Teacher Teacher First Teacher Second Grade
t no Code ID Name Name

1 L24 DB 11 Hamza Hayat B

1 M90 Math 3 Qasim Nazar C
1 G13 IT 7 Anum Asgar B
2 M90 Math 3 Qasim Nazar A
Unnormalize 1st Normal Form

Student no P Student no
Name K
Address First Name
Town Second Name
Post Code Address
Gender Town
Course Code Post Code
Course Name Gender
Teacher
Grade
P Student no
K
P Course Code
K
Second Normal form (2NF) Course Name
A table is in second normal form (2NF) if: Teacher ID
Teacher First Name
1. It is first in First Normal Form (1NF) Teacher Second Name
2. All non key attributes are dependent on all parts of Grade
the primary key (does not contain Partial Functional
Dependency)
3. Each field has a unique name
4. It has a primary key
Fully Functional Dependency:
Say we have a relation R(A, B, C, D). If A determines the values of B,C, and D, then B,C and D
are said to be Fully Functionally Dependent upon A
- A B, C, D
Here A is DETERMINANT, and B,C and D are DEPENDENTS
Partial Functional dependency:
say we have a relation R(A, B, C, D, E) having A and D as composite primary keys. If A can
determine B, and D can determine C, then B is said to be Partially Functionally dependent upon
D. And E is determined by the full primary key, i.e., A and D. therefore known as fully
functionally dependent upon composite Primary key.
- A B (P.F.D)
- D C (P.F.D)
- A,D E (F.F.D)
 Checking for Partial Dependency

Student First Name Second Address Town Post Code Gender

no Name

1 Ali Alam 243-Gulburg Karachi 497 M

2 Anjum Tariq 143-Gulshan Karachi 467 F

Studen Course Teacher Teacher First Teacher Second Grade

t no Code ID Name Name

1 L24 11 Hamza Hayat B

1 M90 3 Qasim Nazar C
1 G13 7 Anum Asgar B
2 M90 3 Qasim Nazar A

Course Code Course Name

L24 DB
M90 Math
G13 IT
Unnormalize 1st Normar Form 2nd Normar Form

Student no P Student no P Student no

Name K K
Address First Name First Name
Town Second Name Second Name
Post Code Address Address
Gender Town Town
Course Code Post Code Post Code
Course Name Gender Gender
Teacher
Grade
P Student no P Student no
K K
P Course Code P Course Code
K K
Course Name Teacher ID
Teacher ID Teacher First Name
Teacher First Name Teacher Second Name
Teacher Second Name Grade
Third Normal Form (3NF) Grade

1. It is first in Second Normal Form (2NF) P Course Code

2. All non key attributes are not dependent on any other K
non-key attributes ( does not have Transitive Dependency) Course Name

Transitive Dependency: Say if we have a relation R( A, B, C, D) such that A B,C,D. if C

(a non-key attribute) determines D (another non-key attribute), then this phenomenon is
known as transitive dependency.
Checking for Transitive Dependency
Student First Name Second Address Town Post Code Gender
no Name

1 Ali Alam 243-Gulburg Karachi 497 M

2 Anjum Tariq 143-Gulshan Karachi 467 F
 Studen Course Teacher Grade
t no Code ID

1 L24 11 B
1 M90 3 C
1 G13 7 B
2 M90 3 A

Teacher ID Teacher First Name Teacher Second Name

11 Hamza Hayat
3 Qasim Nazar
7 Anum Asgar

Course Code Course Name

L24 DB
M90 Math
G13 IT
M90 Math
Unnormalize 1st Normal Form 2nd Normal Form

Student no P Student no P Student no

Name K K
Address First Name First Name
Town Second Name Second Name
Post Code Address Address
Gender Town Town
Course Code Post Code Post Code
Course Name Gender Gender
Teacher
Grade
P Student no P Student no
K K
P Course Code P Course Code
K K
Course Name Teacher ID
Teacher ID Teacher First Name
Teacher First Name Teacher Second Name
3rd Normal Form Teacher Second Name Grade
Grade
P Student no
K P Course Code
First Name K
Second Name Course Name
Address
Town
Post Code
Gender

P Student no
K
P Course Code
K
Teacher ID
Grade

P Course Code
K
Course Name

P Teacher ID
K
Teacher First Name
Teacher Second Name