WyFCCxp8

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