SQL Script for MySQL / MariaDB
-- -----------------------------------------------------
-- Table `Quizzes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ChoiceAnswerItems` ;
DROP TABLE IF EXISTS `BooleanAnswerItems` ;
DROP TABLE IF EXISTS `ChoiceAnswers` ;
DROP TABLE IF EXISTS `FillGapAnswers` ;
DROP TABLE IF EXISTS `PairNames` ;
DROP TABLE IF EXISTS `MatchingAnswers` ;
DROP TABLE IF EXISTS `CustomAnswers` ;
DROP TABLE IF EXISTS `SlideDocs` ;
DROP TABLE IF EXISTS `Slide` ;
DROP TABLE IF EXISTS `Questions` ;
DROP TABLE IF EXISTS `Categories` ;
DROP TABLE IF EXISTS `Quizzes` ;
DROP TABLE IF EXISTS `CustomHeadTags` ;
DROP TABLE IF EXISTS `CustomParameters` ;
DROP TABLE IF EXISTS `CustomQuestionTypes` ;
CREATE TABLE IF NOT EXISTS `Quizzes` (
`QZ_ID` INT NOT NULL AUTO_INCREMENT,
`quizname` VARCHAR(255) NOT NULL,
`Title` VARCHAR(1024) NOT NULL,
`IntroText` TEXT NULL,
`EpiText` TEXT NULL,
`NumQuestions` INT NOT NULL,
`Author` VARCHAR(255) NULL,
`Argument` VARCHAR(255) NULL,
`Season` VARCHAR(255) NULL,
`DateCreated` DATETIME NULL,
`DateModified` DATETIME NULL,
PRIMARY KEY (`QZ_ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Categories` (
`QZ_ID` INT NOT NULL,
`CategoryName` VARCHAR(80) NOT NULL,
`Description` TEXT NULL,
`MaxQuestForQuiz` INT NULL,
`IsRandom` TINYINT(1) NULL,
PRIMARY KEY (`CategoryName`, `QZ_ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Questions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Questions` (
`QZ_ID` INT NOT NULL,
`Num` INT NOT NULL,
`CategoryName` VARCHAR(80) NOT NULL,
`TextQuestion` MEDIUMTEXT NOT NULL,
`Type` INT NOT NULL,
`Weight` FLOAT NOT NULL,
`Include` TINYINT(1) NOT NULL,
`TextFormat` INT NOT NULL,
`SortAnswers` TINYINT(1) NOT NULL,
`TextAbove` MEDIUMTEXT NULL,
`TextBelow` MEDIUMTEXT NULL,
`Remark` MEDIUMTEXT NULL,
`PutSeparator` TINYINT(1) NULL,
`Timeout` INT NULL,
`Html` MEDIUMTEXT 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
ON UPDATE CASCADE,
CONSTRAINT `Category_FK`
FOREIGN KEY (`CategoryName` , `QZ_ID`)
REFERENCES `Categories` (`CategoryName` , `QZ_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `ChoiceAnswers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ChoiceAnswers` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`TypeOfAns` INT NOT NULL,
`SingleAns` TINYINT(1) NOT NULL,
PRIMARY KEY (`QZ_ID`, `NumQst`),
CONSTRAINT `ChoiceAns_FK`
FOREIGN KEY (`QZ_ID` , `NumQst`)
REFERENCES `Questions` (`QZ_ID` , `Num`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `ChoiceAnswerItems`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ChoiceAnswerItems` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`NumAns` INT NOT NULL,
`TextFormat` INT NOT NULL,
`Ans` MEDIUMTEXT NOT NULL,
`RightAns` TINYINT(1) NOT NULL,
`Hint` MEDIUMTEXT NULL,
`Rem` MEDIUMTEXT NULL,
`PointsIn` INT NULL,
`PointsOut` INT NULL,
`Html` MEDIUMTEXT NULL,
PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
CONSTRAINT `ChoiceItems_FK`
FOREIGN KEY (`QZ_ID` , `NumQst`)
REFERENCES `ChoiceAnswers` (`QZ_ID` , `NumQst`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `BooleanAnswerItems`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BooleanAnswerItems` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`NumAns` INT NOT NULL,
`Sentence` MEDIUMTEXT NOT NULL,
`TrueAns` TINYINT(1) NOT NULL,
`TextFormat` INT NOT NULL,
`Html` MEDIUMTEXT NULL,
PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
CONSTRAINT `BoolAns_FK`
FOREIGN KEY (`QZ_ID` , `NumQst`)
REFERENCES `Questions` (`QZ_ID` , `Num`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `FillGapAnswers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FillGapAnswers` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`Phrase` MEDIUMTEXT NOT NULL,
`Type` INT NOT NULL,
`TextFormat` INT NOT NULL,
`Html` MEDIUMTEXT NULL,
PRIMARY KEY (`QZ_ID`, `NumQst`),
CONSTRAINT `FillGap_FK`
FOREIGN KEY (`QZ_ID` , `NumQst`)
REFERENCES `Questions` (`QZ_ID` , `Num`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `MatchingAnswers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `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
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `PairNames`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PairNames` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`NumAns` INT NOT NULL,
`LeftItem` MEDIUMTEXT NOT NULL,
`RightItem` MEDIUMTEXT NOT NULL,
`TextFormat` INT NOT NULL,
`Html` MEDIUMTEXT NULL,
PRIMARY KEY (`QZ_ID`, `NumQst`, `NumAns`),
CONSTRAINT `PairNames_FK`
FOREIGN KEY (`QZ_ID` , `NumQst`)
REFERENCES `MatchingAnswers` (`QZ_ID` , `NumQst`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CustomQuestionTypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CustomQuestionTypes` (
`QZ_ID` INT NOT NULL,
`CustomName` VARCHAR(255) NOT NULL,
`Description` TEXT NULL,
`Version` INT NULL,
`Subversion` INT NULL,
`IconData` MEDIUMTEXT NULL,
`IncludeConfirmButton` TINYINT(1) NULL,
`ValuateRightWrong` TINYINT(1) NULL,
`MaxScore` INT NULL,
`MinScore` INT NULL,
`JscriptCode` MEDIUMTEXT NULL,
`HtmlCode` MEDIUMTEXT NULL,
PRIMARY KEY (`QZ_ID`, `CustomName`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CustomParameters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CustomParameters` (
`QZ_ID` INT NOT NULL,
`CustomName` VARCHAR(255) NOT NULL,
`ParamName` VARCHAR(255) NOT NULL,
`IsEncrypt` TINYINT(1) 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
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CustomHeadTags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CustomHeadTags` (
`QZ_ID` INT NOT NULL,
`CustomName` VARCHAR(255) NOT NULL,
`FileName` VARCHAR(255) NOT NULL,
`Content` MEDIUMTEXT NOT NULL,
`ContentType` INT NOT NULL,
PRIMARY KEY (`FileName`, `CustomName`, `QZ_ID`),
INDEX `FK_CustomHeadTags_idx` (`QZ_ID` ASC, `CustomName` ASC),
CONSTRAINT `FK_CustomHeadTags`
FOREIGN KEY (`QZ_ID` , `CustomName`)
REFERENCES `CustomQuestionTypes` (`QZ_ID` , `CustomName`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CustomAnswers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `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`),
INDEX `FK_CustomAns_To_CustomParams_idx` (`QZ_ID` ASC, `CustomName`
ASC, `ParamName` ASC),
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
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Slides`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Slides` (
`QZ_ID` INT NOT NULL,
`NumQst` INT NOT NULL,
`SlideName` VARCHAR(80) NOT NULL,
`Content` LONGTEXT NOT NULL,
`TextFormat` INT NOT NULL,
`BeforeQst` TINYINT(1) NOT NULL,
`IsExternal` TINYINT(1) 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
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `SlideDocs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `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`),
INDEX `FK_Doc_To_Slide_idx` (`QZ_ID` ASC, `NumQst` ASC, `SlideName`
ASC),
CONSTRAINT `FK_Doc_To_Slide`
FOREIGN KEY (`QZ_ID` , `NumQst` , `SlideName`)
REFERENCES `Slides` (`QZ_ID` , `NumQst` , `SlideName`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;