1

Hospital Database Report: A Hospital Healthcare Solution

Submitted to:

Dr. Thidarat Tinnakornsrisuphap

Prepared by:

Aditi Marathe

Masters in Health Care Informatics, University of San Diego

HCIN 543: Database Design and Knowledge Management

August 15, 2022

 2

Executive Summary

The use of excel spreadsheets is often insufficient to tackle the complex healthcare needs

and address the barriers to accessing critical healthcare data. There is a need to manage, store and

share data in a safe and structured manner. The Hospital Database is a way to deal with issues

that impede the effective use of healthcare data and provides better health outcomes. It helps to

focus on physician and patient-sensitive information which are critical for providing patient-

centric care. For a healthcare organization that uses excel spreadsheets to manage data, a

Hospital Database is an important tool to encounter problems that arise while storing and

managing data.

Our objective is to use the Hospital Database to provide solutions that are encountered

while using excel spreadsheets and support healthcare organizations for effective functioning.

Three tables have been constructed in this database. The first is the Physician table, the second is

the Patient table and the third is the PhysicianPatient table. To represent additional features like a

secondary key (SK) and foreign key (FK), all the tables have been listed in a Database Design

Language (DBDL) format. The primary keys have been underlined to demonstrate their

uniqueness. Forms, queries, and reports have been generated to effectively manage data. With

the help of all the features required for managing, storing, and sharing data, we will fulfill our

vision of providing effective methods to deal with healthcare data and provide health outcomes.
 3

Hospital Database Report: A Hospital Healthcare Solution

The purpose of the healthcare data report is to create a database for the healthcare

organization to help store pertinent information regarding the physician and the patient. The

main aim is to create a hospital database that contains information about different categories of

healthcare information and relationships among those categories. Numerous healthcare

organizations operate on excel sheets. Some of the difficulties with excel spreadsheet use are

redundancy, unable to share data across different users, and security issues (Prat &Last, 2015).

Access to the hospital database helps to overcome these issues by overcoming the barriers of the

excel spreadsheet. It helps to provide access to healthcare data efficiently and provides data

independence (Prat &Last, 2015).

The hospital database provides information regarding physicians and patients to aid in

better health outcomes. The addition of a PhysicianPatient table provides access to data without

changing pertinent information in the Physician and Patient table. With a hospital database, users

can enter patient and physician information, as well as identify which patient is being treated by

a physician by utilizing the patient ID. Users can start with a physician ID and find out a

physician's specialty along with the physician's phone number. A hospital database not only

helps to store better data, but it can support in the production of numerous reports crucial for a

healthcare organization.
 4

Methodology

• Tables

o There are three tables that have been created 1. Physician table 2. Patient table 3.

PhysicianPatient table. The healthcare database handles entities, attributes, and

relationship between entities. The data source from which the information has

been extracted is the Hospital Database. In this database the physician and patient

are the two entities. An attribute is also called as columns in the database. The

table is as below:

o Physician (PhysicianID, PhysicianFirstName, PhysicianLastName,

PhysicianPhone, PhysicianSpeciality)

Patient (PatientID, PatientFirstName, PatientLastName, PatientPhone,

PatientDateOfBirth, PatientStreet, PatientCity, PatientState, PatientZipCode,

PhysicianID)

o The physician entity has five attributes: PhysicianID, PhysicianFirstName,

PhysicianLastName, PhysicianPhone, PhysicianSpeciality. The underlined

PhysicianID is a unique primary key to this table.

o The patient entity has ten attributes: PatientID, PatientFirstName,

PatientLastName, PatientPhone, PatientDateOfBirth, PatientStreet, PatientCity,

PatientState, PatientZipCode, PhysicianID. The underlined PatientID is a unique

primary key to the patient table and PhysicianID is the foreign key.

o There is an association between the entities. The table above has a many-to-many

relationship. A physician is associated with many patients and a patient is

associated with many physicians.

 5

o To handle the many-to-many relationship a third table has been added to the

database as below:

o Physician (PhysicianID, PhysicianFirstName, PhysicianLastName,

PhysicianPhone, PhysicianSpeciality)

Patient (PatientID, PatientFirstName, PatientLastName, PatientPhone,

PatientDateOfBirth, PatientStreet, PatientCity, PatientState, PatientZipCode,

PhysicianID)

PhysicianPatient (PhysicianID, Patient ID, HospitalName, DateOfVisit)

o The PhysicianPatient table has four attributes: PhysicianID, PatientID,

HospitalName and DateOfVisit. The primary key is a combination of the

PhysicianID and PatientID. The attributes to this table are HospitalName and

DateOfVisit.

o To represent secondary or foreign keys an additional feature has been added

which is the Database Design Language (DBDL). Below are three tables.

Table 1
Physician

Physician (PhysicianID, PhysicianFirstName, PhysicianLastName, PhysicianPhone,

PhysicianSpeciality)

SK PhysicianLastName, PhysicianFirstName
 6

Table 2
Patient
Patient (PatientID, PatientFirstName, PatientLastName, PatientPhone,
PatientDateOfBirth, PatientStreet, PatientCity, PatientState, PatientZipCode,
PhysicianID)

SK PatientLastName, PatientFirstName
FK PhysicianID Physician

Table 3
PhysicianPatient

PhysicianPatient (PhysicianID, Patient ID, HospitalName, DateOfVisit)

SK HospitalName

o The primary keys are underlined. Secondary keys are identified by abbreviation

SK, followed by the column that make up the secondary keys. Foreign keys are

identified by the abbreviation FK, followed by the column that make up the

foreign key. Foreign keys are followed by an arrow pointing to the table identified

by the foreign key.

• Forms

o There are three forms in the Hospital Database 1. Patient 2. Physician 3.

PhysicianPatient. The purpose of Patient form for the users is to enter personal

information regarding the patient directly into the forms without utilizing the

tables. Personal information includes PatientID, PatientFirstName,

PatientLastName, PatientPhone, PatientDateOfBirth, PatientStreet, PatientCity,

PatientState, PatientZipCode, and PhysicianID. Users enter data through a form.

 7

o The Physician form has been created to enter physician data or enter a new

record. This includes PhysicianID, PhysicianFirstName, PhysicianLastName,

PhysicianPhone and PhysicianSpecialty.

o The PhysicianPatient form has been constructed to enter data regarding physician

and the patient. This encompasses PhysicianID, PatientID, HospitalName, and

DateOfVisit.

• Queries

o The purpose of the query is to retrieve selected data based on the specified

criteria, update data, delete records and create a new table in the current or

another database. In this database, the purpose is to display records in a query by

satisfying a criteria.

▪ For query 1: Find the name of the patients with PatientID and PatientState

who belong to California.

SELECT PatientID, PatientState

FROM Patient

WHERE PatientState = “California”

Results

PatientID PatientState
1236543 California

2425672 California

3456789 California

3488695 California
 8

7230263 California

7373030 California

9856786 California

▪ For query 2: Find the list of hospitals and dates where Samantha Phillips

saw patients.

SELECT PhysicianPatient.HospitalName, PhysicianPatient.DateOfVisit,

Physician.PhysicianFirstName, Physician.PhysicianLastName

FROM PhysicianPatient, Physician

WHERE Physician.PhysicianID = PhysicianPatient.PhysicianID

AND Physician.PhysicianFirstName = “Samantha”

AND Physician.PhysicianLastName = “Phillips”

Results

HospitalName DateOfVisit PhysicianFirstName PhysicianLastName

Kaiser 5/4/2010 Samantha Phillips
Permanente
 9

Results

• Reports

o A PatientState query has been used for the Patient State report. This report

contains information regarding patients whose PatientID and PatientState belong

to California.

o A PhysicianName query has been utilized for the Physician Name report. This

report encompasses information regarding physician including HospitalName,

DateOfVisit, PhysicianFirstName and PhysicianLastName.

 10

Conclusions

The Hospital Healthcare Database provides solutions to store and retrieve data in an

efficient and organized way. This database can effectively manage redundancy, facilitate

consistency, and increase productivity as compared to the excel spreadsheet (Prat & Last, 2015).

The Hospital Database can effectively share data across different users and aid in data

independence for updating data at the same time. This feature is not present in the excel

spreadsheet. This database helps to provide patient-centric care and support healthcare

organization to improve integrity, expand security and reduce cost (Prat & Last, 2015). This

database aims to provide long-term solutions to the healthcare organizations, meet their needs

and support them with better health outcomes.

Recommendations

There are potential limitations to this innovative model, despite its distinctive qualities.

These limitations are increased complexity, more difficult recovery, and larger file size (Prat &

Last, 2015). Other limitation is lesser attributes. To overcome this, we need to expand on the

number of attributes to fit to the size of bigger organizations. Addition of tables could also be

done to deal with various aspects of healthcare organizations.

 11

References

Pratt, P., & Last, M. (2015). Concepts of database management (8th ed.). United States of

America: Cengage Learning.