Exercise - Convert Employee-Project Table to 2NF in DBMS

📝 Exercise: Convert Employee-Project Table to 2NF in DBMS

Understanding how to apply normalization rules in real-world scenarios is essential for designing clean and efficient databases. Let’s analyze the following table and determine whether it complies with Second Normal Form (2NF).

🧾 Problem Statement

A company maintains a table to track employee projects:

Employee_ID	Employee_Name	Project_ID	Project_Name	Project_Manager
101	Alice	P1	Alpha	John
101	Alice	P2	Beta	Sarah
102	Bob	P1	Alpha	John
103	Charlie	P3	Gamma	Alice

❓ Is the Table in 2NF?

Step 1: Identify the Primary Key

In this table, each row represents an assignment of an employee to a project. So, the composite primary key is:

{Employee_ID, Project_ID}

Step 2: Check for Partial Dependencies

Let’s examine each attribute:

• Employee_Name depends only on Employee_ID, not on the full composite key → ❌ Partial Dependency
• Project_Name and Project_Manager depend only on Project_ID → ❌ Partial Dependency

Since non-key attributes depend only on part of the primary key, this table violates 2NF.

✅ Converting the Table to 2NF

To achieve 2NF, we must eliminate partial dependencies by splitting the table into three separate tables:

1. Employee Table

Stores employee details.

Employee_ID	Employee_Name
101	Alice
102	Bob
103	Charlie

2. Project Table

Stores project details.

Project_ID	Project_Name	Project_Manager
P1	Alpha	John
P2	Beta	Sarah
P3	Gamma	Alice

3. Employee_Project Table

Tracks which employee is working on which project.

Employee_ID	Project_ID
101	P1
101	P2
102	P1
103	P3

🎯 Result After 2NF

Now:

• Each table has a single primary key.
• All non-key attributes are fully dependent on their respective primary keys.
• We’ve removed all partial dependencies and achieved Second Normal Form (2NF).

🧠 Solution to Exercise 1: Convert Employee-Project Table to 2NF

In the previous exercise, we were given a table tracking employee assignments to projects. Let's now analyze whether it complies with Second Normal Form (2NF) and learn how to convert it if it doesn't.

📋 Original Table: Employee Projects

Employee_ID	Employee_Name	Project_ID	Project_Name	Project_Manager
101	Alice	P1	Alpha	John
101	Alice	P2	Beta	Sarah
102	Bob	P1	Alpha	John
103	Charlie	P3	Gamma	Alice

🔍 Step-by-Step Analysis

✅ Step 1: Identify the Primary Key

Since the table tracks which employee is assigned to which project, the composite primary key is:

{Employee_ID, Project_ID}

⚠️ Step 2: Check for Partial Dependencies

A partial dependency occurs when a non-key attribute depends on only part of the composite primary key.

Let’s look at the dependencies:

• Employee_Name depends only on Employee_ID → ❌ Partial dependency
• Project_Name and Project_Manager depend only on Project_ID → ❌ Partial dependency

Since these dependencies don't rely on the full key, the table violates 2NF.

🛠️ Converting to 2NF

To eliminate partial dependencies, we break the table into three:

📄 1. Employee Table

Stores data related only to the employee.

Employee_ID (PK)	Employee_Name
101	Alice
102	Bob
103	Charlie

🔎 Explanation:

• Employee_Name is fully functionally dependent on Employee_ID.
• No partial dependency → ✅ 2NF

📄 2. Project Table

Stores data related only to the project.

Project_ID (PK)	Project_Name	Project_Manager
P1	Alpha	John
P2	Beta	Sarah
P3	Gamma	Alice

🔎 Explanation:

• Project_Name and Project_Manager are functionally dependent on Project_ID.
• No partial dependency → ✅ 2NF

📄 3. Employee_Project Table

Represents the many-to-many relationship between employees and projects.

Employee_ID (PK, FK)	Project_ID (PK, FK)
101	P1
101	P2
102	P1
103	P3

🔎 Explanation:

• Composite primary key {Employee_ID, Project_ID}
• No non-key attributes → ✅ 2NF

✅ Final Result: Achieved 2NF

After decomposing:

• ✅ All partial dependencies removed
• ✅ Each table is in 1NF and 2NF
• ✅ Data integrity is preserved
• ✅ Redundancy reduced

🧠 Key Takeaways

• Always identify the composite key in tables with many-to-many relationships.
• Partial dependencies violate 2NF—break them out into separate tables.
• Use foreign keys to preserve relationships after decomposition.