Covid-19 Tracking System
You will form groups of 4-5 to develop a database system a contact tracing system. You will design the database, insert some sample data, and implement a set of required features. Each feature will be implemented as one or more Oracle PL/SQL procedures/functions. You do NOT need to write a graphic user interface. You also need to provide statements to execute your procedures.
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.
There are two types of features: individual features and group features. Individual features will be assigned to individual group members and will be graded individually. Group features will be assigned to the whole group and graded group-wise. The first two deliverables are also graded group-wise.
Each member of the team should contribute more or less equally. It is unfair for a few members to do most of the work while others do less. You will be asked to evaluate your teammate’s effort at the end of the project. The instructor will adjust the grade based on the evaluation. Normally if most of your teammates agree that you do not contribute at all or contribute too little (e.g., your group has 4 members and you contribute only 5%), you may lose up to 80% of your group-wise grade. If your teammates agree that you contribute much more than anyone else (e.g., your group has 4 members and you contribute 40%), you may gain up to 20% of your group-wise grade (but not exceeding 100% of project grade). Multiple peer evaluations may be conducted throughout the semester to determine the contribution of each team member.
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.)
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!’.
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.
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.
Feature 7: Enter a list of people attending an event. Hint: use varray 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.
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).
There will be 4 deliverables will be due midnight of the due date. Delayed submission will result in a penalty of 30% of your score (e.g., if your score for part 2 is 20 but you are late, your score will be 14) within the first week after the deadline and will not be accepted after that. The final presentation (D3) is due on 4/26 and no delay is allowed. All team members are required to participate and demo their own components. You need to mention member number and Feature number and Name in each deliverable.
1. 10%. Due 2/22. Project Management Schedule.
a. Include team members and a timeline showing each phase of your project with its activities and time duration, for the entire effort.
b. It is expected that every member should participate in all phases of the project.
c. Please specify which feature is assigned to which member (for group features it is still possible to assign a lead for each feature).
d. Activities should include system design, populating tables, writing code, testing code, running example queries, writing documents, preparing for presentation, etc. Smaller milestones shall be set for deliverable 3 and 4.
e. This deliverable will be graded based on whether items a) to d) are included and whether the schedule is reasonable (e.g., enough time such as 2-3 weeks are left for testing and integration).
2. 25%. Due 3/8. Design Document which includes the following:
a. ER diagram of the database. You don’t have to follow exact notations of ER diagram, but need to show tables, columns, primary keys, and foreign key links.
b. SQL statements to create database tables and to insert some sample data (at least 3 rows per table). Please include drop table and drop sequence statements before create table, create sequence and insert.
c. Specification for each required feature. The specification should include a description of input parameters and output (usually printing a message), and a few test cases (normally there should be one normal case and a few special cases). You don’t need to implement any of these procedures at this point.
3. 35%. Due 4/26. Presentation of database design and demonstration of each feature.
I will post demo schedule on blackboard. To demo each feature, you need to prepare a couple of test cases, usually one normal case and the rest as special cases. For each test case you need to be able to explain why your answer is correct (this can be typically done by showing some tables or screen output). The instructor may ask you to only demo some of the features but you need to have all features ready.
4. 30%. Due 5/3. Please also upload final code through blackboard. The code should include:
a. Drop table and sequence statements to drop tables if they exist (remember to use cascade constraints).
b. Create table statements and create sequence statements
c. Insert statements
d. Create procedure statements (with code for the procedures). Each feature can be implemented as one PL/SQL procedure (in the procedure you may call other procedures or functions). Please include some comments in your code explaining the major steps. You should use create or replace to avoid procedure name conflict.
e. Test script to show that all your features work correctly. The script shall include some examples to test different cases. E.g., for feature 1, one example for new user (email is not in database) and one example for existing user (using existing email). Please include:
i. PL/SQL script to call the appropriate PL/SQL procedure for this feature. E.g., exec procedure-name(parameter values)
ii. Explanation of what should be the correct output. The output could be updated tables (you can have some select statement to show the updated tables), some print out, etc.
iii. Make sure you have tested your examples from beginning to end. Remember that database tables may have been changed in the process. So you may need to start with a clean database (i.e., right after you execute all the drop table, create table, and insert statements).
What I look for while grading deliverable 3:
• Clarity of presentation
• Timeliness and smoothness of demo.
• Correctness of each demoed feature including explanation.
For deliverable 4:
• Existence of code
• Comments: Both descriptive and inline for every procedure/function
• Software quality
• Whether it is correct (giving correct results).
• Whether it is complete and clear.
• Efficiency of code. You shall not use too many SQL statements, and you shall put as much work as possible in SQL. For example, if you can do a join, do not use two select statements and then do a join in your program.
• Whether it has considered all special cases such as whether a user has already registered in feature 1.
1. Start early. Do not wait until last month to start coding. Do not wait until one week before the demo to start putting things together. Past experiences show that more than 50% of time should be devoted to testing and putting things together.
2. Learn how to debug SQL and PL/SQL code. You can insert screen output statements to check intermediate results. Oracle also returns error messages and error code. You can google the error messages and error code to find possible causes. You may also use Oracle SQL Developer which allows you to insert break points during debugging.
3. It is highly recommended to use SQL Developer rather than the web interface for the project.
4. Use homework, in class exercises, and programs in slides as templates of your PL/SQL program. For example, if you need to write a cursor, find a cursor example and use it as a starting point.
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.