hospital database project

(1)

Hospital Database Project

MIS 6326: Database Management

The University of Texas at Dallas Dr.

Radha Mookerjee

12/3/2013

Written By:

(2)

Problem Description ... 3

Entity Relationship Diagram ... 5

Relational Database Schema ... 6

Data Input Screen Forms ... 9

Reports ... 18

Menu Screens ... 21

Member Contributions... 23

(3)

Problem Description

Our project will address the issue of updating a hospital system from a paper based to a relational database. This database will contain customer demographics, billing and insurance information, as well as their medical history. This database will allow administrators to easily track both inpatient and outpatient records and 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, 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 user 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

(4)

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 to view and use all payment information, including insurance policy and billing details such as total amount due, current balance, insurance deductibles, etc. By 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 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 medical field.

(5)

(6)

(7)

(8)

(9)

Data Input Screen Forms

Figure: Doctor Form

(10)

Figure Doctor Specialty Form

(11)

Figure Insurance Policy Form

(12)

Figure 2 Patient Form

(13)

Figure 4Payment Form

(14)

Figure 5 Prescription History Form

(15)

Figure 7Ward Form

(16)

Figure 9Treatment Details Form

(17)

(18)

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

(19)

Table 4Report shows list patient allergy and prescriptions given

(20)

(21)

Menu Screens

1 Switchboard Menu

(22)

3 Form Menu

(23)

Member Contributions

1. Joel Ball – problem description, ERD, relational database schema, database constraints 2. Leah Bowyer – ERD, tables, relational database schema, overall editing

3. Farhin Hasan – tables, ERD, forms, reports, menus, report, presentation 4. Faisal Khan – ERD, tables

5. Zeeshan Faisal Khan – ERD, tables, overall editing

6. Alycia Ranjendran – ERD, tables, relational database schema

hospital database project