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

Concept Flow - Third Normal Form (3NF)

Start with a Table

↓

Check 1NF: Atomic values?

Yes↓

Check 2NF: No partial dependency?

Yes↓

Check 3NF: No transitive dependency?

Yes↓

Table is in 3NF

No↓

Remove transitive dependencies

↓

Result: Table in 3NF

The flow shows starting from a table, ensuring it meets 1NF and 2NF, then checking for transitive dependencies to achieve 3NF.

Execution Sample

DBMS Theory

Table: Student
Columns: StudentID, Name, DeptID, DeptName

Check 3NF: DeptName depends on DeptID, not StudentID

This example shows a table where DeptName depends on DeptID, a non-key attribute, causing transitive dependency violating 3NF.

Analysis Table

Step	Check	Condition	Result	Action
1	Check 1NF	Are all values atomic?	Yes	Proceed to 2NF
2	Check 2NF	Any partial dependency on part of primary key?	No	Proceed to 3NF
3	Check 3NF	Any transitive dependency (non-key depends on non-key)?	Yes	Table not in 3NF
4	Remove transitive dependency	Separate DeptName into Dept table	Now DeptName depends only on DeptID	Table is in 3NF

💡 After removing transitive dependencies, the table meets 3NF requirements.

State Tracker

Variable	Start	After Step 3	After Step 4
Table Structure	StudentID, Name, DeptID, DeptName	Same (transitive dependency exists)	StudentID, Name, DeptID; DeptID, DeptName (separated)
Dependency	DeptName depends on DeptID (non-key)	Detected transitive dependency	Removed by splitting tables

Key Insights - 2 Insights

Why is DeptName depending on DeptID a problem for 3NF?

Because DeptName depends on a non-key attribute (DeptID), it creates a transitive dependency, which violates 3NF as shown in execution_table step 3.

What does removing transitive dependency mean in practice?

It means splitting the table so that non-key attributes depend only on the primary key, as shown in execution_table step 4 where DeptName is moved to a separate table.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the condition checked?

A Is there any partial dependency?

B Is there any transitive dependency?

C Are all values atomic?

D Is the table empty?

💡 Hint

Refer to the 'Check 3NF' row in execution_table which checks for transitive dependency.

According to variable_tracker, what changes after step 4?

A Table structure splits to remove transitive dependency

B Table adds more columns

C Primary key changes to DeptName

D No change in dependencies

💡 Hint

Look at the 'Table Structure' row in variable_tracker after step 4.

If DeptName depended directly on StudentID, what would happen in step 3?

A Transitive dependency would still exist

B Partial dependency would be detected

C There would be no transitive dependency

D 1NF would fail

💡 Hint

Transitive dependency means a non-key depends on another non-key, so direct dependency on primary key avoids it.

Concept Snapshot

Third Normal Form (3NF):
- Table must be in 2NF first.
- No transitive dependencies allowed.
- Non-key attributes depend only on primary key.
- Remove transitive dependencies by splitting tables.
- Ensures data consistency and reduces redundancy.

Full Transcript

Third Normal Form (3NF) is a database design rule that ensures tables have no transitive dependencies. Starting from a table, we first check if it meets First Normal Form (1NF) by having atomic values, then Second Normal Form (2NF) by having no partial dependencies. Next, we check for transitive dependencies where a non-key attribute depends on another non-key attribute. If such dependencies exist, the table is not in 3NF. To fix this, we split the table to remove transitive dependencies, ensuring all non-key attributes depend only on the primary key. This process improves data integrity and reduces redundancy.