Here’s a MySQL script to create the SS Hospital Database along with the required tables:
Explanation:
- Patients Table: Contains patient details like name, contact, and registration date.
- Doctors Table: Includes information about doctors and their specializations.
- Opd_Booking Table: Handles outpatient department bookings by linking patients and doctors.
- Hospital_Wards Table: Manages wards, including types and the number of beds.
- Hospital_Beds Table: Tracks individual beds in wards with charges and availability.
- Bed_Allotment Table: Records bed allotments to patients with allotment and discharge dates.
-- Create the Hospital Database
CREATE DATABASE IF NOT EXISTS SS_Hospital;
USE SS_Hospital;
-- Create Patients Table
CREATE TABLE Patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
date_of_birth DATE NOT NULL,
gender ENUM('Male', 'Female', 'Other') NOT NULL,
address TEXT NOT NULL,
contact_number VARCHAR(15),
email VARCHAR(150),
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create Doctors Table
CREATE TABLE Doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
specialization VARCHAR(150),
contact_number VARCHAR(15),
email VARCHAR(150),
joining_date DATE NOT NULL
);
-- Create Opd Booking Table
CREATE TABLE Opd_Booking (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
booking_date DATE NOT NULL,
booking_time TIME NOT NULL,
visit_reason TEXT,
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id)
);
-- Create Hospital_Wards_Table
CREATE TABLE Hospital_Wards (
ward_id INT AUTO_INCREMENT PRIMARY KEY,
ward_name VARCHAR(100) NOT NULL,
ward_type ENUM('General', 'Private', 'ICU', 'Emergency') NOT NULL,
total_beds INT NOT NULL,
status ENUM('Active', 'Inactive') DEFAULT 'Active'
);
-- Create Hospital_Beds Table
CREATE TABLE Hospital_Beds (
bed_id INT AUTO_INCREMENT PRIMARY KEY,
ward_id INT NOT NULL,
bed_number INT NOT NULL,
bed_charges DECIMAL(10, 2) NOT NULL,
bed_status ENUM('Available', 'Occupied') DEFAULT 'Available',
FOREIGN KEY (ward_id) REFERENCES Hospital_Wards(ward_id)
);
-- Create Bed_Allotment Table
CREATE TABLE Bed_Allotment (
allotment_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
bed_id INT NOT NULL,
allotment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
discharge_date TIMESTAMP NULL,
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
FOREIGN KEY (bed_id) REFERENCES Hospital_Beds(bed_id)
);
-- Verify successful creation
SHOW TABLES;Data with Sample Values
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Dec 14, 2024 at 06:31 PM
-- Server version: 10.4.28-MariaDB
-- PHP Version: 8.2.4
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `ss_hospital`
--
-- --------------------------------------------------------
--
-- Table structure for table `bed_allotment`
--
CREATE TABLE `bed_allotment` (
`allotment_id` int(11) NOT NULL,
`patient_id` int(11) NOT NULL,
`bed_id` int(11) NOT NULL,
`allotment_date` timestamp NOT NULL DEFAULT current_timestamp(),
`discharge_date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `bed_allotment`
--
INSERT INTO `bed_allotment` (`allotment_id`, `patient_id`, `bed_id`, `allotment_date`, `discharge_date`) VALUES
(3, 1, 3, '2024-12-10 09:00:00', NULL),
(4, 2, 4, '2024-12-11 09:30:00', '2024-12-12 04:30:00'),
(5, 3, 7, '2024-12-13 04:30:00', NULL),
(6, 4, 8, '2024-12-13 11:00:00', NULL),
(7, 1, 9, '2024-12-10 02:30:00', '2024-12-12 04:30:00'),
(8, 5, 10, '2024-12-14 12:30:00', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `doctors`
--
CREATE TABLE `doctors` (
`doctor_id` int(11) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`specialization` varchar(150) DEFAULT NULL,
`contact_number` varchar(15) DEFAULT NULL,
`email` varchar(150) DEFAULT NULL,
`joining_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `doctors`
--
INSERT INTO `doctors` (`doctor_id`, `first_name`, `last_name`, `specialization`, `contact_number`, `email`, `joining_date`) VALUES
(1, 'Dr. Emily', 'Brown', 'Cardiologist', '1112223333', 'emily.brown@hospital.com', '2020-01-15'),
(2, 'Dr. Mark', 'Wilson', 'Orthopedic', '4445556666', 'mark.wilson@hospital.com', '2019-09-10'),
(3, 'Dr. Sarah', 'Miller', 'Neurologist', '5556667777', 'sarah.miller@hospital.com', '2021-06-01'),
(4, 'Dr. James', 'Anderson', 'Dermatologist', '8889990000', 'james.anderson@hospital.com', '2018-04-22'),
(5, 'Dr. Laura', 'Clark', 'Pediatrician', '9998887777', 'laura.clark@hospital.com', '2023-02-10');
-- --------------------------------------------------------
--
-- Table structure for table `hospital_beds`
--
CREATE TABLE `hospital_beds` (
`bed_id` int(11) NOT NULL,
`ward_id` int(11) NOT NULL,
`bed_number` int(11) NOT NULL,
`bed_charges` decimal(10,2) NOT NULL,
`bed_status` enum('Available','Occupied') DEFAULT 'Available'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `hospital_beds`
--
INSERT INTO `hospital_beds` (`bed_id`, `ward_id`, `bed_number`, `bed_charges`, `bed_status`) VALUES
(1, 1, 101, 200.00, 'Available'),
(2, 1, 102, 200.00, 'Available'),
(3, 2, 201, 1000.00, 'Occupied'),
(4, 2, 202, 1000.00, 'Occupied'),
(5, 3, 301, 500.00, 'Available'),
(6, 3, 302, 500.00, 'Available'),
(7, 3, 303, 500.00, 'Occupied'),
(8, 4, 401, 1500.00, 'Available'),
(9, 4, 402, 1500.00, 'Available'),
(10, 4, 403, 1500.00, 'Occupied');
-- --------------------------------------------------------
--
-- Table structure for table `hospital_wards`
--
CREATE TABLE `hospital_wards` (
`ward_id` int(11) NOT NULL,
`ward_name` varchar(100) NOT NULL,
`ward_type` enum('General','Private','ICU','Emergency') NOT NULL,
`total_beds` int(11) NOT NULL,
`status` enum('Active','Inactive') DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `hospital_wards`
--
INSERT INTO `hospital_wards` (`ward_id`, `ward_name`, `ward_type`, `total_beds`, `status`) VALUES
(1, 'General Ward A', 'General', 10, 'Active'),
(2, 'ICU Ward 1', 'ICU', 5, 'Active'),
(3, 'Private Ward B', 'Private', 8, 'Active'),
(4, 'Emergency Ward', 'Emergency', 6, 'Active');
-- --------------------------------------------------------
--
-- Table structure for table `opd_booking`
--
CREATE TABLE `opd_booking` (
`booking_id` int(11) NOT NULL,
`patient_id` int(11) NOT NULL,
`doctor_id` int(11) NOT NULL,
`booking_date` date NOT NULL,
`booking_time` time NOT NULL,
`visit_reason` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `opd_booking`
--
INSERT INTO `opd_booking` (`booking_id`, `patient_id`, `doctor_id`, `booking_date`, `booking_time`, `visit_reason`) VALUES
(1, 1, 1, '2024-12-13', '10:00:00', 'Chest pain'),
(2, 2, 2, '2024-12-13', '11:00:00', 'Knee pain'),
(3, 3, 3, '2024-12-14', '09:30:00', 'Migraine'),
(4, 4, 4, '2024-12-14', '12:00:00', 'Skin rash'),
(5, 5, 5, '2024-12-15', '15:00:00', 'High fever'),
(6, 2, 2, '2024-12-16', '10:45:00', 'Back pain'),
(8, 4, 3, '2024-12-12', '10:05:00', 'Chest Pain');
-- --------------------------------------------------------
--
-- Table structure for table `patients`
--
CREATE TABLE `patients` (
`patient_id` int(11) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`date_of_birth` date NOT NULL,
`gender` enum('Male','Female','Other') NOT NULL,
`address` text NOT NULL,
`contact_number` varchar(15) DEFAULT NULL,
`email` varchar(150) DEFAULT NULL,
`registration_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `patients`
--
INSERT INTO `patients` (`patient_id`, `first_name`, `last_name`, `date_of_birth`, `gender`, `address`, `contact_number`, `email`, `registration_date`) VALUES
(1, 'John', 'Doe', '1985-05-12', 'Male', '123 Main St, Cityville', '1234567890', 'john.doe@example.com', '2024-12-12 10:58:02'),
(2, 'Jane', 'Smith', '1990-08-20', 'Female', '456 Elm St, Townsville', '9876543210', 'jane.smith@example.com', '2024-12-12 10:58:02'),
(3, 'Alice', 'Johnson', '1988-03-15', 'Female', '789 Oak St, Metropolis', '3216549870', 'alice.johnson@example.com', '2024-12-12 11:02:19'),
(4, 'Bob', 'Taylor', '1975-11-22', 'Male', '246 Pine St, Villagetown', '4561237890', 'bob.taylor@example.com', '2024-12-12 11:02:19'),
(5, 'Eve', 'White', '2000-01-05', 'Female', '135 Maple St, Citytown', '7893216540', 'eve.white@example.com', '2024-12-12 11:02:19');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `bed_allotment`
--
ALTER TABLE `bed_allotment`
ADD PRIMARY KEY (`allotment_id`),
ADD KEY `patient_id` (`patient_id`),
ADD KEY `bed_id` (`bed_id`);
--
-- Indexes for table `doctors`
--
ALTER TABLE `doctors`
ADD PRIMARY KEY (`doctor_id`);
--
-- Indexes for table `hospital_beds`
--
ALTER TABLE `hospital_beds`
ADD PRIMARY KEY (`bed_id`),
ADD KEY `ward_id` (`ward_id`);
--
-- Indexes for table `hospital_wards`
--
ALTER TABLE `hospital_wards`
ADD PRIMARY KEY (`ward_id`);
--
-- Indexes for table `opd_booking`
--
ALTER TABLE `opd_booking`
ADD PRIMARY KEY (`booking_id`),
ADD KEY `patient_id` (`patient_id`),
ADD KEY `doctor_id` (`doctor_id`);
--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
ADD PRIMARY KEY (`patient_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `bed_allotment`
--
ALTER TABLE `bed_allotment`
MODIFY `allotment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `doctors`
--
ALTER TABLE `doctors`
MODIFY `doctor_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `hospital_beds`
--
ALTER TABLE `hospital_beds`
MODIFY `bed_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `hospital_wards`
--
ALTER TABLE `hospital_wards`
MODIFY `ward_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `opd_booking`
--
ALTER TABLE `opd_booking`
MODIFY `booking_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
MODIFY `patient_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `bed_allotment`
--
ALTER TABLE `bed_allotment`
ADD CONSTRAINT `bed_allotment_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`patient_id`),
ADD CONSTRAINT `bed_allotment_ibfk_2` FOREIGN KEY (`bed_id`) REFERENCES `hospital_beds` (`bed_id`);
--
-- Constraints for table `hospital_beds`
--
ALTER TABLE `hospital_beds`
ADD CONSTRAINT `hospital_beds_ibfk_1` FOREIGN KEY (`ward_id`) REFERENCES `hospital_wards` (`ward_id`);
--
-- Constraints for table `opd_booking`
--
ALTER TABLE `opd_booking`
ADD CONSTRAINT `opd_booking_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`patient_id`),
ADD CONSTRAINT `opd_booking_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`doctor_id`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;