ITEC 3220 Assignment 1
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 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).
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.). Ashipment to a provider can included doses ofseveral 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 received 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 immunization 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 administered. (For each reported immunization, V.O. needs to be able to identify the provider who performed 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 received, V.O.’ booking system will schedule the immunization at one of the providers nearest to the person’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 immunization 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.
• [6 points] Identify and list the functional dependencies in your model using the format
discussed in class.
• [4 points] Is your relational model in BCNF? Justify your answer
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx