Normalization in
SQL
Normalization
Decompose larger, complex table into simpler and smaller ones
Moves from Lower Normal forms to higher normal forms
Normalization
Higher
1NF 2NF 3NF
(BCNF,4NF,5NF)
Need for Normalization
In order to produce good database design
To ensure all database operation to be efficiently performed
Avoid any expensive DBMS operations
Avoid Unnecessary replication of information
Need for Normalization
Raw Sample Database
Student Details Course Details Prerequisite Result
101 Jack 11/4/1975 M1 Advance Maths 17 Basic Maths 03/11/2019 82 A
102 Rock 10/04/1974 P4 Advance Physics 12 Basic Physics 22/12/2018 78 B
103 Jen 10/09/1975 B3 Advance Biology 10 Basic Biology 03/11/2019 88 A
104 Smith 15/03/1974 H6 Advance History 19 Basic History 22/11/2018 68 B
105 Ashok 16/12/1976 C3 Advance Chemistry 13 Basic Chemistry 23/11/2019 52 C
Functional Dependency
Consider the relation
◦ Result (Student#, Course#, CourseName,Marks, Grade)
◦ Student # and Course # together defines exactly one value of marks
◦ Student# and Course# Determines, Marks /Marks is functionally Depenedent on Student number and Course Number
Other Functional Dependencies in the Relation
Course# = CourseName
Marks# - Grade
Functional Dependency
In a given relation P & Q are attibutes. Attribute ! Is functionally dependent on attribute P if each
value of P determines exactly one value of Q
P(Student#,Cour
Q(Marks)
se#)
Type of Functional Dependency
Partial Functional Dependency
Transitive Dependency
Partial Functional Dependency
Attribute Q is partially dependent in attribute p, if and only if it is depenedent on the subset of
attribute P
Report (Student#,Course#,StudentName,CourseName,Marks,Grade)
Student #
Marks
Course #
Course # CourseName
Transitive Dependency
X,Y,Z are the 3 Attributes
X Y Y Z
X Z
Student# Course# Marks# Grade#
1NF
A Relation schema is in 1NF if and only if
◦ All Attributes in the relation are atomic (indivisible Value)
◦ And there are no repeating elements of group of elements
Date of
Student# Name DOB Course# CourseName Duration Prerequisite Exam Marks Grade
101Jack 11-04-1975M1 Advance Maths 17Basic Maths 03-11-2019 82A
102Rock 10-04-1974P4 Advance Physics 12Basic Physics 22-12-2018 78B
103Jen 10-09-1975B3 Advance Biology 10Basic Biology 03-11-2019 88A
104Smith 15-03-1974H6 Advance History 19Basic History 22-11-2018 68B
105Ashok 16-12-1976C3 Advance Chemistry 13Basic Chemistry 23-11-2019 52C
2NF
A relation is said to be in 2NF if and only if:
◦ It is in 1st NF
◦ No partial dependency exists between key and non key attributes
◦ Student #, Course # -> Marks
◦ Student#, Course# ->
◦ Marks->Grade
◦ Student#-> Student Name , DOB Partial
Split/Decompose the table
◦ Course#-> CourseName, Prerequisite Dependency
to remove partial
◦ DuationDays,Date of Exam with Key
dependencies
Attributes
2NF
StudentID Name DOB StudentID Course# Marks Grade
101 Jack 11-04-1975 101 M1 82 A
102 Rock 10-04-1974 102 P4 78 B
103 Jen 10-09-1975 103 B3 88 A
104 Smith 15-03-1974 104 H6 68 B
105 Ashok 16-12-1976 105 C3 52 C
CourseNum CourseName Duration Prerequisite Date of Exam
M1 Advance Maths 17 Basic Maths 03-11-2019
P4 Advance Physics 12 Basic Physics 22-12-2018
B3 Advance Biology 10 Basic Biology 03-11-2019
H6 Advance History 19 Basic History 22-11-2018
C3 Advance Chemistry 13 Basic Chemistry 23-11-2019
3NF
A relation is said to be in 3NF if and only if
It is in 2NF
No Transitive Dependency exist between key and non key attributes
StudentID Course# Marks Grade
101 M1 82 A
102 P4 78 B
103 B3 88 A
104 H6 68 B
105 C3 52 C
Student#, Course# ->Marks
Student#, Course# ->Grade
Marks -> Grade
Student#, Course# ->Marks ->Grade (Transitive Dependency) – It has to be removed
3NF
StudentID CourseNum Marks Marks Grade
101 M1 82 82 A
102 P4 78
103 B3 88 78 B
104 H6 68 88 A
105 C3 52 68 B
52 C
