-- Drop and Create statemements for Medical Clinic database
CREATE SCHEMA IF NOT EXISTS `MSIS230`;
USE `MSIS230` ;
-- drop all tables with foreign keys first
DROP TABLE IF EXISTS `Schedule` ;
DROP TABLE IF EXISTS `EmployeeSpecialty` ;
DROP TABLE IF EXISTS `MedicalHistoryCond` ;
DROP TABLE IF EXISTS `MedicalHistoryPres` ;
DROP TABLE IF EXISTS `MedicalHistorySurg` ;
DROP TABLE IF EXISTS `Diagnosis` ;
DROP TABLE IF EXISTS `Appointment` ;
DROP TABLE IF EXISTS `Employee` ;
DROP TABLE IF EXISTS `EmployeeType` ;
-- drop all tables with primary keys only
DROP TABLE IF EXISTS `Prescription` ;
DROP TABLE IF EXISTS `Surgery` ;
DROP TABLE IF EXISTS `MedicalConditions` ;
DROP TABLE IF EXISTS `Specialty` ;
DROP TABLE IF EXISTS `Patient` ;
-- create all tables with primary keys only first
CREATE TABLE IF NOT EXISTS `Patient` (
`PatientID` VARCHAR(10) NOT NULL,
`Password` VARCHAR(20) NOT NULL,
`PatientLName` VARCHAR(25) NOT NULL,
`PatientFName` VARCHAR(25) NOT NULL,
`Street` VARCHAR(25) NULL,
`City` VARCHAR(20) NULL,
`State` CHAR(2) NULL,
`Zip` VARCHAR(10) NULL,
`Gender` CHAR(1) NULL,
PRIMARY KEY (`PatientID`)
);
CREATE TABLE IF NOT EXISTS `Specialty` (
`SpecialtyID` VARCHAR(10) NOT NULL,
`Description` VARCHAR(20) NOT NULL,
PRIMARY KEY (`SpecialtyID`)
);
CREATE TABLE IF NOT EXISTS `MedicalConditions` (
`ConditionID` VARCHAR(10) NOT NULL,
`Description` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ConditionID`)
);
CREATE TABLE IF NOT EXISTS `Surgery` (
`SurgeryID` VARCHAR(10) NOT NULL,
`Description` VARCHAR(40) NOT NULL,
PRIMARY KEY (`SurgeryID`)
);
CREATE TABLE IF NOT EXISTS `Prescription` (
`PrescriptionID` VARCHAR(10) NOT NULL,
`Description` VARCHAR(20) NOT NULL,
PRIMARY KEY (`PrescriptionID`)
);
CREATE TABLE IF NOT EXISTS `EmployeeType` (
`TypeID` VARCHAR(10) NOT NULL,
`Description` VARCHAR(30) NOT NULL,
PRIMARY KEY (`TypeID`)
);
-- create all tables with foreign keys
CREATE TABLE IF NOT EXISTS `Employee` (
`EmployeeID` VARCHAR(10) NOT NULL,
`Password` VARCHAR(20) NOT NULL,
`EmployeeLName` VARCHAR(25) NOT NULL,
`EmployeeFName` VARCHAR(25) NOT NULL,
`TypeID` VARCHAR(10) NULL,
`Gender` CHAR(1) NULL,
`DateOfHire` DATE NULL,
PRIMARY KEY (`EmployeeID`),
CONSTRAINT `fk_Employee_EmployeeType`
FOREIGN KEY (`TypeID`)
REFERENCES `EmployeeType` (`TypeID`)
);
CREATE TABLE IF NOT EXISTS `Appointment` (
`AppointmentID` VARCHAR(10) NOT NULL,
`PatientID` VARCHAR(10) NOT NULL,
`EmployeeID` VARCHAR(10) NOT NULL,
`Date` DATE NULL,
`StartTime` TIME NULL,
`Duration` VARCHAR(10) NULL,
`Status` VARCHAR(10) NULL,
PRIMARY KEY (`AppointmentID`),
CONSTRAINT `fk_Appointment_Employee`
FOREIGN KEY (`EmployeeID`)
REFERENCES `Employee` (`EmployeeID`),
CONSTRAINT `fk_Appointment_Patient`
FOREIGN KEY (`PatientID`)
REFERENCES `Patient` (`PatientID`)
);
CREATE TABLE IF NOT EXISTS `Diagnosis` (
`AppointmentID` VARCHAR(10) NOT NULL,
`Diagnosis` VARCHAR(255) NULL,
`PrescriptionID` VARCHAR(10) NOT NULL,
PRIMARY KEY (`AppointmentID`),
CONSTRAINT `fk_Diagnosis_Appointment`
FOREIGN KEY (`AppointmentID`)
REFERENCES `Appointment` (`AppointmentID`),
CONSTRAINT `fk_Diagnosis_Prescription`
FOREIGN KEY (`PrescriptionID`)
REFERENCES `Prescription` (`PrescriptionID`)
);
CREATE TABLE IF NOT EXISTS `MedicalHistoryPres` (
`PatientID` VARCHAR(10) NOT NULL,
`Date` DATE NOT NULL,
`PrescriptionID` VARCHAR(10) NULL,
PRIMARY KEY (`PatientID`, `Date`),
CONSTRAINT `fk_Patient_Prescription`
FOREIGN KEY (`PatientID`)
REFERENCES `Patient` (`PatientID`),
CONSTRAINT `fk_MedicalHistory_Prescription`
FOREIGN KEY (`PrescriptionID`)
REFERENCES `Prescription` (`PrescriptionID`)
);
CREATE TABLE IF NOT EXISTS `MedicalHistoryCond` (
`PatientID` VARCHAR(10) NOT NULL,
`Date` DATE NOT NULL,
`ConditionID` VARCHAR(10) NULL,
PRIMARY KEY (`PatientID`, `Date`),
CONSTRAINT `fk_Patient_Condition`
FOREIGN KEY (`PatientID`)
REFERENCES `Patient` (`PatientID`),
CONSTRAINT `fk_MedicalHistory_MedicalConditions`
FOREIGN KEY (`ConditionID`)
REFERENCES `MedicalConditions` (`ConditionID`)
);
CREATE TABLE IF NOT EXISTS `MedicalHistorySurg` (
`PatientID` VARCHAR(10) NOT NULL,
`Date` DATE NOT NULL,
`SurgeryID` VARCHAR(10) NULL,
PRIMARY KEY (`PatientID`, `Date`),
CONSTRAINT `fk_Patient_Surgery`
FOREIGN KEY (`PatientID`)
REFERENCES `Patient` (`PatientID`),
CONSTRAINT `fk_MedicalHistory_Surgery`
FOREIGN KEY (`SurgeryID`)
REFERENCES `Surgery` (`SurgeryID`)
);
CREATE TABLE IF NOT EXISTS `EmployeeSpecialty` (
`EmployeeID` VARCHAR(10) NOT NULL,
`Sequence` INT NOT NULL,
`SpecialtyID` VARCHAR(10) NOT NULL,
PRIMARY KEY (`EmployeeID`, `Sequence`, `SpecialtyID`),
CONSTRAINT `fk_EmployeeSpecialty_Employee`
FOREIGN KEY (`EmployeeID`)
REFERENCES `Employee` (`EmployeeID`),
CONSTRAINT `fk_EmployeeSpecialty_Specialty`
FOREIGN KEY (`SpecialtyID`)
REFERENCES `Specialty` (`SpecialtyID`)
);
CREATE TABLE IF NOT EXISTS `Schedule` (
`EmployeeID` VARCHAR(10) NOT NULL,
`DayOfWeek` CHAR(2) NOT NULL,
`SortSeq` INT NOT NULL,
`StartTime` TIME NOT NULL,
`EndTime` TIME NOT NULL,
PRIMARY KEY (`EmployeeID`, `DayOfWeek`),
CONSTRAINT `fk_Schedule_Employee`
FOREIGN KEY (`EmployeeID`)
REFERENCES `Employee` (`EmployeeID`)
);
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('CA001', 'PA', 'Alexander', 'Constance', '9514', 'F', '09/25/2014');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('PP001', 'FR', '5', '12:00', '20:00');
INSERT INTO `MedicalHistoryCond` (PatientID, Date, ConditionID, PrescriptionID, SurgeryID) VALUES ('', '', '', '', '');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('EL001', 'SURG', 'Lawton', 'Eleanor', '5577', 'F', '06/01/2015');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'JM001', '1', 'ORTSG');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'MB002', '1', 'FAM');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('TH001', 'Hastings', 'Timothy', '54 Linden Street', 'Woonsocket', 'RI', '01989', 'M', '2187');
INSERT INTO `MedicalHistoryCond` (PatientID, Date, ConditionID, PrescriptionID, SurgeryID) VALUES ('', '', '', '', '');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('RA001', 'Adams', 'Robert', '420 Brook Ave SE', 'Boston', 'MA', '02155-0154', 'M', '2323');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('SW001', 'Winsor', 'Stephen', '54 Tremont Street', 'Boston', 'MA', '02157-5658', 'M', '1299');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('OHRT', 'Open Heart', '', 'FAMO', 'Famotidine 20mg');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('CM001', 'MacNamara', 'Christine', '37 Colony Circle', 'Tewksbury', 'MA', '02654-0001', 'F', '0014');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('SW001', 'Winsor', 'Stephen', '54 Tremont Street', 'Boston', 'MA', '02157-5658', 'M', '1299');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'LC001', '1', 'FAM');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RM001', 'PHY', 'McNamara', 'Richard', '0015', 'M', '06/30/2019');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RK002', '2', 'INT');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('PP001', 'WE', '3', '8:00', '17:00');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('JE002', 'PA', 'Espanet', 'Jordan', 'jespanet1246', 'F', '03/12/2016');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('CAT', 'Cataract', '', 'EZE', 'Ezetimibe 10mg');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', '', '', '', 'STOM', 'Stomachache and abdominal pain');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('SW001', 'Winsor', 'Stephen', '54 Tremont Street', 'Boston', 'MA', '02157-5658', 'M', '1299');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('LC001', 'WE', '3', '8:00', '17:00');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RK002', 'PHY', 'Kershaw', 'Rowena', '6901', 'F', '10/10/2010');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('TH001', 'Hastings', 'Timothy', '54 Linden Street', 'Woonsocket', 'RI', '01989', 'M', '2187');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('APP', 'Appendectomy', '', 'TAM', 'Tamsulosin 0.4mg');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('TN001', 'RN', 'Nguyen', 'Trin', '2468', 'M', '02/02/2017');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('AB001', 'TECH', 'Bonner', 'Allison', '1265', 'F', '04/15/2015');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('RA001', 'Adams', 'Robert', '420 Brook Ave SE', 'Boston', 'MA', '02155-0154', 'M', '2323');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'TU', '2', '8:00', '12:00');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'TH', '4', '8:00', '12:00');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('SB001', 'ADMIN', 'Brandt', 'Stephen', '5454', 'M', '08/13/2014');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('PP001', 'PHY', 'Pierce', 'Paul', '1234', 'M', '12/15/2009');
INSERT INTO `Appointment` (AppointmentID, PatientID, EmployeeID, Date, StartTime, Duration, Status) VALUES ('APP007', 'JW001', 'WS001', '07/25/2023', '15:00', '2', 'Scheduled');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RK002', 'PHY', 'Kershaw', 'Rowena', '6901', 'F', '10/10/2010');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('SURG', 'Surgeon', '', 'PED', 'Pediatrics', '', 'DIAB2', 'Type 2 Diabetes');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('NS001', 'Smith', 'Nancy', '12 Royal Pointe Drive', 'Cambridge', 'MA', '02144-2525', 'F', '6954');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('SB001', 'ADMIN', 'Brandt', 'Stephen', '5454', 'M', '08/13/2014');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '2', 'NEU');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('MY001', 'Young', 'Margaret', '12 First Street', 'Pawtucket', 'RI', '01925', 'F', '7098');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('EL001', 'SURG', 'Lawton', 'Eleanor', '5577', 'F', '06/01/2015');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('ANGIO', 'Angioplasty', '', 'WARF', 'Warfarin 5mg');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('DW001', 'Winsor', 'Deborah', '14 Lyman Road', 'Nashua', 'NH', '03254', 'M', '0818');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'WE', '3', '8:00', '12:00');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '1', 'CARDIO');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('ADMIN', 'Office Staff', '', 'OBS', 'Obstetrics', '', 'COVID', 'COVID-19');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('JE002', 'PA', 'Espanet', 'Jordan', 'jespanet1246', 'F', '03/12/2016');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'MB002', '2', 'INT');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('JE002', 'PA', 'Espanet', 'Jordan', 'jespanet1246', 'F', '03/12/2016');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('SCHED', 'Scheduler', '', 'NEU', 'Neurology', '', 'CHSTP', 'Chest Pain');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '1', 'CARDIO');
INSERT INTO `Appointment` (AppointmentID, PatientID, EmployeeID, Date, StartTime, Duration, Status) VALUES ('APP003', 'TA001', 'PP001', '07/11/2023', '16:00', '1', 'Scheduled');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('JM001', 'SURG', 'Martinez', 'Jose', '2468', 'M', '01/15/2017');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('AA001', 'SURG', 'Adams', 'Abigail', '1598', 'F', '04/09/2022');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', '', '', '', 'ULCER', 'Stomach ulcer');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('CAT', 'Cataract', '', 'EZE', 'Ezetimibe 10mg');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'MB002', '1', 'FAM');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('KY001', 'Yang', 'Kim', '18 Boston Post Road', 'Sudbury', 'MA', '01958-2345', 'F', '4870');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', '', '', '', 'TONSI', 'Tonsilitis');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('PA', 'Physician Assistant', '', 'OPH', 'Ophthalmology', '', 'DIAB1', 'Type 1 Diabetes');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('CAT', 'Cataract', '', 'EZE', 'Ezetimibe 10mg');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('CA001', 'PA', 'Alexander', 'Constance', '9514', 'F', '09/25/2014');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RK001', 'RN', 'King', 'Robert', '5079', 'M', '11/10/2015');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', 'URO', 'Urology', '', 'OSTEO', 'Osteoporosis');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('INGH', 'Inguinal Hernia Repair', '', 'AMOX', 'Amoxicillin 500mg');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('MB002', 'PHY', 'Brown', 'Marcus', 'mbrown7777', 'M', '02/02/2015');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('WS001', 'FR', '5', '12:00', '17:00');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('LPN', 'Licensed Practical Nurse', '', 'FAM', 'Family Medicine', '', 'ASTH', 'Asthma');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('CN001', 'Noble', 'Charles', '17 Seaside Lane', 'Plymouth', 'MA', '01598-5555', 'M', '9514');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('ARTH', 'Arthroscopy', '', 'PROZ', 'Prozac 20mg');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RM001', 'PHY', 'McNamara', 'Richard', '0015', 'M', '06/30/2019');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('NS001', 'Smith', 'Nancy', '12 Royal Pointe Drive', 'Cambridge', 'MA', '02144-2525', 'F', '6954');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('JM001', 'SURG', 'Martinez', 'Jose', '2468', 'M', '01/15/2017');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('PP001', 'WE', '3', '8:00', '17:00');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('APP', 'Appendectomy', '', 'TAM', 'Tamsulosin 0.4mg');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('MB001', 'LPN', 'Brady', 'Marcia', '9635', 'F', '04/30/2019');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('SM001', 'McDonald', 'Susan', '32 Harvard Street', 'Cambridge', 'MA', '02140', 'F', '1257');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('RN', 'Registered Nurse', '', 'RAD', 'Radiology', '', 'ARTH', 'Arthritis');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', '', '', '', 'STOM', 'Stomachache and abdominal pain');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('ADMIN', 'Office Staff', '', 'OBS', 'Obstetrics', '', 'COVID', 'COVID-19');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'TU', '2', '8:00', '12:00');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('SJ001', 'Jones', 'Samuel', '15 Main Street', 'Chelsea', 'MA', '01503', 'M', '1234');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('ANGIO', 'Angioplasty', '', 'WARF', 'Warfarin 5mg');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', 'VASSG', 'Vascular Surgery', '', 'HDACH', 'Headaches/Migraines');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('TA001', 'Anthony', 'Todd', '1 Cambridge Street', 'Cambridge', 'MA', '02142-2562', 'M', '5972');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('JNTREP', 'Joint Replacement', '', 'SERT', 'Sertraline 50mg');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'WE', '3', '8:00', '12:00');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('PP001', 'WE', '3', '8:00', '17:00');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('LC001', 'WE', '3', '8:00', '17:00');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('PA', 'Physician Assistant', '', 'OPH', 'Ophthalmology', '', 'DIAB1', 'Type 1 Diabetes');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('RN', 'Registered Nurse', '', 'RAD', 'Radiology', '', 'ARTH', 'Arthritis');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('CN001', 'Noble', 'Charles', '17 Seaside Lane', 'Plymouth', 'MA', '01598-5555', 'M', '9514');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', 'ENT', 'Ears, Nose, Throat', '', 'EAR', 'Earache');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'TH', '4', '8:00', '12:00');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('DW001', 'Winsor', 'Deborah', '14 Lyman Road', 'Nashua', 'NH', '03254', 'M', '0818');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('', '', '', 'URO', 'Urology', '', 'OSTEO', 'Osteoporosis');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'AB001', '1', 'RAD');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('OHRT', 'Open Heart', '', 'FAMO', 'Famotidine 20mg');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '1', 'CARDIO');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '1', 'CARDIO');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('WS001', 'FR', '5', '12:00', '17:00');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('MB002', 'TU', '2', '8:00', '12:00');
INSERT INTO `Appointment` (AppointmentID, PatientID, EmployeeID, Date, StartTime, Duration, Status) VALUES ('APP007', 'JW001', 'WS001', '07/25/2023', '15:00', '2', 'Scheduled');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('APP', 'Appendectomy', '', 'TAM', 'Tamsulosin 0.4mg');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('TA001', 'Anthony', 'Todd', '1 Cambridge Street', 'Cambridge', 'MA', '02142-2562', 'M', '5972');
INSERT INTO `Appointment` (AppointmentID, PatientID, EmployeeID, Date, StartTime, Duration, Status) VALUES ('APP003', 'TA001', 'PP001', '07/11/2023', '16:00', '1', 'Scheduled');
INSERT INTO `Schedule` (EmployeeID, DayOfWeek, SortSeq, StartTime, EndTime) VALUES ('PP001', 'FR', '5', '12:00', '20:00');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('PA', 'Physician Assistant', '', 'OPH', 'Ophthalmology', '', 'DIAB1', 'Type 1 Diabetes');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'WS001', '1', 'ANES');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('PH001', 'Hohl', 'Patrick', '12 Michigan Blvd.', 'Revere', 'MA', '02023', 'M', '6007');
INSERT INTO `Surgery` (SurgeryID, Description, , PrescriptionID, Description) VALUES ('ANGIO', 'Angioplasty', '', 'WARF', 'Warfarin 5mg');
INSERT INTO `Patient` (PatientID, PatientLName, PatientFName, Street, City, State, Zip, Gender, Password) VALUES ('CM001', 'MacNamara', 'Christine', '37 Colony Circle', 'Tewksbury', 'MA', '02654-0001', 'F', '0014');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RK002', 'PHY', 'Kershaw', 'Rowena', '6901', 'F', '10/10/2010');
INSERT INTO `EmployeeType` (TypeID, Description, , SpecialtyID, Description, , ConditionID, Description) VALUES ('SURG', 'Surgeon', '', 'PED', 'Pediatrics', '', 'DIAB2', 'Type 2 Diabetes');
INSERT INTO `Employee` (EmployeeID, TypeID, EmployeeLName, EmployeeFName, Password, Gender, DateOfHire) VALUES ('RK001', 'RN', 'King', 'Robert', '5079', 'M', '11/10/2015');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RK002', '2', 'INT');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '1', 'CARDIO');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'RM001', '2', 'NEU');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'JM001', '1', 'ORTSG');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'MB002', '1', 'FAM');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'MB002', '2', 'INT');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'AB001', '1', 'RAD');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'WS001', '1', 'ANES');
INSERT INTO `Diagnosis` (AppointmentID, Diagnosis, PrescriptionID, , EmployeeID, Sequence, SpecialtyID) VALUES ('', '', '', '', 'LC001', '1', 'FAM');
Telegram: DB developers channel