Up3wk4KV

1CREATE TABLE IF NOT EXISTS `subject` (
2  `id` INT(11) NOT NULL AUTO_INCREMENT,
3  `name` VARCHAR(255) NULL DEFAULT NULL,
4  PRIMARY KEY (`id`))
5ENGINE = InnoDB
6DEFAULT CHARACTER SET = utf8;
7
8CREATE TABLE IF NOT EXISTS `user` (
9  `id` INT(11) NOT NULL AUTO_INCREMENT,
10  `first_name` VARCHAR(255) NOT NULL,
11  `last_name` VARCHAR(255) NOT NULL,
12  `patronymic` VARCHAR(255) NOT NULL,
13  `date_of_birth` DATE NOT NULL,
14  `login` VARCHAR(255) NOT NULL,
15  `password` VARCHAR(255) NOT NULL,
16  `avatar` MEDIUMTEXT NULL DEFAULT NULL,
17  `description` VARCHAR(255) NULL DEFAULT NULL,
18  `email` VARCHAR(255) NULL DEFAULT NULL,
19  `phone` VARCHAR(25) NULL DEFAULT NULL,
20  `role` ENUM('ROLE_ADMIN','ROLE_TEACHER','ROLE_USER'),
21  `sex` ENUM('male', 'female'),
22  PRIMARY KEY (`id`))
23ENGINE = InnoDB
24DEFAULT CHARACTER SET = utf8;
25
26
27CREATE TABLE IF NOT EXISTS `teacher` (
28  `id` INT(11) NOT NULL,
29  PRIMARY KEY (`id`),
30  CONSTRAINT `fk_teacher_user`
31    FOREIGN KEY (`id`)
32    REFERENCES `user` (`id`))
33ENGINE = InnoDB
34DEFAULT CHARACTER SET = utf8;
35
36
37
38CREATE TABLE IF NOT EXISTS `clazz` (
39  `id` INT(11) NOT NULL AUTO_INCREMENT,
40  `name` VARCHAR(255) NOT NULL,
41  `academic_year` INT(11) NOT NULL,
42  `description` VARCHAR(255) NULL DEFAULT NULL,
43  `is_active` BIT(1) NOT NULL,
44  PRIMARY KEY (`id`))
45ENGINE = InnoDB
46DEFAULT CHARACTER SET = utf8;
47
48
49
50CREATE TABLE IF NOT EXISTS `class_teacher_subject_link` (
51  `teacher_id` INT(11) NOT NULL,
52  `subject_id` INT(11) NOT NULL,
53  `clazz_id` INT(11) NOT NULL,
54  PRIMARY KEY (`teacher_id`, `subject_id`, `clazz_id`),
55  INDEX `FK2cxobx1gt9ypn7xqvnnu5kelo` (`subject_id` ASC),
56  INDEX `FKs14ndc8vkc4tq78by75egflka` (`clazz_id` ASC),
57  CONSTRAINT `fk_class_subject_teacher1`
58    FOREIGN KEY (`subject_id`)
59    REFERENCES `subject` (`id`),
60  CONSTRAINT `fk_class_subject_teacher2`
61    FOREIGN KEY (`teacher_id`)
62    REFERENCES `teacher` (`id`),
63  CONSTRAINT `fk_class_subject_teacher3`
64    FOREIGN KEY (`clazz_id`)
65    REFERENCES `clazz` (`id`))
66ENGINE = InnoDB
67DEFAULT CHARACTER SET = utf8;
68
69
70CREATE TABLE IF NOT EXISTS `lesson` (
71  `id` INT(11) NOT NULL AUTO_INCREMENT,
72  `clazz_id` INT(11) NOT NULL,
73  `subject_id` INT(11) NOT NULL,
74  `date` DATETIME NOT NULL,
75  `file` LONGBLOB ,
76  `hometask` VARCHAR(255) NULL DEFAULT NULL,
77  `lesson_number` TINYINT(1) NOT NULL,
78  `mark_type` ENUM('Контрольна','Самостійна','Поточна','Лабараторна') CHARACTER SET utf8 COLLATE utf8_unicode_ci,
79  PRIMARY KEY (`id`),
80  INDEX `FKlqfrfxjgij4gjebdvvaeoyr14` (`clazz_id` ASC),
81  CONSTRAINT `fk_lesson_class`
82    FOREIGN KEY (`clazz_id`)
83    REFERENCES `clazz` (`id`),
84  CONSTRAINT `fk_lesson_subject`
85    FOREIGN KEY (`subject_id`)
86    REFERENCES `subject` (`id`))
87ENGINE = InnoDB
88DEFAULT CHARACTER SET = utf8;
89
90
91CREATE TABLE IF NOT EXISTS `student` (
92  `id` INT(11) NOT NULL,
93  PRIMARY KEY (`id`),
94  CONSTRAINT `fk_student_user`
95    FOREIGN KEY (`id`)
96    REFERENCES `user` (`id`))
97ENGINE = InnoDB
98DEFAULT CHARACTER SET = utf8;
99
100
101CREATE TABLE IF NOT EXISTS `mark` (
102  `id` INT(11) NOT NULL AUTO_INCREMENT,
103  `mark` TINYINT(1) NOT NULL,
104  `note` VARCHAR(255) NULL DEFAULT NULL,
105  `lesson_id` INT(11) NOT NULL,
106  `student_id` INT(11) NOT NULL,
107  PRIMARY KEY (`id`),
108  INDEX `FKqw2aqrsqut82rwjb469mpyari` (`lesson_id` ASC),
109  INDEX `FKcwocngy0rfmqdhqwm3qlrfamx` (`student_id` ASC),
110  CONSTRAINT `fk_mark_student`
111    FOREIGN KEY (`student_id`)
112    REFERENCES `student` (`id`),
113  CONSTRAINT `fk_mark_lesson`
114    FOREIGN KEY (`lesson_id`)
115    REFERENCES `lesson` (`id`))
116ENGINE = InnoDB
117DEFAULT CHARACTER SET = utf8;
118
119
120CREATE TABLE IF NOT EXISTS `students_classes` (
121  `student_id` INT(11) NOT NULL,
122  `class_id` INT(11) NOT NULL,
123  PRIMARY KEY (`student_id`, `class_id`),
124  INDEX `FK3p1litksmc8exv2br5l5w0l9h` (`class_id` ASC),
125  CONSTRAINT `fk_student_class1`
126    FOREIGN KEY (`class_id`)
127    REFERENCES `clazz` (`id`),
128  CONSTRAINT `fk_student_class2`
129    FOREIGN KEY (`student_id`)
130    REFERENCES `student` (`id`))
131ENGINE = InnoDB
132DEFAULT CHARACTER SET = utf8;