Problem statement: To increase the speed of the Covid-19 vaccination program, private healthcare organizations (e.g., pharmacies, private clinics, etc.) have been recently licenced to provide immunization services to the population. This measures is intended to help supplement the capacity of the vaccination centers set up by the province. The crown corporation, V.O., that manages the immunization program on behalf of the Government needs to expand its operations rapidly in order to handle the increased volume of vaccination records and the diverse immunization services providers in the system, and hired your team to modernize their information system. Your team is to design a new database schema that meets the requirements outlined below. In order to ensure the same quality of immunization across licensed vaccination providers, V.O. gives each provider (hospitals, pharmacies, private clinics, university health centres, etc.) doses of vaccine it receives from the federal government (who sources the vaccine from its own suppliers). V.O. needs to maintain an inventory for the various vaccines (approved by Health Canada) it receives from the federal government. For each lot of vaccine, in addition to the type of vaccine, quantity of doses it contains and the lot's expiration date, V.O. also needs to record the supplier of the lot, as some of the suppliers contracted by the federal government produce vaccines under license from the original patent holders (Pfizer Inc, Moderna Inc, AstraZeneca Plc, etc.). V.O. keeps standard information about each supplier, i.e., supplier's corporate name, address and contact information for a representative (name, position, phone number and email). Inc, AstraZeneca Plc, etc.). V.O. keeps standard information about each supplier, i.e., supplier's corporate name, address and contact information for a representative (name, position, phone number and email). For each provider, V.O. needs to store the organization's name, address, along with contact information for a person from that organization, i.e., the person's name, position with the organization, phone number and email. Each provider is assigned a representative, a V.O. employee who serves as the point of contact for the provider, i.e., oversees the fulfillment of all vaccine shipments to the provider and helps handle the reports submitted by the provider to V.O. Every provider is required to report any serious adverse reactions, such as anaphylactic shocks, experienced by a person who was vaccinated by the provider, as well as quality issues related to any vaccine lot it receives. This information will be used to determine whether to renew or discontinue supplier contracts. Each provider can receive from V.O. doses for one or more of the approved vaccines (e.g., Comirnaty/Tozinameran from Pfizer, mRNA-1273 from Moderna, AZD1222/ChAdOx from AstraZeneca, etc.). A shipment to a provider can included doses of several vaccine types. For each shipment, V.O. needs to store how many doses of which type of vaccine were delivered (and how many were designated for the initial or booster shots), what vaccine lots the shipment was fulfilled with and the date the shipment was made. In addition to the employees that serve as points of contact for vaccination providers, V.O. has various other employees, from administrative assistants to scientists who test samples from each vaccine lot re- ceived and doctors who follow up on the vaccine adverse effects reported to V.O. by providers. V.O. stores for each employee their name, address and email address and assigns to each of them a unique ID. V.O. also needs to make sure that only doctors are assigned to follow up (with vaccinees) on the adverse effects reported by providers and only scientists are assigned to vaccine testing. Each vaccine test should have information about the vaccine lot tested, the date the test was performed, the type of test performed and the result of the test. For auditing purposes, V.O. needs to be able to identify the scientist who was assigned to test each sample and the doctor who followed up on a severe adverse reaction report. V.O's system also needs to support an online vaccination booking system and serve as an immunization registry (i.e., V.O. will collect information about each vaccination performed by a provider). Each im- munization record submitted by a provider needs to have the vaccinated person's name, SIN, and address, the date of the immunization, the type of vaccine used and whether the first or the booster shot was ad- ministered. (For each reported immunization, V.O. needs to be able to identify the provider who per- formed the vaccination and submitted the record.) Vaccination is voluntary, and once they become eligible, Ontario residents can request to be booked for the initial vaccination. (Requests for vaccination can also be placed on behalf of residents by their family physician, legal guardian or administrator of the group home they live in.) In order to verify eligibility, V.O. needs to store for each resident of Ontario their name, SIN, and date of birth and vaccination status (immunized or non-immunized). A request for vaccination includes the name of the person to receive the vaccination, their SIN, address, phone number and email (optional). Once a request for vaccination is re- ceived, V.O.' booking system will schedule the immunization at one of the providers nearest to the per- son's address and communicate that to the provider and the prospective vaccinee. The second shot (where needed) will be scheduled automatically, without the need for a second request, but only after V.O. can verify that the person has received the first shot (using the immunization reports submitted by providers). V.O. needs to be able to verify for every person registered to be vaccinated whether their immunization was accomplished, i.e., whether they have received all required doses. Once a person's immunization was completed, their vaccination status changes to immunized (and they become eligible to obtain an immun- ization certificate). . [30 points] Draw an Entity-Relationship diagram for a database that supports the basic operations of the V.O. company. Specify all applicable entity sets (their attributes, and primary keys), as well as all relationships between entity sets (and their descriptive attributes, if applicable). Note on the diagram relationship cardinalities, mandatory or optional participation constraints, etc. Clearly state the assumptions that you made (if any) and the constraints not captured in the diagram (if any). [10 points] Transform your E-R diagram into a relational model. List all resulting relations and clearly identify all primary and foreign keys. . Problem statement: To increase the speed of the Covid-19 vaccination program, private healthcare organizations (e.g., pharmacies, private clinics, etc.) have been recently licenced to provide immunization services to the population. This measures is intended to help supplement the capacity of the vaccination centers set up by the province. The crown corporation, V.O., that manages the immunization program on behalf of the Government needs to expand its operations rapidly in order to handle the increased volume of vaccination records and the diverse immunization services providers in the system, and hired your team to modernize their information system. Your team is to design a new database schema that meets the requirements outlined below. In order to ensure the same quality of immunization across licensed vaccination providers, V.O. gives each provider (hospitals, pharmacies, private clinics, university health centres, etc.) doses of vaccine it receives from the federal government (who sources the vaccine from its own suppliers). V.O. needs to maintain an inventory for the various vaccines (approved by Health Canada) it receives from the federal government. For each lot of vaccine, in addition to the type of vaccine, quantity of doses it contains and the lot's expiration date, V.O. also needs to record the supplier of the lot, as some of the suppliers contracted by the federal government produce vaccines under license from the original patent holders (Pfizer Inc, Moderna Inc, AstraZeneca Plc, etc.). V.O. keeps standard information about each supplier, i.e., supplier's corporate name, address and contact information for a representative (name, position, phone number and email). Inc, AstraZeneca Plc, etc.). V.O. keeps standard information about each supplier, i.e., supplier's corporate name, address and contact information for a representative (name, position, phone number and email). For each provider, V.O. needs to store the organization's name, address, along with contact information for a person from that organization, i.e., the person's name, position with the organization, phone number and email. Each provider is assigned a representative, a V.O. employee who serves as the point of contact for the provider, i.e., oversees the fulfillment of all vaccine shipments to the provider and helps handle the reports submitted by the provider to V.O. Every provider is required to report any serious adverse reactions, such as anaphylactic shocks, experienced by a person who was vaccinated by the provider, as well as quality issues related to any vaccine lot it receives. This information will be used to determine whether to renew or discontinue supplier contracts. Each provider can receive from V.O. doses for one or more of the approved vaccines (e.g., Comirnaty/Tozinameran from Pfizer, mRNA-1273 from Moderna, AZD1222/ChAdOx from AstraZeneca, etc.). A shipment to a provider can included doses of several vaccine types. For each shipment, V.O. needs to store how many doses of which type of vaccine were delivered (and how many were designated for the initial or booster shots), what vaccine lots the shipment was fulfilled with and the date the shipment was made. In addition to the employees that serve as points of contact for vaccination providers, V.O. has various other employees, from administrative assistants to scientists who test samples from each vaccine lot re- ceived and doctors who follow up on the vaccine adverse effects reported to V.O. by providers. V.O. stores for each employee their name, address and email address and assigns to each of them a unique ID. V.O. also needs to make sure that only doctors are assigned to follow up (with vaccinees) on the adverse effects reported by providers and only scientists are assigned to vaccine testing. Each vaccine test should have information about the vaccine lot tested, the date the test was performed, the type of test performed and the result of the test. For auditing purposes, V.O. needs to be able to identify the scientist who was assigned to test each sample and the doctor who followed up on a severe adverse reaction report. V.O's system also needs to support an online vaccination booking system and serve as an immunization registry (i.e., V.O. will collect information about each vaccination performed by a provider). Each im- munization record submitted by a provider needs to have the vaccinated person's name, SIN, and address, the date of the immunization, the type of vaccine used and whether the first or the booster shot was ad- ministered. (For each reported immunization, V.O. needs to be able to identify the provider who per- formed the vaccination and submitted the record.) Vaccination is voluntary, and once they become eligible, Ontario residents can request to be booked for the initial vaccination. (Requests for vaccination can also be placed on behalf of residents by their family physician, legal guardian or administrator of the group home they live in.) In order to verify eligibility, V.O. needs to store for each resident of Ontario their name, SIN, and date of birth and vaccination status (immunized or non-immunized). A request for vaccination includes the name of the person to receive the vaccination, their SIN, address, phone number and email (optional). Once a request for vaccination is re- ceived, V.O.' booking system will schedule the immunization at one of the providers nearest to the per- son's address and communicate that to the provider and the prospective vaccinee. The second shot (where needed) will be scheduled automatically, without the need for a second request, but only after V.O. can verify that the person has received the first shot (using the immunization reports submitted by providers). V.O. needs to be able to verify for every person registered to be vaccinated whether their immunization was accomplished, i.e., whether they have received all required doses. Once a person's immunization was completed, their vaccination status changes to immunized (and they become eligible to obtain an immun- ization certificate). . [30 points] Draw an Entity-Relationship diagram for a database that supports the basic operations of the V.O. company. Specify all applicable entity sets (their attributes, and primary keys), as well as all relationships between entity sets (and their descriptive attributes, if applicable). Note on the diagram relationship cardinalities, mandatory or optional participation constraints, etc. Clearly state the assumptions that you made (if any) and the constraints not captured in the diagram (if any). [10 points] Transform your E-R diagram into a relational model. List all resulting relations and clearly identify all primary and foreign keys