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.