JGQQqQ5j

1CREATE DATABASE IF NOT EXISTS quizdb;
2USE quizdb;
3-- user:
4--     [A]id, [B]name, [C]email, [D]password, [E]creation_date
5CREATE TABLE IF NOT EXISTS users(
6  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
7  name varchar(128),
8  email varchar(128),
9  password varchar(128),
10  creation_date date
11);
12
13-- admin:
14--     &[F]id -> [A]
15CREATE TABLE IF NOT EXISTS admins (
16  user_id INT UNSIGNED
17);
18
19-- student:
20--     &[G]id -> [A], [Z]grade (for each id & grade)
21CREATE TABLE IF NOT EXISTS student (
22  user_id INT UNSIGNED,
23  grade FLOAT UNSIGNED
24);
25
26-- grades:
27--     &[X]id -> [G], [Y]quiz_grade,
28CREATE TABLE IF NOT EXISTS grades (
29  user_id INT UNSIGNED,
30  quiz_grade FLOAT UNSIGNED
31);
32-- questions:
33--     [H]id, [I]question, &[J]answer -> [O], &[K]quiz -> [Q]
34CREATE TABLE IF NOT EXISTS questions (
35  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
36  question varchar(1024),
37  points FLOAT UNSIGNED,
38  answer_id INT UNSIGNED,
39  quiz_id INT UNSIGNED
40);
41
42-- responses:
43--     [M]id, [N]response, &[O]question -> [H]
44CREATE TABLE IF NOT EXISTS responses (
45  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
46  response varchar(1024),
47  question_id INT UNSIGNED
48);
49
50-- answers:
51--     &[P]id -> [M]
52CREATE TABLE IF NOT EXISTS answers (
53  response_id INT UNSIGNED
54);
55
56-- quizes:
57--     [Q]id
58CREATE TABLE IF NOT EXISTS quizes (
59  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
60  title varchar(128)
61);