Normalization in DBMS

Normalization in DBMS is a technique using which you can organize the data in the database tables so
that:

• There is less repetition of data,

• A large set of data is structured into a bunch of smaller tables,
• and the tables have a proper relationship between them.

DBMS Normalization is a systematic approach to decompose (break down) tables to eliminate data
redundancy(repetition) and undesirable characteristics like Insertion anomaly in DBMS, Update anomaly
in DBMS, and Delete anomaly in DBMS.

It is a multi-step process that puts data into tabular form, removes duplicate data, and set up the
relationship between tables.

Why we need Normalization in DBMS?

Normalization is required for,

• Eliminating redundant(useless) data, therefore handling data integrity, because if data is

repeated it increases the chances of inconsistent data.
• Normalization helps in keeping data consistent by storing the data in one table and referencing it
everywhere else.
• Storage optimization although that is not an issue these days because Database storage is cheap.
• Breaking down large tables into smaller tables with relationships, so it makes the database
structure more scalable and adaptable.
• Ensuring data dependencies make sense i.e., data is logically stored.

Primary Key and Non-key attributes

Types of DBMS Normal forms
First Normal Form (1NF)
• For a table to be in the First Normal Form, it should follow the following 4 rules:
o It should only have single (atomic) valued attributes/columns.
o Values stored in a column should be of the same domain.
o All the columns in a table should have unique names.
o And the order in which data is stored should not matter.

Let's see an example.

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

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.
Create Separate tables for Employee and Employee Skills

So, the Employee table will look like this,

And the new Employee_Skill table:

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.

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.

Let's take an example to understand Partial dependency and the Second Normal Form.

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.
Confused? Let's take an example.

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

Student table:

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

Now in the above table, the primary key is student_id + subject_id, because both this information is
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:

Updated Score table:

Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

1. It satisfies the First Normal Form and the Second Normal form.
2. And, it doesn't have Transitive Dependency.

What is Transitive Dependency?

In a table we have some column that acts as the primary key and other columns depends on this column.
But what if a column that is not the primary key depends on another column that is also not a primary
key or part of it? Then we have Transitive dependency in our table.

Let's take an example. We had the Score table in the Second Normal Form above. If we have to store
some extra information in it, like,

1. exam_type
2. total_marks
To store the type of exam and the total marks in the exam so that we can later calculate the percentage
of marks scored by each student.

The Score table will look like this,

In the table above, the column exam_type depends on both student_id and subject_id,
because,
1. a student can be in the CSE branch or the Mechanical branch,
2. and based on that they may have different exam types for different subjects.
3. The CSE students may have both Practical and Theory for Compiler Design,
4. whereas Mechanical branch students may only have Theory exams for Compiler Design.
But the column total_marks just depend on the exam_type column. And the exam_type
column is not a part of the primary key. Because the primary key is student_id + subject_id,
hence we have a Transitive dependency here.
How to Transitive Dependency?
You can create a separate table for ExamType and use it in the Score table.
New ExamType table,

We have created a new table ExamType and we have added more related information in it like
duration (duration of exam in mins.), and now we can use the exam_type_id in the Score table.