sicherheitsunterweisung/create_tables.sql

USE sicherheitsunterweisung;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS briefings;
DROP TABLE IF EXISTS participants;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS given_answers;

CREATE TABLE instructors (
  id           INT         NOT NULL AUTO_INCREMENT,
  first_name   VARCHAR(32) NOT NULL,
  last_name    VARCHAR(32) NOT NULL,
  personnel_id INT         NOT NULL,

  PRIMARY KEY(id)
);

CREATE TABLE briefings (
  id            INT         NOT NULL AUTO_INCREMENT,
  date          DATE        NOT NULL,
  time          TIME        NOT NULL,
  location      VARCHAR(32) NOT NULL,
  document_name VARCHAR(16) NOT NULL,
  as_of         DATE        NOT NULL,
  instructor_id INT         NOT NULL,

  PRIMARY KEY(id),
  FOREIGN KEY(instructor_id) REFERENCES instructors(id)
);

CREATE TABLE participants (
  id         INT         NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(32) NOT NULL,
  last_name  VARCHAR(32) NOT NULL,
  company    VARCHAR(32) NOT NULL,

  PRIMARY KEY(id)
);

CREATE TABLE questions (
  id             INT          NOT NULL AUTO_INCREMENT,
  question       VARCHAR(256) NOT NULL,
  answer_1       VARCHAR(64)  NOT NULL,
  answer_2       VARCHAR(64)  NOT NULL,
  answer_3       VARCHAR(64)  NOT NULL,
  answer_4       VARCHAR(64)  NOT NULL,
  correct_answer INT          NOT NULL,

  PRIMARY KEY(id)
);

CREATE TABLE given_answers (
  briefing_id    INT NOT NULL,
  participant_id INT NOT NULL,
  question_id    INT NOT NULL,
  given_answer   INT NOT NULL,

  PRIMARY KEY(briefing_id, participant_id, question_id),
  FOREIGN KEY(briefing_id)    REFERENCES briefings(id),
  FOREIGN KEY(participant_id) REFERENCES participants(id),
  FOREIGN KEY(question_id)    REFERENCES questions(id)
);

INSERT INTO instructors
  (first_name, last_name, personnel_id)
VALUES
  ( 'Jason', 'Streifling',     '123456' ),
  ( 'Tim',   'Taler',          '123457' ),
  ( 'Georg', 'aus dem Jungel', '123458' );

INSERT INTO briefings (
  date, time, location, as_of, instructor_id
) VALUES
  ( '2023-10-16', '17:00:00', 'Werk Langenhagen', 'ICS-2021-LGH', '2021-02-01', '1' ),
  ( '2023-10-16', '17:05:00', 'Werk Langenhagen', 'ICS-2021-LGH', '2021-02-01', '2' );

INSERT INTO participants (
  first_name, last_name, company
) VALUES
  ( 'Peter', 'Enis',  'Körber'     ),
  ( 'Dürüm', 'Döner', 'MP Technic' );

INSERT INTO questions (
  question, answer_1, answer_2, answer_3, answer_4, correct_answer
) VALUES
  ( 'Was ist 1+1?', '1', '2', '3', '4', '2' ),
  ( 'Was ist 1+2?', '1', '2', '3', '4', '3' ),
  ( 'Was ist 2+2?', '1', '2', '3', '4', '4' );

INSERT INTO given_answers (
  briefing_id, participant_id, question_id, given_answer
) VALUES
  ( '1', '1', '1', '2' ),
  ( '1', '1', '2', '3' ),
  ( '1', '1', '3', '3' ),
  ( '2', '2', '1', '2' ),
  ( '2', '2', '2', '3' ),
  ( '2', '2', '3', '4' );