survey db based on the thesis

surveyDB.sql

-- https://wwwiti.cs.uni-magdeburg.de/iti_db/publikationen/ps/auto/thesisJohn22.pdf

BEGIN;

-- Drop tables if they already exist to avoid errors
DROP TABLE IF EXISTS Footnote CASCADE;
DROP TABLE IF EXISTS Additional_Answer_Part_Choice CASCADE;
DROP TABLE IF EXISTS Choice_Text CASCADE;
DROP TABLE IF EXISTS Choice_Group CASCADE;
DROP TABLE IF EXISTS Additional_Answer_Part CASCADE;
DROP TABLE IF EXISTS Question_Part_Instruction CASCADE;
DROP TABLE IF EXISTS Question_Part_Text CASCADE;
DROP TABLE IF EXISTS Answer_Part_Choice CASCADE;
DROP TABLE IF EXISTS Answer_Part CASCADE;
DROP TABLE IF EXISTS Question_Part CASCADE;
DROP TABLE IF EXISTS Survey_Module_Question CASCADE;
DROP TABLE IF EXISTS Questions CASCADE;
DROP TABLE IF EXISTS Survey_Module CASCADE;
DROP TABLE IF EXISTS Survey CASCADE;

-- Create tables in the order of dependency

-- Create Survey Table
CREATE TABLE Survey (
    survey_id SERIAL PRIMARY KEY,
    survey_name TEXT NOT NULL
);

-- Create Survey_Module Table
CREATE TABLE Survey_Module (
    survey_module_id SERIAL PRIMARY KEY,
    survey_id INT NOT NULL,
    survey_module_name TEXT NOT NULL,
    CONSTRAINT fk_survey
        FOREIGN KEY (survey_id) 
        REFERENCES Survey (survey_id)
        ON DELETE CASCADE
);

-- Create Questions Table
CREATE TABLE Questions (
    question_id SERIAL PRIMARY KEY
);

-- Create Survey_Module_Question Table
CREATE TABLE Survey_Module_Question (
    survey_module_id INT,
    question_id INT,
    question_number INT NOT NULL,
    CONSTRAINT pk_survey_module_question PRIMARY KEY (survey_module_id, question_id),
    CONSTRAINT fk_survey_module
        FOREIGN KEY (survey_module_id) 
        REFERENCES Survey_Module (survey_module_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_question
        FOREIGN KEY (question_id) 
        REFERENCES Questions (question_id)
        ON DELETE CASCADE
);

-- Create Question_Part_Text Table
CREATE TABLE Question_Part_Text (
    question_part_text_id SERIAL PRIMARY KEY,
    question_part_text TEXT NOT NULL
);

-- Create Question_Part_Instruction Table
CREATE TABLE Question_Part_Instruction (
    question_part_instruction_id SERIAL PRIMARY KEY,
    question_part_instruction TEXT
);

-- Create Answer_Part Table
CREATE TABLE Answer_Part (
    answer_part_id SERIAL PRIMARY KEY,
    answer_type TEXT NOT NULL
);


-- Create Additional_Answer_Part Table
CREATE TABLE Additional_Answer_Part (
    additional_answer_part_id SERIAL PRIMARY KEY,
    additional_answer_type TEXT NOT NULL
);

-- Create Question_Part Table
CREATE TABLE Question_Part (
    question_id INT NOT NULL,
    part_sequence_number INT NOT NULL,
    level TEXT,
    variable_name TEXT,
    answer_part_id INT,
    question_part_text_id INT NOT NULL,
    question_part_instruction_id INT,
    additional_variable_name TEXT,
    additional_answer_part_id INT,

    CONSTRAINT pk_question_part_seq_num_id PRIMARY KEY (question_id, part_sequence_number),
    CONSTRAINT fk_question_id
        FOREIGN KEY (question_id) 
        REFERENCES Questions (question_id),
    CONSTRAINT fk_question_part_text_id
        FOREIGN KEY (question_part_text_id) 
        REFERENCES Question_Part_Text (question_part_text_id),
    CONSTRAINT fk_answer_part_id
        FOREIGN KEY (answer_part_id) 
        REFERENCES Answer_Part (answer_part_id)
        ON DELETE SET NULL,
    CONSTRAINT fk_question_part_instruction_id
        FOREIGN KEY (question_part_instruction_id) 
        REFERENCES Question_Part_Instruction (question_part_instruction_id)
        ON DELETE SET NULL,
    CONSTRAINT fk_additional_answer_part_id
        FOREIGN KEY (additional_answer_part_id) 
        REFERENCES Additional_Answer_Part (additional_answer_part_id)
        ON DELETE SET NULL
);

-- Create Choice_Group Table
CREATE TABLE Choice_Group (
    choice_group_id SERIAL PRIMARY KEY,
    choice_group_text TEXT NOT NULL
);


-- Create Choice_Text Table
CREATE TABLE Choice_Text (
    choice_text_id SERIAL PRIMARY KEY,
    choice_text TEXT NOT NULL
);

-- Create Answer_Part_Choice Table
CREATE TABLE Answer_Part_Choice (
    answer_part_id INT,
    choice_sequence_number INT NOT NULL,
    choice_text_id INT NOT NULL,
    choice_value TEXT NOT NULL,
    choice_group_id INT,
    additional_variable_name TEXT,
    additional_answer_part_id INT,
    CONSTRAINT pk_answer_part_choice_sequence_number PRIMARY KEY (answer_part_id, choice_sequence_number),
    CONSTRAINT fk_answer_part
        FOREIGN KEY (answer_part_id) 
        REFERENCES Answer_Part (answer_part_id),
    CONSTRAINT fk_additional_answer_part_id
        FOREIGN KEY (additional_answer_part_id) 
        REFERENCES Additional_Answer_Part (additional_answer_part_id),
    CONSTRAINT fk_choice_group_id
        FOREIGN KEY (choice_group_id) 
        REFERENCES Choice_Group(choice_group_id) ON DELETE SET NULL,
    CONSTRAINT fk_choice_text
        FOREIGN KEY (choice_text_id) 
        REFERENCES Choice_Text (choice_text_id)
);

-- Create Additional_Answer_Part_Choice Table
CREATE TABLE Additional_Answer_Part_Choice (
    additional_answer_part_id INT,
    choice_sequence_number INT NOT NULL,
    choice_text_id INT,
    choice_value TEXT NOT NULL,
    CONSTRAINT pk_additional_answer_part_choice_sequence_number PRIMARY KEY (additional_answer_part_id, choice_sequence_number),
    CONSTRAINT fk_additional_answer_part
        FOREIGN KEY (additional_answer_part_id) 
        REFERENCES Additional_Answer_Part (additional_answer_part_id),
    CONSTRAINT fk_choice_text_id
        FOREIGN KEY (choice_text_id) 
        REFERENCES Choice_Text (choice_text_id)
);

-- Create Footnote Table
CREATE TABLE Footnote (
    survey_module_id INT NOT NULL,
    question_id INT NOT NULL,
    part_sequence_number INT NOT NULL,
    footnote_text TEXT NOT NULL,
    CONSTRAINT pk_survey_module_id_question_id_part_sequence_number PRIMARY KEY (survey_module_id, question_id, part_sequence_number),
    CONSTRAINT fk_survey_module
        FOREIGN KEY (survey_module_id) 
        REFERENCES Survey_Module (survey_module_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_question_part
        FOREIGN KEY (question_id, part_sequence_number) 
        REFERENCES Question_Part (question_id, part_sequence_number)
        ON DELETE CASCADE
);

COMMIT;