Database Implementation Project: Hospital Management System Design

This project details the design and implementation of a Hospital Management System database. It begins with an introduction outlining the various departments within a hospital and the patient flow, from OPD registration to discharge. The assignment defines business rules, such as unique registration and IPD numbers, and data storage for OPD and IPD patients, billing, and procedures. An ERD (Entity-Relationship Diagram) is created to visually represent the database structure, followed by the creation of 3NF relational tables, including tables for departments, doctors, beds, patients (OPD and IPD), tests, procedures, billing, and bill details. The document includes table creation scripts, data insertion examples, and SQL queries, such as SELECT and JOIN queries, to retrieve and manipulate data. Forms for input data and a report example are also included, demonstrating the practical application of the database design.

Student Name
Student Id 1
Hospital Management System – Database Implementation
Submitted By
Course
Professor
Date

Student Name
Student Id 2
Introduction
According to hospital system, there are numerous divisions such as Orthopedic, Pathology, Emergency,
Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I.,
Neurology, Cardiology, Cancer Department, Corpse, and so on. Patient first comes to OPD where a
card is issued to new patient who comes first time. This card is to write prescription when doctor checks
the patient. Subsequent to making entry in the card, they go to the concerned specialist's room. The
specialist looks up their illnesses. As per the illnesses, the specialist either endorses prescription or
concedes the patient in the concerned department. The patient can select either private or general room
as per his/her financial status. Before admitting in the hospital, the patient needs to fulfill certain
conventions of the hospital such as room charges, and so on. With the completion of treatment, doctor
discharges the patient. Before getting discharged from the hospital, the patient again needs to complete
few formalities of the hospital like balance charges, test charges, operation charges , blood charges,
specialists' charges, and so on. Being database administrator, my responsibility is to design ERD
diagram and implement that into the hospital.
Business Rules
Following business rules can be considered while developing ERD diagram for the hospital:
 When patient comes to the hospital, a registration no is issued to the patient which remains
unique whether he gets admitted or revisit the hospital for regular checkup.
 When patient gets admitted, an IPD No is issued to the patient which remains unique.
 All opd patient details will be saved into OPD_Patient table and IPD Patient details will be saved
into IPD_Patient like his date of admission etc.

Student Name
Student Id 3
 All opd bill details will be saved into OPD_Billing, which will reference to OPD_Bill_Details as
all text details will be saved in OPD_Bill_Details
 IPD patient billing will be saved into IPD_Billing, and IPD_Bill_details where all procedure
details will be saved including room charges, operation charges etc.
 Procedure is master table where all procedures like room charges, list of surgeries, or any other
procedure are saved.
ERD Diagram
3NF Relational Tables
1. Department  DepartmentID, DepartmentName
Primary Key  DepartmentID
2. Doctor  DoctorID, DoctorName, DocAddress, ContactNo, DepartmentID, DocType, Specialist

Student Name
Student Id 4
Primary Key  DoctorID
Foreign Key  DepartmentID
3. Bed  BedNo, Ward, Room_Charges, Room_Type, Vacant
Primary Key  BedNo
4. OPD_Patient  Regn_No, PatientName, PatientAddress, ContactNo, DoctorID, Regn_Date
Primary Key  Regn_No
Foreign Key  DoctorID
5. Test  TestID, Test_Name, DepartmentID, Test_Amount
Primary Key  TestID
Foreign Key  DepartmentID
6. Procedure  ProcedureID, Procedure_Name, DepartmentID, Procedure_Amount
Primary Key  ProcedureID
Foreign Key  DepartmentID
7. IPD_Patient  IPD_No, Regn_No, BedNo, Admit_Date, DoctorID, Bill_generated
Primary Key  IPD_No
Foreign Key  Regn_No, DoctorID
8. OPD_Billing  OPD_Bill_No, Regn_No, Date_of_visit, Bill_Amount
Primary Key  OPD_Bill_No

Student Name
Student Id 5
Foreign Key  Regn_No
9. OPD_Bill_Details  ID, OPD_Bill_No, Regn_No, TestID, Test_Amount.
Primary Key  ID
Foreign Key  OPD_Bill_No, Regn_No, TestID
10. IPD_Billing  IPD_Bill_No, IPD_No, Date_of_Admission, Date_of_discharge, BillAmount,
PaidAmount, Balance
Primary Key  IPD_Bill_No
Foreign Key  IPD_No
11. IPD_Bill_Details  ID, IPD_Bill_No, ProcedureID, Date_of_Procedure, Procedure_Amount.
Primary Key  ID
Foreign Key  IPD_Bill_No, ProcedureID
Dependency Diagram

Student Name
Student Id 6
Database Implementation
Table Creation
1. Department

Student Name
Student Id 7
2. Doctor
3. Room

Student Name
Student Id 8
4. Procedure
5. Test

Student Name
Student Id 9
6. OPD_Patient
7. OPD_Bill

Student Name
Student Id 10
8. OPD_Bill_Details
9. IPD_Patients

Student Name
Student Id 11
10. IPD_Billing
11. IPD_Bill_Details

Student Name
Student Id 12
Relationship