Solution:Step 1: Identify partial dependencyManufacturer depends only on ItemID, part of the composite key (OrderID, ItemID).Step 2: NormalizeSeparate Manufacturer and ItemName into a new table keyed by ItemID to remove partial dependency.Final Answer:Split into two tables: PurchaseDetails(OrderID,

[Solved] Given a table:CREATE TABLE PurchaseDetails (OrderID INT, ItemID INT, ItemName VARCHAR(50), Quantity INT, Manufacturer VARCHAR(50), PRIMARY... How should the table be redesigned to comply with 2NF? | SQL

SQL - Database Design and Normalization

Given a table:

CREATE TABLE PurchaseDetails (OrderID INT, ItemID INT, ItemName VARCHAR(50), Quantity INT, Manufacturer VARCHAR(50), PRIMARY KEY (OrderID, ItemID));

Manufacturer depends only on ItemID. How should the table be redesigned to comply with 2NF?

ASplit into two tables: PurchaseDetails(OrderID, ItemID, Quantity) and Items(ItemID, ItemName, Manufacturer).

BAdd Manufacturer to the primary key.

CRemove Manufacturer column from the table.

DMake Manufacturer depend on OrderID instead.

Step-by-Step Solution

Solution:

Step 1: Identify partial dependency
Manufacturer depends only on ItemID, part of the composite key (OrderID, ItemID).
Step 2: Normalize
Separate Manufacturer and ItemName into a new table keyed by ItemID to remove partial dependency.
Final Answer:
Split into two tables: PurchaseDetails(OrderID, ItemID, Quantity) and Items(ItemID, ItemName, Manufacturer). -> Option A
Quick Check:
Partial dependency removed by table split [OK]

Quick Trick: Separate attributes dependent on part of key into new table [OK]

Common Mistakes:

Adding dependent attribute to primary key incorrectly
Removing columns without preserving data
Misunderstanding dependency directions

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

Given a table:

Step 1: Identify partial dependency

Step 2: Normalize

Final Answer:

Quick Check:

Want More Practice?