Description
You will be working on a schema for a database to track covid-19 vaccinations.1 Vaccines batches are tracked
from the factory that produces them. Their arrival in time Canada, and in the province or territory they are
to be administered in, are recorded. Minimum and maximum intervals for follow-up doses are also recorded.
There is a unique identifier for each vial.
Patients, vaccine administrators and attendants are each recorded, both to follow up on subsequent doses
(where required by the manufacturer), and to track vaccine efficacy and safety. Each patient’s covid status
at the time of vaccination is recorded, and the time of the latest subsequent infection is recorded. Patients
are observed by the attendants for at least 15 minutes after vaccination, and any bad reactions are treated
and recorded.
relations
• Batch(bID, mID, productionDate, vialCount)
Vaccine batch bID, manufacturer mID, was produced on productionDate, with vialCount vials in
this batch.
1Details may not be accurate.
1
• Vial(vID, bID, thawTime, dose_count)
Vial vID from batch bID removed from cold storage at thawTime, with dose_count doses remaining.2
• Manufacturer(mID, name, thawMax, intervalMin, intervalMax)
Manufacturer mID, with company name, thawMax maximum hours vaccine is usable after being
removed from cold storage, intervalMin minimum days to second dose, intervalMax maximum days
to second dose (both zero for a single-dose vaccine).
• Tracking(bID, canadaDate, locationDate, locationName)
Batch bID arrived in Canada on canadaDate, shipped to province or territory locationName on
locationDate.
• Vaccination(pID, date, vID, adID, atID, reaction, covidStatus)
Patient pID vaccinated on date from vial vID. The dose was administered by adID, the patient was
attended by atID. At vaccination time the patient had infection status covidStatus and reaction to
vaccine reaction.
• Patient(pID, latestPositiveTest)
Patient pID had most recent positive Covid-19 test on latestPositiveTest (00:00:00, January 1st, 1970
if this never happened).
• Staff(sID, pID, specialty)
Medical staff sID is also patient pID, and has medical specialty speciality.
our constraints
For each of the following constraints give a one sentence explanation of what the constraint implies, and
why it is required.
• pIDStaff pIDP atient = ;
• (adIDV accination [ atIDV accination) sIDStaff
• specialtyStaff f0RN0
;
0 RP N0
;
0 MD0
;
0 P harmacist0g
• pIDV accination pIDP atient
• bIDV ial bIDBatch = ;
• covidStatusV accination f0positive0
;
0 negative0g
• reactionV accination f0
true0
;
0 false0g
• mIDBatch mIDManufacturer
• bIDT racking bIDBatch = ;
• UPDATED 6/2/21 vIDV accination vIDV ial = ;
2A timestamp of 00:00:00, January 1st, 1970 is recorded for any events that have not happened (yet)
