Normalize a Table to Second Normal Form (2NF)

Resolving Partial Dependencies for Second Normal Form (2NF)

Write a SQL query to resolve partial dependencies and achieve 2NF.

Solution:

-- Original table with partial dependency.
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    CustomerName VARCHAR(100),
    ProductID INT,
    Quantity INT
);

-- Resolve partial dependency by splitting into two tables.
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Orders_2NF (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Explanation:

• Purpose of the Query :
• The goal is to eliminate partial dependencies by separating non-key attributes into their own tables.
• Key Components :
• Customers: Stores customer details independently.
• Orders_2NF: Links orders to customers via a foreign key.
• Why Use 2NF? :
• 2NF reduces redundancy by ensuring that non-key attributes depend on the entire primary key.
• It improves data consistency and reduces update anomalies.
• Real-World Application :
• In inventory systems, separating customer and order details avoids duplicating customer information.

Notes:

• Partial dependencies occur when non-key attributes depend on part of a composite key.
• Use primary and foreign keys to enforce relationships between tables.
• Important Considerations:
• Ensure referential integrity when splitting tables.

For more Practice: Solve these Related Problems:

• Write a SQL query to resolve partial dependencies in a table where employee details and department details are stored together, ensuring 2NF compliance.
• Write a SQL query to split a table containing product orders and customer details into separate tables to achieve 2NF.
• Write a SQL query to normalize a table where project details and team member details are combined, resolving partial dependencies for 2NF.
• Write a SQL query to redesign a table storing course enrollments and instructor details to eliminate partial dependencies and achieve 2NF.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Designing a Table in First Normal Form (1NF).
Next SQL Exercise: Eliminating Transitive Dependencies for Third Normal Form (3NF).