Third Normal Form (3NF) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Definition of Third Normal Form (3NF)

Which of the following best describes the condition for a database table to be in Third Normal Form (3NF)?

A The table is free of any functional dependencies.

B Every attribute is dependent on the primary key, but transitive dependencies are allowed.

C Every non-key attribute is fully functionally dependent on the primary key, and there are no transitive dependencies.

D The table has no repeating groups or arrays, but transitive dependencies may exist.

💡 Hint

Think about what transitive dependency means and how 3NF handles it.

Third Normal Form requires that all non-key attributes depend only on the primary key and not on other non-key attributes. This means no transitive dependencies are allowed.

📋 Factual

intermediate

Identifying Transitive Dependency

In a table with columns StudentID, StudentName, and DepartmentName, where DepartmentName depends on StudentID through StudentName, what kind of dependency is this?

A Transitive dependency

B Full functional dependency

C Partial dependency

D No dependency

💡 Hint

Consider if a non-key attribute depends on another non-key attribute.

Transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key.

🚀 Application

advanced

Applying 3NF to a Table

Given a table with columns OrderID, CustomerID, CustomerAddress, and OrderDate, which of the following changes will bring the table into Third Normal Form (3NF)?

A Add <strong>CustomerName</strong> to the table to remove transitive dependencies.

B Combine <strong>CustomerID</strong> and <strong>CustomerAddress</strong> into a single column.

C Remove <strong>OrderDate</strong> from the table to avoid partial dependencies.

D Split the table into two: one with <strong>OrderID</strong>, <strong>CustomerID</strong>, <strong>OrderDate</strong> and another with <strong>CustomerID</strong>, <strong>CustomerAddress</strong>.

💡 Hint

Think about how to remove transitive dependencies by separating related data.

Splitting the table removes the transitive dependency of CustomerAddress on OrderID through CustomerID, satisfying 3NF.

🔍 Analysis

advanced

Analyzing a Table for 3NF Violations

Consider a table with columns: EmployeeID, EmployeeName, DepartmentID, and DepartmentLocation. Which statement is true regarding its normalization status?

A The table is in 1NF but not in 2NF.

B The table violates 3NF because <strong>DepartmentLocation</strong> depends on <strong>DepartmentID</strong>, not directly on <strong>EmployeeID</strong>.

C The table violates 2NF due to partial dependency.

D The table is in 3NF because all columns depend on <strong>EmployeeID</strong>.

💡 Hint

Check if any non-key attribute depends on another non-key attribute.

DepartmentLocation depends on DepartmentID, which is not the primary key, causing a transitive dependency and violating 3NF.

❓ Reasoning

expert

Determining the Number of Tables After 3NF Decomposition

A table has columns: BookID, Title, AuthorID, AuthorName, and PublisherName. AuthorName depends on AuthorID, and PublisherName depends on BookID. After decomposing this table into Third Normal Form (3NF), how many tables will result?

A 2

B 3

C 1

D 4

💡 Hint

Consider separating tables to remove transitive dependencies for AuthorName.

The table has a transitive dependency: BookID → AuthorID → AuthorName. PublisherName depends directly on BookID. Decompose into two tables: Books (BookID, Title, AuthorID, PublisherName) and Authors (AuthorID, AuthorName), resulting in 2 tables.

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