Third Normal Form (3NF) in SQL - Step-by-Step Execution

Concept Flow - Third Normal Form (3NF)

Start with a Table

↓

Check 1NF: Atomic values?

Yes↓

Check 2NF: No partial dependencies?

Yes↓

Check 3NF: No transitive dependencies?

Yes↓

Result: Tables in 3NF

Start with a table, ensure it meets 1NF and 2NF, then remove transitive dependencies to achieve 3NF.

Execution Sample

SQL

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  CustomerName VARCHAR(100),
  OrderDate DATE
);

This table has a transitive dependency: CustomerName depends on CustomerID, not directly on OrderID.

Execution Table

Step	Check	Condition	Result	Action
1	Check 1NF	Are all values atomic?	Yes	Proceed to 2NF check
2	Check 2NF	Are there partial dependencies?	No	Proceed to 3NF check
3	Check 3NF	Are there transitive dependencies?	Yes	Decompose table
4	Decompose	Split Orders into Orders and Customers	Orders(OrderID, CustomerID, OrderDate), Customers(CustomerID, CustomerName)	Tables now in 3NF
5	Final Check	Are all tables in 3NF?	Yes	Normalization complete

💡 All tables satisfy 3NF after decomposition, no transitive dependencies remain.

Variable Tracker

Table	Initial Columns	After Decomposition	Final Columns
Orders	OrderID, CustomerID, CustomerName, OrderDate	Orders: OrderID, CustomerID, OrderDate Customers: CustomerID, CustomerName	Orders: OrderID, CustomerID, OrderDate Customers: CustomerID, CustomerName

Key Moments - 2 Insights

Why do we remove CustomerName from the Orders table?

Because CustomerName depends on CustomerID, not directly on OrderID, causing a transitive dependency (see execution_table step 3). Removing it breaks this dependency.

What does it mean for a table to have no transitive dependencies?

It means non-key columns depend only on the primary key, not on other non-key columns (refer to execution_table step 3 and 4).

Visual Quiz - 3 Questions

Look at the execution_table, at which step do we find transitive dependencies?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Check 3NF' row in execution_table where transitive dependencies are identified.

According to variable_tracker, what columns does the Customers table have after decomposition?

A CustomerID, CustomerName

B OrderID, CustomerID, CustomerName

C OrderID, OrderDate

D CustomerName, OrderDate

💡 Hint

Look at the 'After Decomposition' and 'Final Columns' rows for Customers in variable_tracker.

If CustomerName stayed in Orders, what problem would remain?

A Partial dependency

B Atomicity violation

C Transitive dependency

D Primary key missing

💡 Hint

Refer to execution_table step 3 where transitive dependencies are detected.

Concept Snapshot

Third Normal Form (3NF):
- Table must be in 2NF first.
- Remove transitive dependencies: no non-key column depends on another non-key column.
- Decompose tables to separate related data.
- Result: each non-key attribute depends only on the primary key.
- Ensures data integrity and reduces redundancy.

Full Transcript

Third Normal Form (3NF) is a database design principle to organize tables so that every non-key column depends only on the primary key. We start by ensuring the table is in First Normal Form (1NF) with atomic values, then Second Normal Form (2NF) with no partial dependencies. Next, we check for transitive dependencies, where a non-key column depends on another non-key column. If found, we split the table into smaller tables to remove these dependencies. For example, in an Orders table containing CustomerName, since CustomerName depends on CustomerID and not directly on OrderID, we separate Customers into its own table. This process reduces redundancy and improves data integrity. The execution table shows each step, and the variable tracker shows how columns move between tables during decomposition.