createTables.txt

CREATE TABLE ADDRESS (ADDRESS_ID INT NOT NULL, STREET_NAME VARCHAR(255) NOT NULL, ZIP_CODE VARCHAR(255) NOT NULL, UNIT_NUMBER VARCHAR(255), CONSTRAINT ADDRESS_PK PRIMARY KEY (ADDRESS_ID) ENABLE); CREATE TABLE INSURANCE (INSURANCE_ID INT NOT NULL, COMPANY_NAME VARCHAR(255) NOT NULL, COMPANY_PHONE VARCHAR(255) NOT NULL, EMAIL VARCHAR(255) NOT NULL, WEBSITE VARCHAR(255) NOT NULL, ADDRESS_ID INT NOT NULL, CONSTRAINT INSURANCE_PK PRIMARY KEY (INSURANCE_ID), CONSTRAINT FK_INSURANCE_ADDRESS FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID) ENABLE); CREATE TABLE PATIENT (PATIENT_ID INT NOT NULL, NAME VARCHAR(255) NOT NULL, EMAIL VARCHAR(255) NOT NULL, PATIENT_ADDRESS INT NOT NULL, PATIENT_INSURANCE INT, PHONE_NUMBER VARCHAR(255) NOT NULL, CONSTRAINT PATIENT_PK PRIMARY KEY (PATIENT_ID), CONSTRAINT FK_PATIENT_ADDRESS FOREIGN KEY (PATIENT_ADDRESS) REFERENCES ADDRESS(ADDRESS_ID), CONSTRAINT FK_PATIENT_INSURANCE FOREIGN KEY (PATIENT_INSURANCE) REFERENCES INSURANCE(INSURANCE_ID) ENABLE); CREATE TABLE PROCEDURES (PROCEDURE_ID INT NOT NULL, MEDICAL_PROCEDURE VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255) NOT NULL, PRICE FLOAT(126) NOT NULL, CONSTRAINT PROCEDURES_PK PRIMARY KEY (PROCEDURE_ID) ENABLE); CREATE TABLE STAFF (STAFF_ID INT NOT NULL, NAME VARCHAR(255) NOT NULL, EMAIL VARCHAR(255) NOT NULL, STAFF_ADDRESS INT NOT NULL, PHONE_NUMBER VARCHAR(255) NOT NULL, JOB_TITLE VARCHAR(255) NOT NULL, PROCEDURE_OPTIONS CLOB, CONSTRAINT STAFF_PK PRIMARY KEY (STAFF_ID), CONSTRAINT FK_STAFF_ADDRESS FOREIGN KEY (STAFF_ADDRESS) REFERENCES ADDRESS(ADDRESS_ID) ENABLE); CREATE TABLE PART_TIME_STAFF (PART_TIME_STAFF_ID INT NOT NULL, HOURLY_SALARY FLOAT(126) NOT NULL, HOURS_WORKED INT NOT NULL, CONSTRAINT FK_PART_TIME_STAFF_ID FOREIGN KEY (PART_TIME_STAFF_ID) REFERENCES STAFF(STAFF_ID) ENABLE); CREATE TABLE FULL_TIME_STAFF (FULL_TIME_STAFF_ID INT NOT NULL, ANNUAL_SALARY INT NOT NULL, BENEFITS VARCHAR(255) NOT NULL, PENSION INT NOT NULL, CONSTRAINT FK_FULL_TIME_STAFF FOREIGN KEY (FULL_TIME_STAFF_ID) REFERENCES STAFF(STAFF_ID) ENABLE); CREATE TABLE MEDICAL_HISTORY (MEDICAL_HISTORY_ID INT NOT NULL, MEDICAL_PATIENT_ID INT NOT NULL, PROCEDURE_ID INT NOT NULL, COMMENTS VARCHAR(255), STAFF_NAME VARCHAR(255) NOT NULL, CONSTRAINT MEDICAL_HISTORY_PK PRIMARY KEY (MEDICAL_HISTORY_ID), CONSTRAINT FK_MEDICAL_PATIENT_ID FOREIGN KEY (MEDICAL_PATIENT_ID) REFERENCES PATIENT(PATIENT_ID) ENABLE); CREATE TABLE APPOINTMENT (APPOINTMENT_ID INT NOT NULL, APPOINTMENT_PATIENT_ID INT NOT NULL, APPOINTMENT_STAFF_ID INT NOT NULL, APPOINTMENT_PROCEDURE_ID INT NOT NULL, APPOINTMENT_TIME DATE NOT NULL, COMMENTS VARCHAR(255), CONSTRAINT APPOINTMENT_PK PRIMARY KEY (APPOINTMENT_ID), CONSTRAINT FK_APPOINTMENT_PATIENT_ID FOREIGN KEY (APPOINTMENT_PATIENT_ID) REFERENCES PATIENT(PATIENT_ID), CONSTRAINT APPOINTMENT_STAFF_ID FOREIGN KEY (APPOINTMENT_STAFF_ID) REFERENCES STAFF(STAFF_ID), CONSTRAINT APPOINTMENT_PROCEDURE_ID FOREIGN KEY (APPOINTMENT_PROCEDURE_ID) REFERENCES PROCEDURES(PROCEDURE_ID) ENABLE);