Case $2($Ava$\text{'}$s Consulting$)$

Ava's Consulting $($ AC Inc$)$ is a consulting company. AC Inc Operations Database will keep track of the

following:

For each consultant: a unique cid, as well as the consultant's name, one or more skills, start date

with the company, and the number of years with company;

For each project manager: a unique pmid, the project manager's name;

For each customer: a unique custid, well as the customer's name composed of customer's first

and last name, and a phone number;

For each supervisor: a unique supid, as well as the supervisor's name, number of supervised

contracts, and $($if supervisor owns AC Inc. stock$)$ number of stocks owned. $($Not every supervisor

owns AC Inc. stock.$)$

For each contract: a contract id and contract start date. Two or more contracts can have the same

contract id$,$ but all contracts for the same customer will have different contract ids.

Each contract is managed by one project manager. A project manager manages one contract or no

contracts at all.

Each contract has at least three consultants assigned to it$.$ A consultant is assigned to one contract.

A customer has at least one contract but may have many. Each contract is associated with one

customer.

Each contract is supervised by one supervisor. Each supervisor must supervise at least one

contract but can supervise many contracts.

Each supervisor has one or more consultants assigned as administrative helpers. A consultant can

be assigned as administrative helper to between zero and many supervisors. We keep track of how

many hours per week a particular consultant is working as an administrative helper for a

particular supervisor.

Question $2$a $(20$ points$)$: Create the ER diagram for the AC Inc Operations Database based on the given

requirements.

Question $2$b $(20$ points$)$: Map the ER diagram for Ava's Consulting Operations Database into a relational

schema.