BASIC SQL
Tutor: Nguyễn Lê Chí Bảo
MODULE 4.
DATA
NORMALIZATION
Module 4: DATA NORMALIZATION
Data normalization
Normal Forms
Data normalization
Module 4: DATA NORMALIZATION
WHAT IS NORMALIZATION?
• Database Normalization is a technique of organizing the data in the database. Normalization is a
systematic approach of decomposing tables to eliminate data redundancy(repetition) and undesirable
characteristics like Insertion, Update and Deletion Anomalies. It is a multi-step process that puts data
into tabular form, removing duplicated data from the relation tables.
• Normalization is used for mainly two purposes:
- Eliminating redundant(useless) data
- Ensuring data dependencies make sense i.e data is logically stored.
Module 4: DATA NORMALIZATION
Problems without normalization?
• If a table is not properly normalized and have data redundancy then it will not only eat up extra memory
space but will also make it difficult to handle and update the database, without facing data loss. Insertion,
Updation and Deletion Anomalies are very frequent if database is not normalized.
Module 4: DATA NORMALIZATION
Problems without normalization - Example
• Insertion Anomaly
• Updation Anomaly
• Deletion Anomaly
Module 4: DATA NORMALIZATION
Normal Forms
• First Normal Form
• Second Normal Form
• Third Normal Form
• BCNF
• Fourth Normal Form
• Fifth Normal Form
Module 4: DATA NORMALIZATION
First Normal Form (1NF)
For a table to be in the First Normal Form, it should follow the following 4 rules:
• It should only have single(atomic) valued attributes/columns.
• Values stored in a column should be of the same domain
• All the columns in a table should have unique names.
• And the order in which data is stored, does not matter.
Module 4: DATA NORMALIZATION
First Normal Form (1NF) - Example
Module 4: DATA NORMALIZATION
Second Normal Form (2NF)
For a table to be in the Second Normal Form,
• It should be in the First Normal form.
• And, it should not have Partial Dependency.
A Table in 2NF if all of the fields in the PK are required to determine the other non -key fields.
Module 4: DATA NORMALIZATION
Second Normal Form (2NF) - Example
Subject_id Subject_name
1 Java
2 C++
Partial Dependency
3 Php
Student_id Subject_id Marks Teacher
10 1 70 Java teacher
10 2 75 C++ teacher
11 1 80 Java teacher
Module 4: DATA NORMALIZATION
Second Normal Form (2NF) - Example
Subject_id Subject_name Teacher
1 Java Java teacher
2 C++ C++ teacher
3 Php Java teacher Remove Partial Dependency
Student_id Subject_id Marks
10 1 70
10 2 75
11 1 80
Module 4: DATA NORMALIZATION
Third Normal Form (3NF)
A table is said to be in the Third Normal Form when,
• It is in the Second Normal form.
• And, it doesn't have Transitive Dependency.
A Table in 3NF if all of non-key fields are independent from any other non-key field.
Module 4: DATA NORMALIZATION
Third Normal Form (3NF) - Example
ID Name SUBJECT STATE COUNTRY
1 Harry English Gujrat INDIA
2 Stephan Geography Punjab INDIA
3 Lan Mathematics Maharashtra INDIA
4 Katharine History Bihar INDIA
Module 4: DATA NORMALIZATION
Third Normal Form (3NF) - Example
ID Name SUBJECT STATE STATE COUNTRY
1 Harry English Gujrat Gujrat INDIA
2 Stephan Geography Punjab Punjab INDIA
3 Lan Mathematics Maharashtra Maharashtra INDIA
4 Katharine History Bihar Bihar INDIA
Module 4: DATA NORMALIZATION
Boyce- Codd Normal Form(BCNF)
A table is said to be in the Boyce-Codd Normal Form when,
• It should be in the Third Normal Form.
• And, for any dependency A → B, A should be a super key.
Module 4: DATA NORMALIZATION
Boyce- Codd Normal Form(BCNF)- Example
• Important point to note here is, one professor teaches only Student_id subject professor
one subject, but one subject may have two different
professors. 101 Java [Link]
• student_id, subject form primary key, which means subject
101 C++ [Link]
column is a prime attribute. But, there is one more
dependency, professor → subject. 102 Java P.Java2
• Subject is a prime attribute, professor is a non-prime
attribute, which is not allowed by BCNF. 103 C# [Link]
104 Java [Link]
Module 4: DATA NORMALIZATION
Boyce- Codd Normal Form(BCNF)- Example
Student_id Prof_ID
Prof_ID Prof_name Subject
Module 4: DATA NORMALIZATION
Data normalization
Normal Forms
THANKS
CREDITS: This presentation template was created by
Slidesgo, including icons by Flaticon, and Name: Nguyễn Lê Chí Bảo
infographics & images by Freepik and illustrations Email: bao2101998@[Link]
by Storyset Phone: 091 544 2420
Linkedin: nguyenlechibao