Case study: Create a Conceptual ER diagram using Chen's notation

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 14h to July 30". 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. 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 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? Sp 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?S 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