University of Canterbury
COSC265 assignment 2022
This assignment focuses on the use of the EER model to design conceptual schema for various databases.
You should complete your work with EER Tutor found at https://ictg.cosc.canterbury.ac.nz:8005/
This assignment is worth 24% of the overall grade for COSC265. It is made up of two parts: part 1 consists
of three assessed problems worth 8%, and Part 2, to be submitted via Learn, worth 16%. Note that these two
components are due at different times!
Assignment Guidelines: This assignment must be done individually using EER-Tutor. You should be
aware of the University of Canterbury Academic Integrity Policy:
Part 1 (8%): Assessed problems
We have selected three problems in EER-Tutor for you to complete:
• 24 Library (2%)
• 34 Couriers (3%)
• 54 Art collection (3%)
Your solutions to these problems are due by 12/08/2022 at 5:00 pm.
Note: that you can only submit a solution to each problem once, and that no feedback is given at the point of
Part 2 (16%): General instructions
The requirements for Part 2 are given in the next section. Your solution is due by 26/08/2022 at 5:00 pm.
Submissions will be accepted for a week beyond this time, but a deduction of 15 marks will be made to your
final mark. (So if you got 80 of the 100 marks available, you would receive a final mark of 65.) Submissions
beyond this time will not be accepted.
It is required that you prepare your submission using EER-Tutor, using the free hand mode (question 99). It
is a good idea to develop your initial solution on paper. You must also generate a report. This is a simple
document that should list the set of assumptions that you are making about the mini-world that you are
modelling. Note that assumptions should not just be restatements of the requirements, or be obvious
consequences of the requirements. You should aim to limit your report to approximately 500 words —
exceeding this limit should be done with care!
You can submit your solution via Learn. Your schema should be a .png image, and your report a .pdf
document. Both should clearly display your full name and student ID. To print your diagram to a .png file in
EER-Tutor, click the Print Diagram button to create a print page, then Right-Click on that print page, and
choose Save Image As, then give your image file a name but make sure the file type is .PNG, which is the
Part 2: Requirements
You are chosen to design a database for tracking COVID-19 information. The database should support all
necessary operations for the Web portal, as specified in the text below.
The information collected in the database for each person includes details of vaccinations, as well as
COVID-19 tests done. Every person has a unique National Health Index (NHI) number, which consists of
three letters followed by four digits. The database contains information about the person’s name (including
alternative names such as maiden names), address, date/place of birth, gender, New Zealand resident and
citizenship status, ethnicity and date of death (if appropriate).
There are two types of tests: Rapid Antigen Tests (RAT) and PCR tests. RATs are reported by people taking
the tests (or other people reporting on their behalf). PCR tests are reported by institutions who processed the
It is possible to order RATs for symptomatic people and/or household contacts. To order RATs, it is
necessary to enter the mobile number first. The verification code would then be sent to the provided mobile
number, which the user needs to enter in order to complete the request. The user also needs to:
– agree with the privacy statement
– specify whether they arrived to New Zealand from overseas in the last seven days
– specify whether they have an immunodeficiency condition
– specify whether they are a critical worker
– specify whether they have any of COVID-19 symptoms
– specify whether someone in the household has tested positive for COVID-19
– specify how many people there are in the household
– provide their name and address
If they qualify for free RATs, they would need to select one of the nearby collection places. As the final
stage of the order, the user will receive an order number. The RATs can be collected from any of the
authorized sites, by presenting the order number. The RAT collection sites could be pharmacies, GP clinics,
vaccination or testing centres.
A person can report the result of a RAT test for themselves, or for someone else. In the latter case, they need
to provide the following information about the affected person: NHI number, first name, middle name(s)
(optional), last name, date of birth, mobile number, and also need to specify that they have the authority to
report the RAT result on behalf of the affected person. If the user is reporting a RAT for themselves, they
need to read and agree to the privacy statement. To report the test results, the user needs to specify whether
the control line and the test lines are visible. Other necessary information includes the date and time the test
was taken. The test results have to be reported within a week of taking the test. The Web portal allows the
user to see all the tests done within the last 90 days. Only one test result can be recorded per day.
Once a positive RAT result has been recorded, the web portal sends a request to complete details for contact
tracing to the provided mobile number. The user is required to provide information about large gatherings
they have attended within the previous week. Each person with a positive RAT is required to isolate for a
week. The GP clinic for which the person is registered is also informed about the positive result.
The database should also contain information about community testing centres. For each such centre, there is
a (unique) name, address, information about opening hours. Testing centres offer RAT or PCR tests.
PCR tests are performed at authorized sites only. Some positive PCR samples can be further analysed by
genome sequencing, in which case the actual strain of COVID-19 is recorded. PCR tests can also be done
for people travelling overseas, in which case they need to pay for the test.
Details about vaccination include the type of the vaccine (e.g. Pfizer BioNTech COVID-19), the dose (such
as 1, 2, booster 1 or booster 2), and the date. For each type of vaccine the database should store its name
(unique), the dosage for adults, the dosage for children (if appropriate), the number doses required and the
number of weeks between them. If boosters are recommended, then for each booster the database should
store the number of weeks after the previous dose. The number of weeks between doses may be different for
adults and for immunocompromised people.
Users can get certificates of vaccination from the website.
A few notes on marking
Your solution will be marked on several aspects:
▪ Appropriateness of constructs you use. In EER diagrams, often there are several ways of representing
the same collection of data. However, some of these ways would be more appropriate than others, due
to various factors, such as the semantics of the domain, the space required to store the data, the typical
kinds of operations that will be performed on the database etc. Make sure you analyze the requirements
▪ Make sure you use the correct EER notation and naming guidelines.
▪ You are allowed to introduce additional assumptions. However, not every assumption is reasonable.
Think carefully about any assumption you would like to add. You are welcome to discuss your
assumptions with the lecturer/tutors.
University of Canterbury