Achieve Third Normal Form (3NF) by Removing Transitive Dependencies

Eliminating Transitive Dependencies for Third Normal Form (3NF)

Write a SQL query to eliminate transitive dependencies and achieve 3NF.

Solution:

-- Original table with transitive dependency.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    DepartmentName VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- Eliminate transitive dependency by splitting into two tables.
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Employees_3NF (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Explanation:

• Purpose of the Query :
• The goal is to eliminate transitive dependencies by separating dependent attributes into their own tables.
• Key Components :
• Departments: Stores department details independently.
• Employees_3NF: Links employees to departments via a foreign key.
• Why use 3NF? :
• 3NF ensures that non-key attributes depend only on the primary key, reducing redundancy.
• It prevents anomalies caused by updates or deletions.
• Real-World Application :
• In HR systems, separating department details avoids duplicating department names.

Notes:

• Transitive dependencies occur when a non-key attribute depends on another non-key attribute.
• Use normalization to simplify queries and improve data integrity.
• Important Considerations:
• Avoid over-normalization, which can complicate joins.

For more Practice: Solve these Related Problems:

• Write a SQL query to eliminate transitive dependencies in a table where employee details include department names, ensuring 3NF compliance.
• Write a SQL query to normalize a table containing supplier details and product categories by resolving transitive dependencies for 3NF.
• Write a SQL query to redesign a table storing customer orders and shipping addresses to eliminate transitive dependencies and achieve 3NF.
• Write a SQL query to split a table containing employee performance reviews and reviewer details into separate tables for 3NF compliance.

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

Previous SQL Exercise: Resolving Partial Dependencies for Second Normal Form (2NF).
Next SQL Exercise: Achieving Boyce-Codd Normal Form (BCNF).