Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 30

Data Normalization & Anomaly Prevention

Recommended resources

Welcome back! In our previous lessons, we focused on the high-level structure of a database by creating and verifying Entity-Relationship Diagrams (ERDs). We learned how to model entities and the relationships between them. Now, we're going to zoom in and look at how we can best organize the data within those entities.

This lesson introduces normalization, a fundamental concept in database design. You will discover why simply dumping all related data into a single large table is a bad idea. We will explore the problems this causes, namely data redundancy and the resulting "anomalies" that can corrupt your data. Understanding why normalization is necessary is the first critical step before learning how to do it.

By the end of this lesson, you will be able to explain the purpose of normalization and identify the specific problems—insertion, update, and deletion anomalies—that are caused by data redundancy.

1. Data Redundancy: The Root of the Problem

When designing tables, a common first instinct is to put all related information together. For example, if you have students and the departments they belong to, you might create a single table with columns for student ID, student name, department name, department building, and department head.

While this seems simple, it leads to a significant issue called data redundancy—the unnecessary repetition of data.

Look at the table in the image below. Can you spot which data is being repeated?

Illustration of Data Redundancy in an Employee Table
This table shows employee and department information. The repetition of department details for each employee in that department is an example of data redundancy.

As you can see, the details for the "Sales" and "Accounts" departments are stored multiple times, once for every employee in that department. Storing the same information over and over not only wastes storage space but, more critically, it creates a high risk of data integrity problems. These problems are known as anomalies.

2. The Three Anomalies

Data redundancy leads to three main types of data management problems, or anomalies:

  1. Insertion Anomaly
  2. Update (or Modification) Anomaly
  3. Deletion Anomaly

These anomalies make it difficult to maintain the accuracy and consistency of your data. The following video provides an excellent walkthrough of these three problems using a clear, practical example.

Lec 9: Normalization in DBMS | Need of Normalization | DBMS Tutorials

This video from Jenny's Lectures CS IT clearly demonstrates how a single, large table design leads to data anomalies.

Please watch the following segments: First, watch the section that introduces the example table and highlights the redundancy problem. Next, watch the explanations for each of the three anomalies: Insertion Anomaly Update Anomaly Deletion Anomaly As you watch, focus on understanding how the structure of the table prevents simple, logical data operations from being performed correctly.

A Closer Look at the Anomalies

Let's recap the concepts from the video with concise definitions. Imagine a single table containing both EMPLOYEE and PROJECT data.

  • Insertion Anomaly: This prevents you from adding certain data unless you also add some other, unrelated data.

    • Problem: You cannot add a new project to the table if it has no employees assigned to it yet. This is because every row requires an employee ID (which is likely part of the primary key), and you can't leave it null. The database structure itself prevents you from recording a valid piece of business information (a new project).

  • Update Anomaly: This occurs when an update to a single piece of information requires you to change multiple rows. If you miss even one, the data becomes inconsistent.

    • Problem: If a project's name changes, you must find every single employee assigned to that project and update the project name in each of their rows. If you update it for some employees but not others, the database will contain conflicting information about the same project.

  • Deletion Anomaly: This is the unintentional loss of data that occurs when you delete a different piece of data.

    • Problem: If an employee is the only person assigned to a particular project and that employee leaves the company, deleting their record from the table will also delete the only record of that project's existence. You wanted to delete an employee, but you accidentally deleted a project.
Normalization and Database Anomalies
A concise summary defining normalization and the three data anomalies it is designed to prevent.

3. Normalization: The Solution

Now that we've seen the problems, we can define the solution. Normalization is the process of organizing the attributes and tables in a relational database to minimize data redundancy and, therefore, eliminate the insertion, update, and deletion anomalies.

The core technique of normalization is decomposition. We break down large, problematic tables into smaller, well-structured ones. Each table is designed to describe a single entity (like STUDENT or DEPARTMENT), and the tables are linked together using foreign keys.

Let's see how this solves the problems we identified.

Lec 9: Normalization in DBMS | Need of Normalization | DBMS Tutorials

This segment of the same video shows how decomposing the table resolves the anomalies.

Please watch the section explaining the solution. Notice how splitting the original table into a STUDENT table and a DEPARTMENT table immediately removes the data redundancy.

By splitting the table:

  • Department information (like building and room number) is now stored only once in the DEPARTMENT table.
  • Insertion Anomaly is solved: We can now add a new department to the DEPARTMENT table without needing to assign any students to it.
  • Update Anomaly is solved: If a department's room number changes, we only need to update it in one single row in the DEPARTMENT table.
  • Deletion Anomaly is solved: We can delete a student from the STUDENT table without losing the information about their department, which remains safely in the DEPARTMENT table.

To reinforce these ideas, the following reading provides a clear summary of the purpose and features of normalization.

Introduction to Database Normalization

This article from GeeksforGeeks succinctly lists the objectives and benefits of normalization.

Please read the sections titled "Need of Normalization" and "Features of Database Normalization". These sections summarize the goals and advantages of applying this process to your database design.

Conclusion

In this lesson, you've learned about the fundamental "why" behind database normalization. You saw that attempting to store information about multiple distinct entities (like students and departments) in a single table leads to data redundancy. This redundancy, in turn, causes insertion, update, and deletion anomalies that seriously compromise the integrity and reliability of your database.

Key Takeaways:

  • Data Redundancy is the unnecessary repetition of data in a database, which wastes space and creates data management problems.
  • Redundancy leads to three types of anomalies:
    • Insertion Anomaly: Inability to add new data because other data is missing.
    • Update Anomaly: A simple change requires multiple updates, creating a risk of inconsistency.
    • Deletion Anomaly: Deleting one record unintentionally erases unrelated data.
  • Normalization is the process of decomposing large tables into smaller, well-structured tables to minimize redundancy and eliminate these anomalies.

You now understand the problems that normalization solves. In our next lesson, we will begin to explore how to normalize by learning about functional dependencies and the rules for achieving the First Normal Form (1NF).