CREATE TABLE PERSONS
(
ID NUMBER(5) NOT NULL,
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(200) NOT NULL,
JOB_TITLE VARCHAR2(4000),
ORGANIZATION VARCHAR2(200),
YEAR_OF_BIRTH NUMBER(4),
GENDER VARCHAR2(1)
)
;

CREATE TABLE RESPONSES
(
ID NUMBER(5) NOT NULL,
TIMESTAMP_OF_RESPONSE DATE,
COMMENTS VARCHAR2(4000),
PSN_ID NUMBER(5) NOT NULL,
SVY_ID NUMBER(5) NOT NULL
)
;

CREATE TABLE SURVEYS
(
ID NUMBER(5) NOT NULL,
TITLE VARCHAR2(250) NOT NULL,
DESCRIPTION VARCHAR2(4000),
START_DATE DATE
)
;

CREATE TABLE ANSWERS
(
ID NUMBER(6) NOT NULL,
VALUE VARCHAR2(4000),
RPE_ID NUMBER(5) NOT NULL,
QTN_ID NUMBER(5) NOT NULL
)
;

CREATE TABLE QUESTIONS
(
ID NUMBER(5) NOT NULL,
TEXT VARCHAR2(4000) NOT NULL,
STYLE VARCHAR2(10) DEFAULT 'OPEN' NOT NULL,
DATA_TYPE VARCHAR2(15) DEFAULT 'STRING' NOT NULL,
DISPLAY_TYPE VARCHAR2(5),
EXPLANATION VARCHAR2(4000),
SVY_ID NUMBER(5) NOT NULL
)
;

CREATE TABLE ANSWER_OPTIONS
(
ID NUMBER(5) NOT NULL,
VALUE VARCHAR2(100) NOT NULL,
LABEL VARCHAR2(500) NOT NULL,
EXPLANATION VARCHAR2(4000),
QTN_ID NUMBER(5) NOT NULL
)
;

ALTER TABLE PERSONS
ADD CONSTRAINT PERSON_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE RESPONSES
ADD CONSTRAINT RESPONSE_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE SURVEYS
ADD CONSTRAINT SURVEY_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE ANSWERS
ADD CONSTRAINT ANSWER_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE QUESTIONS
ADD CONSTRAINT QUESTION_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE ANSWER_OPTIONS
ADD CONSTRAINT ANSWER_OPTION_PK PRIMARY KEY
(
ID
)
 ENABLE
;

ALTER TABLE RESPONSES
ADD CONSTRAINT RESPONSE_PERSON_FK FOREIGN KEY
(
PSN_ID
)
REFERENCES PERSONS
(
ID
) ENABLE
;

ALTER TABLE RESPONSES
ADD CONSTRAINT RESPONSE_SURVEY_FK FOREIGN KEY
(
SVY_ID
)
REFERENCES SURVEYS
(
ID
) ENABLE
;

ALTER TABLE ANSWERS
ADD CONSTRAINT ANSWER_RESPONSE_FK FOREIGN KEY
(
RPE_ID
)
REFERENCES RESPONSES
(
ID
) ENABLE
;

ALTER TABLE ANSWERS
ADD CONSTRAINT ANSWER_QUESTION_FK FOREIGN KEY
(
QTN_ID
)
REFERENCES QUESTIONS
(
ID
) ENABLE
;

ALTER TABLE QUESTIONS
ADD CONSTRAINT QUESTION_SURVEY_FK FOREIGN KEY
(
SVY_ID
)
REFERENCES SURVEYS
(
ID
) ENABLE
;

ALTER TABLE ANSWER_OPTIONS
ADD CONSTRAINT ANSWER_OPTION_QUESTION_FK FOREIGN KEY
(
QTN_ID
)
REFERENCES QUESTIONS
(
ID
) ENABLE
;