This project aims to help reduce COVID-19 exposure at hospitals and minimize disease transmission to and between medical professionals. This database provides medical staff with access to up-to-date, accurate patient information such as admission dates, symptoms, treatments, and the assigned medical professional.
- MySQL Workbench >= 8.0.
- Scripts included in the SQL Folder.
-
Database Creation
- From MySQL Workbench open the create_db_hospital.sql script.
- Execute the script.
-
Patient Information
- From MySQL Workbench open the patient_information.sql script.
- Execute the script.
-
Stored procedures and views
- From MySQL Workbench open the stored_procedures_views.sql script.
- Execute the script.
-
- Created a Statement of Requirements by summarizing user requirements.
-
- Provided a high-level overview of the hospital domain and the things involved in this domain.
-
Building a Logical - Physical Model
- Using MySQL, I created a Logical Model by defining the entities and relationships.
- Inserted primary keys and foreign keys.
- Specified the data type for each attribute.
- Guaranteed flexibility for future change, applied Normalization (1NF,2NF,3NF) forms to prevent data duplication.
-
Nurse 1: “I want to know the name of COVID patients, their symptoms, doctors that treated them, including the date and method of their last COVID test so that medical staff can access this information and to better protect ourselves from exposure and plan.
-
Nurse 2: “We want to limit COVID-19 exposure on our floor by identifying all COVID patients and storing information such as names, numbers, and room/floor number they are located at because we have limited rooms and beds for COVID patients.”
-
Social Worker: “Our department requests a database that stores phone numbers of patients' work numbers and immediate family to advise them to get checked for COVID as well as quarantine."
-
Doctor: “I am looking for the safety of my fellow doctors and nurses. COVID is extremely contagious, therefore I request detailed info of doctors and staff who treat COVID patients each day and grant our staff access to this information to limit the exposure of this virus.”
-
Hospital CEO: “We are trying to improve our system and operate effectively during the COVID-19 pandemic and follow infection prevention and control recommendations tailored to their setting minimizing disease transmission to patients, HCP, and others. If you could develop a database that can help with minimizing the amount of exposure to others.
- Patient:
- Information: Full Name, Phone Number, Home Address, Contact Information and Relationship.
- Symptom:
- Symptom Name
- Symptoms Start Date
- Testing:
- COVID Testing Date
- COVID Testing Method
- Treatment:
- Treatment Name
- Treatment Date/Time
- Medical Professional:
- Doctor: Full Name
- Nurse: Full Name
- Shift Start/End
- Admissions:
- Floor #
- Room #
Pass Parameters to a Stored Procedure to return a result set, Like viewing patients in a single zipcode.
