CSE 303
Database Management System
CSE 303 Database Management System Prepared By Nafees Mansoor 1
Overview
– Introduction to Normalization
– 1st Normal Form
– 2nd Normal Form
– 3rd Normal Form
CSE 303 Database Management System Prepared By Nafees Mansoor 2
2
Normalization
●The main goal of Database
Normalization is to restructure the
logical data model of a database to:
●Eliminate redundancy
●Organize data efficiently
●Reduce the potential for data
anomalies.
CSE 303 Database Management System Prepared By Nafees Mansoor 3
1st Normal Form - The Requirements
– Each table has a primary key: minimal set of
attributes which can uniquely identify a record
– The values in each column of a table are atomic
(No multi-value attributes allowed).
– There are no repeating groups: two columns do
not store similar information in the same table.
CSE 303 Database Management System Prepared By Nafees Mansoor 4
4
1st Normal Form
Un-normalized Students table:
S_ID S_Name HOD HOD_off Dep Class1 Class2
111 THMY Prof. C601 CSE A302 B501
MLP
222 EDCR Prof. C602 ETE B502 A301
BHU
333 QAZW Dr. RTY C603 USB B501 A303
444 ERTY Prof. C601 CSE A301 B501
MLP
555 GYTH Prof. C601 CSE A302 B502
MLP
CSE 303 Database Management System Prepared By Nafees Mansoor 5
1st Normal Form
S_ID S_Name HOD HOD_off Dep Class1
111 THMY Prof. MLP C601 CSE A302
111 THMY Prof. MLP C601 CSE B501
222 EDCR Prof. BHU C602 ETE B502
222 EDCR Prof. BHU C602 ETE A301
333 QAZW Dr. RTY C603 USB B501
333 QAZW Dr. RTY C603 USB A303
444 ERTY Prof. MLP C601 CSE A301
444 ERTY Prof. MLP C601 CSE B501
555 GYTH Prof. MLP C601 CSE A302
555 GYTH Prof. MLP C601 CSE B502
CSE 303 Database Management System Prepared By Nafees Mansoor 6
6
2ND Normal Form - The Requirements
– All requirements for 1st NF must be met.
– Eliminate fields that do not depend on the
primary key;
• That is, any field that is dependent not only on the
primary key but also on another field must be moved
to another table.
CSE 303 Database Management System Prepared By Nafees Mansoor 7
2ND Normal Form
S_ID S_Name Class1 Dep HOD HOD_off
111 THMY A302 CSE Prof. C601
MLP
111 THMY B501
ETE Prof. C602
222 EDCR B502 BHU
222 EDCR A301 BBA Dr. RTY C603
333 QAZW B501
333 QAZW A303 S_ID Dep
444 ERTY A301
111 CSE
444 ERTY B501
222 ETE
555 GYTH A302
555 GYTH B502 333 USB
444 CSE
555 CSE
CSE 303 Database Management System Prepared By Nafees Mansoor 8
8
3rd Normal Form - The Requirements
● The requirements to satisfy the 3rd NF:
– All requirements for 2nd NF must be met.
– Every non-prime attribute of table must be
dependent on primary key,
• There should not be the case that a non-prime
attribute is determined by another non-prime
attribute. So this transitive functional dependency
should be removed from the table.
CSE 303 Database Management System Prepared By Nafees Mansoor 9
3rdNormal Form
S_ID Dep
S_ID S_Name 111 CSE
111 THMY 222 ETE Dep HOD HOD_off
222 EDCR 333 USB CSE Prof. C601
444 CSE MLP
333 QAZW
555 CSE ETE Prof. C602
444 ERTY
BHU
555 GYTH S_ID Class1
BBA Dr. RTY C603
111 A302
111 B501
222 B502 Class Campus
222 A301 A302 A
333 B501
B501 B
333 A303
444 A301
B502 B
444 B501
A301 A
555 A302
555 B502
A303 A
CSE 303 Database Management System Prepared By Nafees Mansoor 10
10