SQL Script for Oracle
-- -----------------------------------------------------
-- Table Quizzes
-- -----------------------------------------------------
CREATE TABLE Quizzes (
QZ_ID INT NOT NULL,
quizname VARCHAR(255) NOT NULL,
Title VARCHAR(1024) NOT NULL,
IntroText CLOB NULL,
EpiText CLOB NULL,
NumQuestions INT NOT NULL,
Author VARCHAR(255) NULL,
Argument VARCHAR(255) NULL,
Season VARCHAR(255) NULL,
DateCreated TIMESTAMP NULL,
DateModified TIMESTAMP NULL,
PRIMARY KEY (QZ_ID));
CREATE SEQUENCE QUIZ_ID_SEQ;
CREATE OR REPLACE TRIGGER QUIZ_ID_TRIG
BEFORE INSERT ON Quizzes
FOR EACH ROW
WHEN (new.QZ_ID IS NULL)
BEGIN
SELECT QUIZ_ID_SEQ.NEXTVAL
INTO :new.QZ_ID
FROM dual;
END;
-- -----------------------------------------------------
-- Table Categories
-- -----------------------------------------------------
CREATE TABLE Categories (
QZ_ID INT NOT NULL,
CategoryName VARCHAR(80) NOT NULL,
Description CLOB NULL,
MaxQuestForQuiz INT NULL,
IsRandom NUMBER(1,0) NULL,
PRIMARY KEY (CategoryName, QZ_ID));
-- -----------------------------------------------------
-- Table Questions
-- -----------------------------------------------------
CREATE TABLE Questions (
QZ_ID INT NOT NULL,
Num INT NOT NULL,
CategoryName VARCHAR(80) NOT NULL,
TextQuestion CLOB NOT NULL,
Type INT NOT NULL,
Weight FLOAT NOT NULL,
Include NUMBER(1,0) NOT NULL,
TextFormat INT NOT NULL,
SortAnswers NUMBER(1,0) NOT NULL,
TextAbove CLOB NULL,
TextBelow CLOB NULL,
Remark CLOB NULL,
PutSeparator NUMBER(1,0) NULL,
Timeout INT NULL,
Html CLOB NULL,
PRIMARY KEY (QZ_ID, Num),
--INDEX Category_FK_idx (CategoryName ASC, QZ_ID ASC),
CONSTRAINT Quest_FK
FOREIGN KEY (QZ_ID)
REFERENCES Quizzes (QZ_ID)
ON DELETE CASCADE,
CONSTRAINT Category_FK
FOREIGN KEY (CategoryName , QZ_ID)
REFERENCES Categories (CategoryName , QZ_ID)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table ChoiceAnswers
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswers (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
TypeOfAns INT NOT NULL,
SingleAns NUMBER(1,0) NOT NULL,
PRIMARY KEY (QZ_ID, NumQst),
CONSTRAINT ChoiceAns_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table ChoiceAnswerItems
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswerItems (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
NumAns INT NOT NULL,
TextFormat INT NOT NULL,
Ans CLOB NOT NULL,
RightAns NUMBER(1,0) NOT NULL,
Hint CLOB NULL,
Rem CLOB NULL,
PointsIn INT NULL,
PointsOut INT NULL,
Html CLOB NULL,
PRIMARY KEY (QZ_ID, NumQst, NumAns),
CONSTRAINT ChoiceItems_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES ChoiceAnswers (QZ_ID , NumQst)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table BooleanAnswerItems
-- -----------------------------------------------------
CREATE TABLE BooleanAnswerItems (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
NumAns INT NOT NULL,
Sentence CLOB NOT NULL,
TrueAns NUMBER(1,0) NOT NULL,
TextFormat INT NOT NULL,
Html CLOB NULL,
PRIMARY KEY (QZ_ID, NumQst, NumAns),
CONSTRAINT BoolAns_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table FillGapAnswers
-- -----------------------------------------------------
CREATE TABLE FillGapAnswers (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
Phrase CLOB NOT NULL,
Type INT NOT NULL,
TextFormat INT NOT NULL,
Html CLOB NULL,
PRIMARY KEY (QZ_ID, NumQst),
CONSTRAINT FillGap_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table MatchingAnswers
-- -----------------------------------------------------
CREATE TABLE MatchingAnswers (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
Type INT NOT NULL,
PRIMARY KEY (QZ_ID, NumQst),
CONSTRAINT Match_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table PairNames
-- -----------------------------------------------------
CREATE TABLE PairNames (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
NumAns INT NOT NULL,
LeftItem CLOB NOT NULL,
RightItem CLOB NOT NULL,
TextFormat INT NOT NULL,
Html CLOB NULL,
PRIMARY KEY (QZ_ID, NumQst, NumAns),
CONSTRAINT PairNames_FK
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES MatchingAnswers (QZ_ID , NumQst)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table CustomQuestionTypes
-- -----------------------------------------------------
CREATE TABLE CustomQuestionTypes (
QZ_ID INT NOT NULL,
CustomName VARCHAR(255) NOT NULL,
Description CLOB NULL,
Version INT NULL,
Subversion INT NULL,
IconData CLOB NULL,
IncludeConfirmButton NUMBER(1,0) NULL,
ValuateRightWrong NUMBER(1,0) NULL,
MaxScore INT NULL,
MinScore INT NULL,
JscriptCode CLOB NULL,
HtmlCode CLOB NULL,
PRIMARY KEY (QZ_ID, CustomName));
-- -----------------------------------------------------
-- Table CustomParameters
-- -----------------------------------------------------
CREATE TABLE CustomParameters (
QZ_ID INT NOT NULL,
CustomName VARCHAR(255) NOT NULL,
ParamName VARCHAR(255) NOT NULL,
IsEncrypt NUMBER(1,0) NOT NULL,
Type INT NOT NULL,
DefValue VARCHAR(255) NULL,
PRIMARY KEY (QZ_ID, CustomName, ParamName),
CONSTRAINT FK_Params
FOREIGN KEY (QZ_ID , CustomName)
REFERENCES CustomQuestionTypes (QZ_ID , CustomName)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table CustomHeadTags
-- -----------------------------------------------------
CREATE TABLE CustomHeadTags (
QZ_ID INT NOT NULL,
CustomName VARCHAR(255) NOT NULL,
FileName VARCHAR(255) NOT NULL,
Content CLOB NOT NULL,
ContentType INT NOT NULL,
PRIMARY KEY (FileName, CustomName, QZ_ID),
CONSTRAINT FK_CustomHeadTags
FOREIGN KEY (QZ_ID , CustomName)
REFERENCES CustomQuestionTypes (QZ_ID , CustomName)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table CustomAnswers
-- -----------------------------------------------------
CREATE TABLE CustomAnswers (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
CustomName VARCHAR(255) NOT NULL,
ParamName VARCHAR(255) NOT NULL,
ParamValue VARCHAR(255) NOT NULL,
PRIMARY KEY (QZ_ID, NumQst, CustomName, ParamName),
CONSTRAINT FK_CustomAns_To_Question
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK_CustomAns_To_CustomParams
FOREIGN KEY (QZ_ID , CustomName , ParamName)
REFERENCES CustomParameters (QZ_ID , CustomName , ParamName)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table Slides
-- -----------------------------------------------------
CREATE TABLE Slides (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
SlideName VARCHAR(80) NOT NULL,
Content CLOB NOT NULL,
TextFormat INT NOT NULL,
BeforeQst NUMBER(1,0) NOT NULL,
IsExternal NUMBER(1,0) NOT NULL,
HtmlFileName VARCHAR(255) NULL,
SpeechFileName VARCHAR(255) NULL,
Timeout INT NULL,
PRIMARY KEY (QZ_ID, NumQst, SlideName),
CONSTRAINT FK_Slide_To_Question
FOREIGN KEY (QZ_ID , NumQst)
REFERENCES Questions (QZ_ID , Num)
ON DELETE CASCADE
);
-- -----------------------------------------------------
-- Table SlideDocs
-- -----------------------------------------------------
CREATE TABLE SlideDocs (
QZ_ID INT NOT NULL,
NumQst INT NOT NULL,
SlideName VARCHAR(80) NOT NULL,
DocFileName VARCHAR(255) NOT NULL,
LinkName VARCHAR(500) NOT NULL,
PRIMARY KEY (DocFileName, QZ_ID, NumQst, SlideName),
CONSTRAINT FK_Doc_To_Slide
FOREIGN KEY (QZ_ID , NumQst , SlideName)
REFERENCES Slides (QZ_ID , NumQst , SlideName)
ON DELETE CASCADE
);