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 42

Normalizing to 3NF

Recommended resources

Welcome back. In our previous lessons, you've learned the rules for First, Second, and Third Normal Forms (1NF, 2NF, and 3NF) individually. Most recently, we focused on the final step: converting a 2NF table into 3NF by removing transitive dependencies.

Now, it's time to put all the pieces together. This lesson synthesizes the entire normalization process into a single, step-by-step workflow. Your goal is to take a single, complex, unnormalized table and, given its functional dependencies, systematically transform it into a set of well-structured 3NF tables. This skill is the cornerstone of good database design and will be essential for your upcoming project.

1. The Normalization Workflow: A Visual Map

Before diving into an example, let's visualize the entire process. Normalization is a sequential journey where you solve one type of problem at each stage.

Normalization Process Flowchart (UNF to 3NF)
This flowchart shows the progression from an unnormalized table to 3NF. First, we remove repeating groups to achieve 1NF. Then, we check for a composite primary key and remove any partial dependencies to reach 2NF. Finally, we remove transitive dependencies to arrive at 3NF.

As the diagram shows, the process involves three key transformations:

  1. Unnormalized to 1NF: Eliminate any repeating groups or multi-valued columns.
  2. 1NF to 2NF: Identify and remove any partial dependencies, where a non-key attribute depends on only part of a composite primary key.
  3. 2NF to 3NF: Identify and remove any transitive dependencies, where a non-key attribute depends on another non-key attribute.

2. A Complete Normalization Walkthrough

The best way to understand this workflow is to see it in action from start to finish. The following video provides a detailed walkthrough of normalizing a database for a motel reservation system. It starts with a single, messy collection of data and methodically applies the rules to achieve a clean 3NF design.

Easy explanation of Normalization Relational Database Design for Beginners - 1NF, 2NF, 3NF

This video from OdinTree provides a comprehensive, step-by-step example of normalization.

Watch the video as it progresses through the normalization stages. Focus on how each problem is identified and then resolved by creating new tables. Unnormalized to 1NF (watch this section): Observe how the initial table is created from business requirements. Pay close attention to how multi-valued attributes (like a guest booking multiple rooms) are handled by creating new rows and how a composite primary key (Room Number, Reservation Number) is identified. 1NF to 2NF (watch this section): The video identifies attributes that depend on only part of the composite primary key (partial dependencies). For example, Room Type depends only on Room Number. See how these are split into new tables. 2NF to 3NF (watch this section): Finally, the video finds transitive dependencies, where one non-key attribute determines another (e.g., Card Number determines Card Type). Watch how these are moved into their own tables to achieve the final 3NF design.

By the end of this process, the single unnormalized table has been decomposed into five clean, related tables: Room, RoomType, Reservation, Guest, and Card. This new structure eliminates the data redundancy and anomalies present in the original design.

3. Another Perspective: A Text-Based Walkthrough

To reinforce these concepts, you can review another complete example in a text format. This resource walks through the normalization of a Student_Grade_Report, applying the same principles.

Chapter 12 Normalization

This chapter from an open-source textbook demonstrates the full normalization process with a different scenario.

Read through the chapter, focusing on the three main process sections: Process for 1NF: See how the repeating group (course information) is removed from the initial table. Process for 2NF: Notice how attributes that are not fully dependent on the composite key are split into a new table. Process for 3NF: Observe how the transitive dependency (instructor details depending on InstructorNo, not the course) is resolved.

Seeing the same logic applied to a different problem will help solidify your understanding of the universal nature of these rules.

4. Practice Exercise: Your Turn

Now it's time to apply what you've learned. You are given an unnormalized table, ORDER_DATA, which contains information about customer orders for various products.

Unnormalized Table Structure:
ORDER_DATA contains the following columns:
OrderID, OrderDate, CustID, CustName, CustEmail, ProdID, ProdName, ProdCategory, CatDescription, UnitPrice, Quantity

Given Functional Dependencies (FDs):

  • OrderID → OrderDate, CustID
  • CustID → CustName, CustEmail
  • ProdID → ProdName, ProdCategory, UnitPrice
  • ProdCategory → CatDescription

The primary key of the main linking table (the order line items) will be a composite of OrderID and ProdID. The Quantity attribute depends on this composite key.

Your Task:
Normalize the ORDER_DATA table to 3NF. List the final set of tables, their attributes, and identify the primary key (PK) for each table.

Click to reveal the solution step-by-step

Step 1: Convert to 1NF

The initial structure has no repeating groups specified, so we can consider it already in 1NF. We can represent all the data in one large table with the composite primary key (OrderID, ProdID).

  • ORDER_LINE(OrderID, ProdID, OrderDate, CustID, CustName, CustEmail, ProdName, ProdCategory, CatDescription, UnitPrice, Quantity)
    • PK: (OrderID, ProdID)

Step 2: Convert to 2NF (Remove Partial Dependencies)

We look for attributes that depend on only part of the composite key (OrderID, ProdID).

  • Depends on OrderID only: OrderDate, CustID (and transitively, CustName, CustEmail).
  • Depends on ProdID only: ProdName, ProdCategory, UnitPrice (and transitively, CatDescription).
  • Depends on the full key (OrderID, ProdID): Quantity.

We create new tables for these partial dependencies:

  1. ORDER Table:
    • ORDERS(OrderID, OrderDate, CustID)
    • PK: OrderID
  2. PRODUCT Table:
    • PRODUCTS(ProdID, ProdName, ProdCategory, UnitPrice)
    • PK: ProdID
  3. Original Table (now just the linking table):
    • ORDER_LINE(OrderID, ProdID, Quantity)
    • PK: (OrderID, ProdID)

We also have the CustID → CustName, CustEmail and ProdCategory → CatDescription dependencies to deal with, but those are transitive, not partial. We address them in the next step.

Step 3: Convert to 3NF (Remove Transitive Dependencies)

Now, we examine our 2NF tables for transitive dependencies.

  • In the ORDERS table, we have OrderID → CustID and CustID → CustName, CustEmail. This is a transitive dependency. We need to separate customer information.
  • In the PRODUCTS table, we have ProdID → ProdCategory and ProdCategory → CatDescription. This is also a transitive dependency. We need to separate category information.

We perform the final decomposition:

  1. CUSTOMER Table (from ORDERS):
    • CUSTOMER(CustID, CustName, CustEmail)
    • PK: CustID
  2. CATEGORY Table (from PRODUCTS):
    • CATEGORY(ProdCategory, CatDescription)
    • PK: ProdCategory

Final 3NF Tables:

After removing the transitively dependent attributes from their original tables, we are left with the following five 3NF tables:

  • ORDERS(OrderID, OrderDate, CustID)
    • PK: OrderID
    • FK: CustID (references CUSTOMER)
  • CUSTOMER(CustID, CustName, CustEmail)
    • PK: CustID
  • PRODUCTS(ProdID, ProdName, ProdCategory, UnitPrice)
    • PK: ProdID
    • FK: ProdCategory (references CATEGORY)
  • CATEGORY(ProdCategory, CatDescription)
    • PK: ProdCategory
  • ORDER_LINE(OrderID, ProdID, Quantity)
    • PK: (OrderID, ProdID)
    • FK: OrderID (references ORDERS), ProdID (references PRODUCTS)

Conclusion

In this lesson, you consolidated your knowledge of normalization by learning how to apply the rules for 1NF, 2NF, and 3NF in a single, unified process. Starting from an unnormalized table, you can now methodically decompose it to create a set of clean, efficient, and robust 3NF tables.

Key Takeaways:

  • Normalization is a sequential process: first achieve 1NF, then 2NF, then 3NF.
  • 1NF is achieved by ensuring all values are atomic and removing repeating groups.
  • 2NF is achieved by removing partial dependencies from tables with composite primary keys.
  • 3NF is achieved by removing transitive dependencies, where a non-key attribute depends on another non-key attribute.
  • The final result is a set of smaller, interconnected tables where every non-key attribute depends on "the key, the whole key, and nothing but the key."

This lesson concludes our deep dive into the theory of normalization. In the next lesson, we will link this back to our earlier work on Entity-Relationship Diagrams. You will learn how to take a final set of 3NF tables and construct a complete ERD that visually represents the database schema, preparing you to tackle your final project.