Covid tracing app INFO20003 S2 2021 A1 Page 1 of 5
INFO20003 Semester 2, 2021
Assignment 1: ER Modelling
Due: 27 August 2021 6:00pm
Submission: Via LMS https://canvas.lms.unimelb.edu.au/

Covid contact tracing
To slow down the spread of COVID-19, the Victorian Department of Health made testing mandatory
for those who have recently been in close physical proximity to a patient who has tested positive.
Testing is mandatory for all close contacts even if the contacts do not exhibit any COVID symptoms.
This pre-emptive action is taken due to the contagiousness of the disease: a patient is likely to have
passed on the contagion to their contacts, and the infected contacts have the potential to infect
others even before they show symptoms. Testing and isolating the infected can stop the infected
individuals from spreading the disease early on.
The Department of Health has hired your team to design a MySQL database to store contact tracing
information along with the testing details, whereabouts of the contacts (to identify exposure sites)
and travelling details. With this data, the state of Victoria can curb the spread of infections. The
following specification has been provided to your team to assist in your design.
Person
Each person who has tested positive for COVID-19 must provide details of everyone they have been
in direct contact with during their infectious period (generally, the infectious period is considered to
be 14 days, but could vary depending on the strain). These contacts are referred to as ‘primary
contacts’. For each person (primary contact, or positive patient), their first name, last name, middle
name (if they have any), phone number(s), date of birth, residential address including house/unit
number, street number, street name, suburb, postcode and home state must all be stored. An email
address is also stored if the person has any.
If a person was added to the database because they were identified as a primary contact, the
database should also allow the government to determine which positive case identified this person
as a close contact.
One person can be a primary contact of multiple cases during different time intervals. For example,
John can be identified as a primary contact of Arial during her infectious period lasting from June 2nd
to June 17th and he can then again be identified as a primary contact of Andrew during Andrew’s
infectious period lasting from July 14th to July 30th. This detail will help the government establish

Covid tracing app INFO20003 S2 2021 A1 Page 2 of 5
connections between the cases to identify possible clusters. A previously infected person might also
become reinfected with covid after recovering, and thus have several infectious periods.
For each infected case, their symptoms must also be stored. The list of possible symptoms is
updated regularly as the new variants of corona virus emerge (e.g. cough, sore throat, fever,
tiredness, etc). The date of onset of any symptom in a person should also be stored, as well as when
the symptom ceased to exist. Some people may not have any symptoms.
Testing
Everyone who is identified as a primary contact must get tested, regardless of whether they exhibit
any symptoms or not. A person can get the test done multiple times from different testing centres.
The date of testing, the location of testing centre and the result such as “Positive”, “Negative” or
“Undefined” should be stored for each test. Poor sampling may result into an undefined result and
require a subsequent test.
In order to better guide people to the most appropriate testing centre, information such as the
approximate wait time in minutes for test, opening hours (e.g., “Monday: 09:00am-05:00pm,
Tuesday: 09:00am-05:00pm, ...”), and the mode of testing such as by appointment, drive through,
or drop in is stored for each testing centre. Due to less demand in the times when there is low COVID
positivity rate, some testing centres might not be operating at all. For such centres their status as
“operational” or “not operational” should be regularly updated to better represent the information
about the available centres.
Exposure sites
The department of health also needs information about the possible exposure sites to identify places
where there is a risk anyone may have been exposed to COVID-19. An exposure site is a location
that a positive case visited during their infectious period. For such an exposure, the department
captures when the case visited the site and when they have departed.
For each site, the name of the site, type of the location (e.g., shopping mall, shop, grocery store,
retail shop, hospital, parking lot, etc) and geolocation with latitude and longitude must be recorded.
The exposure sites are classified as Tier 1, Tier 2 or Tier 3. The classification of the exposure site
should be stored as it guides the next steps for the attendees e.g., testing, isolation or quarantine
requirements.
Interstate travel
With the situation changing daily in different states, it is vital to store up-to-date information to
manage the risks associated with interstate travel. Therefore, Victoria has decided to use a travel
permit system for anyone who is planning to travel to Victoria. It is a traffic light system that
designates postcodes across Australia as ‘green’, ‘orange’, ‘red’ or ‘extreme risk’ zones according
to their public health risk. This list is maintained actively, with new regions added, and old regions

Covid tracing app INFO20003 S2 2021 A1 Page 3 of 5
updated/removed regularly depending on changing circumstances. The system should store the
date when a region was updated to its current colour (however capturing the history of changes is
not required).
Travellers returning from an extreme risk zone cannot enter Victoria unless they have permission
granted by the Victorian government. Anyone travelling back from any other zone must obtain a valid
travel permit and may be subject to different restrictions and requirements, e.g., home isolation, hotel
quarantine, immediate testing if symptoms emerge within 14 days, etc. The system must store travel
details for all travellers entering Victoria, such as the date of entry to Victoria, the origin address of
their trip, and the destination address of their trip. Depending on the origin address, a red, orange or
green permit will be issued to the traveller. When travelling as families, each individual member of
the family will have their own permit issued. Each permit has a unique QR code for identification
stored as 24-character long hash. These permits are single-use and are valid for 14 days; therefore,
the date of issue should also be stored. The used permit should be marked to track the travellers for
any follow-up testing and the rest of the requirements. The traveller will be required to comply with
the conditions of their permit e.g., red permit holders are required to self-isolate as soon as they
reach their destination, get tested on the third day of the isolation period and again on day 13 of
isolation. For each such condition, the Department of Health stores an id, and description capturing
the details of the requirements.
Business Requirements
Your database design needs to be able to meet the business' needs to answer questions like:
• How many people have been tested positive so far?
• Which day in 2020 had the highest number of positive cases coming from ‘Royal
Melbourne Hospital’ testing center?
• Are there any people who got infected with covid multiple times in 2021?
• How many times was ‘Noble Park’ categorized as Tier 1 exposure in 2020? 

• What is the name of the person who had most primary contacts during their infectious
period?
• How many positive cases were fully asymptomatic?
• Which permit type (e.g., red, green, orange) was issued the most in July 2021? 

• Has “Alice Moore”, who travelled from a red zone area, undertaken testing on day 3 and
day 13 of her stay in Victoria as described in her permit conditions?
• How many permits were issued in 2020 but left unused?
• How many positive cases had previously returned to Victoria from interstate?
• Were there any travelers who tried to enter Victoria on a permit that got expired?
• Which state currently has the most red zone regions?


Covid tracing app INFO20003 S2 2021 A1 Page 4 of 5
Instructions:

Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as
described in the table below:
ER Physical Model with assumptions 80%
Conceptual Model in Chen’s Notation 20%
.mwb Physical Model File submitted Assignment Hurdle

• You are to analyse this business case and design a Conceptual ER Model in Chen’s
notation (can be hand drawn) as taught in class and a Physical ER Model for a MySQL
Relational Database in Crow’s foot notation (modelled with MySQL Workbench).
• You may list any assumptions you have made about the model on a separate page. There is
a 200-word limit for assumptions. Assumptions must not be used to simplify the assignment,
but only to justify your decision about any ambiguity in the study.
• As part of your submission, you must submit your final MySQL Workbench .mwb file of your
Physical ER Model.
NOTE: Items 1 (Conceptual Model & Physical ER model) and 2 (Assumptions) must be submitted
as a single PDF. The title of the PDF document must be your student id (e.g. 987654.pdf).
Similarly, the title of your .mwb file should be your studentid.mwb (e.g. 987654.mwb).


Covid tracing app INFO20003 S2 2021 A1 Page 5 of 5
Assignment Submission:
You are to submit the assignment under the Assignments tab on Canvas LMS. The submission will
require you to submit two files:
1. ONE PDF document named as your student id (e.g. 987654.pdf) containing: 

i. a legible (hand drawn, or otherwise modelled) picture of a conceptual model in Chen’s
notation
ii. a screenshot/export of your Physical ER Model done in MySQL workbench. Ensure that all
attributes are readable, and tables are fully expanded. 

iii. any assumptions you made (limit this to a maximum of 200 words) 

2. A copy of your final mwb MySQL Workbench file of the Physical ER model. 

Note: This model will only be used by staff in circumstances where the screenshot/export of your
model in the PDF is unreadable (i.e. if we have to open this file, a penalty of
10% will occur), so remember to include the export of your physical model in the PDF!
Note: If you fail to submit clear and legible models your assignment will be penalized.

Late submission
Unless you have an approved extension (see below), you will be penalised -10% of the total number
of marks in the assignment per day that your submission is late. For instance, if you received a 78%
raw score, but submitted 2 days late, you'd receive a 58% score for the assignment.
Requesting a Submission Deadline Extension
If you need an extension due to a valid (medical) reason, you will need to provide evidence to support
your request by 5 pm on 26th of August. Medical certificates need to be at least two days in length.
To request an extension:
• Email Farah Zaib Khan ([email protected]) from your university email address,
supplying your student ID, the extension request and supporting evidence. Please add
INFO20003 in the subject title. 

• If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email! 

Reminder: INFO20003 Hurdle Requirements
To pass INFO20003, you must pass two hurdles:
• Hurdle 1: Obtain at least 50% (15/30) for the three assignments (each worth 10%) 

• Hurdle 2: Obtain at least 50% (35/70) for the combination of the quizzes and final exam 

Therefore, it is our recommendation that you attempt every assignment and question in the exam.
GOOD LUCK!

欢迎咨询51作业君