JUSTICE BASHEER AHMED SAYEED COLLEGE FOR WOMEN

(Autonomous) Afternoon Session Chennai 18.

S.I.E.T.

NORMALIZATION
Prepared by

M. MINU MEERA, Assistant Professor

A. JUNAITHA BARVEEN, Assistant Professor

DEPARTMENT OF COMPUTER SCIENCE

 INTRODUCTION
Problems Without Normalization
• The biggest problem needed to be solved in database is data redundancy.
• If a table is not properly normalized and have data redundancy then it will occupy extra memory
space and also make it difficult to handle and update the database, without facing data loss.
• Why data redundancy is the problem? Because it causes:
▪ Insert Anomaly
▪ Update Anomaly
▪ Delete Anomaly
• To understand these anomalies let us take an example of a Student table.
Roll no Name Branch Hod . Clg_phn In this table, we have data of 4 CS students. As we can see,
101 Anitha CS Mrs.X 11111 data for the fields Branch, Hod and Clg_phn is repeated for
102 Booma CS Mrs.X 11111 the students who are in the same branch in the college, this
103 Chitra CS Mrs.X 11111 is Data Redundancy.
101 Devi CS Mrs.X 11111

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 2

 INTRODUCTION
• Insert Anomaly :
• Suppose for a new admission, until and unless a student opts for a branch, data of the student cannot be inserted, or else we
will have to set the branch information as NULL.
• Also, if we have to insert data of 100 students of same branch, then the branch information will be repeated for all those
100 students.
• This is Insert anomaly.
• Update Anomaly :
• What if Mr. X leaves the college? or is no longer the HOD of computer science department?
• In that case all the student records will have to be updated, and if by mistake we miss any record, it will lead to data
inconsistency.
• This is Update anomaly.
• Delete Anomaly :
• In our Student table, two different informations are kept together, Student information and Branch information.
• Hence, at the end of the academic year, if student records are deleted, we will also lose the branch information.
• This is Delete anomaly.

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 3

 NORMALIZATION
• Normalization is the process of organizing the data in the database.
• Normalization is the process of removing redundant data from tables to improve storage efficiency, data integrity,
and scalability.
• It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.
• Normalization divides the larger table into the smaller table and links them using relationship.
• The normal form is used to reduce redundancy from the database table.
Types of Normalization

Normal Forms

Domain-
1NF 2NF 3NF BCNF 4NF
key NF

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 4

 First Normal Form (1NF)
• For a table to be in the First Normal Form, it should follow the following 4 rules:
1. It should only have single(atomic) valued attributes/columns.
2. Values stored in a column should be of the same domain.
3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.
➢ Rule 1: Single Valued Attributes
• Each column of our table should be single valued which means they should not contain multiple values.
➢ Rule 2: Attribute Domain should not change
• In each column the values stored must be of the same kind or type.
➢ For example:
• If we have a column DOB to save date of births of a set of people, then we cannot or we must not save 'names' of
some of them in that column along with 'date of birth' of others in that column. It should hold only 'date of birth' for
all the records/rows.

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 5

 First Normal Form (1NF) (Cont..)
➢ Rule 3: Unique name for Attributes/Columns
▪ Each column in a table should have a unique name.
▪ This is to avoid confusion at the time of retrieving data or performing any other operation on the stored data.
▪ If one or more columns have same name, then the DBMS system will be left confused.
➢ Rule 4: Order doesn't matters
▪ The order in which we store the data in our table doesn't matter.
➢ Example
item colors price tax
•This table is not in first normal form because:
T-shirt red, blue 320.00 0.60 •As per the 1NF each column must contain atomic value.
polo red, yellow 280.00 0.60 •But the column colors contains non-atomic values.
Levis red, blue 400.00 0.60 •Duplicate records / no primary key
sweatshirt blue, black 250.00 1.25

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 6

 First Normal Form (1NF) (Cont..)
➢ How to solve this Problem?
• Break the values into atomic values.
• This is done as follows and it now satisfies the First Normal Form.

item colors price tax

T-shirt red 320.00 0.60
•Now the table is in First Normal Form.
T-shirt blue 320.00 0.60
•Few values are getting repeated but values for
polo red 280.00 0.60
the subject column are now atomic for each record/row.
polo yellow 280.00 0.60
•Using the First Normal Form, data redundancy
Levis red 400.00 0.60
increases, as there will be many columns with same data
Levis blue 400.00 0.60
in multiple rows but each row as a whole will be unique.
sweatshirt blue 250.00 1.25
sweatshirt black 250.00 1.25

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 7

 Second Normal Form (2NF)
• For a table to be in the Second Normal Form,
1. It should be in the First Normal form.
2. And, it should not have Partial Dependency. That is all non key attributes must depend on key
attributes.
Partial Dependency : It occurs when a non-prime attribute is functionally dependent on part of a
candidate key.
item colors price tax
T-shirt red 320.00 0.60
▪Table is not in second normal form because:
T-shirt blue 320.00 0.60
▪price and tax depend on item, but not color.
polo red 280.00 0.60
polo yellow 280.00 0.60
Levis red 400.00 0.60
Levis blue 400.00 0.60
sweatshirt blue 250.00 1.25
sweatshirt black 250.00 1.25
10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 8
 Second Normal Form (2NF) (Cont..)
➢ How to solve this Problem?
• Split the table to remove partial dependency.
item colors item price tax
T-shirt red T-shirt 320.00 0.60
T-shirt blue polo 280.00 0.60
polo red Levis 400.00 0.60
polo yellow sweatshirt 250.00 1.25
Levis red
Levis blue
sweatshirt blue
sweatshirt black

• Tables are now in second normal form.

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 9

 Third Normal Form (3NF)
• A table is said to be in the Third Normal Form when,
1. It should be in the Second Normal form.
2. And, it doesn't have Transitive Dependency.
Transitive Dependency : When a non-prime attribute depends on other non-prime attributes rather than
depending upon the prime attributes or primary key.
item colors item price tax
T-shirt red T-shirt 320.00 0.60
T-shirt blue polo 280.00 0.60
polo red Levis 400.00 0.60
polo yellow sweatshirt 250.00 1.25
Levis red
Levis blue
sweatshirt blue
sweatshirt black
• Tables are not in third normal form because: tax depends on price, not item

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 10

 Third Normal Form (3NF) (Cont..)
➢ How to solve this Problem?
• Split the table to remove Transitive Dependency.
item price
item colors T-shirt 320.00
T-shirt red polo 280.00
T-shirt blue Levis 400.00
polo red sweatshirt 250.00
polo yellow
price tax
Levis red
320.00 0.60
Levis blue
280.00 0.60
sweatshirt blue
400.00 0.60
sweatshirt black
250.00 1.25

• Tables are now in third normal form

10/9/2024 Presenter Name: [Link] MEERA,[Link] BARVEEN, Department of Computer Science 11