Background
A major hospital is looking to streamline its emergency room procedures and introduce IT
support systems that will hopefully increase efficiency, patient throughput and medical
outcomes while tracking costs and resource utilization. You are tasked to develop a
database solution with a GUI frontend that allows hospital workers to achieve those goals.
Task
You will look at the given problem specification below, design an E/R model that
corresponds to this problem, implement your model in a relational database and build a GUI frontend that interacts with the database to accomplish the specified solution requirements.
You will populate the database from a SQL command file with enough data to be able to
demonstrate your system and its functionality at the lab presentation.
Problem specification
Patients arrive at the emergency room either by showing up on their own or else by
ambulance. When they arrive, the triage nurse assigns every patient a priority based on the
severity of their condition (with 5 being the gravest and 1 being the least pressing) and
assign them to a medical treatment team. The emergency room contains 5 different such
teams and every team is competent to deal with a 3 different medical issues, out of a list of 10 different medical issues (eg. impact trauma, chest pains, seizures, allergic reactions
a.s.o) in total. The patients are then put in their assigned medical team’s queue and when
they reach the top of the queue they will get to see their medical team. Patients with higher
priorities will be moved up the queue before patients with lower priorities, but inside every
priority classification, they should move up on a first come, first serve basis. During
treatment, the team may utilize up to 3 different medical procedures (procedures are unique for every medical issue) and decide to provide the patient with up to 3 drugs (drugs are common for all issues). After the immediate treatment, the physician in charge of the team decides whether to release the patient back home, or else to commit the patient to a general hospital ward for further care.
Solution requirements
You will have to motivate your E/R design choices and present your reasoning about your
chosen entities and how they relate to each other. You will create a separate SQL command file that allows you to empty and fill this database with a set of dummy data required to demonstrate your solution at any time.
This file should contain all that you need to to have in the database in order to run your programs (eg. teams, medical specializations, medical procedures for the various medical specializations, drugs, patients in queues a.s.o). Note that every medical procedure and every drug has a cost associated. You will then create programs with the following interfaces (note that all interface choices must be populated from the database and not hard-coded) and background processing tasks:
* A triage nurse’s form: This interface will allow the nurse to enter the patient’s name, gender and age. In addition, using menu choices, the nurse will also assign patient priority and which medical issue the patient has. Once this is done, the nurse will be presented with the queues of those medical teams that are competent to handle that medical issue and will be able to assign the patient to one of those (the presentation must show the patients already waiting in each queue and their priorities).
* Once the nurse submits this form your program will simulate the assigned medical team
queue advancing, by advancing the time the patient has to wait by (10 minutes * priority
assignment) for every patient before him or her in the queue. Remember to account for
priority queue rules correctly.
* A team doctor’s form: using menu choices the team physician will be able to specify which medical procedures the patient required and which drugs were used. The physician will also choose whether the patient gets sent home or committed to further care.
* Once the physician submits this form your program will generate a report that shows the
patient’s vital statistics, the assigned medical issue, how long the patient had to wait, which treatments and drugs were used and their costs and finally if the patient was sent home or not. That data will also be logged in a specific log in the database. This log must remain uncleared when you use the your SQL command file to restore the rest of your database to its initial condition.
Presentation
At the lab presentation, you will have a paper copy of your E/R diagram and a paper copy of
your CREATE TABLE statements ready. Your database will be running and populated from
your SQL command file. You will fill your interfaces as the lab assistant instructs you and
show that your program fulfills the specification. You will explain how your program works
both in general in in specific as instructed by the lab assistant. Be ready to use your SQL
command file to restore your database to its initial condition, and to show that this does not affect the data in the patient report log.