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;