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

Concept Flow - Second Normal Form (2NF)

Start with 1NF table

↓

Check for composite primary key?

No→Already in 2NF

Yes↓

Check for partial dependencies

↓

Yes

↓

Remove partial [Table is in 2NF

↓

Create new tables

↓

Result: Tables in 2NF

Start with a table in 1NF, check if it has a composite key. If yes, find and remove partial dependencies by splitting tables, resulting in 2NF.

Execution Sample

DBMS Theory

Table: Orders
PK: (OrderID, ProductID)
Attributes: OrderDate, ProductName, Quantity

Check partial dependencies

This table has a composite key and attributes; we check if any attribute depends only on part of the key.

Analysis Table

Step	Action	Check	Result	Next Step
1	Start with Orders table	Is table in 1NF?	Yes	Check composite key
2	Check primary key	Is key composite?	Yes (OrderID, ProductID)	Check partial dependencies
3	Check OrderDate dependency	Depends on full key?	Depends only on OrderID (partial)	Partial dependency found
4	Check ProductName dependency	Depends on full key?	Depends only on ProductID (partial)	Partial dependency found
5	Remove partial dependencies	Split table into Orders and Products	Orders(OrderID, OrderDate), Products(ProductID, ProductName)	Tables now in 2NF
6	Final check	Are there partial dependencies?	No	Stop

💡 No partial dependencies remain; tables are in Second Normal Form.

State Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
Table	Orders(OrderID, ProductID, OrderDate, ProductName, Quantity)	Partial dependency on OrderID found	Partial dependency on ProductID found	Split into Orders and Products tables	Orders and Products tables in 2NF

Key Insights - 3 Insights

Why do we check if the primary key is composite?

Because Second Normal Form only applies when the primary key has more than one column, as partial dependencies can only exist then (see execution_table step 2).

What is a partial dependency?

A partial dependency is when a non-key attribute depends on only part of a composite primary key, not the whole key (see execution_table steps 3 and 4).

Why do we split the table to remove partial dependencies?

Splitting removes attributes that depend on part of the key into separate tables, ensuring all attributes depend on the full key, achieving 2NF (see execution_table step 5).

Visual Quiz - 3 Questions

Look at the execution table, at which step do we find the first partial dependency?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Result' column for 'Partial dependency found' in the execution_table.

According to the variable tracker, what is the state of the table after step 5?

A Still one table with partial dependencies

B Table removed all attributes

C Split into Orders and Products tables

D Table has no primary key

💡 Hint

Look at the 'After Step 5' column in variable_tracker.

If the primary key was not composite, what would the execution table say at step 2?

A Is key composite? Yes

B Is key composite? No

C Partial dependencies found

D Split tables

💡 Hint

Refer to execution_table step 2 about checking if the key is composite.

Concept Snapshot

Second Normal Form (2NF):
- Applies to tables with composite primary keys.
- Removes partial dependencies (attributes depending on part of the key).
- Achieved by splitting tables to ensure all attributes depend on the full key.
- Ensures better data organization and reduces redundancy.

Full Transcript

Second Normal Form (2NF) is a database design rule that builds on First Normal Form (1NF). It applies only when a table has a composite primary key, meaning the key is made of more than one column. The main goal is to remove partial dependencies, which happen when some columns depend on only part of the key, not the whole key. To fix this, we split the table into smaller tables so that each non-key attribute depends on the entire primary key. This process reduces data duplication and improves organization. The execution steps start by checking if the table is in 1NF, then if the key is composite. If yes, we look for partial dependencies and remove them by creating new tables. When no partial dependencies remain, the tables are in 2NF.