DDL_scripts_v2

-- Generated by Oracle SQL Developer Data Modeler 18.4.0.339.1532
--   at:        2020-02-28 11:38:02 GMT
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c



CREATE TABLE department (
    name               VARCHAR2(15 CHAR) NOT NULL,
    floor              VARCHAR2(2 CHAR) NOT NULL,
    ward               VARCHAR2(3 CHAR) NOT NULL,
    dept_id            NUMBER NOT NULL,
    hospital_hosp_id   NUMBER NOT NULL
);

ALTER TABLE department ADD CONSTRAINT dept_pk PRIMARY KEY ( dept_id );

-- Error - Unique Constraint Department.Department__UN doesn't have columns

CREATE TABLE equipment (
    id                    VARCHAR2(15 CHAR) NOT NULL,
    barcode               VARCHAR2(12 CHAR) NOT NULL,
    name                  VARCHAR2(20 CHAR) NOT NULL,
    category              VARCHAR2(25 CHAR) NOT NULL,
    quantity              NUMBER(3) NOT NULL,
    latittude             NUMBER(10, 7) NOT NULL,
    timestamp             TIMESTAMP NOT NULL,
    loaned                DATE,
    return_due            DATE,
    longitude             NUMBER(10, 7) NOT NULL,
    longitude2            NUMBER(10, 7) NOT NULL,
    person_hospital_id1   VARCHAR2(10 CHAR) NOT NULL,
    person_patient_id     VARCHAR2(10 CHAR) NOT NULL,
    person_staff_id       VARCHAR2(10 CHAR) NOT NULL
);

ALTER TABLE equipment
    ADD CONSTRAINT equipment_pk PRIMARY KEY ( person_hospital_id1,
                                              id,
                                              person_patient_id,
                                              person_staff_id );

CREATE TABLE hospital (
    hosp_id   NUMBER NOT NULL,
    id        VARCHAR2(10 CHAR) NOT NULL,
    name      VARCHAR2(20 CHAR) NOT NULL,
    address   VARCHAR2(35 CHAR) NOT NULL,
    town      VARCHAR2(15 CHAR),
    county    VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE hospital ADD CONSTRAINT hosp_pk PRIMARY KEY ( hosp_id );

ALTER TABLE hospital ADD CONSTRAINT hospital_id_un UNIQUE ( id );

CREATE TABLE ids (
    patient_id   VARCHAR2(10 CHAR) NOT NULL,
    staff_id     VARCHAR2(10 CHAR) NOT NULL
);

ALTER TABLE ids ADD CONSTRAINT ids_pk PRIMARY KEY ( patient_id,
                                                    staff_id );

CREATE TABLE maintenance (
    issue#             VARCHAR2(2 CHAR) NOT NULL,
    details            VARCHAR2(60 CHAR) NOT NULL,
    hospital_hosp_id   NUMBER NOT NULL
);

CREATE UNIQUE INDEX maintenance__idx ON
    maintenance (
        hospital_hosp_id
    ASC );

CREATE TABLE "Pat-Equip" (
    patient_pats_id   NUMBER NOT NULL,
    equipment_id      VARCHAR2(15 CHAR) NOT NULL
);

ALTER TABLE "Pat-Equip" ADD CONSTRAINT "Pat-Equip_PK" PRIMARY KEY ( patient_pats_id,
                                                                    equipment_id );

CREATE TABLE patient (
    patient_pats_id   NUMBER NOT NULL,
    pats_id           VARCHAR2(10) NOT NULL
);

ALTER TABLE patient ADD CONSTRAINT pats_pkv1 PRIMARY KEY ( patient_pats_id );

ALTER TABLE patient ADD CONSTRAINT patient_pats_id_un UNIQUE ( pats_id );

CREATE TABLE "Pers-Equip" (
    patient_pats_id   NUMBER NOT NULL,
    equipment_id      VARCHAR2(15 CHAR) NOT NULL
);

ALTER TABLE "Pers-Equip" ADD CONSTRAINT "Pers-Equip_PK" PRIMARY KEY ( patient_pats_id,
                                                                      equipment_id );

CREATE TABLE person (
    surname          VARCHAR2(15 CHAR) NOT NULL,
    first_name       VARCHAR2(15 CHAR) NOT NULL,
    address          VARCHAR2(25 CHAR) NOT NULL,
    town             VARCHAR2(15 CHAR),
    county           VARCHAR2(30 CHAR) NOT NULL,
    pats_id          NUMBER NOT NULL,
    hospital_id      VARCHAR2(10 CHAR),
    hospital_id1     VARCHAR2(10 CHAR),
    ids_patient_id   VARCHAR2(10 CHAR) NOT NULL,
    ids_staff_id     VARCHAR2(10 CHAR) NOT NULL
);

CREATE UNIQUE INDEX person__idx ON
    person (
        ids_patient_id
    ASC,
        ids_staff_id
    ASC );

ALTER TABLE person ADD CONSTRAINT pats_pk PRIMARY KEY ( pats_id );

ALTER TABLE person
    ADD CONSTRAINT person_hospital_id2_un UNIQUE ( hospital_id1,
                                                   ids_patient_id,
                                                   ids_staff_id );

CREATE TABLE staff (
    patient_pats_id   NUMBER NOT NULL,
    staff_id          VARCHAR2(10 CHAR) NOT NULL,
    staff_id1         NUMBER NOT NULL
);

ALTER TABLE staff ADD CONSTRAINT staff_pk PRIMARY KEY ( patient_pats_id );

ALTER TABLE staff ADD CONSTRAINT staff_staff_id_un UNIQUE ( staff_id );

ALTER TABLE staff ADD CONSTRAINT staff_staff_id1_un UNIQUE ( staff_id1 );

ALTER TABLE department
    ADD CONSTRAINT department_hospital_fk FOREIGN KEY ( hospital_hosp_id )
        REFERENCES hospital ( hosp_id );

-- Error - Foreign Key Equipment_Department_FK has no columns

-- Error - Foreign Key Equipment_Maintenance_FK has no columns

ALTER TABLE equipment
    ADD CONSTRAINT equipment_person_fk FOREIGN KEY ( person_hospital_id1,
                                                     person_patient_id,
                                                     person_staff_id )
        REFERENCES person ( hospital_id1,
                            ids_patient_id,
                            ids_staff_id );

ALTER TABLE maintenance
    ADD CONSTRAINT maintenance_hospital_fk FOREIGN KEY ( hospital_hosp_id )
        REFERENCES hospital ( hosp_id );

ALTER TABLE "Pat-Equip"
    ADD CONSTRAINT "Pat-Equip_Equipment_FK" FOREIGN KEY ( equipment_id )
        REFERENCES equipment ( person_hospital_id1,
                               id,
                               person_patient_id,
                               person_staff_id );

ALTER TABLE "Pat-Equip"
    ADD CONSTRAINT "Pat-Equip_Patient_FK" FOREIGN KEY ( patient_pats_id )
        REFERENCES person ( pats_id );

ALTER TABLE person
    ADD CONSTRAINT patient_hospital_fk FOREIGN KEY ( hospital_id )
        REFERENCES hospital ( id );

ALTER TABLE patient
    ADD CONSTRAINT patientv1_patient_fk FOREIGN KEY ( patient_pats_id )
        REFERENCES person ( pats_id );

ALTER TABLE "Pers-Equip"
    ADD CONSTRAINT "Pers-Equip_Equipment_FK" FOREIGN KEY ( equipment_id )
        REFERENCES equipment ( person_hospital_id1,
                               id,
                               person_patient_id,
                               person_staff_id );

ALTER TABLE "Pers-Equip"
    ADD CONSTRAINT "Pers-Equip_Patient_FK" FOREIGN KEY ( patient_pats_id )
        REFERENCES person ( pats_id );

ALTER TABLE person
    ADD CONSTRAINT person_hospital_fk FOREIGN KEY ( hospital_id1 )
        REFERENCES hospital ( id );

ALTER TABLE person
    ADD CONSTRAINT person_ids_fk FOREIGN KEY ( ids_patient_id,
                                               ids_staff_id )
        REFERENCES ids ( patient_id,
                         staff_id );

ALTER TABLE staff
    ADD CONSTRAINT staff_patient_fk FOREIGN KEY ( patient_pats_id )
        REFERENCES person ( pats_id );

--  ERROR: No Discriminator Column found in Arc FKArc_1 - constraint trigger for Arc cannot be generated 

--  ERROR: No Discriminator Column found in Arc FKArc_1 - constraint trigger for Arc cannot be generated

CREATE SEQUENCE dept_dept_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER dept_dept_id_trg BEFORE
    INSERT ON department
    FOR EACH ROW
    WHEN ( new.dept_id IS NULL )
BEGIN
    :new.dept_id := dept_dept_id_seq.nextval;
END;
/

CREATE SEQUENCE hosp_hosp_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER hosp_hosp_id_trg BEFORE
    INSERT ON hospital
    FOR EACH ROW
    WHEN ( new.hosp_id IS NULL )
BEGIN
    :new.hosp_id := hosp_hosp_id_seq.nextval;
END;
/

CREATE SEQUENCE person_pats_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER person_pats_id_trg BEFORE
    INSERT ON person
    FOR EACH ROW
    WHEN ( new.pats_id IS NULL )
BEGIN
    :new.pats_id := person_pats_id_seq.nextval;
END;
/

CREATE SEQUENCE staff_staff_id1_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER staff_staff_id1_trg BEFORE
    INSERT ON staff
    FOR EACH ROW
    WHEN ( new.staff_id1 IS NULL )
BEGIN
    :new.staff_id1 := staff_staff_id1_seq.nextval;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            10
-- CREATE INDEX                             2
-- ALTER TABLE                             26
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           4
-- 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                          4
-- 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
-- TSDP POLICY                              0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   5
-- WARNINGS                                 0