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.