-- Generated by Oracle SQL Developer Data Modeler 18.4.0.339.1536
--   at:        2019-04-21 17:13:10 EDT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE appointment (
    appointment_id     INTEGER NOT NULL,
    doctor_id          INTEGER NOT NULL,
    patient_id         INTEGER NOT NULL,
    reason             VARCHAR2(30 BYTE),
    appointment_date   VARCHAR2(50 BYTE)
);

ALTER TABLE appointment ADD CONSTRAINT appointment_pk PRIMARY KEY ( appointment_id );

CREATE TABLE contacts (
    product_id   INTEGER NOT NULL,
    brand        VARCHAR2(50 BYTE),
    type         VARCHAR2(50 BYTE),
    color        VARCHAR2(50 BYTE)
);

ALTER TABLE contacts ADD CONSTRAINT contacts_pk PRIMARY KEY ( product_id );

CREATE TABLE doctor (
    doctor_id        INTEGER NOT NULL,
    first_name       VARCHAR2(20 BYTE),
    last_name        VARCHAR2(20 BYTE),
    specialization   VARCHAR2(20 BYTE)
);

ALTER TABLE doctor ADD CONSTRAINT doctor_pk PRIMARY KEY ( doctor_id );

CREATE TABLE frames (
    product_id   INTEGER NOT NULL,
    brand        VARCHAR2(50 BYTE),
    "Size"       VARCHAR2(50 BYTE),
    color        VARCHAR2(50 BYTE),
    material     VARCHAR2(50 BYTE)
);

ALTER TABLE frames ADD CONSTRAINT frames_pk PRIMARY KEY ( product_id );

CREATE TABLE medical_insurance (
    patient_id       INTEGER NOT NULL,
    insurance_name   VARCHAR2(30 BYTE),
    member_id        INTEGER,
    group_number     INTEGER,
    insurance_type   VARCHAR2(10 BYTE),
    co_pay           FLOAT
);

ALTER TABLE medical_insurance ADD CONSTRAINT medical_insurance_pk PRIMARY KEY ( patient_id );

CREATE TABLE order_line (
    product_id   INTEGER NOT NULL,
    quantity     INTEGER,
    order_id     INTEGER NOT NULL
);

ALTER TABLE order_line ADD CONSTRAINT order_line_pk PRIMARY KEY ( product_id,
                                                                  order_id );

CREATE TABLE orders (
    order_id      INTEGER NOT NULL,
    order_date    VARCHAR2(50 BYTE),
    order_total   INTEGER,
    patient_id    INTEGER NOT NULL
);

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY ( order_id );

CREATE TABLE patient (
    patient_id                   INTEGER NOT NULL,
    first_name                   VARCHAR2(30 BYTE),
    last_name                    VARCHAR2(30 BYTE),
    sex                          VARCHAR2(10 BYTE),
    dob                          VARCHAR2(50 BYTE),
    phone_number                 VARCHAR2(20 BYTE),
    address                      VARCHAR2(50 BYTE),
    city                         VARCHAR2(20 BYTE),
    state                        VARCHAR2(10 BYTE),
    zip                          VARCHAR2(10 BYTE),
    ssn                          VARCHAR2(20 BYTE),
    preferred_pharmacy           VARCHAR2(50 BYTE),
    preferred_pharmacy_address   VARCHAR2(50 BYTE)
);

ALTER TABLE patient ADD CONSTRAINT patient_pk PRIMARY KEY ( patient_id );

CREATE TABLE prescription (
    rx_id        INTEGER NOT NULL,
    doctor_id    INTEGER NOT NULL,
    patient_id   INTEGER NOT NULL,
    rx_date      VARCHAR2(50 BYTE),
    drug         VARCHAR2(50 BYTE),
    dosage       VARCHAR2(50 BYTE)
);

ALTER TABLE prescription ADD CONSTRAINT prescription_pk PRIMARY KEY ( rx_id );

CREATE TABLE product (
    product_id            INTEGER NOT NULL,
    product_name          VARCHAR2(50 BYTE),
    product_description   VARCHAR2(50 BYTE),
    unit_price            NUMBER(10, 10),
    product_type          VARCHAR2(8) NOT NULL
);

ALTER TABLE product
    ADD CONSTRAINT ch_inh_product CHECK ( product_type IN (
        'Contacts',
        'Frames'
    ) );

ALTER TABLE product ADD CONSTRAINT product_pk PRIMARY KEY ( product_id );

CREATE TABLE vision_plan (
    patient_id     INTEGER NOT NULL,
    plan_name      VARCHAR2(30),
    member_id      INTEGER,
    group_number   INTEGER,
    plan_type      VARCHAR2(20 BYTE)
);

ALTER TABLE vision_plan ADD CONSTRAINT vision_plan_pk PRIMARY KEY ( patient_id );

ALTER TABLE appointment
    ADD CONSTRAINT appointment_doctor_fk FOREIGN KEY ( doctor_id )
        REFERENCES doctor ( doctor_id );

ALTER TABLE appointment
    ADD CONSTRAINT appointment_patient_fk FOREIGN KEY ( patient_id )
        REFERENCES patient ( patient_id );

ALTER TABLE contacts
    ADD CONSTRAINT contacts_product_fk FOREIGN KEY ( product_id )
        REFERENCES product ( product_id );

ALTER TABLE frames
    ADD CONSTRAINT frames_product_fk FOREIGN KEY ( product_id )
        REFERENCES product ( product_id );

ALTER TABLE medical_insurance
    ADD CONSTRAINT medical_insurance_patient_fk FOREIGN KEY ( patient_id )
        REFERENCES patient ( patient_id );

ALTER TABLE order_line
    ADD CONSTRAINT order_line_orders_fk FOREIGN KEY ( order_id )
        REFERENCES orders ( order_id );

ALTER TABLE order_line
    ADD CONSTRAINT order_line_product_fk FOREIGN KEY ( product_id )
        REFERENCES product ( product_id );

ALTER TABLE orders
    ADD CONSTRAINT orders_patient_fk FOREIGN KEY ( patient_id )
        REFERENCES patient ( patient_id );

ALTER TABLE prescription
    ADD CONSTRAINT prescription_doctor_fk FOREIGN KEY ( doctor_id )
        REFERENCES doctor ( doctor_id );

ALTER TABLE prescription
    ADD CONSTRAINT prescription_patient_fk FOREIGN KEY ( patient_id )
        REFERENCES patient ( patient_id );

ALTER TABLE vision_plan
    ADD CONSTRAINT vision_plan_patient_fk FOREIGN KEY ( patient_id )
        REFERENCES patient ( patient_id );

CREATE OR REPLACE TRIGGER arc_fkarc_2_frames BEFORE
    INSERT OR UPDATE OF product_id ON frames
    FOR EACH ROW
DECLARE
    d VARCHAR2(8);
BEGIN
    SELECT
        a.product_type
    INTO d
    FROM
        product a
    WHERE
        a.product_id = :new.product_id;

    IF ( d IS NULL OR d <> 'Frames' ) THEN
        raise_application_error(-20223, 'FK Frames_Product_FK in Table Frames violates Arc constraint on Table Product - discriminator column Product_TYPE doesn''t have value ''Frames'''
        );
    END IF;

EXCEPTION
    WHEN no_data_found THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
END;
/

CREATE OR REPLACE TRIGGER arc_fkarc_2_contacts BEFORE
    INSERT OR UPDATE OF product_id ON contacts
    FOR EACH ROW
DECLARE
    d VARCHAR2(8);
BEGIN
    SELECT
        a.product_type
    INTO d
    FROM
        product a
    WHERE
        a.product_id = :new.product_id;

    IF ( d IS NULL OR d <> 'Contacts' ) THEN
        raise_application_error(-20223, 'FK Contacts_Product_FK in Table Contacts violates Arc constraint on Table Product - discriminator column Product_TYPE doesn''t have value ''Contacts'''
        );
    END IF;

EXCEPTION
    WHEN no_data_found THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                            11
-- CREATE INDEX                             0
-- ALTER TABLE                             23
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           2
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- REDACTION POLICY                         0
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0