Table of Contents
Worked collaboratively in a team of three developers via Github to develop a database system called COVID-19 Public Health Care System (covid19_phcs). A graphic user interface was also provided to make it easier for users to interact with the system. The goal of the application was to help the public health administration monitor and control the spread of COVID-19.
This web application portion of the project was developed in Microsoft's Visual Studio Code. The database portion was built in DBeaver.
- MySQL
- PHP
- JavaScript (jQuery)
- HTML
The MySQL code to create tables and triggeres and populate tables is provided in MySQL_Create_Tables_Triggers.sql. The MySQL code to perform queries is provided in MySQL_Display_Tables_Queries.sql.
The Alert table describes the four alert states: green, yellow, orange, red.
The Region table maintains an alert history of alerts for a given region (e.g., Montreal, Monteregie, Laurentides, etc.).
The Address table holds information such as house number, street name, city, etc. It also has field for the corresponding region. This way, if the alert state for a region changes, all individuals with addresses belonging in that region can be notified.
The Person table holds personal information for an individual such as ID, first name, last name, medicare number, date of birth, email, etc.
The LivesAt table represents the relationship between a person and their address.
The IsParentOf table represents the relationship between two people, a child and their parent.
The PublicHealthWorker table contains the IDs of all people who are also public health workers.
The PublicHealthCenter table holds information on facilities such as ID, name, phone number, type (hospital, clinic or special installment), etc.
The WorksAt table represents the relationship between public health centers and the public health workers it employs. A public health center can hire more than one worker and a worker can work for more than one center.
The Diagnosis table holds information regarding a COVID test which includes the the person tested, the public health center at which the test took place, the public health worker who performed the test, the test result (positive or negative) and relevant dates.
The Messages table simulates an email service. It contains information such as date, region, first and last name of the person receiving the message, email, message description, etc. For instance, when a region's alert state changes, a message is added for each person who lives in that region informing them of the change and of the new guidelines that come with it. Furthermore, when someone gets a diagnosis (be it positive or negative), a message is added for that person. Inserting into the Messages table is accomplished through SQL triggers.
The HealthRecommendations table contains instructions that a self-isolating individual is asked to follow to prevent the spread of COVID-19.
The Symptoms table documents the known symptoms of COVID-19 classified under three categories: main, other, non-listed. Whenever an infected person is diagnosed with symptoms not yet recorded in the database, the system updates the table to include these new symptoms. This is done through a form completed by the person on the web application.
The SymptomsHistory table maintains two weeks of symptom history for people who are diagnosed positive for COVID-19. This is done through a form completed by the person on the web application.
Initially, the Messages table is empty and the Region table only has two entries.
An alert can only be upgraded or downgraded one level at a time. Since we tried to set Montreal's new alert state as 3 when it used to be 1, an
error message is displayed.
Setting a new alert state of 2 works however. After changing the alert state, the system notfies all individuals living in that region (Montreal).
This process is simulated using the Messages table which now has an entry for each person living in Montreal.
To fill in the COVID-19 symptoms form, the user must first login using their medicare number and date of birth.
After testing positive for COVID-19, the individual must fill up the symptoms form for the fourteen consecutive days following the diagnosis.
Since conjunctivitis is a new symptom, the system adds it to its database in the Symptoms table under 'non-listed'.
The SymptomsHistory now has an entry for the person that just filled in the form.
In MySQL_Display_Tables_Queries.sql, there is a list of queries that demonstrate the how our relational database can be used to obtain information.
Ex.: Query 11. Give a list of all the people in a specific address. For every person, provide the their personal information and their parents' full names.
