Hospital Management System Database Design

Here's a distinction-level database design assignment for my year 2 project.

Hospital Management System Case Study

1. Introduction

Golden Care hospital is a community hospital established in June 1993 to serve the healthcare needs of the community. The hospital includes several departments, rooms, doctors, nurses, and other staff working in the hospital. Patients having different kinds of ailments come to the hospital and get checkup done from the concerned doctors. If required, they are admitted in the hospital and discharged after treatment. The aim of this case study is to design and develop a database for the hospital to maintain the records of various departments and doctors in the hospital. It also maintains records of the patients, the checkup of patients done by the doctors, admissions if patients must stay in the hospital for operation or treatment.

2. Data Requirements

In hospital, there are many departments like Orthopedic, Pathology, Emergency, Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I., Neurology, Cardiology, Cancer Department, etc. Each department has an ID, name, and location. Each department has a doctor as the department head. There are two types of the doctors in the hospital, namely, regular doctors and call-on doctors. Regular doctors are those doctors who come to the hospital daily. Call-on doctors are those doctors who are called by the hospital if the concerned doctor is not available. Each doctor is given a unique number for identification and belongs to a department. For regular doctor following information need to be recorded: name, qualification, address, phone number, salary, date of joining. For call-on doctors following information need to be recorded: name, qualification, address, phone number, fees per call. When a calls-on doctor is called on duty, the system must record the date and time, reasons for call-on, and payment date. When a patient sees doctor for the first time, he/she needs to register with following information: name, country, IC number, date of birth, sex, address, phone number. Upon registration, a unique number is assigned to the patient and registration date is also assigned. Normally, a patient sees a doctor for a checkup. A patient’s checkup record includes the checkup doctor, the patient’s description of the symptoms, diagnosis, medications, consultation charge, medicine charge, payment date and payment mode . The checkup date and time will be automatically set when the doctor enters the symptoms, diagnosis etc. Doctors can admit patients to the hospital. Admission information includes department, date of admission, ward, bed number, initial condition, diagnosis, treatment, attending doctor, date of discharge. A patient may be admitted by the same doctor a few times.

3. Transaction Requirements

The database must be able to support the following query transactions:

(a) List details of all doctors in a department.

(b) List calls on doctors and details of call-on, payment amount of a given month.

(c) List doctors and number of patients they had consulted in a given period.

(d) List the details of admitted patients in a given ward. Patient’s admission date, admitting doctor, attending doctor, diagnoses, and treatment.

(e) View a patient’s consultation record.

Here‘s how I designed my database:

1. Conceptual Database Design

I created an Entity–Relationship (ER) model to show the data requirements for the Hospital Management System case study using UML notation. I then indicated transaction paths of all the query transactions stated in the case study to ensure that your data model is able to support all the transactions.

2. Logical Database design

In this section, I derived relational schema from your ER model that represents the entities and relationships. I then identified primary and foreign keys.

3. Physical Database Design

For this section, I created the tables for the database using MySQL Workbench. I set appropriate data types and proper primary key, foreign key constraints. I inserted at least 3 rows of data in each table. I then used Reverse Engineer to generate the Database Diagram.

This was the ERD diagram I designed.

I then wrote SQL query to fulfil 5 transactions required.

Transaction Queries

1. List details of all doctors in a department

2. List calls on doctors and details of call-on, payment amount of a given month.

3. List doctors and number of patients they had consulted in a given period.

4. List the details of admitted patients in a given ward. Patient’s admission date, admitting doctor, attending doctor, diagnoses, and treatment.

5. View a patient’s consultation record.