A simple system for creating multiple-choice questions for an audience to answer on their phones. Responses are tallied and displayed in real-time. https://openquestion.netsyms.com

database.sql 2.4KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172
  1. -- MySQL Script generated by MySQL Workbench
  2. -- Mon 05 Feb 2018 11:09:37 PM MST
  3. -- Model: New Model Version: 1.0
  4. -- MySQL Workbench Forward Engineering
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  8. -- -----------------------------------------------------
  9. -- Schema openquestion
  10. -- -----------------------------------------------------
  11. -- -----------------------------------------------------
  12. -- Schema openquestion
  13. -- -----------------------------------------------------
  14. CREATE SCHEMA IF NOT EXISTS `openquestion` DEFAULT CHARACTER SET utf8 ;
  15. USE `openquestion` ;
  16. -- -----------------------------------------------------
  17. -- Table `openquestion`.`questions`
  18. -- -----------------------------------------------------
  19. CREATE TABLE IF NOT EXISTS `openquestion`.`questions` (
  20. `qid` INT NOT NULL AUTO_INCREMENT,
  21. `qtext` TEXT NOT NULL,
  22. `qcode` VARCHAR(20) NOT NULL,
  23. PRIMARY KEY (`qid`),
  24. UNIQUE INDEX `qid_UNIQUE` (`qid` ASC))
  25. ENGINE = InnoDB;
  26. -- -----------------------------------------------------
  27. -- Table `openquestion`.`answers`
  28. -- -----------------------------------------------------
  29. CREATE TABLE IF NOT EXISTS `openquestion`.`answers` (
  30. `aid` INT NOT NULL AUTO_INCREMENT,
  31. `atext` TEXT NOT NULL,
  32. `qid` INT NOT NULL,
  33. PRIMARY KEY (`aid`, `qid`),
  34. UNIQUE INDEX `aid_UNIQUE` (`aid` ASC),
  35. INDEX `fk_answers_questions_idx` (`qid` ASC),
  36. CONSTRAINT `fk_answers_questions`
  37. FOREIGN KEY (`qid`)
  38. REFERENCES `openquestion`.`questions` (`qid`)
  39. ON DELETE NO ACTION
  40. ON UPDATE NO ACTION)
  41. ENGINE = InnoDB;
  42. -- -----------------------------------------------------
  43. -- Table `openquestion`.`responses`
  44. -- -----------------------------------------------------
  45. CREATE TABLE IF NOT EXISTS `openquestion`.`responses` (
  46. `rid` INT NOT NULL AUTO_INCREMENT,
  47. `aid` INT NOT NULL,
  48. `qid` INT NOT NULL,
  49. `timestamp` DATETIME NOT NULL,
  50. `name` VARCHAR(100) NULL,
  51. PRIMARY KEY (`rid`, `aid`, `qid`),
  52. UNIQUE INDEX `rid_UNIQUE` (`rid` ASC),
  53. INDEX `fk_responses_answers1_idx` (`aid` ASC, `qid` ASC),
  54. CONSTRAINT `fk_responses_answers1`
  55. FOREIGN KEY (`aid` , `qid`)
  56. REFERENCES `openquestion`.`answers` (`aid` , `qid`)
  57. ON DELETE NO ACTION
  58. ON UPDATE NO ACTION)
  59. ENGINE = InnoDB;
  60. SET SQL_MODE=@OLD_SQL_MODE;
  61. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  62. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;