Second Normal Form (2NF) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify partial dependency violation in 2NF

Consider a table Orders with columns: OrderID, ProductID, ProductName, Quantity. The primary key is a composite key of (OrderID, ProductID).

Which column violates the Second Normal Form (2NF) by having a partial dependency?

SQL

CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  ProductName VARCHAR(100),
  Quantity INT,
  PRIMARY KEY (OrderID, ProductID)
);

A ProductName

B Quantity

C OrderID

D ProductID

💡 Hint

Think about which column depends only on part of the composite key.

ProductName depends only on ProductID, not on the full composite key (OrderID, ProductID). This is a partial dependency and violates 2NF.

🧠 Conceptual

intermediate

Understanding 2NF requirements

Which of the following statements correctly describes the requirement for a table to be in Second Normal Form (2NF)?

A It must have a single-column primary key.

B It must have no transitive dependency on any non-key attribute.

C It must have no partial dependency on any candidate key.

D It must have no duplicate rows.

💡 Hint

2NF focuses on dependencies related to composite keys.

2NF requires that no non-key attribute depends on only part of a composite key, i.e., no partial dependency.

❓ schema

advanced

Decompose table to achieve 2NF

Given the table StudentCourses with columns StudentID, CourseID, StudentName, CourseName, and composite primary key (StudentID, CourseID), which decomposition correctly achieves 2NF?

A Create two tables: <code>Students(StudentID, StudentName)</code> and <code>Courses(CourseID, CourseName)</code> plus <code>Enrollments(StudentID, CourseID)</code>

B Create one table with all columns and remove the primary key

C Create two tables: <code>Students(StudentID, StudentName)</code> and <code>Enrollments(StudentID, CourseID, CourseName)</code>

D Create one table with all columns and add a unique constraint on <code>StudentName</code>

💡 Hint

Think about separating attributes that depend only on part of the key.

Separating StudentName and CourseName into their own tables removes partial dependencies, achieving 2NF.

🔧 Debug

advanced

Find the error causing 2NF violation

Given this table definition:

CREATE TABLE Sales (
  SaleID INT,
  ProductID INT,
  ProductDescription VARCHAR(255),
  Quantity INT,
  PRIMARY KEY (SaleID, ProductID)
);

Which problem causes this table to violate 2NF?

A Quantity depends on both SaleID and ProductID.

B ProductDescription depends only on ProductID, a part of the composite key.

C SaleID is not unique.

D Primary key should be a single column.

💡 Hint

Look for columns that depend on only part of the key.

ProductDescription depends only on ProductID, causing partial dependency and violating 2NF.

❓ optimization

expert

Optimize table design to remove partial dependencies

You have a table OrderDetails with columns OrderID, ProductID, ProductPrice, OrderDate, and composite primary key (OrderID, ProductID).

Which design change best removes partial dependencies to achieve 2NF?

A Make ProductPrice part of the primary key

B Keep all columns in one table and add a unique constraint on ProductPrice

C Remove ProductPrice column from the table

D Split into <code>Orders(OrderID, OrderDate)</code> and <code>OrderItems(OrderID, ProductID, ProductPrice)</code>

💡 Hint

Separate attributes that depend only on part of the key into their own table.

OrderDate depends only on OrderID, so splitting tables removes partial dependencies and achieves 2NF.

Practice

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

easy

A. It removes partial dependencies on a part of a composite key.

B. It eliminates all transitive dependencies.

C. It requires that all columns be atomic.

D. It allows duplicate rows in a table.

Solution

1. Step 1: Understand 2NF definition

   2NF requires that no non-key attribute depends on only part of a composite primary key.

2. Step 2: Compare options with 2NF

   'It removes partial dependencies on a part of a composite key.' correctly states this. 'It eliminates all transitive dependencies.' describes 3NF. 'It requires that all columns be atomic.' describes 1NF, and 'It allows duplicate rows in a table.' is incorrect.

3. Final Answer:

   It removes partial dependencies on a part of a composite key. -> Option A

4. Quick Check:

   2NF = Remove partial dependencies [OK]

Hint: 2NF fixes partial dependencies on composite keys [OK]

Common Mistakes:

• Confusing 2NF with 3NF
• Thinking 2NF removes all duplicates
• Believing 2NF requires atomic columns

2. Which SQL table design violates Second Normal Form (2NF)?
CREATE TABLE Orders (OrderID INT, ProductID INT, ProductName VARCHAR(50), Quantity INT, PRIMARY KEY (OrderID, ProductID));

easy

A. OrderID and ProductID together form the primary key.

B. Quantity depends on both OrderID and ProductID.

C. ProductName depends only on ProductID, not the whole key.

D. The table has a composite primary key.

Solution

1. Step 1: Identify dependencies in the table

   ProductName depends only on ProductID, which is part of the composite key (OrderID, ProductID).

2. Step 2: Check 2NF violation

   Since ProductName depends on part of the key, this violates 2NF which forbids partial dependencies.

3. Final Answer:

   ProductName depends only on ProductID, not the whole key. -> Option C

4. Quick Check:

   Partial dependency = 2NF violation [OK]

Hint: Check if non-key columns depend on part of composite key [OK]

Common Mistakes:

• Assuming composite key alone ensures 2NF
• Ignoring partial dependencies
• Confusing primary key with foreign key

3. Given the table below:
StudentCourses(StudentID, CourseID, StudentName, CourseName, Grade)
Which of the following correctly identifies the partial dependencies violating 2NF?

medium

A. Grade depends only on StudentID.

B. StudentName depends only on StudentID, and CourseName depends only on CourseID.

C. StudentName depends on both StudentID and CourseID.

D. CourseName depends on Grade.

Solution

1. Step 1: Understand composite key and dependencies

   The composite key is (StudentID, CourseID). StudentName depends only on StudentID, CourseName only on CourseID.

2. Step 2: Check 2NF compliance

   2NF requires no partial dependencies. StudentName depending only on StudentID and CourseName only on CourseID are partial dependencies on parts of the composite key, violating 2NF.

3. Final Answer:

   StudentName depends only on StudentID, and CourseName depends only on CourseID. -> Option B

4. Quick Check:

   Partial dependencies exist if 2NF not met [OK]

Hint: In 2NF, no partial dependency on composite key parts [OK]

Common Mistakes:

• Thinking Grade depends only on StudentID
• Assuming CourseName depends on Grade
• Ignoring partial dependencies

4. You have this table:
CREATE TABLE Enrollment (StudentID INT, CourseID INT, StudentName VARCHAR(50), CourseName VARCHAR(50), PRIMARY KEY (StudentID, CourseID));
Which change fixes the 2NF violation?

medium

A. Keep the table as is; 2NF is not violated.

B. Add StudentName and CourseName as part of the primary key.

C. Remove the composite key and use a single primary key.

D. Split into three tables: Students(StudentID, StudentName), Courses(CourseID, CourseName), and Enrollment(StudentID, CourseID).

Solution

1. Step 1: Identify 2NF violation

   StudentName depends only on StudentID, CourseName only on CourseID, causing partial dependencies.

2. Step 2: Fix by splitting tables

   Splitting into Students(StudentID, StudentName), Courses(CourseID, CourseName), and Enrollment(StudentID, CourseID) removes partial dependencies, achieving 2NF.

3. Final Answer:

   Split into three tables: Students(StudentID, StudentName), Courses(CourseID, CourseName), and Enrollment(StudentID, CourseID). -> Option D

4. Quick Check:

   Split tables to remove partial dependencies [OK]

Hint: Split tables to fix partial dependencies [OK]

Common Mistakes:

• Adding non-key columns to primary key
• Removing composite key incorrectly
• Ignoring partial dependencies

5. A table has columns: OrderID, ProductID, ProductName, Quantity, SupplierName with composite primary key (OrderID, ProductID). SupplierName depends only on ProductID. How should you redesign the table to achieve 2NF?

hard

A. Create two tables: Orders(OrderID, ProductID, Quantity) and Products(ProductID, ProductName, SupplierName).

B. Add SupplierName to the primary key.

C. Remove ProductID from the primary key.

D. Keep all columns in one table; 2NF is already satisfied.

Solution

1. Step 1: Identify partial dependency

   SupplierName depends only on ProductID, part of the composite key, violating 2NF.

2. Step 2: Normalize by splitting tables

   Separate Products table with ProductID, ProductName, SupplierName removes partial dependency, Orders table keeps OrderID, ProductID, Quantity.

3. Final Answer:

   Create two tables: Orders(OrderID, ProductID, Quantity) and Products(ProductID, ProductName, SupplierName). -> Option A

4. Quick Check:

   Split tables to remove partial dependencies [OK]

Hint: Move partial dependency columns to separate table [OK]

Common Mistakes:

• Adding non-key columns to primary key
• Removing part of composite key incorrectly
• Assuming 2NF allows partial dependencies