Created
February 28, 2024 17:05
-
-
Save sahilkashyap64/30766f19d89e0daccddabaa4edaf425d to your computer and use it in GitHub Desktop.
survey db based on the thesis
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment