SQL-complete-tutorial

1
-- MySQL Workbench Forward Engineering
2

3
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6

7
-- -----------------------------------------------------
8
-- Schema school
9
-- -----------------------------------------------------
10

11
-- -----------------------------------------------------
12
-- Schema school
13
-- -----------------------------------------------------
14
CREATE SCHEMA IF NOT EXISTS `school` DEFAULT CHARACTER SET utf8 ;
15
USE `school` ;
16

17
-- -----------------------------------------------------
18
-- Table `school`.`students`
19
-- -----------------------------------------------------
20
CREATE TABLE IF NOT EXISTS `school`.`students` (
21
  `student_id` INT NOT NULL AUTO_INCREMENT,
22
  `first_name` VARCHAR(50) NOT NULL,
23
  `last_name` VARCHAR(50) NOT NULL,
24
  `email` VARCHAR(255) NOT NULL,
25
  `date_registered` DATETIME NOT NULL,
26
  PRIMARY KEY (`student_id`))
27
ENGINE = InnoDB;
28

29

30
-- -----------------------------------------------------
31
-- Table `school`.`instructors`
32
-- -----------------------------------------------------
33
CREATE TABLE IF NOT EXISTS `school`.`instructors` (
34
  `instructor_id` SMALLINT NOT NULL AUTO_INCREMENT,
35
  `name` VARCHAR(50) NOT NULL,
36
  PRIMARY KEY (`instructor_id`))
37
ENGINE = InnoDB;
38

39

40
-- -----------------------------------------------------
41
-- Table `school`.`courses`
42
-- -----------------------------------------------------
43
CREATE TABLE IF NOT EXISTS `school`.`courses` (
44
  `course_id` INT NOT NULL AUTO_INCREMENT,
45
  `instructor_id` SMALLINT NOT NULL,
46
  `title` VARCHAR(255) NOT NULL,
47
  `price` DECIMAL(5,2) NOT NULL,
48
  PRIMARY KEY (`course_id`, `instructor_id`),
49
  INDEX `fk_courses_instructors1_idx` (`instructor_id` ASC) VISIBLE,
50
  CONSTRAINT `fk_courses_instructors`
51
    FOREIGN KEY (`instructor_id`)
52
    REFERENCES `school`.`instructors` (`instructor_id`)
53
    ON DELETE NO ACTION
54
    ON UPDATE CASCADE)
55
ENGINE = InnoDB;
56

57

58
-- -----------------------------------------------------
59
-- Table `school`.`enrollments`
60
-- -----------------------------------------------------
61
CREATE TABLE IF NOT EXISTS `school`.`enrollments` (
62
  `student_id` INT NOT NULL,
63
  `course_id` INT NOT NULL,
64
  `date` DATETIME NOT NULL,
65
  `price` DECIMAL(5,2) NOT NULL,
66
  INDEX `fk_enrollments_students_idx` (`student_id` ASC) VISIBLE,
67
  INDEX `fk_enrollments_courses1_idx` (`course_id` ASC) VISIBLE,
68
  PRIMARY KEY (`student_id`, `course_id`),
69
  CONSTRAINT `fk_enrollments_students`
70
    FOREIGN KEY (`student_id`)
71
    REFERENCES `school`.`students` (`student_id`)
72
    ON DELETE NO ACTION
73
    ON UPDATE CASCADE,
74
  CONSTRAINT `fk_enrollments_courses`
75
    FOREIGN KEY (`course_id`)
76
    REFERENCES `school`.`courses` (`course_id`)
77
    ON DELETE NO ACTION
78
    ON UPDATE NO ACTION)
79
ENGINE = InnoDB;
80

81

82
-- -----------------------------------------------------
83
-- Table `school`.`tags`
84
-- -----------------------------------------------------
85
CREATE TABLE IF NOT EXISTS `school`.`tags` (
86
  `tag_id` TINYINT NOT NULL,
87
  `name` VARCHAR(50) NOT NULL,
88
  PRIMARY KEY (`tag_id`))
89
ENGINE = InnoDB;
90

91

92
-- -----------------------------------------------------
93
-- Table `school`.`course_tabs`
94
-- -----------------------------------------------------
95
CREATE TABLE IF NOT EXISTS `school`.`course_tabs` (
96
  `course_id` INT NOT NULL,
97
  `tag_id` TINYINT NOT NULL,
98
  PRIMARY KEY (`course_id`, `tag_id`),
99
  INDEX `fk_course_tabs_tags1_idx` (`tag_id` ASC) VISIBLE,
100
  CONSTRAINT `fk_course_tabs_courses1`
101
    FOREIGN KEY (`course_id`)
102
    REFERENCES `school`.`courses` (`course_id`)
103
    ON DELETE NO ACTION
104
    ON UPDATE NO ACTION,
105
  CONSTRAINT `fk_course_tabs_tags1`
106
    FOREIGN KEY (`tag_id`)
107
    REFERENCES `school`.`tags` (`tag_id`)
108
    ON DELETE NO ACTION
109
    ON UPDATE NO ACTION)
110
ENGINE = InnoDB;
111

112

113
SET SQL_MODE=@OLD_SQL_MODE;
114
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
115
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
116