text: -- 7. Create a script to do the following (Write the script for this)
  -- a. If first time backup take backup of all the tables
  -- b. If not the first time remove the previous backup tables and take new
  -- backups.
  
  DROP SCHEMA IF EXISTS DocTableBackUp;
  CREATE SCHEMA IF NOT EXISTS DocTableBackUp;
  -- INSERT INTO DocTableBackUp SELECT * from DocTableBackUp;
  -- ^ Wish this would work, only works on tables not database and without mysqldumps:
  
  DROP SCHEMA IF EXISTS DocTableBackUp;
  CREATE SCHEMA IF NOT EXISTS DocTableBackUp;
  SET FOREIGN_KEY_CHECKS=0;
  
  
  -- Definition of table 'DEPARTMENT'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.DEPARTMENT (
      departmentId INT NOT NULL,
      name VARCHAR(50),
      PRIMARY KEY (departmentId)
  );
  
  -- Definition of table 'SPECIALTY'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.SPECIALTY (
      specialtyId INT NOT NULL AUTO_INCREMENT,
      specialtyName VARCHAR(50) NULL,
      PRIMARY KEY (specialtyID)
  );
  
  -- Definition of table 'DOCTOR'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.DOCTOR (
      doctorId VARCHAR(08) NOT NULL,
      lastName  VARCHAR(50) NULL,
      firstName VARCHAR(50) NULL,
      gender VARCHAR(10) NULL,
      specialtyId INT NULL,
      departmentId INT NOT NULL,
  	phoneNumber VARCHAR(10) NULL,
      email VARCHAR(50) NULL,
      streetAddress VARCHAR(100) NULL,
      city VARCHAR(50) NULL,
      state VARCHAR(2) NULL,
      zip INT NULL,
      PRIMARY KEY (doctorId),
      CONSTRAINT FK_Doctor_Specialty
          FOREIGN KEY (specialtyId)
          REFERENCES DocTableBackUp.SPECIALTY (specialtyId)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION,
      CONSTRAINT FK_Doctor_Department
          FOREIGN KEY (departmentId)
          REFERENCES DocTableBackUp.DEPARTMENT (departmentId)
          ON DELETE NO ACTION ON UPDATE NO ACTION
  );
  
  -- Definition of table 'PATIENT'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.PATIENT(
      patientID INT NOT NULL AUTO_INCREMENT,
      careProvider VARCHAR(20) NULL,
  	ssn VARCHAR(09) NULL,
      lastName  VARCHAR(50) NULL,
      firstName VARCHAR(50) NULL,
      gender VARCHAR(10) NULL,
      dateOfBirth DATE NULL,
      phoneNumber VARCHAR(10) NULL,
      email VARCHAR(50) NULL,
      streetAddress VARCHAR(100) NULL,
      city VARCHAR(50) NULL,
      state VARCHAR(2) NULL,
      zip INT NULL,
  	PRIMARY KEY (patientId)
  );
  
  -- Definition of table 'TREATMENT' or PATIENT HAS TEST/PRESCRIPTION AKA PatientVisits
  -- Primary Key has multiple because in one day:
      -- Patient can see multiple doctors
      -- Doctors can see multiple patients
      -- Patients can recieve more than one test (Tests/Prescriptions)
  CREATE TABLE IF NOT EXISTS DocTableBackUp.TREATMENT (
      patientID INT NOT NULL,
      doctorID VARCHAR(08) NOT NULL,
      test_ID INT NOT NULL,
      medicine_ID INT NOT NULL,
      appointmentDate DATE NOT NULL,
      PRIMARY KEY (patientID, doctorID, test_ID, appointmentDate),
      CONSTRAINT FK_TREATMENT_HAS_PATIENT
          FOREIGN KEY (patientID)
          REFERENCES DocTableBackUp.PATIENT (patientId)
          ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT FK_TREATMENT_HAS_DOCTOR
          FOREIGN KEY (doctorID)
          REFERENCES DocTableBackUp.DOCTOR (doctorID)
          ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT FK_PATIENT_HAS_TEST
          FOREIGN KEY (test_ID)
          REFERENCES DocTableBackUp.TEST (test_ID)
          ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT FK_PATIENT_HAS_MEDICINE
          FOREIGN KEY (medicine_ID)
          REFERENCES DocTableBackUp.PRESCRIPTION (medicine_ID)
          ON DELETE NO ACTION ON UPDATE NO ACTION
  );
  
  -- Definition of table 'PRESCRIPTION'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.PRESCRIPTION
  ( 
      medicine_ID INT NOT NULL, 
      dosage INT NULL,
      medName VARCHAR(50) NULL,
      medPrice FLOAT NULL,
      PRIMARY KEY (medicine_ID)
  );
  
  
  -- Definition of table 'TEST'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.TEST ( 
      test_ID INT NOT NULL,
      testName VARCHAR(50) NULL,
      testPrice FLOAT NULL,
      PRIMARY KEY (test_ID)
  );
  
  -- Definition of table 'APPOINTMENT'
      -- UNUSED, but may be implemented in the future
  CREATE TABLE IF NOT EXISTS DocTableBackUp.APPOINTMENT (
      appointmentTime datetime NOT NULL,
      medical_reason VARCHAR(50) NULL,
      startTime DATETIME NULL,
      endTime DATETIME NULL,
      location VARCHAR(50) NULL
  );
  
  -- Definition of table 'AUDIT_TABLE'
  CREATE TABLE IF NOT EXISTS DocTableBackUp.AUDIT_TABLE (
      doctorFirstName VARCHAR(50) NULL,
      actionType VARCHAR(10) NULL,
      specialtyName VARCHAR(50) NULL,
      dateOfModification DATE NULL
  );
  
  
  SET FOREIGN_KEY_CHECKS=1;
  
  -- Copy data from DocOffice Schema to DocTableBackup
  INSERT INTO DocTableBackUp.DEPARTMENT SELECT * FROM DocOffice.DEPARTMENT;
  INSERT INTO DocTableBackUp.SPECIALTY SELECT * FROM DocOffice.SPECIALTY;
  INSERT INTO DocTableBackUp.DOCTOR SELECT * FROM DocOffice.DOCTOR;
  INSERT INTO DocTableBackUp.PATIENT SELECT * FROM DocOffice.PATIENT;
  INSERT INTO DocTableBackUp.PRESCRIPTION SELECT * FROM DocOffice.PRESCRIPTION;
  INSERT INTO DocTableBackUp.TEST SELECT * FROM DocOffice.TEST;
  INSERT INTO DocTableBackUp.TREATMENT SELECT * FROM DocOffice.TREATMENT;
  INSERT INTO DocTableBackUp.AUDIT_TABLE SELECT * FROM DocOffice.AUDIT_TABLE;
  
  