Solution to Exercise 2 - Normalize Employee Table to Third Normal Form (3NF)

🧠 Solution to Exercise 2: Normalize Employee Table to Third Normal Form (3NF)

In the previous exercise, we examined a table containing employee details along with their location information. Although the table satisfied 1NF and 2NF, it contained transitive dependencies, which violate the rules of Third Normal Form (3NF).

📋 Original Table: Employee Information

Emp_ID	Emp_Name	DOB	Area	City	State	Zip
101	Alice	1990-05-01	Downtown	New York	NY	10001
102	Bob	1988-08-12	Midtown	New York	NY	10002
103	Charlie	1992-11-23	Central	Los Angeles	CA	90001
104	David	1985-03-15	West End	Chicago	IL	60601

🔍 Step-by-Step Analysis

✅ Step 1: 1NF Check

• All attributes contain atomic values.
• Each row is uniquely identified by Emp_ID.

✅ Table is in 1NF

✅ Step 2: 2NF Check

• Primary Key: Emp_ID (not composite).
• All attributes depend directly on the primary key.

✅ Table is in 2NF

⚠️ Step 3: 3NF Check

To satisfy 3NF:

• The table must be in 2NF.
• There must be no transitive dependencies.

Let’s look at the dependencies:

• Emp_Name, DOB, and Zip depend on Emp_ID ✅
• Area, City, and State depend on Zip, not directly on Emp_ID ❌

This introduces transitive dependencies, meaning the table is not in 3NF.

🛠️ Converting to 3NF

We’ll decompose the table into two:

📄 1. Employee Table

Emp_ID (PK)	Emp_Name	DOB	Zip
101	Alice	1990-05-01	10001
102	Bob	1988-08-12	10002
103	Charlie	1992-11-23	90001
104	David	1985-03-15	60601

🔎 Explanation:

• Emp_ID is the primary key.
• All attributes depend directly on Emp_ID.

✅ Now in 3NF

📄 2. Location Table

Zip (PK)	Area	City	State
10001	Downtown	New York	NY
10002	Midtown	New York	NY
90001	Central	Los Angeles	CA
60601	West End	Chicago	IL

🔎 Explanation:

• Zip is the primary key.
• Area, City, and State depend directly on Zip.

✅ Transitive dependency eliminated

✅ Final Result: Achieved 3NF

After decomposition:

• ✂️ Transitive dependencies removed
• 🔗 Foreign key Zip links the two tables
• 📦 Data is cleaner, less redundant, and easier to maintain

🧠 Key Takeaways

• 3NF removes indirect dependencies (transitive).
• Normalize by creating a separate table for attributes dependent on other non-key attributes.
• Maintaining foreign key relationships ensures data remains connected and consistent.