Database Application Development (HELPP PLEASE, URGENT!!)

Covid-19 Tracking System

Assumptions:

You can make the following assumptions in this project.

1. The database stores information about houses, each with a house ID, address, and zip code.
2. The database stores information about person, including person ID, house ID where the person lives, person name, phone number, and status where 1 means currently tested positive (i.e., the most recent test result is positive), 0 means currently tested negative (i.e., the most recent test result is negative), and null (no test so far so status is unknown).
3. The database stores information about a person's all past tests, including person ID, test date, and result (1 means positive and 0 negative).
4. The database stores information about events (could be gathering, party, etc.). Each event has an event ID, event name, date, and address.
5. The database stores information about a person participated in an event.
6. The database stores information about flights, including flight ID, flight date, flight number (a varchar type e.g., 'DL 345' is a delta 345 flight).
7. The database stores IDs of people on a specific flight.

Individual Features: each member of the group choose features for one member. If your team has fewer than five members, you don't have to choose all features (e.g., if your team has 4 members, you can pick features for member 1 to 4 or 2 to 5 etc.) Member 1: Feature 1: add a house to the database. Input includes house address and zip code. Please do the following: 1) first check whether there exists a house with same address and zip code, if so print a message the house already exists! 2) otherwise, insert the house into house table and print out new house ID. Feature 2: add a person to an existing house, including name of person, house ID, phone#, and set status to null. Please do the following: 1) check if a person already exists with same name and phone#. If so, print a message: 'person already exists! 2) otherwise, check whether there is a house with given ID. If so, just insert a row into person table with input name, phone number, the given house ID, and status as null. Otherwise print a message 'No such house!'. Member 2: Feature 3: Enter a new test result. Input includes person ID, test date and test result. First check whether the person ID is a valid. If not please print an error message. Next check whether an existing test exists with the same person ID and test date, if so just update the test result. Otherwise insert the test result into the database. Feature 4: Update status of a person. Input is a person ID. First check whether the person exists. If not print out an error message. Otherwise find the latest test result for the person and update the status of that person in person table to the latest result. Member 3: Feature 5: Enter a new event including date, name and address. First check whether the same event exists with the same name, date and address. If so, print a message the event already exists'. Otherwise insert a row into event table and print out event id. Feature 6: Print out people's names, phone numbers, and zip code for those whose current status is positive. Finally print out number of people tested positive per zip code. Member 4: Feature 7: Enter a list of people attending an event. Hint: use yarray data type as input parameter. Input includes event ID and list of person ids. First check whether the event exists. If not print a message saying the event does not exist. Next check each person ID, if it does not exist print a message saying the person does not exist. Next check whether the person id and event id combination are already in event_person table. If so print a message 'No need to insert'. Otherwise insert a row into person_event. Member 5: Feature 8: Given the name and phone number of a person, print out test dates and test results for this person. Please first check whether the person table has such a person with the input name and phone#. If not, please print an error message 'No such person'. Sort the result by test date in descending order. Feature 9: Given an input date, print out the accumulated number of positive cases by that date. Hint: Accumulated number of positive cases means the number of distinct people who have tested positive by the input date. E.g., if input date is date '2021-5-1', then anyone tested positive on or before that date will be counted. Group Features: if your group has five members, your group need to implement all five group features. If your group has four members, your group need to implement four of the five group features (you can pick any four). Feature 10: Print out names, phone numbers, house ID, and status of people who live in the same house as anyone whose current status is 1 (tested positive). Feature 11: Given a start and end date, print out names, phone number, status, and flight ID of people who were on the same flight with someone whose status is 1 (tested positive) and the flight date is between the start and end date. Feature 12: Given a start and end date, print out name, phone number, status and event ID of people who attended the same event with someone whose status is 1 (tested positive) and the event date is between the start and end date. Feature 13: Given an input date, print out names of people who are considered new cases on that date. A new case means a person tested positive on that date but never tested positive before that date. For example, if the input is May 5th 2021, and John tested negative on May 3rd 2021 but positive on May 5th 2021, and Alice tested positive on May 5th 2021 but never tested before, then both John and Alice should be counted. Feature 14: print out names of people who have recovered. A person has recovered if the person tested positive before but the current status is 0 (not positive). Individual Features: each member of the group choose features for one member. If your team has fewer than five members, you don't have to choose all features (e.g., if your team has 4 members, you can pick features for member 1 to 4 or 2 to 5 etc.) Member 1: Feature 1: add a house to the database. Input includes house address and zip code. Please do the following: 1) first check whether there exists a house with same address and zip code, if so print a message the house already exists! 2) otherwise, insert the house into house table and print out new house ID. Feature 2: add a person to an existing house, including name of person, house ID, phone#, and set status to null. Please do the following: 1) check if a person already exists with same name and phone#. If so, print a message: 'person already exists! 2) otherwise, check whether there is a house with given ID. If so, just insert a row into person table with input name, phone number, the given house ID, and status as null. Otherwise print a message 'No such house!'. Member 2: Feature 3: Enter a new test result. Input includes person ID, test date and test result. First check whether the person ID is a valid. If not please print an error message. Next check whether an existing test exists with the same person ID and test date, if so just update the test result. Otherwise insert the test result into the database. Feature 4: Update status of a person. Input is a person ID. First check whether the person exists. If not print out an error message. Otherwise find the latest test result for the person and update the status of that person in person table to the latest result. Member 3: Feature 5: Enter a new event including date, name and address. First check whether the same event exists with the same name, date and address. If so, print a message the event already exists'. Otherwise insert a row into event table and print out event id. Feature 6: Print out people's names, phone numbers, and zip code for those whose current status is positive. Finally print out number of people tested positive per zip code. Member 4: Feature 7: Enter a list of people attending an event. Hint: use yarray data type as input parameter. Input includes event ID and list of person ids. First check whether the event exists. If not print a message saying the event does not exist. Next check each person ID, if it does not exist print a message saying the person does not exist. Next check whether the person id and event id combination are already in event_person table. If so print a message 'No need to insert'. Otherwise insert a row into person_event. Member 5: Feature 8: Given the name and phone number of a person, print out test dates and test results for this person. Please first check whether the person table has such a person with the input name and phone#. If not, please print an error message 'No such person'. Sort the result by test date in descending order. Feature 9: Given an input date, print out the accumulated number of positive cases by that date. Hint: Accumulated number of positive cases means the number of distinct people who have tested positive by the input date. E.g., if input date is date '2021-5-1', then anyone tested positive on or before that date will be counted. Group Features: if your group has five members, your group need to implement all five group features. If your group has four members, your group need to implement four of the five group features (you can pick any four). Feature 10: Print out names, phone numbers, house ID, and status of people who live in the same house as anyone whose current status is 1 (tested positive). Feature 11: Given a start and end date, print out names, phone number, status, and flight ID of people who were on the same flight with someone whose status is 1 (tested positive) and the flight date is between the start and end date. Feature 12: Given a start and end date, print out name, phone number, status and event ID of people who attended the same event with someone whose status is 1 (tested positive) and the event date is between the start and end date. Feature 13: Given an input date, print out names of people who are considered new cases on that date. A new case means a person tested positive on that date but never tested positive before that date. For example, if the input is May 5th 2021, and John tested negative on May 3rd 2021 but positive on May 5th 2021, and Alice tested positive on May 5th 2021 but never tested before, then both John and Alice should be counted. Feature 14: print out names of people who have recovered. A person has recovered if the person tested positive before but the current status is 0 (not positive)