Hospital Management System (HMS)

1. Create Database
CREATE DATABASE hospital_db;
USE hospital_db;

2. Create Core Tables
CREATE TABLE doctors (
  doctor_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  specialization VARCHAR(100),
  experience INT,
  fees DECIMAL(10,2)
);

CREATE TABLE patients (
  patient_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  age INT,
  gender VARCHAR(10),
  phone VARCHAR(20)
);

CREATE TABLE appointments (
  appointment_id INT PRIMARY KEY AUTO_INCREMENT,
  patient_id INT,
  doctor_id INT,
  date DATE,
  time TIME,
  status VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

3. Prescription & Billing Tables
CREATE TABLE medicines (
  medicine_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  stock INT,
  price DECIMAL(10,2)
);

CREATE TABLE prescriptions (
  prescription_id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id INT,
  medicine_id INT,
  quantity INT,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id),
  FOREIGN KEY (medicine_id) REFERENCES medicines(medicine_id)
);

CREATE TABLE bills (
  bill_id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id INT,
  total DECIMAL(10,2)
);

4. Trigger – Reduce Medicine Stock
CREATE TRIGGER reduce_stock AFTER INSERT ON prescriptions
FOR EACH ROW
UPDATE medicines 
SET stock = stock - NEW.quantity
WHERE medicine_id = NEW.medicine_id;

5. Stored Procedure – Generate Bill
DELIMITER //
CREATE PROCEDURE generate_bill(IN appt INT)
BEGIN
   DECLARE total_amount DECIMAL(10,2);

   SELECT SUM(p.quantity * m.price) INTO total_amount
   FROM prescriptions p
   JOIN medicines m 
   ON p.medicine_id = m.medicine_id
   WHERE p.appointment_id = appt;

   INSERT INTO bills(appointment_id, total) VALUES (appt, total_amount);
END //
DELIMITER ;

6. Transaction – Book Appointment
START TRANSACTION;

INSERT INTO appointments(patient_id, doctor_id, date, time, status)
VALUES (1, 3, '2025-01-22', '09:30:00', 'Booked');

COMMIT;