Step 1: Check for First Normal Form (1NF)

To determine if the given table is in 1NF, we need to ensure:

1. Each column contains atomic values (no multivalued or composite attributes).
2. Each row is unique and identifiable by a primary key.

Given Table:

Conclusion:

• The table satisfies the requirements for First Normal Form (1NF).

Step 2: Check for Second Normal Form (2NF)

To determine if the table is in 2NF, we need to:

1. Ensure the table is already in 1NF.
2. Ensure there are no partial dependencies, where a non-key attribute depends only on part of a composite primary key.

Primary Key:

• The primary key is Emp_ID (single column, not composite).

Dependencies:

• All non-key attributes (Emp_Name, DOB, Area, City, State, Zip) depend directly on Emp_ID.

Conclusion:

• There are no partial dependencies, and the table satisfies the requirements for Second Normal Form (2NF).

Step 3: Check for Third Normal Form (3NF)

To determine if the table is in 3NF, we need to:

1. Ensure the table is already in 2NF.
2. Ensure there are no transitive dependencies, where a non-key attribute depends on another non-key attribute rather than directly on the primary key.

Dependencies:

• Emp_Name, DOB, and Zip depend directly on Emp_ID.
• However, Area, City, and State depend on Zip, which in turn depends on Emp_ID. This is a transitive dependency.

Conclusion:

• The table is not in 3NF due to the transitive dependency between Zip and Area, City, and State.

Step 4: Convert to Third Normal Form (3NF)

To remove the transitive dependency, we need to:

1. Create a separate table for the location details (dependent on Zip).
2. Retain a reference to Zip in the original table.

Employee Table

This table will store employee-specific information, with Zip as a foreign key.

Explanation:

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

Location Table

This table will store the location details based on Zip.

Explanation:

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

Final Relational Model

After normalization:

1. Employee Table:

   • Contains Emp_ID, Emp_Name, DOB, and Zip.
   • No transitive dependencies exist in this table.

2. Location Table:

   • Contains Zip, Area, City, and State.
   • Location details are fully normalized and uniquely identified by Zip.