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.

Practice

1. What is the main goal of the Third Normal Form (3NF) in database design?

easy

A. To ensure no non-key column depends on another non-key column

B. To remove all duplicate rows from a table

C. To combine multiple tables into one

D. To allow NULL values in all columns

Solution

1. Step 1: Understand 3NF definition

   3NF requires that every non-key column depends only on the primary key, not on other non-key columns.

2. Step 2: Compare options with 3NF goal

   Only To ensure no non-key column depends on another non-key column correctly states this goal. Other options describe different concepts.

3. Final Answer:

   To ensure no non-key column depends on another non-key column -> Option A

4. Quick Check:

   3NF = No transitive dependency [OK]

Hint: 3NF removes transitive dependencies between non-key columns [OK]

Common Mistakes:

• Confusing 3NF with removing duplicates
• Thinking 3NF merges tables
• Believing 3NF allows NULLs everywhere

2. Which of the following functional dependencies violates the Third Normal Form (3NF)?

easy

A. PrimaryKey -> NonKeyColumn

B. NonKeyColumn1 -> NonKeyColumn2

C. PrimaryKey -> CompositeKey

D. PrimaryKey -> ForeignKey

Solution

1. Step 1: Identify 3NF violation

   3NF disallows non-key columns determining other non-key columns (transitive dependency).

2. Step 2: Analyze each dependency

   NonKeyColumn1 -> NonKeyColumn2 shows a non-key column determining another non-key column, violating 3NF.

3. Final Answer:

   NonKeyColumn1 -> NonKeyColumn2 -> Option B

4. Quick Check:

   Transitive dependency = 3NF violation [OK]

Hint: Watch for non-key to non-key dependencies breaking 3NF [OK]

Common Mistakes:

• Assuming all dependencies from primary key violate 3NF
• Confusing foreign keys with functional dependencies
• Ignoring transitive dependencies

3. Consider a table with columns StudentID (primary key), AdvisorName, and AdvisorOffice. If AdvisorName determines AdvisorOffice, which of the following is true about this table regarding 3NF?

medium

A. The table is in 3NF because StudentID is the primary key

B. The table violates 3NF because AdvisorOffice depends on StudentID

C. The table is in 3NF because AdvisorOffice depends on AdvisorName

D. The table violates 3NF due to transitive dependency

Solution

1. Step 1: Identify dependencies

   StudentID -> AdvisorName and AdvisorName -> AdvisorOffice shows a transitive dependency.

2. Step 2: Check 3NF rules

   3NF disallows non-key columns (AdvisorName) determining other non-key columns (AdvisorOffice).

3. Final Answer:

   The table violates 3NF due to transitive dependency -> Option D

4. Quick Check:

   Transitive dependency breaks 3NF [OK]

Hint: Check if non-key columns determine others to spot 3NF violation [OK]

Common Mistakes:

• Thinking primary key alone ensures 3NF
• Ignoring transitive dependencies
• Confusing direct and transitive dependencies

4. A table has columns OrderID (primary key), CustomerName, and CustomerCity. The dependency CustomerName -> CustomerCity exists. What is the problem with this table in terms of 3NF, and how can it be fixed?

medium

A. Violates 3NF; fix by splitting into two tables separating customer info

B. No problem; table is already in 3NF

C. Violates 3NF; fix by adding CustomerCity to primary key

D. No problem; just rename columns

Solution

1. Step 1: Identify 3NF violation

   CustomerName -> CustomerCity is a transitive dependency violating 3NF.

2. Step 2: Fix violation by normalization

   Split table into Orders and Customers tables to separate customer details.

3. Final Answer:

   Violates 3NF; fix by splitting into two tables separating customer info -> Option A

4. Quick Check:

   Split tables to remove transitive dependencies [OK]

Hint: Split tables to remove non-key to non-key dependencies [OK]

Common Mistakes:

• Adding columns to primary key incorrectly
• Ignoring transitive dependencies
• Thinking renaming fixes normalization

5. You have a table with columns EmployeeID (primary key), DepartmentID, DepartmentName, and ManagerID. DepartmentID determines DepartmentName and ManagerID. How should you redesign the table to achieve Third Normal Form (3NF)?

hard

A. Keep all columns in one table because EmployeeID is primary key

B. Add DepartmentName and ManagerID to EmployeeID primary key

C. Create two tables: one for Employees and one for Departments with DepartmentID as key

D. Remove ManagerID column to fix 3NF

Solution

1. Step 1: Identify transitive dependencies

   DepartmentID -> DepartmentName and ManagerID means non-key columns depend on another non-key column.

2. Step 2: Normalize by splitting tables

   Create separate Department table with DepartmentID as key to remove transitive dependencies.

3. Final Answer:

   Create two tables: one for Employees and one for Departments with DepartmentID as key -> Option C

4. Quick Check:

   Split tables to remove transitive dependencies [OK]

Hint: Separate dependent columns into new table keyed by determinant [OK]

Common Mistakes:

• Keeping all columns in one table ignoring transitive dependency
• Adding columns to primary key unnecessarily
• Removing important columns instead of normalizing