A startup company is using an excel spreadsheet to keep track of the billable hours that each employee spends on a project. As y
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.
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.
1 answer:
Answer:
Hello there! There are 4 parts to this question, all regarding relational database concepts.
Explanation:
Parts 1, 2, 3, 4 are drawn in attachments. For Part 4, the "foreign keys" would be the id fields of the joining table, and the "primary keys" are the IDs of the table itself. Data types are "integer" for ID, "string" for text columns like Name and Location, and "Decimal" for Hourly rate in the Timesheet table. Note that we can further simplify the erd from the one constructed earlier in part 3 to remove the Project Manager model and instead add a Project Manager "boolean" flag in the Employee table since a Project Manager is also an Employee.
You might be interested in