-- Design Problem 1

-- Sean Roberts

-- IT 265 04/10/2011



--CLEAR TABLES AND SEQUENCE

DROP TABLE REST_TABLE;

DROP TABLE NBR_TABLE;

DROP TABLE CMNT_TABLE;

DROP TABLE URTN_TABLE;

DROP TABLE RVR_TABLE;

DROP TABLE REV_TABLE;

DROP SEQUENCE REST_SEQ_ID;

DROP SEQUENCE NBR_SEQ_ID;

DROP SEQUENCE CMNT_SEQ_ID;

DROP SEQUENCE URTN_SEQ_ID;

DROP SEQUENCE RVR_SEQ_ID;

DROP SEQUENCE REV_SEQ_ID;







-- Step 1) Tables, Data Type and Constraints



-- Auto Commit off

SET AUTOCOMMIT OFF;



CREATE SEQUENCE REST_SEQ_ID

START WITH 1

INCREMENT BY 1;

CREATE TABLE REST_TABLE

(REST_ID      NUMBER(3) not null,

R_NAME      VARCHAR2(40) not null,

CONSTRAINT REST_PK PRIMARY KEY (REST_ID));



CREATE SEQUENCE NBR_SEQ_ID

START WITH 100

INCREMENT BY 1;

CREATE TABLE NBR_TABLE

(NBR_ID      NUMBER(3) not null,

NBR_NAME     VARCHAR2(40) not null,

CONSTRAINT NBR_PK PRIMARY KEY (NBR_ID));



CREATE SEQUENCE CMNT_SEQ_ID

START WITH 200

INCREMENT BY 1;

CREATE TABLE CMNT_TABLE

(CMNT_ID      NUMBER(3) not null,

CMNT       VARCHAR2(240) not null,

CONSTRAINT CMNT_PK PRIMARY KEY (CMNT_ID));



CREATE SEQUENCE URTN_SEQ_ID

START WITH 300

INCREMENT BY 1;

CREATE TABLE URTN_TABLE

(URTN_ID      NUMBER(3) not null,

RATING        VARCHAR2(240) not null,

R_ID          NUMBER(3) not null,

CONSTRAINT URTN_PK PRIMARY KEY (URTN_ID));



CREATE SEQUENCE RVR_SEQ_ID

START WITH 400

INCREMENT BY 1;

CREATE TABLE RVR_TABLE

(RVR_ID      NUMBER(3) not null,

REV_FNAME        VARCHAR2(15) not null,

REV_LNAME        VARCHAR2(15) not null,

CONSTRAINT RVR_PK PRIMARY KEY (RVR_ID));



CREATE SEQUENCE REV_SEQ_ID

START WITH 500

INCREMENT BY 1;

CREATE TABLE REV_TABLE

(REV_ID      NUMBER(3) not null,

REVIEW       VARCHAR2(240) not null,

REST_ID      NUMBER(3) not null,

URTN_ID      NUMBER(3) not null,

NBR_ID         NUMBER(3) not null,

CMNT_ID         NUMBER(3) not null,

RVR_ID       NUMBER(3) not null,

CONSTRAINT REVIEW_PK PRIMARY KEY (REV_ID),



CONSTRAINT (FK_REST_ID

FOREIGN KEY (REST_ID)

REFERENCES REST_TABLE(REST_ID)),



CONSTRAINT (FK_URTN_ID

FOREIGN KEY (URTN_ID)

REFERENCES URTN_TABLE(URTN_ID)),



CONSTRAINT (FK_NBR_ID

FOREIGN KEY (NBR_ID)

REFERENCES NBR_TABLE(NBR_ID)),



CONSTRAINT (FK_CMNT_ID

FOREIGN KEY (CMNT_ID)

REFERENCES CMNT_TABLE(CMNT_ID)),



CONSTRAINT (FK_RVR_ID

FOREIGN KEY (RVR_ID)

REFERENCES RVR_TABLE(RVR_ID)));











--Auto Commit on and commit

COMMIT;

SET AUTOCOMMIT ON;