N3rcCj9G

1SET FOREIGN_KEY_CHECKS = 0;
2DROP TABLE IF EXISTS `students`;
3DROP TABLE IF EXISTS `admins`;
4DROP TABLE IF EXISTS `suggestions`;
5DROP TABLE IF EXISTS `questions`;
6DROP TABLE IF EXISTS `answers`;
7DROP TABLE IF EXISTS `questionaries`;
8DROP TABLE IF EXISTS `generators`;
9DROP TABLE IF EXISTS `questionaries_students`;
10DROP TABLE IF EXISTS `answers_questions`;
11DROP TABLE IF EXISTS `answers_questionaries_students`;
12DROP TABLE IF EXISTS `questions_questionaries`;
13DROP TABLE IF EXISTS `questionaries_students_suggestions`;
14SET FOREIGN_KEY_CHECKS = 1;
15
16CREATE TABLE `students` (
17    `student_id` CHAR(9) NOT NULL,
18    `name` VARCHAR(32) NOT NULL,
19    `surname` VARCHAR(32) NOT NULL,
20    PRIMARY KEY (`student_id`)
21) ENGINE=InnoDB;
22
23CREATE TABLE `admins` (
24    `admin_id` VARCHAR(255) NOT NULL,
25    `username` VARCHAR(32) NOT NULL,
26    `password` VARCHAR(255) NOT NULL,
27    PRIMARY KEY (`admin_id`)
28) ENGINE=InnoDB;
29
30CREATE TABLE `suggestions` (
31    `suggestion_id` INTEGER(4) NOT NULL,
32    `text` TEXT NOT NULL,
33    `penality` FLOAT(4, 2) DEFAULT 0.25,
34    `is_active` TINYINT(1) DEFAULT 1,
35    `question_id` INTEGER(4) NOT NULL,
36    PRIMARY KEY (`suggestion_id`)
37) ENGINE=InnoDB;
38
39CREATE TABLE `questions` (
40    `question_id` INTEGER(4) NOT NULL,
41    `text` TEXT NOT NULL,
42    `points` FLOAT(4, 2) DEFAULT 2.0,
43    `question_type` ENUM('single', 'multi') DEFAULT 'multi',
44    `question_difficulty` ENUM('easy', 'med', 'hard') DEFAULT 'easy',
45    PRIMARY KEY (`question_id`)
46) ENGINE=InnoDB;
47
48CREATE TABLE `answers` (
49    `answer_id` INTEGER(8) NOT NULL,
50    `text` TEXT NOT NULL,
51    `is_right` TINYINT(1) DEFAULT 0,
52    `penality` FLOAT(4, 2) DEFAULT 0.5,
53    `question_id` INTEGER(4) NOT NULL,
54    PRIMARY KEY (`answer_id`)
55) ENGINE=InnoDB;
56
57CREATE TABLE `questionaries` (
58    `questionary_id` VARCHAR(255) NOT NULL,
59    `date_start` DATETIME NOT NULL,
60    `date_end` DATETIME NOT NULL,
61    `is_active` TINYINT(1) DEFAULT 1,
62    PRIMARY KEY (`questionary_id`),
63    UNIQUE (`date_start`)
64) ENGINE=InnoDB;
65
66CREATE TABLE `generators` (
67    `generetor_id` VARCHAR(255) NOT NULL,
68    `max_points` FLOAT(4, 2) DEFAULT 32.00,
69    `max_suggestions` INTEGER(2) DEFAULT 7,
70    PRIMARY KEY (`generetor_id`)
71) ENGINE=InnoDB;
72
73CREATE TABLE `questionaries_students` (
74    `questionary_student_id` INTEGER(8) NOT NULL,
75    `has_completed` TINYINT(1) NOT NULL,
76    `questionary_id` VARCHAR(255) NOT NULL,
77    `student_id` CHAR(9) NOT NULL,
78    PRIMARY KEY (`questionary_student_id`)
79) ENGINE=InnoDB;
80
81CREATE TABLE `answers_questionaries_students` (
82    `answer_questionary_student_id` INTEGER(7) NOT NULL,
83    `questionary_student_id` INTEGER(8) NOT NULL,
84    `answer_id` INTEGER(8) NOT NULL,
85    PRIMARY KEY (`answer_questionary_student_id`)
86) ENGINE=InnoDB;
87
88CREATE TABLE `questions_questionaries` (
89    `question_questionary_id` INTEGER(6) NOT NULL,
90    `question_id` INTEGER(4) NOT NULL,
91    `questionary_id` VARCHAR(255) NOT NULL,
92    PRIMARY KEY (`question_questionary_id`)
93) ENGINE=InnoDB;
94
95CREATE TABLE `questionaries_students_suggestions` (
96    `questionary_student_suggestion_id` INTEGER(4) NOT NULL,
97    `questionary_student_id` INTEGER(8) NOT NULL,
98    `suggestion_id` INTEGER(4) NOT NULL,
99    PRIMARY KEY (`questionary_student_suggestion_id`)
100) ENGINE=InnoDB;
101
102ALTER TABLE `suggestions` ADD FOREIGN KEY (`question_id`) REFERENCES `questions`(`question_id`);
103ALTER TABLE `answers` ADD FOREIGN KEY (`question_id`) REFERENCES `questions`(`question_id`);
104ALTER TABLE `questionaries_students` ADD FOREIGN KEY (`questionary_id`) REFERENCES `questionaries`(`questionary_id`);
105ALTER TABLE `questionaries_students` ADD FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`);
106ALTER TABLE `answers_questionaries_students` ADD FOREIGN KEY (`questionary_student_id`) REFERENCES `questionaries_students`(`questionary_student_id`);
107ALTER TABLE `answers_questionaries_students` ADD FOREIGN KEY (`answer_id`) REFERENCES `answers`(`answer_id`);
108ALTER TABLE `questions_questionaries` ADD FOREIGN KEY (`question_id`) REFERENCES `questions`(`question_id`);
109ALTER TABLE `questions_questionaries` ADD FOREIGN KEY (`questionary_id`) REFERENCES `questionaries`(`questionary_id`);
110ALTER TABLE `questionaries_students_suggestions` ADD FOREIGN KEY (`questionary_student_id`) REFERENCES `questionaries_students`(`questionary_student_id`);
111ALTER TABLE `questionaries_students_suggestions` ADD FOREIGN KEY (`suggestion_id`) REFERENCES `questionaries_students_suggestions`(`suggestion_id`);