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

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Purpose of 3NF

Which of the following best describes the main goal of the Third Normal Form (3NF) in database design?

A To eliminate transitive dependencies and ensure that non-key attributes depend only on the primary key.

B To remove duplicate rows from a table by enforcing unique constraints.

C To combine multiple tables into one to reduce the number of joins needed.

D To allow null values in foreign key columns to enable optional relationships.

💡 Hint

Think about what kind of dependencies 3NF tries to remove to avoid data anomalies.

3NF requires that all non-key columns depend only on the primary key, removing transitive dependencies that can cause redundancy and update anomalies.

❓ query_result

intermediate

Identifying Transitive Dependency

Consider the table below:

EmployeeID | EmployeeName | DepartmentID | DepartmentName
-----------------------------------------------------
1          | Alice        | 10           | Sales
2          | Bob          | 20           | Marketing
3          | Carol        | 10           | Sales

Which column(s) cause a transitive dependency violating 3NF?

A DepartmentID depends on EmployeeName, causing transitive dependency.

B DepartmentName depends on DepartmentID, which is not a key, causing transitive dependency.

C EmployeeID depends on DepartmentName, causing transitive dependency.

D EmployeeName depends on EmployeeID, which is a primary key, so no transitive dependency.

💡 Hint

Look for columns that depend on other non-key columns instead of the primary key.

DepartmentName depends on DepartmentID, which is not the primary key. This is a transitive dependency violating 3NF.

❓ schema

advanced

Decomposing a Table into 3NF

You have a table:

Orders(OrderID, CustomerID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity)

Which of the following decompositions correctly achieves 3NF?

A Orders(OrderID, CustomerID, ProductID), Customers(CustomerName, CustomerAddress), Products(ProductName, Quantity)

B Orders(OrderID, CustomerName, CustomerAddress, ProductName, Quantity), Customers(CustomerID), Products(ProductID)

C Orders(OrderID, CustomerID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity)

D Orders(OrderID, CustomerID, ProductID, Quantity), Customers(CustomerID, CustomerName, CustomerAddress), Products(ProductID, ProductName)

💡 Hint

3NF requires removing transitive dependencies by separating entities into their own tables.

Option D separates customers and products into their own tables, removing transitive dependencies and ensuring all non-key attributes depend only on the primary key.

❓ optimization

advanced

Optimizing Queries on 3NF Tables

Given a database normalized to 3NF with separate tables for Customers, Orders, and Products, which query approach is most efficient to get the total quantity ordered by each customer?

A Join Customers and Products tables directly without Orders table.

B Query the Orders table only and sum quantities without joining other tables.

C Use JOINs between Customers and Orders tables with GROUP BY CustomerID.

D Use nested subqueries to fetch customer names for each order separately.

💡 Hint

Think about which tables contain the necessary data and how to combine them efficiently.

JOINs between Customers and Orders allow grouping by customer to sum quantities. Products table is not needed for quantity sums but may be joined if product info is required.

🔧 Debug

expert

Identifying 3NF Violation in SQL Table Definition

Examine the following SQL table definition:

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  EmployeeName VARCHAR(100),
  DepartmentID INT,
  DepartmentName VARCHAR(100),
  FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

What is the main problem violating 3NF in this design?

A DepartmentName is stored in Employee table causing transitive dependency on DepartmentID.

B EmployeeID should not be the primary key because it is not unique.

C Foreign key constraint is missing on DepartmentID.

D EmployeeName depends on DepartmentName causing circular dependency.

💡 Hint

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

DepartmentName depends on DepartmentID, which is not the primary key of Employee table, causing a transitive dependency violating 3NF.