create_tables.sql

-- Author : Pachappagari gurunadh

-- done:
-- Death_Records -
-- Visitor -
-- Nurse -
-- Admit_Room -
-- Doctor -
-- Emergency_Patient -
-- Patient -
-- Apppointment -
-- surgery -
-- operation theatre -
-- billing -
-- service -
-- visits 
-- booking





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

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;


--  Visitor

create table `visitor`
	( `id` varchar(10) primary key,
    `name` varchar(50),
    `contact_number` varchar(10),
    `gender` varchar(10),
    `address` varchar(80)
    );

--  Patient

create table `patient`
	( `id` varchar(10),
    `name` varchar(50),
    `address` varchar(80),
    `contact_number` varchar(10),
    `gender` varchar(10),
    primary key (`id`, `name`)
    );


-- Doctor

create table 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`, `name`)
    );

-- death_record

create table death_record
	(`id` varchar(10),
    `patient_name` varchar(50),
    `cause` varchar(50),
    `date_of_death` date,
    primary key (`id`)
    );


-- nurse

create table nurse
	( `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`, `name`)
    );


-- admit_room


create table admit_room 
	(
    `id` varchar(10) primary key,
    `availability` boolean
    );


-- emergency_patient

create table emergency_patient
	( `id` varchar(10),
    `name` varchar(50),
    `contact_number` varchar(10),
    `gender` varchar(10),
    `address` varchar(80),
    primary key (`id`)
    );


-- appointment
 

create table appointment 
	(
    `id` varchar(10),
    `patient_id` varchar(10),
    `patient_name` varchar(50),
    `doctor_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 delete cascade on update cascade
    );


-- surgery


create table 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 delete cascade on update cascade
    );


-- operation theatre

create table operation_theatre
	(
    `id` varchar(10),
    `surgery_id` varchar(10),
    `patient_id` varchar(10),
    `availability` boolean,
    `room_number` int,
    primary key (`id`),
    foreign key (`surgery_id`) references `surgery`(`id`) on delete cascade on update cascade,
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade
    );

-- billing 

create table billing 
	(`id` varchar(10),
    `type` varchar(10),
    `patient_id` varchar(10),
    `amount` float,
    `date` date,
    `contact_number` varchar(10),
    primary key (`id`),
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade
    );


-- service

create table service
	(
    `id` varchar(10),
    `name` varchar(50),
    `patient_id` varchar(10),
    `availed_date` date,
    primary key (`id`),
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade
    );

-- -------------------------------------------------------------------

-- visit

create table `visit`
	(
    `visitor_id` varchar(10),
    `patient_id` varchar(10),
    foreign key (`visitor_id`) references `visitor`(`id`) on delete cascade on update cascade,
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade
    );


-- booking

create table `booking`
	(
    `patient_id` varchar(10) primary key,
    `appointment_id` varchar(10),
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade,
    foreign key (`appointment_id`) references `appointment`(`id`) on delete cascade on update cascade
    );


-- treat

create table `treat`
	(
    `epatient_id` varchar(10),
    `doctor_id` varchar(10),
    foreign key (`epatient_id`) references `emergency_patient`(`id`) on delete cascade on update cascade,
    foreign key (`doctor_id`) references `doctor`(`id`) on delete cascade on update cascade
    );


-- avail

create table `avail`
	(
    `patient_id` varchar(10),
    `service_id` varchar(10),
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade,
    foreign key (`service_id`) references `service`(`id`) on delete cascade on update cascade
    );


-- schedule 


create table `schedule`
	(
    `patient_id` varchar(10),
    `doctor_id` varchar(10),
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade,
    foreign key (`doctor_id`) references `doctor`(`id`) on delete cascade on update cascade
    );

-- attend


create table `attend`
	(
    `epatient_id` varchar(10),
    `nurse_id` varchar(10),
    foreign key (`epatient_id`) references `emergency_patient`(`id`) on delete cascade on update cascade,
    foreign key (`nurse_id`) references `nurse`(`id`) on delete cascade on update cascade
    );

-- payment


create table `payment`
	(
    `service_id` varchar(10),
    `billing_id` varchar(10) primary key,
    foreign key (`service_id`) references `service`(`id`) on delete cascade on update cascade,
    foreign key (`billing_id`) references `billing`(`id`) on delete cascade on update cascade
    );
    
    
-- perform


create table `perform`
	(
    `surgery_id` varchar(10),
    `doctor_id` varchar(10),
    foreign key (`surgery_id`) references `surgery`(`id`) on delete cascade on update cascade,
    foreign key (`doctor_id`) references `doctor`(`id`) on delete cascade on update cascade
    );


-- assign


create table `assign`
	(
    `nurse_id` varchar(10),
    `patient_id` varchar(10),
    foreign key (`nurse_id`) references `nurse`(`id`) on delete cascade on update cascade,
    foreign key (`patient_id`) references `patient`(`id`) on delete cascade on update cascade
    );


-- generate 


create table `generate`
	(
    `surgery_id` varchar(10),
    `billing_id` varchar(10),
    primary key (`surgery_id`, `billing_id`),
    foreign key (`surgery_id`) references `surgery`(`id`) on delete cascade on update cascade,
    foreign key (`billing_id`) references `billing`(`id`) on delete cascade on update cascade
    );


-- occur


create table `occur`
	(
    `surgery_id` varchar(10),
    `theatre_id` varchar(10) primary key,
    foreign key (`surgery_id`) references `surgery`(`id`) on delete cascade on update cascade,
    foreign key (`theatre_id`) references `operation_theatre`(`id`) on delete cascade on update cascade
    );


-- admit


create table `admit`
	(
    `patient_id` varchar(10),
    `room_id` varchar(10),
    foreign key (`patient_id`) references `surgery`(`patient_id`) on delete cascade on update cascade,
    foreign key (`room_id`) references `admit_room`(`id`) on delete cascade on update cascade
    );

insert_data.sql

-- pATIENT TABLE FORMAT (id, name, address, contact_number, gender)
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801001', 'Captain Jack Sparrow', '1-1, Black Pearl Island, Atlantic Ocean', '2715144000', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801003', 'Elizabeth Swann', '34-(90/A) High Land, California', '9752762770', 'female');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801002', 'Gellert Grindelwald', '22-121, Maldives, Indian Ocean', '8558134440', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801004', 'Harry Potter', '4, Privet Drive, Little Whinging, Surrey', '7603341062', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801005', 'Ronald Weasley', 'The main, Tree house, Amazon Forest', '8132047892', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801006', 'Hermione Granger', '1755  Karen Lane, Shepherdsville, KY', '5789962996', 'female');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801007', 'Albus Dumbledore', '3166  Abia Martin Drive, New York, NY', '7524991112', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801029', 'Tom Riddle', '141  Stiles Street, Pittsburgh, PA', '5427563280', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801031', 'Sirius Black', 'Main Pyramid, Egypt Desert', '4088658157', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801034', 'Neville Longbottom', '1911  Brentwood Drive, Austin, TX', '7942396652', 'male');
INSERT INTO `patient`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('111801045', 'Draco Malfoy', '2-68, LA, Vegan, Airport, Street', '3817196249', 'male');



-- visitor Table (id, name, address, contact_number, gender)

INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801001', 'Will Turner', '2-1, Black Pearl Island, Atlantic Ocean', '3715144000', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801003', 'Hector', '35-(90/A) High Land, California', '8752762770', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801002', 'James Norry', '23-121, Maldives, Indian Ocean', '9558134440', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801004', 'Ragetti', '5, Privet Drive, Little Whinging, Surrey', '8603341062', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801005', 'Ana Maria', 'The second main, Tree house, Amazon Forest', '9132047892', 'female');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801006', 'Joshamee', '1756  Karen Lane, Shepherdsville, KY', '6789962996', 'female');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801007', 'Marty', '3167  Abia Martin Drive, New York, NY', '8524991112', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801008', 'Mull roy', '142  Stiles Street, Pittsburgh, PA', '6427563280', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801009', 'Murtogg', 'Second Pyramid, Egypt Desert', '5088658157', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801010', 'Lieutenant', '1912  Brentwood Drive, Austin, TX', '8942396652', 'male');
INSERT INTO `visitor`(`id`, `name`, `address`,`contact_number`, `gender`) VALUES ('v111801011', 'Twigg', '2-69, LA, Vegan, Airport, Street', '4817196249', 'male');


-- visit (visitor_id, patient_id)

INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801001', '111801001');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801001', '111801002');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801002', '111801001');

INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801003', '111801003');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801003', '111801004');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801004', '111801003');

INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801005', '111801005');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801005', '111801006');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801006', '111801005');


INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801007', '111801029');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801008', '111801031');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801009', '111801034');
INSERT INTO `visit` (`visitor_id`, `patient_id`) VALUES ('v111801010', '111801045');


-- appointment (id, patient_id, patient_name, date_and_time, reason)

-- INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801002', '111801001', 'Captain Jack Sparrow', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801001', '111801001', 'Captain Jack Sparrow', curdate(), 'Stomach Pain');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801003', '111801002', 'Gellert Grindelwald', curdate(), 'Stomach Pain');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801004', '111801003', 'Elizabeth Swann', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801005', '111801004', 'Harry Potter', curdate(), 'Stomach Pain');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801006', '111801005', 'Ronald Weasley', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801007', '111801006', 'Hermione Granger', curdate(), 'Stomach Pain');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801008', '111801007', 'Albus Dumbledore', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801009', '111801029', 'Tom Riddle', curdate(), 'Stomach Pain');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801010', '111801031', 'Sirius Black', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801011', '111801034', 'Neville Longbottom', curdate(), 'Head ache');
INSERT INTO `appointment` (`id`, `patient_id`, `patient_name`, `date_and_time`, `reason`) VALUES ('a111801012', '111801045', 'Draco Malfoy', curdate(), 'Head ache');


-- booking (patient_id, appointment_id)

INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801001', 'a111801001');
-- INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801001', 'a111801002');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801002', 'a111801003');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801003', 'a111801004');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801004', 'a111801005');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801005', 'a111801006');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801006', 'a111801007');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801007', 'a111801008');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801029', 'a111801009');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801031', 'a111801010');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801034', 'a111801011');
INSERT INTO `booking` (`patient_id`, `appointment_id`) VALUES ('111801045', 'a111801012');




-- Author : Pavan Karthik


-- doctor TABLE FORMAT (id, name, experience,designation,date_joined,contact_number,working_hours, salary)
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801045','Edward Jenner','6.5','MD','2015-08-20','7665547230','8','125000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801046','Elizabeth Blackwell','7.6','MD  FRCPE','2014-07-16','9030393939','6','150000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801047','Helene D.Gayle','5','MS','2016-09-25','9222535365','8','100000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801048','Georges Mathe','12','DMD','2009-10-05','8757855781','6','90000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801049','Virginia Apgar','9','DDS','2012-12-12','9848022338','6','80000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801050','Michael Ellis DeBakey','10','DScPT','2011-05-05','8465864295','7','50000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801051','Charles Richard Drew','8','ENT','2013-03-20','9666175686','5','100000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801052','Helen Brooke Taussig','4','PharmD','2017-06-17','9939938401','10','90000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801053','Alexander Fleming','15','MD FRCS','2014-07-16','9646263616','5','300000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801054','Myles.B.Abbott','3','DPT','2018-08-10','9121314151','5','50000');
Insert into `doctor`(`Id`,`name`,`experience`,`designation`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('121801055','Khalid Abbed','7','DO','2016-03-09','9131476959','7','70000');

-- emergency_patient TABLE FORMAT (id, name, gender,address,contact_number)
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801056','Olivia','female','Martha House,00120 Citta Vaticano,Vatican City','9646656666');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801057','George Smith','male','931 Thomas Jefferson Parkway,Va. 22902,USA','8232324256');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801058','Richard','male','Prinsengracht 263-267,1016 GV,USA','8662625231');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801059','Amelia','female','10 Downing St.,London,U.K','7252462777');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801060','Johnson Bravo','male','351 Farmington Ave.,Hartford,Conn. 06105,USA','9341578473');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801061','Mitchell Santner','male','10236 Charing Cross Rd.,Los Angeles,USA','5567688390');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801062','David Robbinson','male','Henley Street,Stratford-upon-Avon CV37 6QW,U.K','4553253241');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801063','Jimmy williams','female','Manger Square,Bethlehem,West Bank,U.S.A','7895290105');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801064','Jenson Nicolson','male','221B Baker St,London,U.K','7060605040');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801065','William Mattews','male','350 Fifth Avenue New York,NY 10118,U.S.A','6555655565');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801066','Olivia Morris','female','2 Macquarie Street,Sydney','7804949494');
INSERT INTO `emergency_patient` (`id`,`name`,`gender`,`address`,`contact_number`) VALUES ('131801067','Graeme wincent','male','Apartment 5A,129 West 81st Street','9888977787');

-- treat TABLE FORMAT (epatient_id,doctor_id)
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801060','121801045');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801062','121801051');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801056','121801047');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801057','121801049');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801066','121801045');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801064','121801051');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801063','121801049');
INSERT INTO `treat` (`epatient_id`,`doctor_id`) VALUES ('131801061','121801045');


-- Nurse TABLE FORMAT (id, name, experience,date_joined,contact_number,working_hours, salary)
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801001','Julie Watson','9','2012-01-10','7888788808','9','20000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801002','Marie Phillips','5','2016-10-18','9484988949','7','10000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801003','Tiffany Morrison','3','2018-08-16','8090898989','5','8000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801004','Jamie Rose','2.3','2019-12-29','6454748888','4','7000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801005','Emily Parker','3.3','2018-12-20','7227915201','8','15000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801006','Jamie Thomas','10','2011-09-29','6555422212','10','25000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801007','Sophie Jane','6','2015-05-25','4555352219','5','6000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801008','Lily Robberts','4.5','2017-10-13','8544583580','6','9000');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801009','Jennifer seifert','5.8','2016-08-21','9876543220','7','9500');
Insert into `nurse`(`Id`,`name`,`experience`,`date_joined`,`contact_number`,`working_hours`,`salary`) values ('141801010','Stephany Johnson','9.8','2012-08-24','9812345677','8','6500');

-- attend TABLE FORMAT (nurse_id,epatient_id)
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801003','131801064');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801001','131801056');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801010','131801057');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801005','131801066');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801001','131801063');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801002','131801061');
INSERT INTO `attend` (`nurse_id`,`epatient_id`) VALUES ('141801003','131801060');





-- Author: Sai Vipul


-- sURGERY TABLE FORMAT (id, patient_id, patient_name, time_of_surgery)
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('245', '111801004', 'Harry Potter', '2017-05-24 14:35:42');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('343', '111801005', 'Ronald Weasley', '2017-11-20 00:14:34');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('129', '111801029', 'Tom Riddle', '2004-09-23 23:37:27');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('176', '111801031', 'Sirius Black', '1980-04-15 13:44:00');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('233', '111801034', 'Neville Longbottom', '1999-05-14 10:17:02');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('222', '111801045', 'Draco Malfoy', '1990-05-28 19:36:28');
INSERT INTO `surgery`(`id`, `patient_id`, `patient_name`, `time_of_surgery`) VALUES ('146', '111801002', 'Gellert Grindelwald', '2017-11-02 21:18:31');

-- ADMIT ROOM TABLE FORMAT (id, availability)
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('101', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('103', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('102', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('104', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('105', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('106', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('107', TRUE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('201', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('202', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('203', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('204', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('205', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('206', FALSE);
INSERT INTO `admit_room`(`id`, `availability`) VALUES ('207', FALSE);

-- OPERATION THEATRE TABLE FORMAT (id, surgery_id, patient_id, availability, room_number)
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('001', '245', '111801004', TRUE, '108');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('002', '343', '111801005', TRUE, '109');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('003', '129', '111801029', TRUE, '208');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('004', '176', '111801031', TRUE, '209');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('005', '233', '111801034', FALSE, '210');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('006', '222', '111801045', TRUE, '210');
INSERT INTO `operation_theatre`(`id`, `surgery_id`, `patient_id`, `availability`, `room_number`) VALUES ('007', '146', '111801002', TRUE, '208');

-- ADMIT TABLE FORMAT (patient_id, room_id)
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801004', '101');
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801005', '102');
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801029', '103');
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801031', '104');
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801045', '105');
INSERT INTO `admit`(`patient_id`, `room_id`) VALUES ('111801002', '106');

-- OCCUR TABLE FORMAT (surgery_id, theatre_ids)
INSERT INTO `occur`(`surgery_id`, `theatre_id`) VALUES ('245', '001');
INSERT INTO `occur`(`surgery_id`, `theatre_id`) VALUES ('343', '002');
INSERT INTO `occur`(`surgery_id`, `theatre_id`) VALUES ('129', '003');
INSERT INTO `occur`(`surgery_id`, `theatre_id`) VALUES ('176', '004');
INSERT INTO `occur`(`surgery_id`, `theatre_id`) VALUES ('233', '005');






-- Author Name: Pulavarti Vinay

INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('123546', 'Cash', '111801001', '2480.02', '2009-06-20', '2715144000');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('123578', 'UPI', '111801003', '5480.00', '2010-07-02', '9752762770');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('145546', 'Debit Card', '111801002', '2000.00', '2011-04-26', '8558134440');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('122146', 'Cash', '111801007', '17845.48', '2009-05-15', '7524991112');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('189546', 'Debit Card', '111801005', '15480.02', '2008-06-24', '8132047892');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('125846', 'Cash', '111801029', '2480.02', '1992-11-10', '5427563280');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('123124', 'UPI', '111801031', '17845.48', '1995-12-22', '4088658157');
-- (Vipul)

INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('342423', 'Cash', '111801004', '2480.02', '2005-09-28', '4767380237');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('112121', 'UPI', '111801005', '5480.00', '1988-11-29', '8813530357');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('434343', 'Debit Card', '111801029', '2000.00', '2018-07-19', '7723769219');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('675765', 'Cash', '111801031', '17845.48', '2020-05-31', '5463309385');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('987012', 'Debit Card', '111801034', '15480.02', '2014-07-30', '8872687969');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('473298', 'Cash', '111801045', '2480.02', '1984-08-02', '4749839367');
INSERT INTO `billing`(`id`, `type`, `patient_id`, `amount`, `date`, `contact_number`) VALUES ('123222', 'UPI', '111801002', '17845.48', '2013-11-20', '7224994918');
-- (\Vipul)

INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('12456', 'Blood test', '111801001', '2009-06-20');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('17845', 'X-Ray test', '111801003', '2010-07-02');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('17954', 'HRCT test', '111801002', '2011-04-26');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('11524', 'Medical Checkup', '111801007', '2009-05-15');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('19568', 'ENT', '111801005', '2008-06-24');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('12948', 'Blood test', '111801029', '1992-11-10');
INSERT INTO `service`(`id`, `name`, `patient_id`, `availed_date`) VALUES ('17548', 'Medical Checkup', '111801031', '1995-12-22');


INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801001', '12456');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801003', '17845');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801002', '17954');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801007', '11524');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801005', '19568');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801029', '12948');
INSERT INTO `avail`(`patient_id`, `service_id`) VALUES ('111801031', '17548');


INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('12456', '123546');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('17845', '123578');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('17954', '145546');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('11524', '122146');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('19568', '189546');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('12948', '125846');
INSERT INTO `payment`(`service_id`, `billing_id`) VALUES ('17548', '123124');

INSERT INTO `death_record`(`id`, `patient_name`, `cause`,`date_of_death`) VALUES ('1032', 'Adalberto Dray', 'Heart Attack', '1995-05-21');
INSERT INTO `death_record`(`id`, `patient_name`, `cause`,`date_of_death`) VALUES ('1045', 'Hilda Flanery', 'Accident', '1990-04-12');
INSERT INTO `death_record`(`id`, `patient_name`, `cause`,`date_of_death`) VALUES ('1154', 'Eve Rampton', 'Kidneys Failure', '1985-12-14');
INSERT INTO `death_record`(`id`, `patient_name`, `cause`,`date_of_death`) VALUES ('1847', 'Nguyet Dutra', 'Blood Cancer', '1983-11-25');
INSERT INTO `death_record`(`id`, `patient_name`, `cause`,`date_of_death`) VALUES ('1561', 'June Terhune', 'Accident', '1997-09-05');





-- Author: Gurunadh


-- Schedule (patient_id, doctor_id)

INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801001', '121801045');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801002', '121801046');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801003', '121801047');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801004', '121801048');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801005', '121801049');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801006', '121801050');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801007', '121801051');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801029', '121801052');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801031', '121801053');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801034', '121801054');
INSERT INTO `schedule` (`patient_id`, `doctor_id`)  VALUES ('111801045', '121801055');




-- Author: Vinay

INSERT INTO `assign`(`nurse_id`, `patient_id`) VALUES ('141801001', '111801004');
INSERT INTO `assign`(`nurse_id`, `patient_id`) VALUES ('141801005', '111801005');
INSERT INTO `assign`(`nurse_id`, `patient_id`) VALUES ('141801004', '111801029');
INSERT INTO `assign`(`nurse_id`, `patient_id`) VALUES ('141801003', '111801031');
INSERT INTO `assign`(`nurse_id`, `patient_id`) VALUES ('141801007', '111801034');



-- Author: Pavan


INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801045','343');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801046','129');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801047','146');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801051','176');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801051','222');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801047','245');
INSERT INTO `perform` (`doctor_id`,`surgery_id`) VALUES ('121801048','233');



-- Author: Vipul

INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('245', '342423');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('343', '112121');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('129', '434343');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('176', '675765');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('233', '987012');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('222', '473298');
INSERT INTO `generate` (`surgery_id`, `billing_id`) VALUES ('146', '123222');