Consider a construction company X that specializes in the development of single-family homes. Company X organizes its internal structure in four operations - Structure, Plumbing, Electrical, and Heating and Ventilation. The company charges its clients by billing the hours spent on each assignment. The hourly billing varies per assignment, and is independent of the project, employee, or job.

The figure below shows a short portion of a basic report that company X staff would like to see every week concerning what projects are being assigned, the overall assignment hours, and the charges for the assignment. Analyze this figure to understand the kinds of data the company needs to keep and start to think of the relationships that may exist within the data.

Company X has decided to implement a relational database model to track the data related to their projects. The details are explained next.

Tables:

• Clients: clients are individual people, so the table needs to have columns to keep track of personal data such as name and contact data (address, phone, and email).
• Projects: Projects have a name, a starting date, a planned end date, and an actual end date. We also need information about the location (address of the construction site).
• Employees: This table keeps track of employeess personal information, such as name, contact (address, phone, and email), and job title.
• Jobs: this table simply keeps track of the kinds of jobs, and is the following:

JobCode	Description
J01	Structure
J02	Plumbing
J03	Electrical
J04	Heating and Ventilation

• Assignments: For each assignment, we need to keep track of the a start date and time and number of hours. We also need to know who performed the assignment, which project it is for, and the kind of job it consists of (more on this below).

Do not forget that a primary key is necessary for every table.

Recall: The company charges its clients by billing the hours spent on each assignment. The hourly billing varies per assignment, and is independent of the project, employee, or job. Which table should we put the Charge/hour in?... Think about it and then add Charge/hour to that table.

Relationships:

1. Client Project: Each project corresponds to one and always one Each client may have many projects. A client may also exist in the database and not have any project.
2. Assignment Project: For each assignment, we need to know the project it applies to. Each assignment corresponds to one and always one Each project requires many assignments to be completed.
3. Assignment Employee: Each assignment is performed by only one employee. Obviously, each employee has multiple assignments.
4. Assignment Job: For each assignment, we need to know the corresponding job category. Each assignment corresponds to one and always one Each job corresponds to 0 or more assignments.

a) Design a database to hold all the data described above. Hand in a database design outline and an ER diagram