Part I. ERD Diagram

You have been hired to consult for a biotech company, Advanced Bio-Experiments, ABE, who wishes to modernize their operations. You are to design a new database schema that embodies the following requirements provided by ABE.

ABE provides a variety of services such as DNA extraction and analysis, gene expression analysis, PCR, etc. , to a wide range of customers based in Ontario and Quebec. ABE assigns to each customer a unique customer identifier and need to have access to each customer's name and address for billing purposes. A customer can request a variety of services, and ABE offers discounts for certain packages of services and for repeat orders.

The company also needs to maintain a list of materials necessary for their activities, such as reagents, antibodies, bacterial culture food, etc. In addition to the name, unit cost and quantity for each individual material, the company also needs to keep track of the suppliers of the materials as well as expiration dates, if applicable, and comments its lab techs may make with regards to the quality of the material.

For a typical client order, ABE needs to keep track of the date it received the request, the deadline, and the person assigned to oversee the completion of the order, as well as the sub-services its lab techs identify as needed to fulfill the request. ABE also wants to keep track of the materials required by each of the sub-services identified as required by a client request, including information such as quantity and the supplier of the material (this is particularly important for antibodies, as the quality varies considerably among suppliers).

ABE has a variety of employees, from administrative assistants to lab techs and scientists who manage the various experiments being run on behalf of clients. Each employee has a unique ID, assigned by the company. Each employee has a portfolio of skills that they can perform at various levels of proficiency.

Each service provided by the company typically consists of several standard tasks, each of which, in turn, requires a specific set of skills from the employees assigned to perform them. While several tasks may require the same set of skills, the proficiency level requirements for these skills may vary significantly between tasks. Currently ABE maintains a dictionary of tasks, each assigned a unique identifier, name, and description, as well as a billing rate per hour and estimated number of hours it would take to complete the task. For billing purposes, ABA needs to be able to generate estimated cost for each client order but also needs to keep a record of the actual hours taken by each task on a client order.

Draw an ERD diagram based on the Chen model for a database which supports the basic operations of a company. Specify the entities, their attributes, and primary keys, as well as any relationships between entities and their descriptive attributes (if applicable), cardinalities, etc.

Transform your E-R diagram into a relational model. List all resulting relations and clearly identify all primary keys.