Given the following table:
Assign_HRS (Emp_name, Project_ID, Task_ID, Phone, Total-Hours)
An employee can work on many projects and a project can have many tasks
(10 points)
Emp_Name Project_ID Task_ID Phone Total-hours
Don 100A B-1 12345 12
Don 100A P-1 12345 12
Don 200B B-1 12345 12
Don 200B P-1 12345 12
Pam 100A C-1 67890 26
Pam 200A C-1 67890 26
Pam 200D C-1 67890 26
Answer the following: (each part is independent)
a. What is the normal form (unnormalized, 1st, 2nd or 3rd) of the ASSIGN_HRS table? Why?
b. Assuming Total-hours are total hours an employee works, i.e. Don works a total of 12 hours irrespective of the project or task
Based on above assumption, convert the above table (ASSGN_HRS) into 3NF
c. Assuming that total_hours are the hours worked by an employee on a given PROJECT (irrespective of the task), i.e. Don worked 12 hours on PROJECT 100A and 12 hours on Project 200B
Based on assumption in part c, convert the original ASSIGN_HRS table into 3NF
d. Assuming total-hours are the number of hours worked by an employee on a given task for a given project, i.e. Don worked 12 hours on task B-1 Project 100A, worked 12 hours on task P-1 on Project 100A etc..
Based on assumption in part d, convert the original ASSIGN_HRS table into 3NF