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 (farah.khan@unimelb.edu.au) 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!