2023年11月25日

数据库代写 | INFO20003 Assignment 1: ER Modelling

本次澳洲代写是数据库ER建模的assignment，需要用到MySQL和Workbench

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
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.

……