For a detailed backstory on how the pandemic inspired me to design this hospital database, please read my article on Medium: How the Pandemic Led Me to Design a Hospital Database.
The goal of this project is to create a solution to 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.
Note: The story and characters in this project are not based on real people or events. Any resemblance to real people or events is purely coincidental.
- 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.
-
Using MYSQL To verify if the sql_mode=only_full_group_by is activated for, you should execute the following query:
SELECT @@sql_mode; //localhost
- Output: IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (If you don't see it, it means it is deactivated)
-
To Fix this to avoid any errors type in:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
> Restart MySQL Workbench.
-
-
- 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.
- Specify 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 and 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 minimize 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 zip code.
