再試験課題2025

-- 外部キー制約を一時解除

SET FOREIGN_KEY_CHECKS = 0;

-- 性別マスタ

drop table if exists genders;

CREATE TABLE genders (

    id    INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 学生マスタ

drop table if exists students;

CREATE TABLE students (

    id         INT AUTO_INCREMENT PRIMARY KEY,

    name       VARCHAR(20) NOT NULL,

    gender_id  INT,

    birth_date DATE,

    FOREIGN KEY (gender_id) REFERENCES genders(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 調査フォーム

drop table if exists surveys;

CREATE TABLE surveys (

    id          INT AUTO_INCREMENT PRIMARY KEY,

    title       VARCHAR(100) NOT NULL,

    description TEXT,

    start_at    DATETIME,

    end_at      DATETIME

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 設問埋込型テーブル

DROP TABLE IF EXISTS survey_questions;

CREATE TABLE survey_questions (

    id            INT AUTO_INCREMENT PRIMARY KEY,

    survey_id     INT NOT NULL,

    title         VARCHAR(255) NOT NULL COMMENT '設問の短いタイトル',

    content       TEXT         NOT NULL COMMENT '設問の詳細本文',

    display_order INT         NOT NULL DEFAULT 0,

    answer_type   ENUM('text','int','yes_no','single_choice','multi_choice') NOT NULL,

    FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 選択肢テーブル

drop table if exists question_options;

CREATE TABLE question_options (

    id                 INT AUTO_INCREMENT PRIMARY KEY,

    survey_question_id INT NOT NULL,

    option_text        VARCHAR(255) NOT NULL,

    display_order      INT         NOT NULL DEFAULT 0,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 回答セッション

drop table if exists survey_responses;

CREATE TABLE survey_responses (

    id           INT AUTO_INCREMENT PRIMARY KEY,

    survey_id    INT NOT NULL,

    student_id   INT NOT NULL,

    responded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (survey_id)   REFERENCES surveys(id) ON DELETE CASCADE,

    FOREIGN KEY (student_id)  REFERENCES students(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- テキスト回答

drop table if exists text_answers;

CREATE TABLE text_answers (

    id                 INT AUTO_INCREMENT PRIMARY KEY,

    survey_response_id INT NOT NULL,

    survey_question_id INT NOT NULL,

    answer             TEXT,

    FOREIGN KEY (survey_response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 数値回答

drop table if exists int_answers;

CREATE TABLE int_answers (

    id                 INT AUTO_INCREMENT PRIMARY KEY,

    survey_response_id INT NOT NULL,

    survey_question_id INT NOT NULL,

    answer             INT,

    FOREIGN KEY (survey_response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Yes/No回答

drop table if exists yes_no_answers;

CREATE TABLE yes_no_answers (

    id                 INT AUTO_INCREMENT PRIMARY KEY,

    survey_response_id INT NOT NULL,

    survey_question_id INT NOT NULL,

    answer             BOOLEAN,

    FOREIGN KEY (survey_response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 単一選択回答

drop table if exists single_choice_answers;

CREATE TABLE single_choice_answers (

    id                   INT AUTO_INCREMENT PRIMARY KEY,

    survey_response_id   INT NOT NULL,

    survey_question_id   INT NOT NULL,

    question_option_id   INT NOT NULL,

    UNIQUE KEY uq_sca(survey_response_id, survey_question_id),

    FOREIGN KEY (survey_response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE,

    FOREIGN KEY (question_option_id) REFERENCES question_options(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 複数選択回答

drop table if exists multi_choice_answers;

CREATE TABLE multi_choice_answers (

    id                   INT AUTO_INCREMENT PRIMARY KEY,

    survey_response_id   INT NOT NULL,

    survey_question_id   INT NOT NULL,

    question_option_id   INT NOT NULL,

    UNIQUE KEY uq_mca(survey_response_id, survey_question_id, question_option_id),

    FOREIGN KEY (survey_response_id) REFERENCES survey_responses(id) ON DELETE CASCADE,

    FOREIGN KEY (survey_question_id) REFERENCES survey_questions(id) ON DELETE CASCADE,

    FOREIGN KEY (question_option_id) REFERENCES question_options(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 外部キー制約を元に戻す

SET FOREIGN_KEY_CHECKS = 1;