Second Normal Form (2NF) in SQL - Step-by-Step Execution

Concept Flow - Second Normal Form (2NF)

Start with 1NF table

↓

Check for composite primary key?

Yes↓

Identify partial dependencies

Yes No↓

Remove partial dependencies

↓

Table in 2NF

↓

End

Start with a table in 1NF, check if it has a composite key. If yes, find and remove partial dependencies to achieve 2NF.

Execution Sample

SQL

CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  ProductName VARCHAR(50),
  Quantity INT,
  PRIMARY KEY (OrderID, ProductID)
);

This table has a composite primary key (OrderID, ProductID) but ProductName depends only on ProductID, showing a partial dependency.

Execution Table

Step	Action	Check/Condition	Result/Next Step
1	Start with Orders table	Table in 1NF with composite key (OrderID, ProductID)	Proceed to check dependencies
2	Check if ProductName depends on whole key	ProductName depends only on ProductID (partial dependency)	Partial dependency found
3	Remove partial dependency by creating Products table	Move ProductID and ProductName to Products table	Orders table now has OrderID, ProductID, Quantity
4	Check Orders table for partial dependencies	No partial dependencies remain	Orders table is now in 2NF
5	Check Products table	Primary key ProductID, no partial dependencies	Products table is in 2NF
6	End	Both tables in 2NF	Normalization complete

💡 No partial dependencies remain; all non-key attributes depend on the whole primary key.

Variable Tracker

Table	Initial Columns	After Step 3	Final Columns
Orders	OrderID, ProductID, ProductName, Quantity	OrderID, ProductID, Quantity	OrderID, ProductID, Quantity
Products	N/A	ProductID, ProductName	ProductID, ProductName

Key Moments - 2 Insights

Why do we need to remove partial dependencies?

Partial dependencies cause redundancy and anomalies. Removing them ensures each non-key attribute depends on the whole key, as shown in execution_table step 2 and 3.

What if the primary key is not composite?

If the primary key is a single column, 2NF is automatically satisfied because partial dependency cannot exist, as implied in execution_table step 2.

Visual Quiz - 3 Questions

Look at the execution table, at which step is the partial dependency identified?

A Step 1

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Check/Condition' column for 'partial dependency found' in execution_table.

According to variable_tracker, which table contains ProductName after normalization?

A Orders

B Products

C Both Orders and Products

D Neither table

💡 Hint

Look at the 'Final Columns' for each table in variable_tracker.

If the Orders table had only OrderID as primary key, what would change in the execution flow?

A No partial dependencies could exist, so 2NF is already satisfied

B Partial dependencies would still need removal

C Composite key would be created automatically

D ProductName would depend on OrderID only

💡 Hint

Refer to key_moments about primary key composition and 2NF.

Concept Snapshot

Second Normal Form (2NF):
- Table must be in 1NF first.
- Remove partial dependencies on composite keys.
- Every non-key attribute depends on the whole primary key.
- If primary key is single column, 2NF is automatic.
- Helps reduce redundancy and update anomalies.

Full Transcript

Second Normal Form (2NF) is a database normalization step that builds on First Normal Form (1NF). It applies when a table has a composite primary key, meaning the key is made of more than one column. In such cases, some columns might depend only on part of the key, called partial dependencies. These cause data duplication and problems when updating. To fix this, we split the table to remove partial dependencies, ensuring every non-key column depends on the entire key. If the primary key is a single column, 2NF is already satisfied. The example Orders table has a composite key (OrderID, ProductID) but ProductName depends only on ProductID. We move ProductName to a new Products table. After this, both tables are in 2NF, reducing redundancy and improving data integrity.