Part 1. Entity-Relationship modeling 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 AM's 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 AM's 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 sub- services 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 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. (30 points] 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. (10 points Transform your E-R diagram into a relational model. List all resulting relations and clearly identify all primary keys. (5 pointsIdentify and list the functional dependencies in your model. [5 points) Is your relational model in BCNF? Justify your answer. Part 1. Entity-Relationship modeling 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 AM's 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 AM's 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 sub- services 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 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. (30 points] 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. (10 points Transform your E-R diagram into a relational model. List all resulting relations and clearly identify all primary keys. (5 pointsIdentify and list the functional dependencies in your model. [5 points) Is your relational model in BCNF? Justify your