SQL Script for Microsoft SQL Server

-- -----------------------------------------------------
-- Table Quizzes
-- -----------------------------------------------------
CREATE TABLE Quizzes (
  QZ_ID INT NOT NULL IDENTITY (1,1),
  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))



-- -----------------------------------------------------
-- Table Categories
-- -----------------------------------------------------
CREATE TABLE Categories (
  QZ_ID INT NOT NULL,
  CategoryName VARCHAR(80) NOT NULL,
  Description TEXT NULL,
  MaxQuestForQuiz INT NULL,
  IsRandom BIT 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 TEXT NOT NULL,
  Type INT NOT NULL,
  Weight FLOAT NOT NULL,
  Include BIT NOT NULL,
  TextFormat INT NOT NULL,
  SortAnswers BIT NOT NULL,
  TextAbove TEXT NULL,
  TextBelow TEXT NULL,
  Remark TEXT NULL,
  PutSeparator BIT NULL,
  Timeout INT NULL,
  Html TEXT NULL,
  PRIMARY KEY (QZ_ID, Num), 
  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)



-- -----------------------------------------------------
-- Table ChoiceAnswers
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  TypeOfAns INT NOT NULL,
  SingleAns BIT 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)



-- -----------------------------------------------------
-- Table ChoiceAnswerItems
-- -----------------------------------------------------
CREATE TABLE ChoiceAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  TextFormat INT NOT NULL,
  Ans TEXT NOT NULL,
  RightAns BIT NOT NULL,
  Hint TEXT NULL,
  Rem TEXT NULL,
  PointsIn INT NULL,
  PointsOut INT NULL,
  Html TEXT 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)



-- -----------------------------------------------------
-- Table BooleanAnswerItems
-- -----------------------------------------------------
CREATE TABLE BooleanAnswerItems (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  Sentence TEXT NOT NULL,
  TrueAns BIT NOT NULL,
  TextFormat INT NOT NULL,
  Html TEXT 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)



-- -----------------------------------------------------
-- Table FillGapAnswers
-- -----------------------------------------------------
CREATE TABLE FillGapAnswers (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  Phrase TEXT NOT NULL,
  Type INT NOT NULL,
  TextFormat INT NOT NULL,
  Html TEXT 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)



-- -----------------------------------------------------
-- 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
    ON UPDATE CASCADE)



-- -----------------------------------------------------
-- Table PairNames
-- -----------------------------------------------------
CREATE TABLE PairNames (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  NumAns INT NOT NULL,
  LeftItem TEXT NOT NULL,
  RightItem TEXT NOT NULL,
  TextFormat INT NOT NULL,
  Html TEXT 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)


      
-- -----------------------------------------------------
-- Table CustomQuestionTypes
-- -----------------------------------------------------

CREATE TABLE CustomQuestionTypes (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  Description TEXT NULL,
  Version INT NULL,
  Subversion INT NULL,
  IconData TEXT NULL,
  IncludeConfirmButton BIT NULL,
  ValuateRightWrong BIT NULL,
  MaxScore INT NULL,
  MinScore INT NULL,
  JscriptCode TEXT NULL,
  HtmlCode TEXT 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 BIT 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)



-- -----------------------------------------------------
-- Table CustomHeadTags
-- -----------------------------------------------------

CREATE TABLE CustomHeadTags (
  QZ_ID INT NOT NULL,
  CustomName VARCHAR(255) NOT NULL,
  FileName VARCHAR(255) NOT NULL,
  Content TEXT 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
    ON UPDATE 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
    ON UPDATE CASCADE)



-- -----------------------------------------------------
-- Table Slides
-- -----------------------------------------------------
CREATE TABLE Slides (
  QZ_ID INT NOT NULL,
  NumQst INT NOT NULL,
  SlideName VARCHAR(80) NOT NULL,
  Content TEXT NOT NULL,
  TextFormat INT NOT NULL,
  BeforeQst BIT NOT NULL,
  IsExternal BIT 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)



-- -----------------------------------------------------
-- 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 NO ACTION
    ON UPDATE NO ACTION)