v3kcqvtB

1CREATE SCHEMA mydb;
2USE mydb ;
3
4-- -----------------------------------------------------
5-- Table `mydb`.`users`
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS `mydb`.`users` (
8  `user_id` INT NOT NULL AUTO_INCREMENT,
9  `first_name` VARCHAR(45) NULL,
10  `last_name` VARCHAR(45) NULL,
11  `email` VARCHAR(45) NULL,
12  `password` VARCHAR(128) NULL,
13  `is_admin` TINYINT(1) NOT NULL,
14  `group_id` INT NOT NULL,
15  PRIMARY KEY (`user_id`),
16  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC))
17ENGINE = InnoDB;
18
19
20-- -----------------------------------------------------
21-- Table `mydb`.`courses`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`courses` (
24  `course_id` INT NOT NULL AUTO_INCREMENT,
25  `course_name` VARCHAR(45) NULL,
26  `start_date` DATE NULL,
27  `finish_date` DATE NULL,
28  `tutor_id` INT NOT NULL,
29  `capacity` INT NULL,
30  PRIMARY KEY (`course_id`),
31  INDEX `fk_courses_users1_idx` (`tutor_id` ASC),
32  UNIQUE INDEX `course_id_UNIQUE` (`course_id` ASC),
33  CONSTRAINT `fk_courses_users1`
34    FOREIGN KEY (`tutor_id`)
35    REFERENCES `mydb`.`users` (`user_id`)
36    ON DELETE NO ACTION
37    ON UPDATE NO ACTION)
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`users_courses`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `mydb`.`users_courses` (
45  `user_id` INT NOT NULL,
46  `course_id` INT NOT NULL,
47  PRIMARY KEY (`user_id`, `course_id`),
48  INDEX `fk_users_courses_users_idx` (`user_id` ASC),
49  INDEX `fk_users_courses_courses1_idx` (`course_id` ASC),
50  CONSTRAINT `fk_users_courses_users`
51    FOREIGN KEY (`user_id`)
52    REFERENCES `mydb`.`users` (`user_id`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION,
55  CONSTRAINT `fk_users_courses_courses1`
56    FOREIGN KEY (`course_id`)
57    REFERENCES `mydb`.`courses` (`course_id`)
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION)
60ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64-- Table `mydb`.`feedback`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `mydb`.`feedback` (
67  `user_id` INT NOT NULL,
68  `course_id` INT NOT NULL,
69  `feedback_body` TEXT NULL,
70  `grade` INT NULL,
71  PRIMARY KEY (`user_id`, `course_id`),
72  INDEX `fk_feedback_users1_idx` (`user_id` ASC),
73  INDEX `fk_feedback_courses1_idx` (`course_id` ASC),
74  CONSTRAINT `fk_feedback_users1`
75    FOREIGN KEY (`user_id`)
76    REFERENCES `mydb`.`users` (`user_id`)
77    ON DELETE NO ACTION
78    ON UPDATE NO ACTION,
79  CONSTRAINT `fk_feedback_courses1`
80    FOREIGN KEY (`course_id`)
81    REFERENCES `mydb`.`courses` (`course_id`)
82    ON DELETE NO ACTION
83    ON UPDATE NO ACTION)
84ENGINE = InnoDB;
85
86
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;