pomponst-final.sql

-- Generated by Oracle SQL Developer Data Modeler 17.3.0.261.1529
--   at:        2017-12-11 01:03:43 EST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE class (
    year     INTEGER NOT NULL,
    "Size"   INTEGER NOT NULL
);

ALTER TABLE class ADD CONSTRAINT class_pk PRIMARY KEY ( year );

CREATE TABLE engineering (
    engineer_id      INTEGER NOT NULL,
    eng_first_name   VARCHAR2(20) NOT NULL,
    eng_last_name    VARCHAR2(20) NOT NULL,
    department       VARCHAR2(30) NOT NULL
);

ALTER TABLE engineering ADD CONSTRAINT engineering_pk PRIMARY KEY ( engineer_id );

CREATE TABLE faculty (
    employee_id      INTEGER NOT NULL,
    emp_first_name   VARCHAR2(20) NOT NULL,
    emp_last_name    VARCHAR2(20) NOT NULL,
    title            VARCHAR2(10) NOT NULL,
    employee_id1     INTEGER NOT NULL,
    building_id      INTEGER NOT NULL
);

ALTER TABLE faculty ADD CONSTRAINT faculty_pk PRIMARY KEY ( employee_id );

CREATE TABLE furniture (
    furniture_id     INTEGER NOT NULL,
    furniture_type   VARCHAR2(50) NOT NULL,
    room_no          INTEGER NOT NULL,
    condition        VARCHAR2(20) NOT NULL
);

ALTER TABLE furniture ADD CONSTRAINT furniture_pk PRIMARY KEY ( furniture_id );

CREATE TABLE invoice (
    invoice_no    INTEGER NOT NULL,
    semester      VARCHAR2(6) NOT NULL,
    payment_due   NUMBER(7,2) NOT NULL,
    scholarship   NUMBER(7,2)
);

ALTER TABLE invoice ADD CONSTRAINT invoice_pk PRIMARY KEY ( invoice_no );

CREATE TABLE residence_hall (
    building_id     INTEGER NOT NULL,
    building_name   VARCHAR2(30) NOT NULL,
    location        VARCHAR2(20) NOT NULL,
    atm             INTEGER
);

ALTER TABLE residence_hall ADD CONSTRAINT residence_hall_pk PRIMARY KEY ( building_id );

CREATE TABLE resident (
    student_id     INTEGER NOT NULL,
    first_name     VARCHAR2(20) NOT NULL,
    last_name      VARCHAR2(20) NOT NULL,
    sex            VARCHAR2(1) NOT NULL,
    age            INTEGER NOT NULL,
    living_style   VARCHAR2(50),
    room_no        INTEGER NOT NULL,
    year           INTEGER NOT NULL,
    invoice_no     INTEGER NOT NULL
);

CREATE UNIQUE INDEX resident__idx ON
    resident ( invoice_no ASC );

ALTER TABLE resident ADD CONSTRAINT resident_pk PRIMARY KEY ( student_id );

CREATE TABLE room (
    room_no       INTEGER NOT NULL,
    room_type     VARCHAR2(20) NOT NULL,
    occupancy     INTEGER NOT NULL,
    building_id   INTEGER NOT NULL
);

ALTER TABLE room ADD CONSTRAINT room_pk PRIMARY KEY ( room_no );

CREATE TABLE security (
    officer_id       INTEGER NOT NULL,
    ofc_first_name   VARCHAR2(20) NOT NULL,
    ofc_last_name    VARCHAR2(20) NOT NULL,
    rank             VARCHAR2(10) NOT NULL,
    building_id      INTEGER NOT NULL
);

ALTER TABLE security ADD CONSTRAINT security_pk PRIMARY KEY ( officer_id );

CREATE TABLE work_order (
    work_order_no   INTEGER NOT NULL,
    priority        VARCHAR2(10) NOT NULL,
    student_id      INTEGER NOT NULL,
    engineer_id     INTEGER NOT NULL
);

ALTER TABLE work_order ADD CONSTRAINT work_order_pk PRIMARY KEY ( work_order_no );

ALTER TABLE faculty
    ADD CONSTRAINT faculty_faculty_fk FOREIGN KEY ( employee_id1 )
        REFERENCES faculty ( employee_id );

ALTER TABLE faculty
    ADD CONSTRAINT faculty_residence_hall_fk FOREIGN KEY ( building_id )
        REFERENCES residence_hall ( building_id );

ALTER TABLE furniture
    ADD CONSTRAINT furniture_room_fk FOREIGN KEY ( room_no )
        REFERENCES room ( room_no );

ALTER TABLE resident
    ADD CONSTRAINT resident_class_fk FOREIGN KEY ( year )
        REFERENCES class ( year );

ALTER TABLE resident
    ADD CONSTRAINT resident_invoice_fk FOREIGN KEY ( invoice_no )
        REFERENCES invoice ( invoice_no );

ALTER TABLE resident
    ADD CONSTRAINT resident_room_fk FOREIGN KEY ( room_no )
        REFERENCES room ( room_no );

ALTER TABLE room
    ADD CONSTRAINT room_residence_hall_fk FOREIGN KEY ( building_id )
        REFERENCES residence_hall ( building_id );

ALTER TABLE security
    ADD CONSTRAINT security_residence_hall_fk FOREIGN KEY ( building_id )
        REFERENCES residence_hall ( building_id );

ALTER TABLE work_order
    ADD CONSTRAINT work_order_engineering_fk FOREIGN KEY ( engineer_id )
        REFERENCES engineering ( engineer_id );

ALTER TABLE work_order
    ADD CONSTRAINT work_order_resident_fk FOREIGN KEY ( student_id )
        REFERENCES resident ( student_id );

CREATE SEQUENCE class_year_seq START WITH 2017 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER class_year_trg BEFORE
    INSERT ON class
    FOR EACH ROW
    WHEN ( new.year IS NULL )
BEGIN
    :new.year := class_year_seq.nextval;
END;
/

CREATE SEQUENCE engineering_engineer_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER engineering_engineer_id_trg BEFORE
    INSERT ON engineering
    FOR EACH ROW
    WHEN ( new.engineer_id IS NULL )
BEGIN
    :new.engineer_id := engineering_engineer_id_seq.nextval;
END;
/

CREATE SEQUENCE faculty_employee_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER faculty_employee_id_trg BEFORE
    INSERT ON faculty
    FOR EACH ROW
    WHEN ( new.employee_id IS NULL )
BEGIN
    :new.employee_id := faculty_employee_id_seq.nextval;
END;
/

CREATE SEQUENCE furniture_furniture_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER furniture_furniture_id_trg BEFORE
    INSERT ON furniture
    FOR EACH ROW
    WHEN ( new.furniture_id IS NULL )
BEGIN
    :new.furniture_id := furniture_furniture_id_seq.nextval;
END;
/

CREATE SEQUENCE invoice_invoice_no_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER invoice_invoice_no_trg BEFORE
    INSERT ON invoice
    FOR EACH ROW
    WHEN ( new.invoice_no IS NULL )
BEGIN
    :new.invoice_no := invoice_invoice_no_seq.nextval;
END;
/

CREATE SEQUENCE residence_hall_building_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER residence_hall_building_id_trg BEFORE
    INSERT ON residence_hall
    FOR EACH ROW
    WHEN ( new.building_id IS NULL )
BEGIN
    :new.building_id := residence_hall_building_id_seq.nextval;
END;
/

CREATE SEQUENCE resident_student_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER resident_student_id_trg BEFORE
    INSERT ON resident
    FOR EACH ROW
    WHEN ( new.student_id IS NULL )
BEGIN
    :new.student_id := resident_student_id_seq.nextval;
END;
/

CREATE SEQUENCE room_room_no_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER room_room_no_trg BEFORE
    INSERT ON room
    FOR EACH ROW
    WHEN ( new.room_no IS NULL )
BEGIN
    :new.room_no := room_room_no_seq.nextval;
END;
/

CREATE SEQUENCE security_officer_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER security_officer_id_trg BEFORE
    INSERT ON security
    FOR EACH ROW
    WHEN ( new.officer_id IS NULL )
BEGIN
    :new.officer_id := security_officer_id_seq.nextval;
END;
/

CREATE SEQUENCE work_order_work_order_no_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER work_order_work_order_no_trg BEFORE
    INSERT ON work_order
    FOR EACH ROW
    WHEN ( new.work_order_no IS NULL )
BEGIN
    :new.work_order_no := work_order_work_order_no_seq.nextval;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            10
-- CREATE INDEX                             1
-- ALTER TABLE                             20
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                          10
-- 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                         10
-- CREATE MATERIALIZED VIEW                 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