-- ============================================================ -- Hospital Management System -- DB: HospitalDB -- ============================================================
/* 1) Create database */
CREATE DATABASE HospitalDB; -- ============================================================ -- 2) Schema: Department, Doctor, Patient, Appointment, Prescription -- ============================================================
-- Department table: master list of hospital departments CREATE TABLE dbo.Department ( Dept_ID INT IDENTITY(1,1) PRIMARY KEY, -- surrogate PK Dept_Name NVARCHAR(100) NOT NULL UNIQUE, -- e.g. "Cardiology" CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL );
-- Doctor table: each doctor belongs to one department CREATE TABLE dbo.Doctor ( Doctor_ID INT IDENTITY(1001,1) PRIMARY KEY, -- start at 1001 FullName NVARCHAR(150) NOT NULL, Dept_ID INT NOT NULL, -- FK to Department Specialization NVARCHAR(150) NULL, Contact NVARCHAR(20) NULL, Email NVARCHAR(150) NULL, CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL, CONSTRAINT FK_Doctor_Department FOREIGN KEY (Dept_ID) REFERENCES dbo.Department(Dept_ID) );
-- Patient table: store DOB and computed Age for accuracy
CREATE TABLE dbo.Patient
(
Patient_ID INT IDENTITY(2001,1) PRIMARY KEY, -- start at 2001
FullName NVARCHAR(150) NOT NULL,
Gender CHAR(1) CHECK (Gender IN ('M','F','O')) NOT NULL, -- M/F/O for other
DOB DATE NOT NULL, -- date of birth
-- computed (non-persisted) Age for query convenience
Age AS DATEDIFF(YEAR, DOB, CAST(GETDATE() AS DATE)),
Contact NVARCHAR(20) NULL,
Email NVARCHAR(150) NULL,
Address NVARCHAR(250) NULL,
CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
-- Appointment table: patient books appointment with a doctor CREATE TABLE dbo.Appointment ( App_ID INT IDENTITY(3001,1) PRIMARY KEY, Patient_ID INT NOT NULL, Doctor_ID INT NOT NULL, AppointmentDate DATETIME2 NOT NULL, -- combined date/time of appointment Reason NVARCHAR(250) NULL, Status NVARCHAR(50) NOT NULL DEFAULT 'Scheduled', -- Scheduled, Completed, Cancelled, No-Show CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL, CONSTRAINT FK_Appointment_Patient FOREIGN KEY (Patient_ID) REFERENCES dbo.Patient(Patient_ID), CONSTRAINT FK_Appointment_Doctor FOREIGN KEY (Doctor_ID) REFERENCES dbo.Doctor(Doctor_ID) ); -- Index to speed up doctor/patient queries CREATE INDEX IX_Appointment_DoctorDate ON dbo.Appointment(Doctor_ID, AppointmentDate); CREATE INDEX IX_Appointment_PatientDate ON dbo.Appointment(Patient_ID, AppointmentDate);
-- Prescription table: each appointment can have zero or more prescriptions (but often 1) CREATE TABLE dbo.Prescription ( Pres_ID INT IDENTITY(4001,1) PRIMARY KEY, App_ID INT NOT NULL, -- FK to appointment Medicine NVARCHAR(200) NOT NULL, Dosage NVARCHAR(100) NOT NULL, -- e.g. "1 tablet twice daily" Duration NVARCHAR(50) NULL, -- e.g. "5 days" Notes NVARCHAR(400) NULL, CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL, CONSTRAINT FK_Prescription_Appointment FOREIGN KEY (App_ID) REFERENCES dbo.Appointment(App_ID) ); CREATE INDEX IX_Prescription_App ON dbo.Prescription(App_ID);
-- Optional: AppointmentLog - an audit table to track changes to appointments CREATE TABLE dbo.AppointmentLog ( Log_ID INT IDENTITY(1,1) PRIMARY KEY, App_ID INT NOT NULL, OldStatus NVARCHAR(50) NULL, NewStatus NVARCHAR(50) NOT NULL, ChangedAt DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL, ChangedBy NVARCHAR(100) NULL, -- username or system CONSTRAINT FK_AppointmentLog_Appointment FOREIGN KEY (App_ID) REFERENCES dbo.Appointment(App_ID) );
-- ============================================================ -- 4) Sample data inserts for Departments, Doctors, Patients -- ============================================================ BEGIN TRANSACTION;
-- Departments INSERT INTO dbo.Department (Dept_Name) VALUES ('General Medicine'), ('Cardiology'), ('Orthopedics'), ('Pediatrics'), ('Radiology');
-- Doctors INSERT INTO dbo.Doctor (FullName, Dept_ID, Specialization, Contact, Email) VALUES ('Dr. Amit Sharma', 1, 'General Physician', '9876500001', '[email protected]'), ('Dr. Priya Verma', 2, 'Interventional Cardiologist', '9876500002', '[email protected]'), ('Dr. Rahul Singh', 3, 'Orthopedic Surgeon', '9876500003', '[email protected]'), ('Dr. Neha Gupta', 4, 'Pediatrician', '9876500004', '[email protected]');
-- Patients INSERT INTO dbo.Patient (FullName, Gender, DOB, Contact, Email, Address) VALUES ('Mehul Kumar', 'M', '2003-10-02', '9876600001', '[email protected]', 'Chandigarh'), ('Seema Kaur', 'F', '1995-05-18', '9876600002', '[email protected]', 'Mohali'), ('Rajesh Patel', 'M', '1980-01-25', '9876600003', '[email protected]', 'Panchkula');
-- ============================================================ -- 5) Sample appointments and prescriptions -- ============================================================ -- Schedule appointments INSERT INTO dbo.Appointment (Patient_ID, Doctor_ID, AppointmentDate, Reason) VALUES (2001, 1001, '2025-11-07 10:00:00', 'Fever and cold'), -- Mehul with Dr. Amit (2002, 1002, '2025-11-07 11:30:00', 'Chest pain checkup'), -- Seema with Dr. Priya (2003, 1003, '2025-11-08 09:00:00', 'Knee pain'); -- Rajesh with Dr. Rahul
-- Add prescriptions for appointments INSERT INTO dbo.Prescription (App_ID, Medicine, Dosage, Duration, Notes) VALUES (3001, 'Paracetamol 500mg', '1 tablet every 8 hours', '3 days', 'Take after food'), (3002, 'Atorvastatin 10mg', '1 tablet at night', '30 days', 'Monitor lipid profile'), (3003, 'Ibuprofen 400mg', '1 tablet every 8 hours', '7 days', 'Avoid driving');
/* 1: List all departments */
SELECT Dept_ID, Dept_Name, CreatedAt FROM dbo.Department ORDER BY Dept_Name;
/* 2: List all doctors with their department names */ SELECT d.Doctor_ID, d.FullName AS DoctorName, d.Specialization, d.Contact, d.Email, dp.Dept_Name FROM dbo.Doctor d JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY dp.Dept_Name, d.FullName; GO
/* 2: List all doctors with their department names */ SELECT d.Doctor_ID, d.FullName AS DoctorName, d.Specialization, d.Contact, d.Email, dp.Dept_Name FROM dbo.Doctor d JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY dp.Dept_Name, d.FullName; GO
/* 3: List all patients with computed age */ SELECT Patient_ID, FullName, Gender, DOB, Age, Contact, Email, Address FROM dbo.Patient ORDER BY FullName;
/* 4. Show all appointments with patient and doctor names */
SELECT a.App_ID, p.FullName AS PatientName, d.FullName AS DoctorName, dp.Dept_Name AS Department, a.AppointmentDate, a.Reason, a.Status, a.CreatedAt FROM dbo.Appointment a JOIN dbo.Patient p ON a.Patient_ID = p.Patient_ID JOIN dbo.Doctor d ON a.Doctor_ID = d.Doctor_ID JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY a.AppointmentDate;
/*5 Show only upcoming (future) appointments */
SELECT a.App_ID, p.FullName AS PatientName, d.FullName AS DoctorName, a.AppointmentDate, a.Status FROM dbo.Appointment a JOIN dbo.Patient p ON a.Patient_ID = p.Patient_ID JOIN dbo.Doctor d ON a.Doctor_ID = d.Doctor_ID WHERE a.AppointmentDate > GETDATE() ORDER BY a.AppointmentDate;
/* 6 Count of doctors per department */
SELECT dp.Dept_Name, COUNT(d.Doctor_ID) AS TotalDoctors FROM dbo.Department dp LEFT JOIN dbo.Doctor d ON dp.Dept_ID = d.Dept_ID GROUP BY dp.Dept_Name ORDER BY TotalDoctors DESC;
/* 1: List all departments */
SELECT Dept_ID, Dept_Name, CreatedAt FROM dbo.Department ORDER BY Dept_Name;
/* 2: List all doctors with their department names */ SELECT d.Doctor_ID, d.FullName AS DoctorName, d.Specialization, d.Contact, d.Email, dp.Dept_Name FROM dbo.Doctor d JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY dp.Dept_Name, d.FullName; GO
/* 2: List all doctors with their department names */ SELECT d.Doctor_ID, d.FullName AS DoctorName, d.Specialization, d.Contact, d.Email, dp.Dept_Name FROM dbo.Doctor d JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY dp.Dept_Name, d.FullName; GO
/* 3: List all patients with computed age */ SELECT Patient_ID, FullName, Gender, DOB, Age, Contact, Email, Address FROM dbo.Patient ORDER BY FullName;
/* 4. Show all appointments with patient and doctor names */
SELECT a.App_ID, p.FullName AS PatientName, d.FullName AS DoctorName, dp.Dept_Name AS Department, a.AppointmentDate, a.Reason, a.Status, a.CreatedAt FROM dbo.Appointment a JOIN dbo.Patient p ON a.Patient_ID = p.Patient_ID JOIN dbo.Doctor d ON a.Doctor_ID = d.Doctor_ID JOIN dbo.Department dp ON d.Dept_ID = dp.Dept_ID ORDER BY a.AppointmentDate;
/*5 Show only upcoming (future) appointments */
SELECT a.App_ID, p.FullName AS PatientName, d.FullName AS DoctorName, a.AppointmentDate, a.Status FROM dbo.Appointment a JOIN dbo.Patient p ON a.Patient_ID = p.Patient_ID JOIN dbo.Doctor d ON a.Doctor_ID = d.Doctor_ID WHERE a.AppointmentDate > GETDATE() ORDER BY a.AppointmentDate;
/* 6 Count of doctors per department */
SELECT dp.Dept_Name, COUNT(d.Doctor_ID) AS TotalDoctors FROM dbo.Department dp LEFT JOIN dbo.Doctor d ON dp.Dept_ID = d.Dept_ID GROUP BY dp.Dept_Name ORDER BY TotalDoctors DESC;