Database Management System

Normalization in Database

What is normalization in DBMS?

Normalization in DBMS is a systematic process of arranging data in a relational database to

minimize redundancy and improve data integrity. It involves decomposing large tables into
smaller, related tables and defining relationships among them using keys.

The primary goal of normalization in DBMS is to ensure that each piece of data is stored only
once, thereby avoiding inconsistencies and duplication.

By following normalization rules, we can:

▪ Eliminate redundant data

▪ Ensure logical data storage
▪ Enhance data integrity
▪ Simplify database maintenance

Normalization is applied using a series of normal forms. Let’s explore them in the next section.
Types of normal forms in DBMS

In DBMS, there are several levels of normalization, commonly referred to as normal forms. Each
form addresses specific types of anomalies and redundancies. Here, we’ll touch on 1NF, 2NF, 3NF,
BCNF, 4NF, and 5NF to understand how they differ in their functionalities.

First Normal Form (1NF)

First Normal Form (1NF) focuses on ensuring that the values in each column of a table are
atomic, meaning they cannot be further divided. A table is in 1NF if:

➢ All attributes (columns) contain only single, indivisible values

➢ Each record is unique and there are no repeating groups

1
2
Why 1NF matters:

Applying 1NF removes nested or grouped data and lays the groundwork for further normalization.
It’s the first step in structuring data for consistency and clarity.

Second Normal Form (2NF)

Second Normal Form (2NF) builds upon 1NF and addresses partial dependencies. A table is in
2NF if:

➢ It is already 1NF
➢ Every non-prime attribute (an attribute that’s not part of a candidate key) is fully
functionally dependent on the entire primary key

3
Here, the composite primary key is (StudentID, CourseID). But “StudentName” depends only on
“StudentID” and “CourseName” depends only on “CourseID”, which indicates partial
dependency.

To convert this into 2NF, we can split the table into three parts:
▪ Student table
▪ Course table
▪ Enrollment table

4
Why 2NF matters:

2NF ensures that data is placed in the appropriate tables and that every column is fully
dependent on the whole key, not just part of it. This eliminates data duplication and makes
updates more efficient.

Third Normal Form (3NF)

Third Normal Form (3NF) eliminates transitive dependencies. A table is in 3NF if:

➢ It is already 2NF
➢ No non-prime attribute is dependent transitively on the primary key

5
In simpler terms, non-key attributes should not depend on other non-key attributes.

Here, “DeptName” depends on “DeptID”, which in turn depends on “EmpID”. This is a

transitive dependency.

To convert this into 3NF, we can split the table into two parts:

▪ Employee table
▪ Department table

6
Why 3NF matters:

3NF promotes data integrity and reduces redundancy by ensuring that each non-key
attribute is directly dependent on the primary key.

Boyce-Codd Normal Form (BCNF)

BCNF (Boyce-Codd Normal Form) is a higher version of 3NF. A table is in BCNF if:

➢ It is in 3NF
➢ Every functional dependency has a super key on the left-hand side

BCNF handles certain anomalies that 3NF cannot. If a table has overlapping candidate
keys, it might violate BCNF even while being in 3NF.

Here, a professor can teach multiple courses, and each course is taught by one professor.
However, some professors may teach the same course at different times.

The functional dependencies in this scenario include:

▪ Professor → Course (Each professor teaches only one course)
▪ Course → Professor (Each course is taught by one professor)

But in this table, neither “Professor” nor “Course” is a super key, so it violates BCNF.

To convert this into BCNF, we can split the table into two parts:

▪ Professor-course table
▪ Course-schedule table

7
Why BCNF matters:

BCNF ensures stricter normalization by resolving complex dependencies, especially in tables with
multiple candidate keys.

Fourth Normal Form (4NF)

A table is in the Fourth Normal Form (4NF) if:

➢ It is in BCNF
➢ It contains no multi-valued dependencies

8
In this scenario, a teacher can teach multiple subjects and speak multiple languages.

This has multi-valued dependencies:

▪ Teacher →→ Subject
▪ Teacher →→ Language

These are independent facts stored in the same table, leading to unnecessary repetition.

To convert this into 4NF, we can split the table into two:

▪ Teacher-subject table
▪ Teacher-language table

9
Why 4NF matters:
4NF separates logically independent data, avoiding data explosion and maintaining clarity.

Fifth Normal Form (5NF)

Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), ensures that a relation
is broken down into smaller relations that can be joined back without any loss of information or
introduction of invalid combinations, and that all join dependencies are implied by candidate keys.

Scenario:

A school tracks which students are learning which subjects from which teachers. However, each
combination (Student, Subject, Teacher) is valid only as a complete triple — it’s not enough to just
know who is learning what or who teaches what.

10
We can’t decompose this into binary relations (e.g., Student-Subject, Student-Teacher, Subject-
Teacher) without possibly recreating invalid combinations when we rejoin the data.

11
Why 5NF matters:

5NF handles highly complex relationships and ensures complete reconstruction of data without
redundancy.

By understanding these normal forms, we can build databases that are logically structured,
scalable, and efficient. Applying these rules during the design phase can save time, reduce
maintenance effort, and prevent anomalies during data operations.

With a good understanding of the normal forms, let’s now discuss why normalization is
important in DBMS.

Why is normalization important?

As our applications grow, so does the complexity of our data. Without proper normalization, a
database can become bloated, inconsistent, and difficult to maintain. Here’s why normalization in
DBMS is essential:

▪ Reduces data redundancy: Repetition of data across multiple rows and tables is minimized.
▪ Improves data consistency: Since each data item is stored in one place, updates are more
reliable.
▪ Enhances query performance: Smaller, well-structured tables can be queried more
efficiently.
▪ Eases maintenance: With logical data separation, updates, deletions, and insertions become
simpler.

12
When to use normalization?

We typically apply normalization in DBMS during the design phase of database development. It’s
especially useful when:

▪ We’re dealing with large datasets prone to duplication

▪ We require high data consistency and accuracy
▪ We want scalable and maintainable database systems
However, in some cases—especially when performance is critical, and the schema is read-heavy—
controlled denormalization (the opposite of normalization) may be appropriate.

We discussed what normalization in DBMS is, understood why it’s important, and discovered
some scenarios where it is ideal to use normalization. We also learned about the different types of
normal forms, including 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF.

Understanding and applying normalization correctly is essential for creating efficient and
reliable relational databases. While 1NF, 2NF, and 3NF cover most practical needs, familiarity
with higher forms ensures we are well-prepared for complex designs.

Some Basic Questions in Normalization

1. What is the difference between 2NF and 3NF?

▪ 2NF removes partial dependencies, while 3NF removes transitive dependencies.

▪ In 2NF, every non-key attribute must depend on the entire primary key. In 3NF, non-key
attributes must depend on the primary key only and not on other non-key attributes.

2. What is the primary benefit of 3NF?

The primary benefit of 3NF is improved data integrity by removing transitive dependencies, which
makes the database easier to maintain and reduces redundancy.

3. What are the limitations of 2NF?

2NF does not address transitive dependencies, which can still lead to redundant data and
anomalies. That’s why moving to 3NF is often necessary.

13
4. What are the rules for normalization?

▪ Remove repeating groups (1NF)

▪ Eliminate partial dependencies (2NF)
▪ Eliminate transitive dependencies (3NF)
▪ Use candidate keys for all dependencies (BCNF)
▪ Eliminate multi-valued and join dependencies (4NF, 5NF)

5. What’s the difference between 3NF and BCNF?

While both aim to eliminate redundancy, BCNF is stricter. A table in 3NF may still violate BCNF
if non-prime attributes depend on overlapping candidate keys.

6. What are the 3 objectives of normalization?

▪ Minimize redundancy
▪ Improve data integrity
▪ Facilitate efficient data management and updates

7. What are anomalies?

Problems that occur due to poor database design:

▪ Insert anomaly
▪ Update anomaly
▪ Delete anomaly

8. What is Transitive Dependency?

A transitive dependency occurs when:

A non-key attribute depends on another non-key attribute, instead of depending directly on the
primary key.

In simple words:

If

A → B and B → C

then A → C (transitive dependency)

14
 Basic Idea

Primary Key → non-key attribute (correct)

Non-key → non-key (transitive dependency → problem)

15
 Why Transitive Dependency is Bad

Causes Problems:

Update Anomaly

Changing department name requires multiple updates

Insert Anomaly

Can't add department without a student

Delete Anomaly

Deleting last student removes department info

16
 Summary
Transitive dependency = indirect dependency
Happens when:
▪ Non-key → Non-key
▪ Must be removed to achieve 3NF
▪ Solution = split into separate tables

17