$30
Comp353-Databases Solved
Project Description
This system builds on and extends the application developed in the warm-up project. It adds some new functionalities and requires a Graphical User Interface to facilitate user interaction with the system.
In the main project, you develop a database system, called COVID-19 Public Health Care Population Vaccination System (C19PVS), to help the public health Care System with the process of vaccinating the population.
The application must maintain personal information about the population registered with the public health care system, information about the population involved in the vaccination process against the COVID-19 pandemic, and information about the public health care employees involved in the vaccination process. The personal information for people registered with the public health care system includes first-name, last-name, date of birth, Medicare card number, date of issue of the Medicare card, date of expiry of the Medicare card, telephone-number, address, city, province, postal-code, citizenship, and email address. Also, history of infection with COVID-19 for every person including date of infection should be maintained by the system. A person could be infected many times.
A person could be vaccinated whether she/he is registered with the public health care system or not. A person who gets vaccinated and is registered with the public health care system must provide her/his Medicare card number. Otherwise, a person who gets vaccinated and is not registered with the public care system must provide her/his passport number, first-name, last-name, middle-initial, date of birth, telephone-number, address, city, province, postal-code, citizenship, and email address. Assume first-name, last-name and middle-initial are unique for every person.
Also, the application must maintain information whether the person being vaccinated has been infected in the past or not, and if she/he has been infected the application need to store the date of the infection and the type of infection. If the type of infection is known then it will be stored, otherwise the type will have the default value UNKNOWN. The type could be {Alpha (B.1.1.7), Beta (B.1.351), Gamma (P1), Delta (B.1.617.2), MU (B.1.621), R.1, etc.} variant, or any other value of the variants known so far. A person could be infected more than once.
Also, the application must maintain information about the public health worker who is giving the vaccination, the type of Vaccination that is given and the dose number as well with the date, location and lot number of each dose given. A person who gets vaccinated outside the country should be able to register her/his vaccination in the system by providing the type of Vaccination she/he has been given and the dose number as well with the date, location, country, and lot number of each dose given. A person could be vaccinated one dose in one country and another dose in the system or vice versa. A person should have a waiting period of at least two weeks between two doses. The type of vaccinations could be Pfizer, Moderna, AstraZeneca, Johnson & Johnson, etc. Also, the dose number could be 1, 2, or more. For example: Alfred McDonald could have taken the first vaccination dose Pfizer on the 20th of January 2021 at CLSC Montréal South, and the second vaccination dose Moderna on the 25th of April 2021 at Olympic Stadium Montréal. The application must maintain the list of approved vaccinations as well as the date of approval of the vaccination and a description of the vaccination type. A vaccination type could have two statuses: SAFE and SUSPENDED. If status is set to SUSPENDED, then the application must maintain the date of suspension and only vaccination type with SAFE status could be given.
Also, the application must maintain information about Public Health Care facilities where the vaccination is being performed. Information about the Public Health facilities could include name, address, phone number, web address, type (Hospital, clinic, or special installment), capacity (Maximum number of people that could be vaccinated at the same time), operating hours and days (ex: Mon-Fri 8:00 to 20:00, Sat-Sun: 8:00 to 17:00, etc.), public health workers assigned to the location and the manager of the location. Each facility can have only one manager at any moment in time. Each facility can have one of two categories: by appointment only or walk-in and appointment. Facilities that are by Appointment only can provide vaccination for people who already have appointment with the specified facility. Facilities that are walk-in and appointment can provide vaccination for people with or without appointment with the specified facility. The system must have the capability to book appointments for people at specified facilities to perform the vaccination. Booking should take into consideration the existing bookings, and the available bookings slots. Also, bookings take into consideration the operating hours of each facility. Each vaccination requires 20 minutes slots by each nurse (normally is much smaller, but we set it to 20 minutes for simulation purposes, or you can use a global variable that is set by your client and set the default to 20 minutes). People with appointments have priorities over people without appointments for getting vaccination in facilities that accept walk in people. Also, the system must have the capability to cancel bookings for people who already have reservations. At the time of vaccination, a facility must have at least one secretary assigned to the facility to do the registrations for people to be vaccinated, and one nurse assigned to provide the vaccination. Some facilities might have more secretaries, might have security personnel assigned to organize and maintain order. Many nurses could be available at the same time at the facility that are providing the vaccination for people. The number of nurses scheduled for each facility at the same time could not exceed the maximum capacity of the facility.
All public health care employees must be registered with the public health care system. A public health care employee can work at one or many facilities at the same time. The start date and end date of work for every public health worker working at each facility must be recorded. A public health worker cannot be scheduled to work at two different facilities at the same time. A public health worker could be a nurse, a manager, a security, a secretary, or a regular employee (include all other tasks that might be needed to operate a facility). Only nurses can give vaccinations to people. A nurse must be vaccinated at least one dose of vaccination for COVID-19 before she/he is able to provide vaccination for people. A public health worker must have a Social Security Number (SSN), an employee ID, and an hourly rate. The employee ID for every public health worker is unique to the facility she/he works at. If a public health worker works at more than one facility, then she/he will have one Employee ID for every facility she/he works at. A public health worker could work in a facility for a period, then leave, then come back depending on the availability and the need for the facility. The history of employment for the public health workers in each facility must be recorded. A public health worker uses the same Employee ID she/he had in the past if she/he come back to the same facility. A public health worker could have different hourly rate when working at different facilities. The hourly rate for the public health worker at one facility could be the same, greater than or less than her/his hourly rate at another facility.
Other information for people includes Age Group. A person can belong to only one Age Group at any moment in time. The Age Group for a person is decided at the date of the vaccination. This is needed to decide who can take a vaccination and who must wait before taking a vaccination. There are 10 Age Groups as follows: Age Group 1 is for people whose age is 80 years old and above; Age Group 2: for people between 70 and 79 years old; Age Group 3: for people between 60 and 69 years old; Age Group 4: for people between 50 and 59 years old; Age Group 5: for people between 40 and 49 years old; Age Group 6: for people between 30 and 39 years old; Age Group 7: for people between 18 and 29 years old; Age Group 8: for people between 12 and 17 years old; Age Group 9: for people between 5 and 11 years old and Age Group 10: for people between 0 and 4 years old.
At any moment, only one eligible Age Group value is set for every province which could be changed over time. The default value for Age Group for every province is set to 0 which means that there is no vaccination available in the province due to the lack of vaccines or due to any other criteria set by the province. A person can be vaccinated if she/he belongs to an Age Group that is smaller than or equal to the current Age Group value set in the province where the facility doing the vaccination is located. For example, if a person is to be vaccinated in stadium of Montréal, then her/his Age Group should be smaller than or equal to the Age Group set in the province of Québec. Or, if a person is to be vaccinated in a location in downtown of Toronto, then her/his Age Group should be smaller than or equal to the Age Group set in the province of Ontario. Public Health Workers could be vaccinated without following the rules set by the province for the Age Group.
What you should do:
In the above, we provided the minimum requirements for this application. You could add more details if you find suitable and useful. Considering the information so far, do the following steps in your database design process:
1. Develop an E/R diagram to represent the conceptual database design for the above application.
2. In the diagram, mark various constraints (keys, functional dependencies, cardinalities of the relationships, etc.) Identify any constraints that are not captured by the E/R diagram.
3. Convert your E/R diagram into a relational database schema. Make refinements to the DB schema if necessary. Identify various integrity constraints such as primary keys, foreign keys, functional dependencies, and referential constraints. Make sure that your database schema is at least in 3NF.
4. Is your database in BCNF? (Explain why/why not)
5. If your database is not in BCNF, then show that it is in 3NF.
Formulate and evaluate the following SQL DDL and DML commands against your database in which every relation is populated with 'sufficient' representative tuples.
1. Create/Delete/Edit/Display a Person (this includes the record of infection for the person).
2. Create/Delete/Edit/Display a Public Health Worker.
3. Create/Delete/Edit/Display a Public Health Facility.
4. Create/Delete/Edit/Display a Vaccination Type (ex: Pfizer, Moderna, etc.)
5. Create/Delete/Edit/Display a Covid-19 infection Variant type (ex: Alpha variant, etc.)
6. Create/Delete/Edit/Display an Age Group.
7. Add/Delete/Edit a province as well as display Provinces.
8. Set a new Age Group for a Province.
9. Create/Delete/Edit/Display an appointment for vaccination (An appointment could be taken only if the person to be vaccinated fulfill the requirements of the Age Group of the province at the day of vaccination. The person taking the appointment will belong to the Age Group allowed by the province on the day of the appointment. These rules do not apply to public health workers).
10. Create/Delete/Edit/Display Assignment for a public health worker (Schedule of work from a given period of time to a given period of time, including multiple facilities if applicable).
11. Display the bookings and the availability of spots for vaccination for a given facility from a given period of time to a given period of time.
12. Display the first available spot for vaccination in a given facility starting from a given date (for example give the first available spot in Montréal West facility starting December 20th, 2021. The answer could be: First available spot at Montréal West facility starting December 20th, 2021 is Wednesday January 5th, 2022 at 3:20 PM).
13. For a given facility and on a given date, display the Nurses that work for the facility but are not assigned to the facility on the specified date. The answer should include the nurse’s employee ID, first name, last name, email address and hourly rate. The output should be displayed in ascending order by hourly rate.
14. For a given date, display all the facilities that do not have any nurse scheduled to work at the facility. The answer should include the facility name, address, phone number, capacity, and operating hours for that date.
15. For a given facility and on a given date, display the schedule for the facility. The schedule includes all nurses’ name assigned to the facility and schedule for each nurse, all other public health workers name, duty (such as secretary, security, etc.), and schedule for each worker. Also, the schedule for people who have appointments to be vaccinated on that date.
16. Perform a vaccine to a person with an appointment. (This requires the firstname, last-name, middle-initial of the person and the location where the appointment is taken.
17. Perform a vaccine to a person without an appointment. (The person could be registered or non-registered with the system. This requires the first-name, lastname, middle-initial of the person and the location where the person want to be vaccinated.
18. Provide a report that display for each nurse in the system, the nurse’s name telephone number, and the total number of vaccines that the nurse has provided. The report should only include nurses that have performed at least 20 vaccinations in total. The report should be displayed in descending order by total number of vaccines performed by the nurse.
19. Give a detailed report of all the facilities in the city of Montréal. The report should include the name, address, type, phone number and capacity of the facility, the total number of public health workers working in the facility, the total number of doses people have received in the facility and the total number of doses scheduled by people to be vaccinated in the facility in the future. The report should be displayed in ascending order by the total number of doses given by each facility.
20. For a given person, display the bookings if applicable (include the facility name, address of the facility, day, and time of the booking), the history of vaccinations if applicable, and the history of infections if applicable.
A sample Output with appointment:
Health and Social Services Proof of Vaccination against COVID-19
User Information
Name Dimitri, Rodrigez
DOB 1 / 12 / 1975
Appointment :
Date 12/11/2022 @ 10:20 AM
Location Clinique de vaccination de Montréal-Nord
Adresse 123 Lacordaire, Montréal, Québec, A1B2C3
Vaccine Administered Dose # 1
Name PB COVID-19
Code Pfizer
Lot CW0123
Date 12/6/2021
Location Arena Martin Brodeur
Positive COVID-19 Diagnostic
Positive COVID-19 Diagnostic on January 10, 2021
A sample Output without appointment:
Health and Social Services Proof of Vaccination against COVID-19
User Information
Name Dimitri, Rodrigez
DOB 1 / 12 / 1975
Vaccine Administered Dose # 1
Name PB COVID-19
Code Pfizer
Lot CW0123
Date 12/6/2021
Location Arena Martin Brodeur
Vaccine Administered Dose # 2
Name PB COVID-19
Code Pfizer
Lot CW0999
Date 12/11/2022
Location Clinique de vaccination de Montréal-Nord
