CASE study 1 GSITS HOSPITAL

ALL CASE STUDIES https://filebin.net/w87flnniwsvte95f

create database CS1;
use CS1;

CREATE TABLE DEPARTMENT (
    D_NAME VARCHAR(30) PRIMARY KEY,
    D_LOCATION VARCHAR(50),
    FACILITIES INT
);

CREATE TABLE ALL_DOCTORS (
    DOC_NO VARCHAR(30) PRIMARY KEY,
    DEPARTMENT VARCHAR(30),
    FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
);

CREATE TABLE DOC_REG (
    DOC_NO VARCHAR(30) PRIMARY KEY,
    D_NAME VARCHAR(30),
    QUALIFICATION VARCHAR(30),
    SALARY DECIMAL(10, 2),
    EN_TIME TIME,
    EX_TIME TIME,
    ADDRESS VARCHAR(50),
    PH_NO VARCHAR(10),
    DOJ DATE,
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE DOC_ON_CALL (
    DOC_NO VARCHAR(30),
    D_NAME VARCHAR(50),
    QUALIFICATION VARCHAR(30),
    FS_PR_CL DECIMAL(10, 2),
    PYMT_DU DECIMAL(10, 2),
    ADDRESS VARCHAR(100),
    PH_NO VARCHAR(10),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_ENTRY (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    PAT_NAME VARCHAR(30),
    CHKUP_DT DATE,
    PT_AGE INT,
    SEX CHAR(1),
    RFRG_CSTNT VARCHAR(50),
    DIAGNOSIS VARCHAR(30),
    ADDRESS VARCHAR(100),
    CITY VARCHAR(30),
    PH_NO VARCHAR(10),
    DEPARTMENT VARCHAR(30),
    FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
);

CREATE TABLE PAT_CHKUP (
    PAT_NO VARCHAR(30),
    DOC_NO VARCHAR(30),
    DIAGNOSIS VARCHAR(30),
    STATUS VARCHAR(30),
    TREATMENT VARCHAR(30),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_ADMIT (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    ADV_PYMT DECIMAL(10, 2),
    MODE_PYMT VARCHAR(10),
    ROOM_NO INT,
    DEPTNAME VARCHAR(50),
    ADMTD_ON DATE,
    COND_ON VARCHAR(50),
    INVSTGTN_DN VARCHAR(30),
    TRMT_SDT VARCHAR(50),
    ATTDNT_NM VARCHAR(50),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
);

CREATE TABLE PAT_DIS (
    PAT_NO VARCHAR(30),
    TR_ADVS VARCHAR(100),
    TR_GVN VARCHAR(100),
    MEDICINES VARCHAR(100),
    PYMT_GV DECIMAL(10, 2),
    DIS_ON DATE,
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
);

CREATE TABLE PAT_REG (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    DATE_VIS DATE,
    CONDITIONN VARCHAR(30),
    TREATMENT VARCHAR(30),
    MEDICINES VARCHAR(30),
    DOC_NO VARCHAR(30),
    PAYMT DECIMAL(10, 2),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_OPR (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    DATE_OPR DATE,
    IN_COND VARCHAR(100),
    AFOP_COND VARCHAR(100),
    TY_OPERATION VARCHAR(100),
    MEDICINES VARCHAR(100),
    DOC_NO VARCHAR(30),
    OPTH_NO VARCHAR(20),
    OTHER_SUG VARCHAR(100),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE ROOM_DETAILS (
    ROOM_NO VARCHAR(30) PRIMARY KEY,
    TYPE VARCHAR(50),
    STATUS VARCHAR(20),
    RM_DL_CRG DECIMAL(10, 2),
    OTHER_CRG DECIMAL(10, 2)
);


ALTER TABLE DOC_REG
ADD CONSTRAINT fk_doctor_number_prefix CHECK (DOC_NO LIKE 'DR%');

ALTER TABLE DOC_ON_CALL
ADD CONSTRAINT fk_doctor_number_prefix_call CHECK (DOC_NO LIKE 'DR%');

ALTER TABLE PAT_ENTRY
ADD CONSTRAINT pk_patient_number_prefix_entry CHECK (PAT_NO LIKE 'PT%'),
ADD CONSTRAINT valid_sex_entry CHECK (SEX IN ('M', 'F'));

ALTER TABLE ROOM_DETAILS
ADD CONSTRAINT valid_room_type CHECK (TYPE IN ('G', 'P')),
ADD CONSTRAINT valid_room_status CHECK (STATUS IN ('Y', 'N'));


-- Inserting data into DEPARTMENT table
INSERT INTO DEPARTMENT (D_NAME, D_LOCATION, FACILITIES) VALUES
('Cardiology', 'Mumbai', 5),
('Pediatrics', 'Delhi', 3),
('Orthopedics', 'Bangalore', 4),
('Gynecology', 'Kolkata', 3),
('Neurology', 'Chennai', 5);

-- Inserting data into ALL_DOCTORS table
INSERT INTO ALL_DOCTORS (DOC_NO, DEPARTMENT) VALUES
('DR001', 'Cardiology'),
('DR002', 'Pediatrics'),
('DR003', 'Orthopedics'),
('DR004', 'Gynecology'),
('DR005', 'Neurology');

-- Inserting data into DOC_REG table
INSERT INTO DOC_REG (DOC_NO, D_NAME, QUALIFICATION, SALARY, EN_TIME, EX_TIME, ADDRESS, PH_NO, DOJ) VALUES
('DR001', 'Gourav Upadhyay', 'MD Cardiology', 150000, '08:00:00', '16:00:00', '123 ABC Street, Mumbai', '9876543210', '2023-01-15'),
('DR002', 'Priya Sharma', 'MD Pediatrics', 120000, '09:00:00', '17:00:00', '456 XYZ Street, Delhi', '9876543211', '2023-02-20'),
('DR003', 'Rahul Singh', 'MS Orthopedics', 130000, '08:30:00', '16:30:00', '789 PQR Street, Bangalore', '9876543212', '2023-03-25'),
('DR004', 'Neha Gupta', 'MD Gynecology', 140000, '08:00:00', '16:00:00', '456 LMN Street, Kolkata', '9876543213', '2023-04-30'),
('DR005', 'Amit Patel', 'DM Neurology', 160000, '09:30:00', '17:30:00', '789 RST Street, Chennai', '9876543214', '2023-05-05');

-- Inserting data into DOC_ON_CALL table
INSERT INTO DOC_ON_CALL (DOC_NO, D_NAME, QUALIFICATION, FS_PR_CL, PYMT_DU, ADDRESS, PH_NO)
VALUES
('DR001', 'Gourav Upadhyay', 'MD Cardiology', 2000, 1500, '456 Sunrise Avenue, Mumbai', '9876543210'),
('DR002', 'Priya Sharma', 'MD Pediatrics', 2500, 1800, '789 Maple Street, Delhi', '9876543211'),
('DR003', 'Rahul Singh', 'MS Orthopedics', 1800, 1200, '123 Oak Lane, Bangalore', '9876543212'),
('DR004', 'Neha Gupta', 'MD Gynecology', 2200, 1600, '345 Elm Street, Kolkata', '9876543213'),
('DR005', 'Amit Patel', 'DM Neurology', 2400, 1700, '678 Pine Avenue, Chennai', '9876543214');


-- Inserting data into PAT_ENTRY table
INSERT INTO PAT_ENTRY (PAT_NO, PAT_NAME, CHKUP_DT, PT_AGE, SEX, RFRG_CSTNT, DIAGNOSIS, ADDRESS, CITY, PH_NO, DEPARTMENT) VALUES
('PT001', 'Ramesh Kumar', '2023-06-01', 35, 'M', 'Fever', 'Common Cold', '123 ABC Street', 'Mumbai', '9876543200', 'Cardiology'),
('PT002', 'Sunita Devi', '2023-06-05', 28, 'F', 'Headache', 'Migraine', '456 XYZ Street', 'Delhi', '9876543201', 'Pediatrics'),
('PT003', 'Amit Sharma', '2023-06-10', 45, 'M', 'Stomach Pain', 'Gastritis', '789 PQR Street', 'Bangalore', '9876543202', 'Orthopedics'),
('PT004', 'Priya Singh', '2023-06-15', 30, 'F', 'Cough', 'Bronchitis', '456 LMN Street', 'Kolkata', '9876543203', 'Gynecology'),
('PT005', 'Gopal Verma', '2023-06-20', 55, 'M', 'Chest Pain', 'Angina', '789 RST Street', 'Chennai', '9876543204', 'Neurology');

-- Inserting data into PAT_CHKUP table
INSERT INTO PAT_CHKUP (PAT_NO, DOC_NO, DIAGNOSIS, STATUS, TREATMENT) VALUES
('PT001', 'DR001', 'Common Cold', 'Admitted', 'Antibiotics'),
('PT002', 'DR002', 'Migraine', 'Regular', 'Painkillers'),
('PT003', 'DR003', 'Gastritis', 'Referred for Operation', 'Surgery'),
('PT004', 'DR004', 'Bronchitis', 'Admitted', 'Bronchodilators'),
('PT005', 'DR005', 'Angina', 'Regular', 'Cardiac Medications');

-- Inserting data into PAT_ADMIT table
INSERT INTO PAT_ADMIT (PAT_NO, ADV_PYMT, MODE_PYMT, ROOM_NO, DEPTNAME, ADMTD_ON, COND_ON, INVSTGTN_DN, TRMT_SDT, ATTDNT_NM) VALUES
('PT001', 5000, 'Cash', 101, 'Cardiology', '2023-06-01', 'Stable', 'ECG, Blood Test', '2023-06-02', 'Rita Sharma'),
('PT004', 7000, 'Card', 201, 'Gynecology', '2023-06-15', 'Critical', 'X-Ray, Ultrasound', '2023-06-16', 'Amit Singh');

-- Inserting data into PAT_DIS table
INSERT INTO PAT_DIS (PAT_NO, TR_ADVS, TR_GVN, MEDICINES, PYMT_GV, DIS_ON) VALUES
('PT001', 'Complete bed rest', 'Antibiotics', 'Paracetamol, Vitamin C', 5000, '2023-06-05'),
('PT004', 'Avoid cold food', 'Bronchodilators', 'Cough Syrup, Inhaler', 7000, '2023-06-20');

-- Inserting data into PAT_REG table
INSERT INTO PAT_REG (PAT_NO, DATE_VIS, CONDITIONN, TREATMENT, MEDICINES, DOC_NO, PAYMT) VALUES
('PT002', '2023-06-05', 'Migraine', 'Painkillers', 'Ibuprofen', 'DR002', 300),
('PT003', '2023-06-10', 'Gastritis', 'Surgery', 'Omeprazole', 'DR003', 500),
('PT005', '2023-06-20', 'Angina', 'Cardiac Medications', 'Aspirin', 'DR005', 400);

-- Inserting data into PAT_OPR table
INSERT INTO PAT_OPR (PAT_NO, DATE_OPR, IN_COND, AFOP_COND, TY_OPERATION, MEDICINES, DOC_NO, OPTH_NO, OTHER_SUG) VALUES
('PT003', '2023-06-11', 'Pre-Operative', 'Post-Operative', 'Appendectomy', 'Antibiotics', 'DR003', 'OT01', 'Avoid oily food');

-- Inserting data into ROOM_DETAILS table
INSERT INTO ROOM_DETAILS (ROOM_NO, TYPE, STATUS, RM_DL_CRG, OTHER_CRG) VALUES
(101, 'P', 'Y', 2000, 500),
(102, 'G', 'N', 1000, 300),
(201, 'P', 'Y', 2500, 600),
(202, 'G', 'Y', 1500, 400),
(203, 'P', 'N', 2200, 550);