Hospital Management System

Professor Robert Rucker

By: Marmik Prajapati, Manish Taori, Poojan Patel

Table of Contents

1. Introduction
2. ORM Diagram
3. Relational Schema
4. Table Design
5. Additional Constraints
6. NoSQL Implementation
7. User Interface
8. Conclusion

​

​

Introduction

• The main purpose of our advanced database management system project is to centralize the hospital management database there is a lot of data to be stored in the database for patients nurses payroll system drugs in-take and out-take.

​

​

• With additional features for billing information storing lab test and making patients appointment.

​

​

• We also would like to add features for accounting of the entire hospital, which would include billing for patients and employees dealing for reasons and medicine bought from pharmacy or medical stores.

​

ORM Model for Hospital Management System.

Relational Schema for Hospital Management System.

Table Design

There are total 9 table in which 6 tables have Parent-Child relationship

• Patients
• Encounters
• Departments
• Vitals
• SurgicalCost
• Hospital
• Practices
• Physician
• Accounts

Additional Constraints

Procedure

Function

Triggers

Queries on Database

Number of Patients treated by Specific Provider

select Encounters.[Master Patient ID] as "Patient ID",

Encounters.[Patient Admission Datetime] as "Admission Date",

Encounters.[Patient Discharge Datetime] as "Discharge date"

from Encounters

where Encounters.[Attending Provider ID]=5049

​

Query to get number of patients having particular Disease

select s.[Surgical DRG Description],count(*) as [Total Patients] from SurgicalEncounters s

group by s.[Surgical DRG Description]

order by 2 desc;

​

Total number of patients in each Zipcode

Select P.[Patient Zip Code],COUNT(P.[Master Patient ID]) as 'Total Patient' from Patients as P

Group by P.[Patient Zip Code]

order by 2 desc;

​

NoSQL Implementation

We have used Couchbase Enterprise version to show our NoSQL Implementation.

There were multiple buckets in the database but we narrowed it down to two buckets consisting of Hospitals and Patients.

Multiple Queries were used to show the implementation on Couchbase.

User Interface

We have created a point of human-computer interaction between our database and general public.

We have used web development to create an interactive user interface for the implementation of our Hospital Management System.

Using Django as our backed for the project and HTML, CSS as our Frontend.

This UI will help in mass scale usage of our Hospital Management System.

​

Conclusion

1. We expanded and introduced additional columns to an open source dataset.
2. Performed a number of searches to the database.
3. Developed stored procedures, functions, and triggers.
4. Updated the db to reflect the present situation and added numeric value for COVID.
5. conducted NoSQL queries and did analysis.
6. Finally, consider how to deploy/expand the project in the real world.

​

References

1. Chamberlin, D. (2018). SQL++ For SQL Users: A Tutorial. Available via Amazon. Com.
2. Halpin, T. (2015). Object-Role Modeling Fundamentals: A practical guide to data modeling with ORM.
3. Praharaj, A. (2020, March 21). Hospital Management System.
4. Github. https://github.com/anwesh90/Hospital-Management-System
5. Ruer, D. (2018, 03 11).Healthcare User Group. Community.
6. What is mongodb? MongoDB. (n.d.). Retrieved April 30, 2022, from https://www.mongodb.com/what-is-mongodb
7. Taylor, D. (2022, March 5). What is mongodb? introduction, architecture, features & example. Guru99. Retrieved April 30, 2022, from https://www.guru99.com/what-is-mongodb.html
8. Couchbase vs mongodb: Differences & use cases. MongoDB. (n.d.). Retrieved April 30, 2022, from https://www.mongodb.com/compare/couchbase-vs-mongodb
9. U.S. Department of Health & Human Services (2021). Covid Data Tracker. Covid.https://covid.cdc.gov/covid-data-tracker/#datatracker-home �

​

Thanks!

By:

​

Marmik Prajapati

​

Manish Taori

​

Poojan Patel