DATABASE SYSTEMS
HOSPITAL MANAGEMENT SYSTEM
PROPOSAL:
Project Overview
This project aims to develop a robust SQL database for managing various aspects of a medical
facility, including patient information, doctor and nurse schedules, appointments, billing, and
inventory management. The database will facilitate efficient data retrieval and management
through the use of well-structured tables, relationships, and complex queries.
Objectives
Patient Management: Create a robust system to manage patient information, including
personal details, medical history, appointments, and billing.
Doctor Scheduling: Implement a scheduling system to manage doctor availability,
appointments, and their respective specializations.
Inventory Management: Develop a module to efficiently manage hospital inventory,
including tracking items, suppliers, quantities, and prices.
Billing System: Design a billing system to manage patient invoices, payments, and
payment status tracking.
Reporting: Implement reporting features to provide insights into hospital operations,
such as appointment statistics, billing summaries, and inventory status.
Project Components
1. Database Tables
The project involves creating the following tables:
Patients
Doctors
Nurse
Appointments
Billing
Inventory
Each table will be designed with appropriate columns and data types, including primary keys
and foreign keys to enforce relationships between tables.
2. Data Insertion
Data will be inserted into each table to provide a realistic dataset for querying. This will include
sample records for patients, doctors, nurses, appointments, billing transactions, and inventory
items.
3. SQL Queries
A comprehensive set of SQL queries will be developed to meet various data retrieval needs.
These will include:
Basic SELECT queries to retrieve data from individual tables.
JOIN operations (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) to combine data
from multiple tables.
UNION operations to merge results from multiple queries.
GROUP BY and aggregation functions to summarize data.
String functions (e.g., UPPER, SUBSTR, REVERSE) to manipulate textual data.
Date functions (e.g., CURRENT_TIMESTAMP, ADD_MONTHS) to handle and analyze
dates.
Implementation Plan
Step 1: Create Database Tables
The first step involves creating the necessary tables using SQL CREATE TABLE statements. Each
table will be defined with the appropriate columns, data types, and constraints (primary keys,
foreign keys).
Step 2: Insert Sample Data
Insert sample data into each table using INSERT INTO statements. This data will be used for
testing and demonstrating the queries.
Step 3: Develop SQL Queries
Develop a comprehensive set of SQL queries to perform various operations, including:
Retrieving patient information.
Listing appointments along with patient and doctor details.
Summarizing billing information.
Analyzing inventory data.
Step 4: Test and Validate Queries
Run each query to ensure it produces the correct results. Validate the queries against the
sample data and adjust as necessary to handle edge cases and ensure accuracy.
Enhanced ERD:
Patients doctor
Patient_ID DoctorID
Name Name
Specialization
contactNumber
ContactNumber
medicalHistory Schedule
fees
Nurses
PatientID
Name
Appointments
FatherName
Appointment_id Contact
Patient_id Salary
Doctor_id
Date
status
Billing
BillingID
(PKPatientID (FAmount
PaymentStatus
BillingDate
Inventory
ItemID
ItemName
Quantity
price
SupplierID
SupplierName
Contact
Adress
Relations:
1. Patients <-> Appointments: One-to-many relationship where a patient can have multiple
appointments.
2. Doctors <-> Appointments: One-to-many relationship where a doctor can have multiple
appointments.
3. Patients <-> Billing: One-to-many relationship where a patient can have multiple billing
records.
4. Inventory <-> Suppliers: One-to-many relationship where an inventory item can have one
supplier, but a supplier can supply multiple items.
Tables:
1: Patients:
2:Doctors:
3:Nurse:
5:Inventory:
6:biling:
7:Appointments:
Queries:
1: List of Appointments with Patient and Doctor Details:
SELECT Appointments.AppointmentID, Patients.Name AS PatientName, Doctors.Name AS
DoctorName, Appointments.AppointmentDate, Appointments.Status
FROM Appointments
INNER JOIN Patients ON Appointments.PatientID = Patients.PatientID
INNER JOIN Doctors ON Appointments.DoctorID = Doctors.DoctorID;
2: -- Display all patients and their appointments (if any)
SELECT Patients.PatientID, Patients.Name AS PatientName, Appointments.AppointmentID,
Appointments.AppointmentDate
FROM Patients
LEFT JOIN Appointments ON Patients.PatientID = Appointments.PatientID;
3: Display Appointment detail with Patient detail
SELECT Appointments.AppointmentID, Appointments.AppointmentDate, Patients.PatientID,
Patients.Name AS PatientName
FROM Appointments
RIGHT JOIN Patients ON Appointments.PatientID = Patients.PatientID;
4: Display name of All the doctors and patients
SELECT Name FROM Patients
UNION
SELECT Name FROM Doctors;
5: To Display Patient name with doctor name
SELECT Patients.Name AS PatientName, Doctors.Name AS DoctorName
FROM Patients
INNER JOIN Appointments ON Patients.PatientID = Appointments.PatientID
INNER JOIN Doctors ON Appointments.DoctorID = Doctors.DoctorID;
6: To get all records from Patients and their corresponding Appointments
SELECT *
FROM Patients
INNER JOIN Appointments ON Patients.PatientID = Appointments.PatientID;
7: List all patients with a specific medical condition
SELECT * FROM Patients WHERE MedicalHistory LIKE '%Allergic to Pollens%';
8: --List all doctors with a specific specialization
SELECT * FROM Doctors WHERE Specialization = 'Cardiology';
9: --List all appointments for a specific patient
SELECT * FROM Appointments WHERE PatientID = 1;
10: List all appointments for a specific doctor
SELECT * FROM Appointments WHERE DoctorID = 1;
11: -Query to Display total number of Doctors
SELECT COUNT(*) AS TotalDoctors FROM Doctors;
Queries Using GROUP BY clause
12: Total Fees Collected by Each PatientID :
SELECT PatientID, name, SUM(FEES) AS TotalFees
FROM Patients
GROUP BY PatientID, name;
13: Total fees by patients
SELECT COUNT(*), SUM(FEES) AS TOTALFEES FROM Patients;
14: Number of Patients Per Doctor:
SELECT d.Name AS DoctorName, COUNT(a.PatientID) AS NumberOfPatients
FROM Doctors d
JOIN Appointments a ON d.DoctorID = a.DoctorID
GROUP BY d.Name;
15: Average Salary of Doctors by Specialization:
SELECT Specialization, AVG(SALARY) AS AvgSalary
FROM Doctors
GROUP BY Specialization;
16: Count of Appointments by Status:
SELECT Status, COUNT(AppointmentID) AS NumberOfAppointments
FROM Appointments
GROUP BY Status;
17: Total Billing Amount by Payment Status:
SELECT PaymentStatus, SUM(Amount) AS TotalAmount
FROM Billing
GROUP BY PaymentStatus;
18: Average Fees Collected by Medical History:
SELECT MedicalHistory, AVG(FEES) AS AverageFees
FROM Patients
GROUP BY MedicalHistory;
Queries by using string Funstions
19: Uppercase Patient Names:
SELECT PatientID, UPPER(Name) AS Name
FROM Patients;
20: Substring of Doctor Names (First 5 Characters):
SELECT DoctorID, SUBSTR(Name, 1, 5) AS NamePrefix FROM Doctors;
21: Reverse Nurse Names:
SELECT NurseID, REVERSE(Name) AS ReversedName FROM Nurse;
22: --Concatenate Patient Name and Address:
SELECT PatientID, Name || ' - ' || Address AS FullInfo FROM Patients;
23: --Right Padding Doctor Names with Dashes:
SELECT DoctorID, RPAD (name, 20, '-') AS Paddedname FROM Doctors;
Queries Using Date Functions
24:Biling in Last 7 days:
SELECT BillingID, PatientID, Amount, BillingDate
FROM Billing
WHERE BillingDate > SYSTIMESTAMP - 7;
25: Appointments for the Next Week:
SELECT AppointmentID, PatientID, DoctorID, AppointmentDate
FROM Appointments
WHERE AppointmentDate BETWEEN SYSTIMESTAMP AND SYSTIMESTAMP + 7;
26: --Number of Days Between Appointments and Billings:
SELECT a.AppointmentID, b.BillingDate - a.AppointmentDate AS DaysBetween
FROM Appointments a JOIN Billing b ON a.PatientID = b.PatientID;
27: Top 3 Most Frequently Appointed Doctors:
SELECT * FROM (
SELECT d.DoctorID, d.Name, COUNT(a.AppointmentID) AS AppointmentCount
FROM Doctors d
JOIN Appointments a ON d.DoctorID = a.DoctorID
GROUP BY d.DoctorID, d.Name
ORDER BY AppointmentCount DESC
WHERE ROWNUM <= 3;
Additional Queries to Explore the Data
28: Patients with Multiple Appointments:
SELECT PatientID, COUNT(AppointmentID) AS NumberOfAppointments
FROM Appointments
GROUP BY PatientID
HAVING COUNT(AppointmentID) > 1;
29: Doctors with Multiple Appointments:
SELECT DoctorID, COUNT(AppointmentID) AS NumberOfAppointments
FROM Appointments
GROUP BY DoctorID
HAVING COUNT(AppointmentID) > 1;
30: Top 3 Most Expensive Billings:
SELECT PatientID, Amount, BillingDate FROM (SELECT PatientID, Amount,
BillingDate FROM Billing ORDER BY Amount DESC)
WHERE ROWNUM <= 3;
31: Patients with the Same Contact Number:
SELECT ContactNumber, COUNT(PatientID) AS NumberOfPatients
FROM Patients
GROUP BY ContactNumber
HAVING COUNT(PatientID) > 1;
32: Count of Patients by Medical History:
SELECT MedicalHistory, COUNT(PatientID) AS PatientCount
FROM Patients
GROUP BY MedicalHistory;
33: Total Fees by Contact Number:
SELECT ContactNumber, SUM(FEES) AS TotalFees FROM Patients
GROUP BY ContactNumber;