MIST4610GroupProject1

Team Name:

21484 Group 2

Team Members

1. Kush Santosh @kushsantosh
2. Megan Aldinger @meganaldinger
3. Patrick Daws @PatrickD93
4. Priya Dey @priyaadey
5. Lucy Moon @lucymoon505
6. Ansley Williams @ansleymw

Problem Description:

Our task at hand was to model and build a relational database system for an emergency healthcare clinic chain. At the core of this model is the Patient entity of the healthcare clinic, representing each new individual checked into the hospital as a patient. There are multiple patients in many different countries and cities. The database includes many different relationships with the patient, including prescription information and billing details. Information about patient appointments and their corresponding diagnostic tests are also available, along with additional detail about these scans. Our goal is to accurately model these relationships to swiftly deliver precise patient information, crucial in high-stakes environments like emergency healthcare clinics. Furthermore, we aim to enable seamless querying of this data, empowering doctors, nurses, and healthcare professionals with vital insights into doctor-patient relationships and the hospital's operational dynamics.

Data Model:

Our data model represents an emergency healthcare clinic chain. We have thirteen entities connected by one-to-many and many-to-many relationships. We have a patient entity that holds attributes such as their name, contact information, date of birth, and medical history. Patient has a one-to-many relationship with three different entities: Bill, Appointment, and Prescription. A patient can have many bills from the clinic (that include the total amount owed, payment status, etc), and a patient can have multiple prescriptions prescribed. Each prescription includes the name of the medicine and the dosage. Multiple prescriptions are in one pharmacy, which is displayed by the one-to-many relationship between the Prescription and Pharmacy entities. Pharmacy attributes include where it is located, the contact information, and the name of the manager.

One patient can have many appointments at the clinic. Appointment stores which patient is associated with the appointment, which medical staff is associated with the appointment, the date of the appointment, the reason for the visit, and its unique identifier. One appointment can have many diagnostic tests, which store the type of test, results of the test, which staff is involved, and which laboratory it took place in. In order to show where the diagnostic test takes place, there is a Laboratory entity connected as a one-to-many relationship with the Diagnostic Test entity. Laboratory holds attributes such as where the lab is, the lab phone number, and the lab manager. An appointment can also have many medical diagnoses. The MedicalDiagnosis entity holds when the diagnosis took place, and the professional’s assessment. Appointment also has a many-to-many relationship with the MedicalEquipment entity (which includes type of equipment, and equipment quantity). This is an identifying relationship, and it has an associative entity called ScansAndEvaluations, which holds the foreign keys of MedicalEquipment and Appointment.

One medical staff can have many appointments, and can perform many diagnostic tests. Information stored in the MedicalStaff entity include the name of the staff member, their specialization, and their contact information. Many medical staff can be in one training session, which includes the training session topic and date. Many medical staff are on one staff schedule, and the staff schedule entity holds the schedule date attribute.

Data Dictionary:

Queries:

1. Query 1 displays important prescription information for a patient (including multiple prescription occurrences for a patient). It lists patient's names, pharmacy IDs, medicines, dosages, and prescription IDs.

This information can be used to identify the pharmacy the patient regularly goes to, making it easy for places like this clinic to transfer details. It also displays a patient’s prescription IDs, medicine, and dosage. These details make it convenient for clinic doctors in case of an emergency. They are easily able to view their patients’ pre-existing prescriptions and medication dosages before administering any other treatments, in case of potential complications.

2. Query 2 displays the past appointment and corresponding billing information for patients. It lists patient IDs, reasons for visiting, appointment IDs, bill IDs, and the total amount they spent.

This would be helpful to gather data about why patients visit and how much they spend each visit.

3. Query 3 displays the full name of the staff member, their specialization, and the day they are scheduled to work if that employee specializes in either surgery or obstetrics.

This query is useful to see what days employees who specialize in surgery or obstetrics are working in the clinic. This important data can be used to see which days on the schedule do not have a staff member who specialize in either of the aforementioned subjects. These scheduled days can be used to coordinate an appropriate appointment day with potential patients trying to come into the clinic for either surgery work or to be looked at by an obstetrician.

4. Query 4 displays the patient names (concatenated and aliased as patientName) and their total bill amount for all customers who have a diagnosis of any type of cancer (lung cancer, throat, etc.). It also orders by the total amounts from highest to lowest so that the organization can prioritize their funds to help the patients in the most financial distress.

This query could be useful for the medical clinic who is partnering with an organization that is fundraising to pay the bills of patients with a specific illness.

5. Query 5 returns concatenated patient first and last names and dosage quantity of patients where their dosage is greater than the average dosage prescribed for the patients whose birthday is in the 2000’s.

This query is relevant and useful because the emergency room might want to monitor medical details for patients who are younger but taking higher dosages of medication than average. It can also help to pinpoint individuals who may need their dosages to be changed due to concerns with age.

6. Query 6 displays the patients and the number of appointments where the amount they spent was greater than the average bill amount.

This query is useful because it an help management identify patients who have spent large amounts of money to determine ways to reduce spending by seeing what is costing patients the most.

7. Query 7 displays the different reasons people may be visiting the clinic and the percentage of patients that tested positive for each of those reasons.

This query is useful because it would allow the clinic to allocate resources and staff to properly address certain conditions. If people test positive they likely will have return visits, and it will also help the clinic to know where they can help with informing people about prevention of diseases where it’s most prevalent.

8. Query 8 displays the date on the staff schedule and the number of employees working that day when there is less than three people scheduled to work.

This query shows the dates on the staff’s schedule where there are less than three employees to work in the clinic that day. This is an important number because it tells upper management, who design the staff schedule, to assign more employees to the appropriate days to ensure the clinic is fully staffed and every patient is properly cared for.

9. Query 9 displays the patient ID, patient name, the total amount they have spent, and the percentage of this amount out of the total amount spent by patients in Georgia.

This can be used to identify which patients in Georgia are spending the most amount of money and how much they contribute to the total amount.

10. Query 10 retrieves the names of labs and diagnostic test times. It additionally filters by a specific test time.

This query would be useful for a district lab manager who works for the healthcare clinic, and would like to analyze a trend in certain tests being done at different points in the year, improving practices by allocating resources accordingly, etc.

Database Information

Name of Database: ns_Sp24_21484_Group2

Procedure Call: CALL TP_Q1 ();