ou can see, they are not data modelers. They would like to convert the spreadsheet into a database that they can use as a back end for a time keeping application. The column names of the spreadsheet are shown below:
Project ID Project Name Project Manager Project Manager Location Employee ID
Employee Name Employee Location Title Hourly Rate Billable Hours
Draw a dependency diagram for the schema and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups. Here are the field descriptions:
Project ID – the identifier for the project
Project Name – the name of the project
Project Manager – the name of the project manager for a project, projects can have more than one project manager
Project Manager Location – location of the project manager
Employee ID – the identification number for an employee
Employee Name – the name of an employee
Employee Location – the location of an employee
Title – the position title for the employee
Hourly Rate – the rate that is charged to the project sponsor based on the position of the employee
Hours worked – the number of billable hours that an employee has charged to a project
2. Remove all partial and transitive dependencies and draw a new set of relations in third normal form. Put your thinking cap on because there are some obvious data management pitfalls associated with the above relation.
3. Draw the entity relationship diagram.
4. Based on your ERD, draw the physical data model including tables, attributes, data types, primary keys, and foreign keys.