vBpEdrHU

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema courses
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema courses
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `courses` DEFAULT CHARACTER SET utf8 ;
15USE `courses` ;
16
17-- -----------------------------------------------------
18-- Table `courses`.`teacher`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `courses`.`teacher` (
21  `idteacher` INT NOT NULL AUTO_INCREMENT,
22  `fio` VARCHAR(100) NOT NULL,
23  `email` VARCHAR(45) NOT NULL,
24  PRIMARY KEY (`idteacher`),
25  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `courses`.`course`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `courses`.`course` (
33  `idcourse` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
34  `title` VARCHAR(45) NOT NULL COMMENT 'Название микрокурса',
35  `duration` TIME NOT NULL DEFAULT '01:00:00' COMMENT 'Продолжительность курса в часах',
36  `description` VARCHAR(300) NULL COMMENT 'Полное описание курса',
37  `type` ENUM('frontend', 'backend', 'devops') NULL COMMENT 'К какому направлению курс относится',
38  `teacher_idteacher` INT NOT NULL,
39  PRIMARY KEY (`idcourse`),
40  INDEX `fk_course_teacher_idx` (`teacher_idteacher` ASC),
41  CONSTRAINT `fk_course_teacher`
42    FOREIGN KEY (`teacher_idteacher`)
43    REFERENCES `courses`.`teacher` (`idteacher`)
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION)
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `courses`.`student`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `courses`.`student` (
53  `idstudent` MEDIUMINT NOT NULL AUTO_INCREMENT,
54  `fio` VARCHAR(100) NOT NULL COMMENT 'Фамилия Имя Отчество слушателя',
55  `phone` CHAR(10) NULL COMMENT 'телефонный номер',
56  `email` VARCHAR(45) NOT NULL COMMENT 'емейл',
57  `bd` DATE NULL,
58  PRIMARY KEY (`idstudent`),
59  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
60ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64-- Table `courses`.`student_has_course`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `courses`.`student_has_course` (
67  `student_idstudent` MEDIUMINT NOT NULL,
68  `course_idcourse` SMALLINT UNSIGNED NOT NULL,
69  `dt` DATETIME NULL,
70  PRIMARY KEY (`student_idstudent`, `course_idcourse`),
71  INDEX `fk_student_has_course_course1_idx` (`course_idcourse` ASC),
72  INDEX `fk_student_has_course_student1_idx` (`student_idstudent` ASC),
73  CONSTRAINT `fk_student_has_course_student1`
74    FOREIGN KEY (`student_idstudent`)
75    REFERENCES `courses`.`student` (`idstudent`)
76    ON DELETE NO ACTION
77    ON UPDATE NO ACTION,
78  CONSTRAINT `fk_student_has_course_course1`
79    FOREIGN KEY (`course_idcourse`)
80    REFERENCES `courses`.`course` (`idcourse`)
81    ON DELETE NO ACTION
82    ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86SET SQL_MODE=@OLD_SQL_MODE;
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
88SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;