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.

Practice

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

easy

A. Every non-key column depends only on the primary key.

B. Tables must have no more than three columns.

C. All columns must be unique across the table.

D. Data must be stored in a single table without duplicates.

Solution

1. Step 1: Understand 3NF definition

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

2. Step 2: Evaluate options

   Every non-key column depends only on the primary key. correctly states this. The other options are incorrect descriptions of 3NF.

3. Final Answer:

   Every non-key column depends only on the primary key. -> Option A

4. Quick Check:

   3NF = Non-key columns depend only on primary key [OK]

Hint: 3NF means no column depends on non-key columns [OK]

Common Mistakes:

• Confusing 3NF with limiting number of columns
• Thinking all columns must be unique
• Believing 3NF means one big table

2. Which SQL statement correctly creates a table that follows Third Normal Form (3NF)?

easy

A. CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, CustomerName VARCHAR(50));

B. CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerName VARCHAR(50), CustomerAddress VARCHAR(100));

C. CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT); CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50));

D. CREATE TABLE Orders (OrderID INT, CustomerName VARCHAR(50));

Solution

1. Step 1: Identify 3NF compliance in table design

   3NF requires separating data so non-key columns do not depend on other non-key columns. CustomerName depends on CustomerID, so it should be in a separate table.

2. Step 2: Analyze options

   CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT); CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50)); splits Orders and Customers, following 3NF. CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, CustomerName VARCHAR(50)); stores CustomerName in Orders, violating 3NF. The other options miss primary keys or mix data improperly.

3. Final Answer:

   CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT); CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50)); -> Option C

4. Quick Check:

   Separate related data into tables = 3NF [OK]

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

Common Mistakes:

• Putting dependent data in the same table
• Missing primary keys
• Ignoring data duplication issues

3. Given the table below, which is NOT in Third Normal Form (3NF)?

EmployeeID | EmployeeName | Department | DepartmentHead

What is the main reason this table violates 3NF?

medium

A. DepartmentHead depends on Department, not EmployeeID.

B. EmployeeName depends on EmployeeID.

C. EmployeeID is not unique.

D. Department is a primary key.

Solution

1. Step 1: Identify dependencies in the table

   DepartmentHead depends on Department, which is a non-key column, not on the primary key EmployeeID.

2. Step 2: Check 3NF rules

   3NF requires no non-key column depends on another non-key column. Here, DepartmentHead depends on Department, violating 3NF.

3. Final Answer:

   DepartmentHead depends on Department, not EmployeeID. -> Option A

4. Quick Check:

   Non-key depends on non-key = violates 3NF [OK]

Hint: Check if non-key columns depend on other non-key columns [OK]

Common Mistakes:

• Thinking EmployeeName dependency breaks 3NF
• Assuming EmployeeID is not unique
• Confusing primary key with foreign key

4. You have a table:

StudentID | StudentName | Advisor | AdvisorOffice

This table violates 3NF. What is the best way to fix it?

medium

A. Remove AdvisorOffice column.

B. Create a separate Advisor table with Advisor and AdvisorOffice.

C. Make StudentName the primary key.

D. Add AdvisorOffice to StudentID as a foreign key.

Solution

1. Step 1: Identify the 3NF violation

   AdvisorOffice depends on Advisor, a non-key column, causing a transitive dependency violating 3NF.

2. Step 2: Fix by separating dependent data

   Creating a separate Advisor table with Advisor and AdvisorOffice removes the transitive dependency.

3. Final Answer:

   Create a separate Advisor table with Advisor and AdvisorOffice. -> Option B

4. Quick Check:

   Separate dependent data into own table to fix 3NF [OK]

Hint: Fix 3NF by splitting tables for transitive dependencies [OK]

Common Mistakes:

• Just removing columns without restructuring
• Changing primary keys incorrectly
• Adding foreign keys without normalization

5. A company has a table:

InvoiceID | ProductID | ProductName | SupplierID | SupplierName

To convert this table into Third Normal Form (3NF), which of the following is the best approach?

hard

A. Remove SupplierName column from the table.

B. Add ProductName and SupplierName as primary keys.

C. Keep all columns in one table but add unique constraints.

D. Create separate tables for Products and Suppliers, linking them by IDs.

Solution

1. Step 1: Identify transitive dependencies

   ProductName depends on ProductID, and SupplierName depends on SupplierID, both non-key columns, causing 3NF violation.

2. Step 2: Normalize by splitting tables

   Creating separate Product and Supplier tables linked by IDs removes these dependencies and achieves 3NF.

3. Final Answer:

   Create separate tables for Products and Suppliers, linking them by IDs. -> Option D

4. Quick Check:

   Split related entities into tables to achieve 3NF [OK]

Hint: Split entities into tables linked by keys for 3NF [OK]

Common Mistakes:

• Trying to keep all data in one table
• Using names as primary keys
• Removing columns without restructuring