A medical lab tests company, Advanced MedLabs, AM, was hired by the Government of Ontario to provide

rapid DNA testing for the new coronavirus that threatens to lead to a new pandemic. AM needs to expand

they operations rapidly in order to handle the increased volume of lab tests they are performing and hired

your team to modernize their information system. Your team is to design a new database schema that meets

the following requirements, provided by AM.

In addition to lab testing services for viruses, including the new coronavirus, AM provides many

other services such as gene expression analysis, PCR, DNA extraction and analysis, etc., to various

healthcare organizations based in Canada. For billing purposes, AM needs to have access to each customer's

name and address. Each customer is assigned an AM sales representative, an AM employee who serves as

the point of contact for the customer and oversees the fulfillment of all the orders the customers places with

AM.

Each customer can request a variety of services from AMs portfolio. For certain packages of

services ordered together, as well as for repeat customers (customers who have placed at least one order

before the current one), AM offers discounts. The discounts are a percentage of the total value of an order

and are set by the AM sales representative who is responsible for the client who placed the order.

For a typical client order, AM needs to keep track of the date it received the request, the deadline

(the time the order is needed by), as well as the sub-services AMs lab techs identify as needed to fulfill the

request. For reporting purposes, AM needs to keep track of the materials required by each of the subservices

associated with an order (if applicable), including information such as the quantity and the supplier

of the material(s) needed (this is particularly important for the antibodies used in certain tests such as TB

tests).

AM also needs to maintain an inventory for the materials required by their activities (such as

reagents, test kits, 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 each material as well

as expiration dates, if applicable, as some materials, e.g., reagents have very short shelf lives and need to

replenished continuously. Lab techs are required to report inconsistencies or issues with the materials they

use, as quality can vary dramatically between different manufacturers. This information also needs to be

stored as it is used to determine whether to renew supplier contracts.

In addition to sales representatives and lab technicians, AM various other employees, from

administrative assistants to scientists who manage the various experiments being run on behalf of clients.

Each employee has a portfolio of skills that they can perform at various levels of proficiency. AM assigns

to each employee a unique ID.

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 skills, the proficiency level requirements for these skills may vary significantly

between tasks. Currently AM 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 customer billing purposes, ABA needs to be able to generate an estimated cost for each client

order based on the services and subservices required by it. For its own internal accounting, AM also also

needs to keep a record of the actual hours taken by each task on a client order.

1.

Draw an Entity-Relationship diagram for a database that supports the basic

operations of the AM company. Specify the entities, their attributes, and primary keys, as well

as any relationships between entities and their descriptive attributes (if applicable),

cardinalities, etc.

2.

Transform your E-R diagram into a relational model. List all resulting relations

and clearly identify all primary keys.

3.

Identify and list the functional dependencies in your model.