Description
goals
This assignment aims to help you learn to:
• read a relational scheme and analyze instances of the schema
• read and apply integrity constraints
• express queries and integrity constraints of your own
• think about the limits of what can be expressed in relational algebra
Your assignment must be typed to produce a PDF document a1.pdf (hand-written submissions are not
acceptable). You may work on the assignment in groups of 1 or 2, and submit a single assignment for
the entire group on MarkUs. You must establish your group well before the due date by submitting an
incomplete, or even empty, submission.
background
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 CSC 343
• 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/2vIDV accination vIDV ial = ;
2A timestamp of 00:00:00, January 1st, 1970 is recorded for any events that have not happened (yet)
