Programs

What is Normalization in DBMS? 1NF, 2NF, 3NF

Introduction to Normalisation in DBMS

Structuring data and its attributes in a database is known as database normalisation. Normalisation aims to keep data logically in a database by eradicating data redundancy. Data redundancy makes it challenging for a data administrator to maintain a database. Master normalisation in DBMS by enrolling in the Master of Science in Computer Science from LJMU course today!

Check out our free technology courses to get an edge over the competition.

The Need for Normalisation

Eliminating data redundancy is a necessary process achieved through database normalisation. The ideal reason is to remove anomalies. Persisting anomalies in the database pose a risk to data integrity. Database normalisation consists of a string of guidelines that assist you in structuring a good database. 

The reasons mentioned below show why businesses across the globe should normalise their customer data: 

  • Recognise duplication of data

Standardised data makes it significantly easier to discover and combine duplicate customer records. Duplicate records hamper customers’ journeys, which companies may not always be aware of. 

  • Boost marketing segmentation 

Database normalisation helps businesses categorise prospects and customers. This, in turn, makes it convenient to target messages. For instance, there is a difference in pitching your solution between a CEO and a CFO. 

  • Enhance lead scoring and routing

Lead scoring refers to providing definite leads or accounts in your CRM value to select the highest possibilities successfully. You require high-quality data for productive lead scoring. 

  • Introduce more data to marketing automation 

Marketing plays a significant role in businesses. Your marketing team must be confident of the data they use. They must use accurate data in the information they provide to their clients. Forwarding tailored messages lacking quality or non-normalised data may negatively impact customer experience, ultimately decreasing conversion rates. 

  • Modify redundant data

Normalising the data will help you locate redundant data by aggregating the same fields with similar meanings. Once it recognises duplicate data, it integrates them to eliminate confusion. 

  • Take care of integrated apps

Database normalisation enables the smooth operation of third-party apps and integrations. Normalising helps format the data as per the specific app to function well with the software.

There are generally four types of normal forms in DBMS used. Read on to learn about the first, second, and third normal forms in database normalisation.

Check Out upGrad’s Software Development Courses to upskill yourself.

First Normal Form (1NF) 

In 1NF, every attribute of a relation must be a single-valued attribute. A relation in 1NF does not consist of any composite or multi-valued attribute. The existence of a composite or multi-valued attribute breaches the 1NF. This means that atomic values must be present at the meeting point of two rows or columns.

To put it simply, here is an example. Let’s consider a table consisting of columns for names and contact numbers. In such cases, each entry for a name must only have one contact number for it to be in the first normal form.

Second Normal Form (2NF)

In 2NF, partial dependency in DBMS is eliminated among the 1NF relations. This means that a relation will be in 2NF when it is in 1NF with the non-key attributes fully functional and dependent on the primary key. A relational table can be considered to be in second normal form if it follows two conditions. The first condition is that the table must be in regular form. The second condition is that it must completely depend on the primary key without partial dependencies. 

Third Normal Form (3NF)

In 3NF, transitive dependency in DBMS is eliminated. This means a relation will be in 3NF when it is in 2NF without any existing transitive dependency. Transitive dependency divides the table to eliminate the transitively dependent attributes. It enables us to place the attributes with a copy of the determinant in a new table. 

A relational table can be considered to be in the third normal if it follows the rules enumerated below: 

  • The table must be in 2NF.
  • There is no existence of transitive dependency between a non-prime attribute and the primary key. 
  • If we consider X and Z in a functional dependency, then X must be a super key of the table, or Z must be a prime attribute of the table. 

Full Stack Software Development Bootcamp is the right course for you if you wish to establish a successful career in software development and learn the basics of data science.

Explore our Popular Software Engineering Courses

The Process of Normalisation With Examples

You need to know how it works to clarify further the concept of the first, second and third normalisation types in DBMS. Let’s say you are building a student management application for a course. The illustrated example will give you an extensive idea of the process of database normalisation:

For 1NF

Student code  Student Name  Student Contact Number
101 Ranveer 3682865483, 202835826
102 Sam 69873897879
103 Manav 63683267867

 

In the above table, the <Student Contac Number> is a multivalued characteristic. Hence, we can say that it is not in 1NF. 

To turn it into 1NF, we need to add new rows for each student’s phone number: 

Student Code  Student Name  Student Contact Number 
101 Ranveer 3682865483
101 Ranveer  202835826
102 Sam 69873897879
103 Manav 63683267867

 

For 2NF

Student Code  Project ID  Student Name  Project Name
101 P03 Ranveer  project103
101 P02 Ranveer  project104
102 P01 Sam  project101
103 P04 Manav  project102

 

To eliminate the partial dependencies in the above table and turn it into 2NF, segregate it into the following three separate tables: 

Student Code  Student Name 
101 Ranveer 
101 Ranveer 
102 Sam 
103 Manav 

 

<StudentProject> 

Student Code  Project ID 
101 P03
101 P02
102 P01
103 P04

 

<ProjectDetail>

Project ID  Project Name
P03 project103
P02 project104
P01 project101
P04 project102

 

For 3NF

Student Code  Student Name  Student Zipcode  Student City
101 Ranveer  110033 Model Town 
101 Ranveer  110044 Badarpur
102 Sam   110064 Hari Nagar
103 Manav  110028 Naraina 

 

To eliminate transitive dependency in the above table and to turn it into 3NF. We can divide it into two separate tables: 

Student Code  Student Name  Student Zipcode 
101 Ranveer  110033
101 Ranveer  110044
102 Sam   110064
103 Manav  110028

 

<StudentLocation>

Student Zipcode  Student City
110033 Model Town 
110044 Badarpur
110064 Hari Nagar
110028 Naraina 

Advantages of Normalisation 

A few advantages of normalisation in DBMS are mentioned below:

  • It helps to lessen the chances of data redundancy 
  • It organises data to give a better structure 
  • It maintains data consistency 
  • It designs the database in a much more flexible manner
  • It enforces the concept of relational integrity 

Disadvantages of Normalisation 

A few disadvantages of normalisation in DBMS are: 

  • You must know the user’s needs to structure a database. 
  • Normalising the relations to higher normal forms (4NF, 5NF) deteriorates the performance. 
  • Normalising relations to a higher degree can be complex and take longer. 
  • Carelessness in decomposing data may result in poor database design, leading to serious issues. 

Explore Our Software Development Free Courses

Comparison Between Normalisation and Denormalisation

The table below discusses the factors distinguishing normalisation from denormalisation in DBMS: 

Factor  Normalisation Denormalisation 
Implementation  Normalisation eliminates data redundancy and stores consistent data. Denormalisation adds some redundant data to a normalised database to refine the database execution time.
Objective  The main objective of normalisation is to reduce data redundancy. The main objective of denormalisation is to improve the execution of the database by implementing redundancy. 
Tables  As data is reduced in normalisation, there is also a decrease in the number of tables As data is increased in denormalisation, there is an increase in the number of tables.
Consumption of memory Normalisation reduces data, optimising the memory. Denormalisation introduces extra data. Hence, it creates memory wastage. 
Data integrity  Normalisation preserves data integrity by adding or deleting mismatched data in the table relationship.  Data integrity is not maintained in denormalisation. 

Conclusion 

When data redundancy is eliminated from a database, it optimises the overall performance. This blog illustrates the concepts of normalisation in DBMS to give you an idea of how data redundancy works in a database. It is important to note that most tables do not usually exceed the 3NF limit. However, it is possible to stretch them to 4NF and 5NF depending on your needs and data size.

Enrol in Executive Post Graduation Programme in Full Stack Development From IIITB to kickstart a career in software development. The programme spans 13 months, with top-notch faculty teaching the fundamentals of the field. The curriculum covers all the elements of software development and database management system. Head to the website to know more.

Frequently Asked Questions

What is the main objective of normalisation?

Normalisation in DBMS mainly aims to remove data redundancy and inconsistency to enhance a database’s structure.

Why is database normalisation important?

Normalising data is essential to maintain data integrity. It ensures the existence of only relevant data in each table. It also minimises problematic occurrences in DBMS.

What role does normalisation play in machine learning?

Normalisation is required in machine learning to prepare data. The dataset’s numerical columns are converted to a common scale without altering the information through normalisation.

Want to share this article?

Leave a comment

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

Our Popular Software Engineering Courses

Get Free Consultation

Leave a comment

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

×
Get Free career counselling from upGrad experts!
Book a session with an industry professional today!
No Thanks
Let's do it
Get Free career counselling from upGrad experts!
Book a Session with an industry professional today!
Let's do it
No Thanks