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.

Assumptions:

You can make the following assumptions in this project.

The database stores information about houses, each with a house ID, address, and zip code.

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

The database stores information about a person's all past tests, including person ID, test date, and result (1 means positive and 0 negative).

The database stores information about events (could be gathering, party, etc.). Each event has an event ID, event name, date, and address.

The database stores information about a person participated in an event.

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

The database stores IDs of people on a specific flight.

TO DO:

Design Document which includes the following:

a. ER diagram of the database. You dont 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 dont need to implement any of these procedures at this point.