DROP TABLE FAQ_AREA
/
DROP TABLE FAQ_TOPIC
/
DROP TABLE FAQ
/
DROP TABLE TOPIC
/
DROP TABLE AREA
/

DROP SEQUENCE FAQ_SEQ
/
DROP SEQUENCE TOPIC_SEQ
/
DROP SEQUENCE AREA_SEQ
/

CREATE TABLE AREA (
	areaID                  NUMBER NOT NULL,
	areaDesc                VARCHAR2(100),
	CONSTRAINT area_pk PRIMARY KEY (areaID)
)
/

CREATE TABLE TOPIC (
	topicID                 NUMBER NOT NULL,
	topicDesc               VARCHAR2(100),
	area                    NUMBER,
	CONSTRAINT topic_pk PRIMARY KEY (topicID),
	CONSTRAINT topic_area_fk FOREIGN KEY (area) REFERENCES AREA(areaID)
)
/

CREATE TABLE FAQ (
	questionID              NUMBER NOT NULL,
	question                VARCHAR2(4000),
	answer                  VARCHAR2(4000),
	internalFlag            NUMBER(1,0),
	productVersion          VARCHAR2(10),
	createdBy               VARCHAR2(50),
	creationDate            DATE,
	modifiedBy              VARCHAR2(50),
	modificationDate        DATE,
	CONSTRAINT faq_pk PRIMARY KEY (questionID)
)
/

CREATE TABLE FAQ_AREA (
	question                NUMBER NOT NULL,
	area                    NUMBER NOT NULL,
	CONSTRAINT faq_area_pk PRIMARY KEY (question, area),
	CONSTRAINT faq_area_faq_fk FOREIGN KEY (question) REFERENCES FAQ(questionID),
	CONSTRAINT faq_area_area_fk FOREIGN KEY (area) REFERENCES AREA(areaID)
)
/

CREATE TABLE FAQ_TOPIC (
	question                NUMBER NOT NULL,
	topic                   NUMBER NOT NULL,
	CONSTRAINT faq_topic_pk PRIMARY KEY (question, topic),
	CONSTRAINT faq_topic_faq_fk FOREIGN KEY (question) REFERENCES FAQ(questionID),
	CONSTRAINT faq_topic_topic_fk FOREIGN KEY (topic) REFERENCES TOPIC(topicID)
)
/

CREATE SEQUENCE AREA_SEQ
	START WITH 1
	INCREMENT BY 10
/

CREATE SEQUENCE TOPIC_SEQ
	START WITH 1
	INCREMENT BY 10
/

CREATE SEQUENCE FAQ_SEQ
	START WITH 1
	INCREMENT BY 1
/
