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.

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

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: 

For 2NF

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

<StudentProject> 

<ProjectDetail>

For 3NF

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

<StudentLocation>

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. 

Comparison Between Normalisation and Denormalisation

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

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.