SATHISH ROUTHU — Published On August 16, 2022
Beginner Data Engineering Database

This article was published as a part of the Data Science Blogathon.

Introduction

As an SQL Developer, you regularly work with enormous amounts of data stored in different tables that are present inside databases. This often becomes difficult to extract the information if it is not organized properly. We can solve this problem using Normalization by structuring the database in different forms. This article will help you understand DBMS’s normalization concept with examples.

We’ll discuss the functional dependencies that may exist in a table and anomalies that occur due to these functional dependencies. We will see the conversion of tables into normal forms to eliminate those anomalies.

So, let’s get started.

Why do we need Normalization?

Normalization is a technique for organizing the data into multiple related tables to minimize Data Redundancy and Data Inconsistency. It aims to eliminate anomalies in data.

Data inconsistency results from anything that affects data integrity. This can cause the data to be correct in one place and wrong elsewhere it is stored. This can lead to unreliable and meaningless information. It occurs between tables when similar data is kept in different formats in two different tables.

For example, consider the following tables :

LibraryVisitors (StudentID, Student_Name, Student_Address, InTime, OutTime );

Students (StudentID, Student_Name, Student_Address, Department, RollNo, CourseRegistered );

In the above tables, Student_Address is stored in both tables. For each student_id, the address must be the same in those two tables. Both these relations need to be considered to retrieve the correct address or update it. The issues mentioned arise due to poorly designed/structured databases.

Data inconsistency can be eliminated in databases by using constraints on the relations.

Data Redundancy is the condition where the same data is stored at different locations leading to wastage of storage space.

Examples :

Student Id Student Name Course ID Course Name
111 John C08 English
112 Alice C08 English
111 John C02 French

In the above table, we have stored student name John twice as he registered for two different courses and course name English twice as two students registered it. This is called data redundancy. Data redundancy causes many problems in databases.

The data redundancy in the databases can be eliminated by the normalization of relations.

Functional Dependency

Before diving into normalization, we need to know clearly about functional dependencies.

An attribute is dependent on another attribute if another attribute uniquely identifies it.

It is denoted by  A –> B which means A determines B and B depends upon A.

Example: Student name can be determined by Student_ID.

An anomaly is an unexpected side effect of trying to insert, update, or delete a row. Essentially more data must be provided to accomplish an operation than expected. 

Consider the following relation : 

Retail_Outlet_ID Outlet_Location Item_Code Description Qty_Available Retail_Unit_Price
R1001 King Street,
Hyderabad, 540001
I1001 Britannia
Marie Gold
25 1600
R1002 Rajaji Nagar,
Bangalore, 600341
I1106 Cookies 58 1289
R1003 MVP Colony,
Visakhapatnam,
500021
I1200 Best Rice 22 2000
R1001 King street,
Hyderabad
I1309 Dal 20 1500

Anomalies :

Insertion anomalies: occurs when we cannot insert a new tuple into the table due to a lack of data.

What happens if we try to insert(add) the details of a new retail outlet with no items in its stock?

  • NULL values would be inserted into the item details columns, which is not preferable.

 

Deletion anomalies: occur when the deletion of some data delete the other required data also (Unintended data loss)

What happens if we try to delete the item of item code I1106?

  • The details of the retail outlet R1002 will also be deleted from the database.

Update anomalies: when an update of a single record requires an update in multiple records.

How many rows will be updated if the retail outlet location of R1002 is changed from King Street to Victoria Street?

  • 2 Rows will be updated

Data redundancy : 

When new items are supplied to a retail outlet, what details do we need to insert?

  • Apart from all necessary details, retail_outlet_location will also be inserted, which is redundant.

We have seen insert, delete, update anomalies, and data redundancy in the above-given example. Functional dependencies may lead to anomalies. To minimize anomalies, there is a need to refine functional dependencies using normalization.

Normalization

Database normalization is the process of organizing a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd.

“Normal Forms” (NF) are the different stages of normalization

  • 1 NF (First Normal Form)
  • 2 NF (Second Normal Form)
  • 3 NF (Third Normal Form)
  • BCNF (Boyce -Codd Normal Form)
  • 4 NF (Fourth Normal Form)
  • 5 NF (Fifth Normal Form)
  • 6 NF (Sixth Normal Form)

4NF to 6NF applies to multivalued dependencies and complex table scenarios. In this article, we discuss up to BCNF.

Normalization

1 NF

A relation R is said to be in 1 NF (First Normal) if and only if:

  1. All the attributes of R are atomic.
  2. There should not be any multi-valued attributes.

In the above-taken example of the Retail_Outlets table, we have stored multiple values in an address field, such as street name, city name, and pin code.

What if we want to know about all retail outlets in a given city? We may need to perform some string operations on the address field, which is not preferable. So we need to make all these values be stored atomic in separate fields.

A multi-valued attribute is an attribute that can have multiple values like Contact numbers. They should also be separated like ContactNo1, ContanctNo2,.. to achieve 1st Normal form.

Normalization

1st Normal form

Advantage : 

1 NF allows users to use the database queries effectively as it removes ambiguity by removing the non-atomic and multi-valued attributes, which creates major issues in the future while updating and extracting the data from the database.

Limitation :

Even after 1st Normal form, the data redundancy still exists, so we need further normalization.

2 NF

A relation R is said to be in 2 NF (Second Normal) form if and only if:

  1. R is already in 1 NF
  2. There is no partial dependency in R between non-key attributes and key attributes.

Suppose we have a composite primary or candidate key in our table. Partial dependency occurs when a part of the primary key (Key attribute) determines the non-key attribute.

In the Retail Outlets table, the Item_Code and Retail_Outlet_ID are key attributes. The item description is partially dependent on Item_Code only. Outlet_Location depends on Retail_Outlet_ID. These are partial dependencies.

To achieve normalization, we need to eliminate these dependencies by decomposing the relations.

2NF

2nd normal form

From the above decomposition, we eliminated the partial dependency.

Advantage :

2 NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in a new table(s), and creating relationships between those tables.

Limitation : 

There are still some anomalies as there might be some indirect dependencies between Non-Key attributes, leading to redundant data.

3 NF

A relation R is said to be in 3 NF (Third Normal Form) if and only if:

  1. R is already in 2 NF
  2. There is no transitive dependency that exists between key attributes and non-key attributes through other non-key attributes

A transitive dependency exists when another non-key attribute determines a non-key attribute. In other words, If A determines B and B determines C, then automatically, A determines C.

TRANSITIVE DEPENDANCY

Transitive dependency

Some other examples :

  • The Year of birth determines the Age of the person
  • The price of an Item determines the class of the Item
  • The ZIP code of a city determines the City’s Name
3NF

3rd normal form

Advantage :

3 NF ensures data integrity. It also reduces the amount of data duplication.

Boyce-Codd Normal Form

It is an upgraded version of the 3rd Normal form it is also called as 3.5 Normal Form.

A relation R is said to be in 3 NF (Third Normal Form) if and only if:

  1. R is already in 3 NF
  2. For any dependency A –> B then A should be Super key.

In simple words if A –> B then A cannot be a non-prime Attribute if B being a prime attribute which means that A non prime attribute cannot determine a prime attribute.

You must be wondered how’s this possible? but Yes, there can be some cases in which the Non Prime attribute will determine the prime attributes even if the relation was in 3rd Normal form. BCNF does not allow this kind of dependencies.

let us take an example :

Look at the below Relation of Student Enrollments:

Student_ID Course_Name Professor
101 JAVA Prof. Java
102 C++ Prof. CPP
101 Python Prof. Python
103 JAVA Prof. Java_2
104 Python Prof. Python_2

In the above relation:

  • One student can enroll in multiple courses.
  • Multiple professors can teach one course.
  • One professor can be assigned only one course.

So the primary key will be formed by ( Student_ID & Course_Name ), so these 2 will compositely determine all other attributes in the Relation. In our case, it is only the professor.

  • The Relation is clearly in 1st Normal Form as there are No Multivalued attributes and all attributes have atomic values.
  • The Relation is in 2nd Normal Form as there are No Partial dependencies.
    • Student_Id cannot determine Course_Name as one student can enroll multiple courses.
    • Course_Name cannot determine the professor as one course can be taught by multiple professors.
  • The relation is in 3rd normal form as there are no transitive dependencies.

If we observe here, The “Professor” Attribute, a nonprime attribute, can determine the Course_Name as each professor could be assigned only one course. But Course_Name is a prime attribute, and Professor is not a Super Key. That means a non-prime attribute determines the prime attribute.

This is Not Allowed in BCNF. So, how do we decompose this relation? :

Normalization

Boyce-Codd Normal Form

Until here, we have seen normal forms up to BCNF, and some guidelines are to be followed while normalizing the database. Some of them are listed below.

Guidelines for using normalization:

  • Depending on the business requirements, the tables can be normalized up to the 2nd normal form or the 3rd normal form.
  • Tables in 3 NF are preferred in applications with extensive data modifications.
  • Tables in 2 NF are preferred in applications with extensive data retrieval.
  • Reason: retrieving data from multiple tables is a costly operation.
  • Converting the tables from higher normal form to lower normal form is called “Denormalization”.

The below picture summarizes how to reach the third normal form from an unnormalized form :

UNF

Any relational database without normalization may lead to problems like large tables, difficulty maintaining the database as it involves searching many records, poor disk space utilization, and inconsistencies. If we failed to eliminate this kind of problem, it would lead to data integrity and redundancy problems. Normalization of a relational database helps to solve these problems. Normalization applies by a series of transformations in terms of normal forms. Any relation in a database must be normalized to get efficient access to the database. Each Normal form eliminates each type of dependency and improves the data integrity.

Conclusion

We have looked at :

  • What is normalization, and the need for normalization?
  • Different types of anomalies due to functional dependency.
  • Different types of normal forms to eliminate data redundancy.
  • Guidelines for data Normalization.

The aim of this article is to make you understand the normalization process and how to apply it when you design a database system. There is another multi-valued dependency that 4NF and 5NF can eliminate. Try to explore those also.

I hope this article helped you to understand the concept of normalization better. If you have any questions, please let me know in the comments.  I wish you great learning ahead.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

About the Author

SATHISH ROUTHU

Our Top Authors

Download Analytics Vidhya App for the Latest blog/Article

Leave a Reply Your email address will not be published. Required fields are marked *