Hospital Database Project
MIS 6326: Database Management The University of Texas at Dallas Dr. Radha Mookerjee 12/3/2013
Written By: Joel Ball, Leah Bowyer, Farhin Hasan, Faisal Khan, Zeeshan Faisal Khan, and Alycia Ranjendran
1|Page
Table of Contents Problem Description .............................. 3 Entity Relationship Diagram ................... 5 Relational Database Schema .................. 6 Data Input Screen Forms ........................ 9 Reports ................................................. ................................ ................. 18 Menu Screens ...................................... ................................ ...... 21 Member Contributions........................ 23
2|Page
Problem Description Our project will address the issue of updating a hospital system from a p aper based to a relational database. This database will contain customer d emographics, billing and insurance information, as well as their medical history. This database will allow admi nistrators to easily track both inpatient and outpatient records an d keep them up to date without information overlap. Administrators will be able to check which room an in-patient is currently occupying, and doctors will effortlessly be able to identify their patients from the pool, and determine which nurses are assigned to assist them on a specified date and shift. The database will require many different types of data. All of the patients contact information such as address, phone number, and email will be necessary. If applicable, the database will require all previous medical history that is on record for the patient, as well as all pharmaceutical and prescription information. All appointment information will be logged and saved to be pulled up when needed. If a patient is an In-patient, then the room in which they are currently residing, will be recorded. Billing and insurance information of the patient will also be stored. There will also be stored data for each doctor in the hospital, describing their specialty, spe cialty, insurance network and supervisors. There will also be information regarding nurses and the ward and rooms they are in charge of during their specified dates and shift times. Nurses are responsible for the care of all patients who are residing in their designated wards and rooms, and will have to report to their supervisor occasionally. There will be several different user roles within this database. The receptionist u ser role would have access to create and alter appointments, as well as keep customer contact information up to date. The doctor user role would allow him to view all contact, medical, prescription, and room location information about each patient under his/her care. The doctors 3|Page
will also be able to view the nurses assigned to the common patients. The nurse user role is also able to see patient details and the medical treatments and prescription they have received at the hospital. Finally, there will also need to be a billing user role who will be able abl e to view and use all payment information, including insurance policy and billing details such as total amount due, current balance, insurance deductibles, etc. B y implementing this database, this hospital will be able to update and check medical records, bill patients and check-in patients to wards and rooms and match them to their appropriate doctor. Assumptions: •
All patients must have insurance policy to be treated at the hospital.
•
A patient may undergo many treatments at the hospital.
•
A patient may or may not no t receive prescriptions from doctors at the hospital.
•
All patients have to put a preferred pharmacy in hospital record forms.
•
All nurses and doctors have supervisors.
•
Patients will receive each treatment cost under individual payments.
•
Patients can make payments in multiple installments.
•
Nurses are assigned to a ward during their shift.
•
Inpatients are checked-in to a room within an appropriate ward.
•
All doctors at the hospital specialize in a certain m edical field.
4|Page
Entity Relationship Diagram
5|Page
Relational Database Schema
6|Page
7|Page
8|Page
Data Input Screen Forms
Figure: Doctor Form
Figure: Doctor Patient Form
9|Page
Figure Doctor Specialty Form
Figure Insurance Payment Form
10 | P a g e
Figure Insurance Policy Form
Figure Nurse Ward Form
11 | P a g e
Figure 2 Patient Form
Figure 1 Nurse Form
12 | P a g e
Figure 4 Payment Form
Figure 4 Pharmacy Form
13 | P a g e
Figure 5 Prescription History Form
Figure 6 Prescription Form
14 | P a g e
Figure 7Ward Form
Figure 8Room Form
15 | P a g e
Figure 9Treatment Details Form
Figure 10Treatment Form
16 | P a g e
Figure 11Specialty Form
17 | P a g e
Reports Table 1Report showing patient appointment dates in relation to doctors according to their specialty field
Table 2Report showing which insurance network covers doctors under each specialty field
Table 3Report shows nurses and their assigned duty area during their work shift
18 | P a g e
Table 4Report shows list patient allergy and prescriptions given
Table 5Report shows patients who have a current remaining balance of more than $300
19 | P a g e
Table 6Report lists all current inpatients and their assigned ward and medical informatio information n
20 | P a g e
Menu Screens
1 Switchboard Menu
2 Another Main Menu
21 | P a g e
3 Form Menu
4 Report Menu
22 | P a g e
Member Contributions 1. Joel Ball – problem problem description, ERD, relational database schema, database constraints 2. Leah Bowyer – ERD, ERD, tables, relational database schema, overall editing 3. Farhin Hasan – tables, tables, ERD, forms, reports, menus, report, presentation 4. Faisal Khan – ERD, ERD, tables 5. Zeeshan Faisal Khan – ERD, ERD, tables, overall editing 6. Alycia Ranjendran – ERD, ERD, tables, relational database schema
23 | P a g e
