Prof ssvn sarma@vaagdevi

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly,
update anomaly & deletion anomaly. Let’s discuss about anomalies first then we will discuss normal forms with
examples.

Following are the various types of Normal forms:

Normal Description
Form

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non

non-key
key attributes are fully functional
dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.

4NF A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi
multi-valued
valued
dependency.
Prof ssvn sarma@vaagdevi

5NF A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining
should be lossless.

Advantages of Normalization

o Normalization helps to minimize data redundancy.

o Greater overall database organization.
o Data consistency within the database.
o Much more flexible database design.
o Enforces the concept of relational integrity.

Disadvantages of Normalization

o You cannot start building the database before knowing what the user needs.
o The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
o It is very time-consuming and difficult to normalize relations of a higher degree.
o Careless decomposition may lead to a bad database design, leading to serious problems.

Anomalies in DBMS
There are three types of anomalies that occur when the database is not normalized. These are – Insertion,
update and deletion anomaly. Let’s take an example to understand this.

Example: Suppose a manufacturing company stores the employee details in a table named employee that has
four attributes: emp_id for storing employee’s id, emp_name for storing employee’s name, emp_address for
storing employee’s address and emp_dept for storing the department details in which the employee works. At
some point of time the table looks like this:

emp_id emp_name emp_address emp_dept

101 Rick Delhi D001

101 Rick Delhi D002

Prof ssvn sarma@vaagdevi

123 Maggie Agra D890

166 Glenn Chennai D900

166 Glenn Chennai D004

The above table is not normalized. We will see the problems that we face when a table is not normalized.

Update anomaly: In the above table we have two rows for employee Rick as he belongs to two departments of
the company. If we want to update the address of Rick then we have to update the same in two rows or the
data will become inconsistent. If somehow, the correct address gets updated in one department but not in other
then as per the database, Rick would be having two different addresses, which is not correct and would lead to
inconsistent data.

Insert anomaly: Suppose a new employee joins the company, who is under training and currently not assigned
to any department then we would not be able to insert the data into the table if emp_dept field doesn’t allow
nulls.

Delete anomaly: Suppose, if at a point of time the company closes the department D890 then deleting the
rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is
assigned only to this department.

To overcome these anomalies we need to normalize the data. In the next section we will discuss about
normalization.

Normalization
Here are the most commonly used normal forms:

 First normal form(1NF)

 Second normal form(2NF)
 Third normal form(3NF)
 Boyce & Codd normal form (BCNF)

First normal form (1NF)

As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold
only atomic values.

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.
Prof ssvn sarma@vaagdevi

4. And the order in which data is stored should not matter.

If we have an Employee table in which we store the employee information along with the employee skillset, the table
will look like this:

emp_id emp_name emp_mobile emp_skills

1 John Tick 9999957773 Python, JavaScript

2 Darth Trader 8888853337 HTML, CSS, JavaScript

3 Rony Shark 7777720008 Java, Linux, C++

The above table has 4 columns:

 All the columns have different names.

 All the columns hold values of the same type like emp_name has all the names, emp_mobile has all the
contact numbers, etc.
 The order in which we save data doesn't matter
 But the emp_skills column holds multiple comma-separated values, while as per the First Normal form, each
column should have a single value.

Hence the above table fails to pass the First Normal form.

So how do you fix the above table? There are two ways to do this:

1. Remove the emp_skills column from the Employee table and keep it in some other table.
2. Or add multiple rows for the employee and each row is linked with one skill.

1. Create Separate tables for Employee and Employee Skills

So the Employee table will look like this,

emp_id emp_name emp_mobile

Prof ssvn sarma@vaagdevi

emp_id emp_name emp_mobile

1 John Tick 9999957773

2 Darth Trader 8888853337

3 Rony Shark 7777720008

And the new Employee_Skill table:

emp_id emp_skill

1 Python

1 JavaScript

2 HTML

2 CSS

2 JavaScript
Prof ssvn sarma@vaagdevi

emp_id emp_skill

3 Java

3 Linux

3 C++

2. Add Multiple rows for Multiple skills

You can also simply add multiple rows to add multiple skills. This will lead to repetition of the data, but that can be
handled as you further Normalize your data using the Second Normal form and the Third Normal form.

emp_id emp_name emp_mobile emp_skill

1 John Tick 9999957773 Python

1 John Tick 9999957773 JavaScript

2 Darth Trader 8888853337 HTML

2 Darth Trader 8888853337 CSS

2 Darth Trader 8888853337 JavaScript

Prof ssvn sarma@vaagdevi

emp_id emp_name emp_mobile emp_skill

3 Rony Shark 7777720008 Java

3 Rony Shark 7777720008 Linux

3 Rony Shark 7777720008 C++

Example: Suppose a company wants to store the names and contact details of its employees. It creates a
table that looks like this:

emp_id emp_name emp_address emp_mobile

101 Herschel New Delhi 8912312390

8812121212
102 Jon Kanpur

9900012222

103 Ron Chennai 7778881212

Prof ssvn sarma@vaagdevi

9990000123
104 Lester Bangalore
8123450987

Two employees (Jon & Lester) are having two mobile numbers so the company stored them in the same field
as you can see in the table above.

This table is not in 1NF as the rule says “each attribute of a table must have atomic (single) values”, the
emp_mobile values for employees Jon & Lester violates that rule.

To make the table complies with 1NF we should have the data like this:

emp_id emp_name emp_address emp_mobile

101 Herschel New Delhi 8912312390

102 Jon Kanpur 8812121212

102 Jon Kanpur 9900012222

103 Ron Chennai 7778881212

104 Lester Bangalore 9990000123

104 Lester Bangalore 8123450987

EMPLOYEE table:
Prof ssvn sarma@vaagdevi

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second normal form (2NF)

A table is said to be in 2NF if both the following conditions hold:

 Table is in 1NF (First normal form)

 No non-prime attribute is dependent on the proper subset of any candidate key of table.
 In the 2NF, relational must be in 1NF.
 In the second normal form, all non-key attributes are fully functional dependent on the primary key

Or

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.

What is Partial Dependency?

When a table has a primary key that is made up of two or more columns, then all the columns(not included in the
primary key) in that table should depend on the entire primary key and not on a part of it. If any column(which is not
in the primary key) depends on a part of the primary key then we say we have Partial dependency in the table.

An attribute that is not part of any candidate key is known as non-prime attribute.
Prof ssvn sarma@vaagdevi

If we have two tables Students and Subjects, to store student information and information related to subjects.

Student table:

student_id student_name branch

1 Akon CSE

2 Bkon Mechanical

Subject Table:

subject_id subject_name

1 C Language

2 DSA

3 Operating System

And we have another table Score to store the marks scored by students in any subject like this,

student_id subject_id marks teacher_name

1 1 70 Miss. C
Prof ssvn sarma@vaagdevi

student_id subject_id marks teacher_name

1 2 82 Mr. D

2 1 65 Mr. Op

Now in the above table, the primary key is student_id + subject_id, because both these information are required to
select any row of data.

But in the Score table, we have a column teacher_name, which depends on the subject information or just
the subject_id, so we should not keep that information in the Score table.

The column teacher_name should be in the Subjects table. And then the entire system will be Normalized as per the
Second Normal Form.

Updated Subject table:

subject_id subject_name teacher_name

1 C Language Miss. C

2 DSA Mr. D

3 Operating System Mr. Op

Updated Score table:

student_id subject_id marks

Prof ssvn sarma@vaagdevi

student_id subject_id marks

1 1 70

1 2 82

2 1 65

Example: Suppose a school wants to store the data of teachers and the subjects they teach. They create a
table that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows
for a same teacher.

teacher_id subject teacher_age

111 Maths 38

111 Physics 38

222 Biology 38

333 Physics 40
Prof ssvn sarma@vaagdevi

333 Chemistry 40

Candidate Keys: {teacher_id, subject}

Non prime attribute: teacher_age

The table is in 1 NF because each attribute has atomic values. However, it is not in 2NF because non prime
attribute teacher_age is dependent on teacher_id alone which is a proper subset of candidate key. This violates
the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper subset of any candidate
key of the table”.

To make the table complies with 2NF we can break it in two tables like this:
teacher_details table:

teacher_id teacher_age

111 38

222 38

333 40

teacher_subject table:

teacher_id subject

111 Maths

111 Physics
Prof ssvn sarma@vaagdevi

222 Biology

333 Physics

333 Chemistry

Now the tables comply with Second normal form (2NF).