Entity-Relationship Modeling

To better manage the pandemic and the emerging viral outbreaks, in addition to pursuing an active vaccination program for the SARS-COV2, monkeypox, polio and influenza viruses, the government of the province is also pursuing an active testing program that monitors the emerging variants of the viruses on its watch list and issues alerts to the various regional public health units under the jurisdiction of the Ministry of Health. All of this is managed by a newly created crown corporation, ONImm. ONImm needs to build an information system that is able to handle not just vaccine distribution, vaccinee registration and booking but also providing proof of vaccination (for employment, travelling, and other purposes) as well as keeping track of the emergence of variants of concern and issue alerts as soon as the prevalence of variant within the weekly test results goes beyond 50%. ONImm. hired your team to build their information system. Your team is to design a new relational database schema that meets the ONImm informational requirements and supports the functionality outlined below. In order to ensure the same quality of immunization across licensed vaccination providers, they all receive the vaccines they will administer from ONImm, which sources them from the federal government and via its own private contracts. ONImm needs to maintain an inventory to keep track of the vaccines it receives from the federal government or it sources itself directly from manufacturers. For each lot of vaccine, in addition to the type of vaccine, quantity of doses it contains and expiration date, ONImm also needs to record the supplier of the lot, along with information about them such as company name, address and the contact information for at least one employee of the supplier (their name, position, phone number and email). ONImm also needs to store information about each vaccine provider (hospitals, pharmacies, private clinics, university health centres, walk-in ad-choc centres, etc.), and the materials they received. ONImm assigns to each provider an ONImm employee who serves as the point of contact for that provider. For each provider, ONImm needs to store the organizations name, address, along with contact information for a person from that organization, i.e., the persons name, position with the organization, phone number and email. ONImm sends vaccines to providers as shipments that may include doses of several different vaccine types. For each shipment, ONImm needs to store, and be able to make available, the date of the shipment as well as how many doses of which type of vaccine were delivered and what vaccine lots the doses of each vaccine type where from. The fulfillment of a vaccine shipment to a provider is personally overseen by that providers ONImm point of contact. In order to support an online vaccination booking system and serve as an immunization registry, ONImms system also needs to help collect information about each vaccination performed by a provider. The ongoing situation is still fluid and a persons status as fully vaccinated depends, in certain jurisdictions and contexts, not only on how many doses the person received but also on when the person received their first, second or booster shots (for multi-dose vaccines)1. Things are further complicated by the fact that due to supply constraints, the province is currently offering only one shot of certain multi-dose vaccines2, which is likely to change as the supply situation improves. In order to facilitate ascertaining whether a person is fully vaccinated according to the rules currently in place (which may change in the future) ONImm must record, and be able to make available, information about the vaccinated persons name, SIN, and address, the date of the immunization, the type of vaccine used as well as the lot the vaccine belonged to. In order to reduce vaccination fraud, for each reported immunization ONIMM needs to be able to help identify the provider who performed the vaccination and submitted the record. Each immunization record submitted by a provider must also contain the location the vaccination was performed at. In addition to immunization records, every vaccination provider is required to report any serious adverse reactions, such as anaphylactic shocks, experienced by a person who was vaccinated by the provider. Besides information identifying the person who experienced the adverse effect, the report must also contain information about the type of vaccine used and the vaccine lot the vaccine dose belonged to. Your database design should also support the assignment of the ONImm employee who serves as a point of contact for the provider who submitted the report to be the person in charge of handling that report. Providers must also report any quality issues they encountered while using vaccines it received from ONImm. This information will be used to determine whether to renew or discontinue supplier contracts. (Some suppliers are producing vaccines under licence from the original manufacturers.) In addition to the employees that serve as points of contact for vaccination providers, ONIMM has various other employees, from administrative assistants to doctors and scientists. Doctors are charged with handling adverse effects reports, while scientists are tasked with testing samples from each vaccine lot. ONImm stores for each employee their name, address, position, status and email address and assigns to each of them a unique ID. ONImm needs to be able to enforce that only doctors are assigned to follow up on the adverse effects reports and only scientists are assigned to perform vaccine tests. For each vaccine sample test, the system should keep 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, ONIMM needs to be able to identify the scientist who was assigned to test a vaccine sample and the doctor who followed up on a severe adverse reaction report. Vaccination is voluntary and the residents of the province can request to be booked for vaccination at any time. In order to verify eligibility, ONImm needs to store for each resident of Ontario their name, SIN, and date of birth. A request for vaccination should include the name of the person requesting the vaccination, their SIN, phone number and/or email. Once a request for vaccination is received, the ONImm booking system will schedule the immunization at one of the providers nearest to the persons address and communicate that to the provider and the prospective vaccinee. ONImm needs to be able to verify at any point in time for every person registered to be vaccinated whether their immunization was accomplished, i.e., whether they have received all required doses. Not all vaccines can be given together, or even be administered within a certain exclusion period. Your database design should also support storing information about which other vaccines a certain vaccine should not be administered together with, as well as the exclusion period. For example, it is recommended that the anti-orthopoxvirus vaccine currently approved in Canada, IMVAMUNE, not be given within 4 weeks of an mRNA vaccine for COVID-19. This information should be available in the system so that it can be used to determine if a person can be booked for an immunization with a particular vaccine, given their most recent immunization history. Lastly, some residents, e.g., international students enrolled in the provinces colleges and universities, may have been vaccinated abroad either on travel trips or in their home countries and ONImms information system must support functionality that allows these residents to upload information about which vaccine they received, when, where and from whom. As far as testing is concerned, ONImm needs to keep track of all the tests that are done in the province, and be able to make available for each test information about the person who got tested, the date of the test, the type of test performed, and, the health provider who administered the test, the lab that processed the test, the type of processing as well as the result and, where applicable, the variant of concern detected. Note that DNA sequencing is not done for all the tests administered in the province due to the prohibitive costs still associated with this type of processing. Also keep in mind that each variant of concern is related to one of the viruses monitored by the province. For each virus, the system needs to keep information related to its colloquial name, its scientific name, its viral family and its reference DNA sequence. For a virus variants of concern, the system needs to keep track of their current WHO-assigned name and their primary DNA sequence. ONImm must also keep track of all the alerts issued as a result of a new variant surpassing the threshold of 50% of all the tests conducted in the province (when the alert was issued and for what variant of concern.) Alerts will be issues automatically and implemented as triggers on the relational database management that will implement your design.

Q1. Design an Entity-Relationship model that would support the operations of the ONIMM corporation. Specify all applicable entity sets (their attributes, and primary keys), as well as all relationships between entity sets (and their descriptive attributes, if applicable). Use the E-R modeling style discussed in class and depict your model as an ER diagram using the notation used in class. Clearly state the assumptions that you made (if any) and the constraints not captured in your diagram (if any). Make sure to note on the diagram all relationship cardinalities, mandatory or optional participation constraints, etc.