hospital database creation script (without data)

hospital_structure.sql

/*HOSPITAL MANAGEMENT SYSTEM*/

CREATE DATABASE IF NOT EXISTS `hospital`;
USE `hospital`;

--------------------------------------------------------
/*creating entities*/
--------------------------------------------------------

CREATE TABLE IF NOT EXISTS `patient` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `address` VARCHAR(80),
    `contact_number` VARCHAR(10),
    `gender` VARCHAR(10),
    PRIMARY KEY (`id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `visitor` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `contact_number` VARCHAR(10),
    `gender` VARCHAR(10),
    `address` VARCHAR(80),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `doctor` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `experience` FLOAT,
    `designation` VARCHAR(20),
    `date_joined` DATE,
    `contact_number` VARCHAR(10),
    `working_hours` FLOAT,
    `salary` FLOAT,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `appointment` (
    `id` VARCHAR(10),
    `patient_id` VARCHAR(10),
    `patient_name` VARCHAR(50),
    `date_and_time` DATETIME,
    `reason` VARCHAR(80),
    PRIMARY KEY (`id`, `patient_id`),
    FOREIGN KEY (`patient_id`, `patient_name`) REFERENCES `patient` (`id`, `name`) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `death_record` (
    `id` VARCHAR(10),
    `patient_name` VARCHAR(50),
    `cause` VARCHAR(50),
    `date_of_death` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `patient_id` VARCHAR(10),
    `availed_date` DATETIME,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `billing` (
    `id` VARCHAR(10),
    `type` VARCHAR(10),
    `patient_id` VARCHAR(10),
    `amount` FLOAT,
    `date` DATETIME,
    `contact_number` VARCHAR(10),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `surgery` (
    `id` VARCHAR(10),
    `patient_id` VARCHAR(10),
    `patient_name` VARCHAR(50),
    `time_of_surgery` DATETIME,
    PRIMARY KEY (`id`, `patient_id`),
    FOREIGN KEY (`patient_id`, `patient_name`) REFERENCES `patient` (`id`, `name`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `nurse` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `experience` FLOAT,
    `date_joined` DATE,
    `contact_number` VARCHAR(10),
    `working_hours` FLOAT,
    `salary` FLOAT,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admit_room` (
    `id` VARCHAR(10),
    `availability` BOOLEAN,
    primary key (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `emergency_patient` (
    `id` VARCHAR(10),
    `name` VARCHAR(50),
    `gender` VARCHAR(10),
    `address` VARCHAR(80),
    `contact_number` VARCHAR(10),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `operation_theatre` (
    `id` VARCHAR(10),
    `surgery_id` VARCHAR(10),
    `patient_id` VARCHAR(10),
    `availability` BOOLEAN,
    `room_number` INT(10),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`surgery_id`) REFERENCES `surgery` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

----------------------------------------------------------------
/*creating relationships*/
----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `visit` (
    `visitor_id` VARCHAR(10),
    `patient_id` VARCHAR(10),
    FOREIGN KEY (`visitor_id`) REFERENCES `visitor` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `booking` (
    `patient_id` VARCHAR(10),
    `appointment_id` VARCHAR(10),
    PRIMARY KEY (`patient_id`),
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `treat` (
    `epatient_id` VARCHAR(10),
    `doctor_id` VARCHAR(10),
    FOREIGN KEY (`epatient_id`) REFERENCES `emergency_patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`doctor_id`) REFERENCES `doctor` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `attend` (
    `nurse_id` VARCHAR(10),
    `epatient_id` VARCHAR(10),
    FOREIGN KEY (`nurse_id`) REFERENCES `nurse` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`epatient_id`) REFERENCES `emergency_patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `avail` (
    `patient_id` VARCHAR(10),
    `service_id` VARCHAR(10),
    FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `schedule` (
    `patient_id` VARCHAR(10),
    `doctor_id` VARCHAR(10),
    FOREIGN KEY (`patient_id`) REFERENCES `appointment` (`patient_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`doctor_id`) REFERENCES `doctor` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment` (
    `service_id` VARCHAR(10),
    `billing_id` VARCHAR(10),
    PRIMARY KEY (`billing_id`),
    FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`billing_id`) REFERENCES `billing` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `perform` (
    `doctor_id` VARCHAR(10),
    `surgery_id` VARCHAR(10),
    FOREIGN KEY (`doctor_id`) REFERENCES `doctor` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`surgery_id`) REFERENCES `surgery` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `generate` (
    `surgery_id` VARCHAR(10),
    `billing_id` VARCHAR(10),
    PRIMARY KEY (`surgery_id`, `billing_id`),
    FOREIGN KEY (`surgery_id`) REFERENCES `surgery` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`billing_id`) REFERENCES `billing` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `assign` (
    `nurse_id` VARCHAR(10),
    `patient_id` VARCHAR(10),
    FOREIGN KEY (`nurse_id`) REFERENCES `nurse` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`patient_id`) REFERENCES `surgery` (`patient_id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `occur` (
    `surgery_id` VARCHAR(10),
    `theatre_id` VARCHAR(10),
    PRIMARY KEY (`theatre_id`),
    FOREIGN KEY (`surgery_id`) REFERENCES `surgery` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`theatre_id`) REFERENCES `operation_theatre` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admit` (
    `patient_id` VARCHAR(10),
    `room_id` VARCHAR(10),
    PRIMARY KEY (`patient_id`, `room_id`),
    FOREIGN KEY (`patient_id`) REFERENCES `surgery` (`patient_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`room_id`) REFERENCES `admit_room` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;