Analysis

Step 1: Identify the Primary Key

• The table's primary key is a composite key consisting of {Employee_ID, Project_ID} because:

  • An Employee can work on multiple Projects.
  • A Project can have multiple Employees working on it.

Step 2: Check for Partial Dependencies

• Partial Dependency occurs when a non-key attribute depends only on a part of the composite primary key.

• Identify Dependencies:

  1. Employee_Name depends only on Employee_ID.
  2. Project_Name and Project_Manager depend only on Project_ID.

• Final Result:

  • Since some non-key attributes depend only on a part of the composite primary key, the table is not in 2NF.

Conversion to Second Normal Form (2NF)

To achieve 2NF, we need to remove partial dependencies by creating separate tables for each entity.

Step 1: Create the Employee Table

This table stores information specific to each employee.

Employee Table

• Primary Key: Employee_ID

• Explanation:

  • Employee_Name is fully functionally dependent on Employee_ID.
  • This table contains no partial dependencies.

Step 2: Create the Project Table

This table stores information specific to each project.

Project Table

• Primary Key: Project_ID

• Explanation:

  • Project_Name and Project_Manager are fully functionally dependent on Project_ID.
  • This table eliminates partial dependencies related to projects.

Step 3: Create the Employee_Project Table

This table links employees to the projects they are working on.

Employee_Project Table

• Composite Primary Key: {Employee_ID, Project_ID}

• Foreign Keys:

  • Employee_ID references the Employee table.
  • Project_ID references the Project table.

• Explanation:

  • This table represents the many-to-many relationship between employees and projects.
  • There are no non-key attributes, so no partial dependencies exist.

Final Result

By decomposing the original table into three tables, we have:

• Eliminated Partial Dependencies:

  • Each non-key attribute is now fully dependent on the primary key of its respective table.

• Achieved Second Normal Form (2NF):

  • All tables are in 1NF and have no partial dependencies.