DROP TABLE TRANSACTION;
DROP TABLE salesperson;
DROP TABLE onDate;

/* author Alex C */
CREATE TABLE TRANSACTION {
    trans_id    VARCHAR2(25)
        CONSTRAINT pk_trans_id PRIMARY KEY,
    trans_total NUMBER (10,2)
        CONSTRAINT nn_trans_total NOT NULL
CONSTRAINT chk_trans_total
    CHECK (trans_total > 0.00)
};

/* author Alex C */
CREATE TABLE salesperson {
    sales_id    VARCHAR2(25)
        CONSTRAINT pk_sales_id PRIMARY KEY,
    sales_first_name    VARCHAR2(20),
    sales_last_name VARCHAR2(20),
    sales_phone_number  VARCHAR2(14)
        CONSTRAINT nn_sales_phone_number NOT NULL,
    sales_email     VARCHAR2(40)
};
/* table changes:
 * 10/17 split up '_name' into first and last name
 */


/*
 * author Alex C
 * TODO: need to compound primary key
 */
CREATE TABLE onDate {
    ondate_trans_id VARCHAR2(25),
    ondate_sales_id VARCHAR2(25),
    ondate_date     DATE,
CONSTRAINT fk_trans_id FOREIGN KEY (trans_id)
    REFERENCES TRANSACTION ON DELETE CASCADE,
CONSTRAINT fk_sales_id FOREIGN KEY (sales_id)
    REFERENCES salesperson ON DELETE CASCADE
};
/* table changes:
 * 10/17 added '_date'
 */

DROP TABLE object;
DROP TABLE admin_location;

/* author Lorena S */
CREATE TABLE object(

    obj_serial NUMBER(10)
        ONSTRAINT pk_object PRIMARY KEY,
    obj_model VARCHAR2(20),
    obj_condition_code NUMBER(5),
    obj_osi_type VARCHAR2(15),
    obj_cost NUMBER(10,12)
        CONSTRAINT ck_obj_cost
            CHECK (obj_cost > 0.00),
    obj_port_count NUMBER(5),
    obj_weight NUMBER(10,15),
    obj_power_consumption VARCHAR2(30),
    obj_slot_dimension NUMBER(10), /*I don't really know what you mean with this attribute, I mean, I don't know what type this attribute should be*/
    trans_id VARCHAR2(25),
    admin_login_id VARCHAR2(25),
    loc_id VARCHAR2(40),
        CONSTRAINT fk_trans_id FOREIGN KEY (trans_id)
            REFERENCES TRANSACTION ON DELETE CASCADE,
        CONSTRAINT fk_admin_login_id FOREIGN KEY (admin_login_id)
            REFERENCES membership ON DELETE CASCADE,
        CONSTRAINT fk_loc_id FOREIGN KEY (loc_id)
            REFERENCES admin_location ON DELETE CASCADE
);

/* author Lorena S */
CREATE TABLE admin_location(
    loc_id VARCHAR2(25)
        CONSTRAINT pk_admin_location PRIMARY KEY,
    loc_dept VARCHAR2(25),
    loc_building VARCHAR2(40)
        CONSTRAINT nn_loc_building NOT NULL,
    loc_room VARCHAR2(10)
        CONSTRAINT nn_loc_room NOT NULL,
    loc_jack VARCHAR2(20) /*I don't know for what is this*/
);

/* author Brian P */
CREATE TABLE ROLE {
        role_name       VARCHAR2(20)
                CONSTRAINT pk_role_name PRIMARY KEY,
        role_access_type        VARCHAR2(20)
                CONSTRAINT nn_access_type NOT NULL,
        role_description        VARCHAR2(50)
};

/* author Brian P */
CREATE TABLE membership {
        admin_login_id  VARCHAR2(25)
                CONSTRAINT fk_login_id FOREIGN KEY (admin_login_id)
                        REFERENCES administration ON DELETE CASCADE,
        role_name       VARCHAR2(20)
                CONSTRAINT fk_role_name (role_name)
                        REFERENCES ROLE ON DELETE CASCADE,
};

/* author Kyle H */
CREATE TABLE administrator(
        admin_login_id VARCHAR2(20)
                CONSTRAINT pk_administrator PRIMARY KEY,
        admin_date_of_hire        DATE,
        admin_work_title        VARCHAR2(25)
);

/* author Kyle H */
CREATE TABLE responsibility(
        resp_object        VARCHAR2(25),
        resp_admin_id        CHAR(8),
        resp_date        DATE,
        CONSTRAINT fk_obj_serial FOREIGN KEY (obj_serial) /*Not sure if we wanna use serial number for access*/
        REFERENCES object ON DELETE CASCADE,
        CONSTRAINT fk_admin_login_id FOREIGN KEY (admin_login_id)
        REFERENCES administrator ON DELETE CASCADE
);