3. Design a database to help a consulting organization keep track of the amount of time an employee spends working on a contract. While working each day, each employee completes an online timesheet that looks like this:

The data stored for this application is shown on the next page. The first three rows on the data shown on the next page represents the timesheet above. The organization wants to keep track of the data at the detailed level shown on the spreadsheet. For each employee, the organization keeps track of a unique Employee ID, as well as a first name and last name, office phone number, hourly billing rate and the employee ID for the manager of that employee (managerID). For each contract, the organization keeps track of a unique ContractID, the date the contract was signed and the date it is due as well as the employee ID of the employee who serves as the manager for the contract. Every time an employee records work on the time sheet, the employee must record the contract related to that work, the type of work that was performed (type of work description above) the start time and the number of minutes worked. Here are some business rules about the application:

• An employee might work on many contracts, and a contract might have many employees working on it.

• An employee may work on more than one contract during a given day. A contract might have more than one employees working on it at any point in time.

• Each time an employee works on a contract, the employee must record a description of the type of work that was done on that contract. The work descriptions are standard across all employees in the organization. Examples of a work description include: "Java Programming," "Tableau Report Generation," "Database Design," and "SQL Programming." There are many different work description values but they are standardized across the organization so that employees can select a description of the type of work performed when entering their timesheet data.

• A given employee may be able to perform many different types of work description, and a type ofwork description may be able to be performed by many employees.

• An employee has a standard hourly billing rate.

• Each type of work has a standard billing rate (called Std Bill Rate on the spreadsheet on the next

  page).

• The employee's billing rate and the standard billing rate for a type of work do not have to be the

  same.

• Each employee has only one manager, but a manager may manage multiple employees. An employee

  does not have to have a manager.

• A contract has only one manager. Each contract must have one manager. An employee does not

  have to manage a contract, but if an employee manages a contract, the employee manages only one

  contract.

• Managers are also employees of the consulting organization. Consider a manager as just another

  employee.

• A contract might also have an employee who serves as the person who sells the contract. It is

  possible that no employee sold a given contract. An employee may serve as the seller for more than one contract. This data is not included in the spreadsheet on the next page, but it is a business rule that should be included in the database.

Date 2/5/2020 Name Employee ID Contract ID Tristan Elliott 3411 Type of Work Description Time Start 444 444 Python Programming Tableau Report Generation Tableau Report Generation 8AM 11AM 4PM Minutes Worked 180 240 120 777 Employee Work Date Time Last Name First Name Manager | Office ID Phone Description Hourly Billing Rate Work Type | ID Std Bill Rate Minutes Contract Worked | ID Date Contract Signed Date Contract Due Contract Manager ID 3411 Tristan Elliott 4522 7753446558 $35 451 Python Programming $75 180 444 02/17/2020 7819 2020-02-05 08:00:00 2020-01-12 00:00:00 3411 Tristan Elliott 4522 7753446558 $35 3 Tableau Report Generation $60 240 2/5/2020 11:00 444 02/17/2020 7819 2020-01-12 00:00:00 3411 Tristan Elliott 4522 7753446558 $35 3 Tableau Report Generation $60 2/5/2020 16:00 120 777 03/30/2020 4522 2020-01-08 00:00:00 3424 Polanski Charles 4522 7754665991 $40 2020-02-02 08:00:00 451 Python Programming $75 480 444 7819 2020-01-12 00:00:00 02/17/2020 3424 Polanski Charles 2020-02-05 14:00:00 45227754665991 $40 3 Tableau Report Generation $60 180 444 7819 2020-01-12 00:00:00 02/17/2020 3424 Polanski 2020-02-02 08:00:00 Charles 45227754665991 $40 3 Tableau Report Generation $60 480 777 | 2020-01-08 00:00:00 3/30/2020 4522 4522 Jenkins Martin 1619 8056448777 $50 255 $155 120 888 2020-01-27 15:00:00 04/15/2020 1227 2020-01-02 00:00:00 Vendor Investigation/Procurement Strategic Analysis and Planning 7819 Martinez Jenicia 1619 7754881222 $85 2020-02-05 8:00:00 455 $285 180 444 02/17/2020 7819 2020-01-12 00:00:00 7819 Martinez Jenicia 1619 7754881222 $85 455 $285 2020-01-29 10:00:00 60 777 03/30/2020 Strategic Analysis and Planning 2020-01-08 00:00:00 4522 1619 Matsumoto Matsumoto Haruko null 8582219001 $125 255 $155 240 444 02/17/2020 7819 2020-01-05 7:00:00 Vendor Investigation/Procurement 2020-01-12 00:00:00 Date 2/5/2020 Name Employee ID Contract ID Tristan Elliott 3411 Type of Work Description Time Start 444 444 Python Programming Tableau Report Generation Tableau Report Generation 8AM 11AM 4PM Minutes Worked 180 240 120 777 Employee Work Date Time Last Name First Name Manager | Office ID Phone Description Hourly Billing Rate Work Type | ID Std Bill Rate Minutes Contract Worked | ID Date Contract Signed Date Contract Due Contract Manager ID 3411 Tristan Elliott 4522 7753446558 $35 451 Python Programming $75 180 444 02/17/2020 7819 2020-02-05 08:00:00 2020-01-12 00:00:00 3411 Tristan Elliott 4522 7753446558 $35 3 Tableau Report Generation $60 240 2/5/2020 11:00 444 02/17/2020 7819 2020-01-12 00:00:00 3411 Tristan Elliott 4522 7753446558 $35 3 Tableau Report Generation $60 2/5/2020 16:00 120 777 03/30/2020 4522 2020-01-08 00:00:00 3424 Polanski Charles 4522 7754665991 $40 2020-02-02 08:00:00 451 Python Programming $75 480 444 7819 2020-01-12 00:00:00 02/17/2020 3424 Polanski Charles 2020-02-05 14:00:00 45227754665991 $40 3 Tableau Report Generation $60 180 444 7819 2020-01-12 00:00:00 02/17/2020 3424 Polanski 2020-02-02 08:00:00 Charles 45227754665991 $40 3 Tableau Report Generation $60 480 777 | 2020-01-08 00:00:00 3/30/2020 4522 4522 Jenkins Martin 1619 8056448777 $50 255 $155 120 888 2020-01-27 15:00:00 04/15/2020 1227 2020-01-02 00:00:00 Vendor Investigation/Procurement Strategic Analysis and Planning 7819 Martinez Jenicia 1619 7754881222 $85 2020-02-05 8:00:00 455 $285 180 444 02/17/2020 7819 2020-01-12 00:00:00 7819 Martinez Jenicia 1619 7754881222 $85 455 $285 2020-01-29 10:00:00 60 777 03/30/2020 Strategic Analysis and Planning 2020-01-08 00:00:00 4522 1619 Matsumoto Matsumoto Haruko null 8582219001 $125 255 $155 240 444 02/17/2020 7819 2020-01-05 7:00:00 Vendor Investigation/Procurement 2020-01-12 00:00:00