Data Dependencies in Employee Database Step by step Solution with Explanation

Your question:

A software consulting firm wishes to keep the following data for an employee and costing database:

• employee number • employee name • employee address • salary • current job code • job history (job promotion code + year) • office location • telephone number • project number • project name • task number • task name • project budget • task expenditure to date • department number • department name

Data Dependencies in Employee Database Answers and Explanation

• employee_number -> employee_name, employee_address, salary, current_job_code (Employee number uniquely identifies name, address, salary, and current job code)

• job_code -> job_history (Job code determines the employee's job history)

b) Partial Dependencies:

We don't have any partial dependencies in this case. A partial dependency exists when only a subset of attributes on the left side of the dependency arrow determines the attributes on the right side. Since all attributes on the left side are necessary to determine the right side in our FDs, there are no partial dependencies.

• Primary Key:

  • employee_number (This uniquely identifies an employee in the database)

Based on the above analysis, the database schema could be designed as follows:

1. Employee Table:

   • current_job_code

   • department_number (Foreign Key)
2. • year

   • (Composite Primary Key: employee_number, job_promotion_code, year)

4. • project_number (Primary Key)

   • project_name
5. • task_name

   • task_expenditure_to_date

7. • employee_number (Foreign Key)

   • project_number (Foreign Key)