[Solved] A table has columns: OrderID, ProductID, ProductName, Quantity, SupplierName with composite primary key (OrderID, ProductID).... How should you redesign the table to achieve 2NF? | SQL

SQL - Database Design and Normalization

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?

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

BAdd SupplierName to the primary key.

CRemove ProductID from the primary key.

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

Step-by-Step Solution

Solution:

Step 1: Identify partial dependency
SupplierName depends only on ProductID, part of the composite key, violating 2NF.
Step 2: Normalize by splitting tables
Separate Products table with ProductID, ProductName, SupplierName removes partial dependency, Orders table keeps OrderID, ProductID, Quantity.
Final Answer:
Create two tables: Orders(OrderID, ProductID, Quantity) and Products(ProductID, ProductName, SupplierName). -> Option A
Quick Check:
Split tables to remove partial dependencies [OK]

Quick Trick: 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

Master "Database Design and Normalization" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

How should you redesign the table to achieve 2NF?

Step 1: Identify partial dependency

Step 2: Normalize by splitting tables

Final Answer:

Quick Check:

Want More Practice?