What is Normalization in DBMS? Explain All Normal Forms With a Real-Time Example?

Normalization in DBMS (DataBase Management System)

After reading the above heading you might have got an understanding of what we are gonna cover today.

Still, Let me make you comfortable with the topic and related terms.

Lets, Dive in.

DBMS stands for the Database Management System.

Now, You’ll ask what Database Management System is? Well, the name itself specifies the meaning. DBMS is a system that is used to manage the database.

Now, When I say to manage a database, I mean it is used for creating, storing, and manipulating a database.

There is various software available that helps us to create, store and manipulate databases.

But there is one important concept about databases that very few are fully understood.

ie., Normalization

And today I will explain the Normalization concept with a real-time example explaining all three forms of normalization.

What is Normalization?

So, it helps to minimize the redundancy in relations.

What is redundancy?

For example: In your phone, if you have a contact number saved with the same name. Obviously, it will cause ambiguity about which one to choose.

Same with database, When you have multiple copies of the same information, ambiguity occurs. This problem arises when a database is not normalized.

For information to be precise, we need unique information stored in the database. So, for this, we use normalization which consists of different normal forms.

Normal forms are used to eliminate or reduce redundancy in database tables.

Mainly, there are 3 types of normal forms

  1. 1NF (First Normal Form)
  2. 2NF (Second Normal Form)
  3. 3NF (Third Normal Form)

GOAL :

STEPS :

  1. Create ER diagram of all entities
  2. Convert to 1NF
  3. Convert to 2NF
  4. Convert to 3NF

First, we will represent our data in the form of an ER diagram. Then we will move further to normalizing our data

What is an ER diagram?

Why is it necessary to use an ER diagram?

To create ER diagram, I have used LucidChart, which is a tool where we can create such diagrams.

Following are the notations and ways we are going to today.

ER Diagram ie., Entity Relationship Diagram representations or notations like entity, key, many to many relationship, many to one relationship and one to one relationship
ER Diagram ie., Entity Relationship Diagram representations or notations like entity, key, many to many relationship, many to one relationship and one to one relationship
ER DIAGRAM REPRESENTATIONS OR ER DIAGRAM NOTATIONS

Step 1: Understand Database Requirements.

Let’s look at the problem statement

“ Create a database to help universities keep track of

students that have enrolled, what subjects they are taking,

and teachers of those subjects.”

Initially, our first task is to identify the table to be created. For this, we need to look for nouns. In this case, we have nouns like university, student, subject, teacher.

Identifying these nouns helps us create entities for databases.

so, our entities are fixed now.

→ university

→ subject

→ student

→ teacher

Step 2: Create ER diagram of all entities

Step 3: Convert to 1NF

Every table must have a primary key. Primary Key is a field that uniquely identifies each tuple.

Each table has the following primary key:

An attribute composed of many other attributes is called a composite attribute.

For example, the Address attribute of student Entity type consists of Street, City, State, and Country, etc.

Hence we have divided the address into fields like

An attribute composed of many other attributes is called a composite attribute.
An attribute composed of many other attributes is called a composite attribute.
Composite Attribute

This is the result of the above statements :

An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
Entity Relationship Diagram(ER diagram) for 1NF (First Normal Form)

By this, our database is in 1NF.

Step 4: Convert to 2NF

An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
Entity Relationship Diagram(ER diagram) for 2NF (Second Normal Form)

Now, let's move to 2nd step ie to convert into 2NF.

In this, we will remove the partial dependency. Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.

Many subjects can be under the same category in the subject table, and if one category name changes, all instances need to be updated.

Hence we’ll create another table of categories and link them using foreign keys. Using this every change will be reflected in respective tables,

Now, a category has many subjects therefore category ID will be a foreign key in the subject table.

A foreign key is a key that is used to link two tables using a common entity present in both tables.

In the subject table, the student name is a field that needs to be in 2NF many students can have many subjects. therefore it is a many-to-many relationship.

But we cannot use such relationships in real-time. Hence we’ll break them into 2 tables of one-to-many relationships.

Since subject student were many-to-many relationships we take their PK and make them a foreign key in another table by turning them into one-to-many relationships

In the teacher's table, the subject taught is the subject taught by the teacher and to be related it must be the primary key which currently is.

Therefore, we should refer to the subject column where the teacher has many subjects. Here we will add the teacher’s primary key into the subject table as a foreign key.

in the university table, it is not related to any table. but it has a unique ID and also the university has many subjects. Hence we will add university ID to the subject table as a foreign key,

Now, our database is in 2NF.

Step 5: Convert to 3NF

An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
An Entity-relationship diagram which explains normalization forms like 1NF(first normal form, 2NF(Second Normal Form), and 3NF ( Third Normal Form)
Entity Relationship Diagram(ER diagram) for 3NF (Third Normal Form)

Let’s look at our last step ie to convert into 3NF.

In 3NF, we have to remove the transitive dependency.

Transitive dependency means every non-key attribute in the table must be dependant only on PK

For Example A->B B->C which means A->C is a transitive dependency.

In our ER diagram address is being repeated over almost all the tables and this address is not dependent on the primary key.

Therefore it must have a separate table. In that table, we must include address ID to uniquely identify that tuple.

Now, our database is in 3NF.

There are more normal forms as you go advanced in this. for example., BCNF(Boyce-Codd normal form) will be cover in the further article.

As you can see now, our database is less redundant and can be implemented in real-time.

I hope you have a clear understanding of what normalization is.

Thank you all for reading!

Note: The example mentioned above is taken from one of the courses I have learned.

Learning is all about simplifying things and make it useful. Reading blogs is worth thing you could do your build your career to next level. START READING NOW.