· 8 years ago · Jun 28, 2018, 07:54 PM
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 mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema web_project
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema web_project
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `web_project` DEFAULT CHARACTER SET utf8 ;
18USE `web_project` ;
19
20-- -----------------------------------------------------
21-- Table `web_project`.`courses`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `web_project`.`courses` (
24 `course_id` INT(11) NOT NULL,
25 `course` INT(11) NOT NULL,
26 PRIMARY KEY (`course_id`))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = utf8;
29
30
31-- -----------------------------------------------------
32-- Table `web_project`.`groups`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `web_project`.`groups` (
35 `group_id` INT(11) NOT NULL AUTO_INCREMENT,
36 `name` VARCHAR(45) NOT NULL,
37 PRIMARY KEY (`group_id`))
38ENGINE = InnoDB
39AUTO_INCREMENT = 9
40DEFAULT CHARACTER SET = utf8;
41
42
43-- -----------------------------------------------------
44-- Table `web_project`.`titles`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `web_project`.`titles` (
47 `title_id` INT(11) NOT NULL AUTO_INCREMENT,
48 `name` VARCHAR(45) NOT NULL,
49 PRIMARY KEY (`title_id`))
50ENGINE = InnoDB
51DEFAULT CHARACTER SET = utf8;
52
53
54-- -----------------------------------------------------
55-- Table `web_project`.`lecturers`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `web_project`.`lecturers` (
58 `leturer_id` INT(11) NOT NULL AUTO_INCREMENT,
59 `name` VARCHAR(45) NOT NULL,
60 `title_id` INT(11) NULL DEFAULT NULL,
61 PRIMARY KEY (`leturer_id`),
62 INDEX `fk_lecturers_titles_idx` (`title_id` ASC),
63 CONSTRAINT `fk_lecturers_titles`
64 FOREIGN KEY (`title_id`)
65 REFERENCES `web_project`.`titles` (`title_id`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB
69DEFAULT CHARACTER SET = utf8;
70
71
72-- -----------------------------------------------------
73-- Table `web_project`.`majors`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `web_project`.`majors` (
76 `major_id` INT(11) NOT NULL AUTO_INCREMENT,
77 `name` VARCHAR(45) NOT NULL,
78 PRIMARY KEY (`major_id`))
79ENGINE = InnoDB
80DEFAULT CHARACTER SET = utf8;
81
82
83-- -----------------------------------------------------
84-- Table `web_project`.`types`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `web_project`.`types` (
87 `type_id` INT(11) NOT NULL,
88 `name` VARCHAR(45) NOT NULL,
89 PRIMARY KEY (`type_id`))
90ENGINE = InnoDB
91DEFAULT CHARACTER SET = utf8;
92
93
94-- -----------------------------------------------------
95-- Table `web_project`.`subject`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `web_project`.`subject` (
98 `subject_id` INT(11) NOT NULL AUTO_INCREMENT,
99 `name` VARCHAR(80) NOT NULL,
100 `credits` DOUBLE NOT NULL,
101 `group_id` INT(11) NOT NULL,
102 `type_id` INT(11) NOT NULL,
103 PRIMARY KEY (`subject_id`),
104 INDEX `fk_subject_groups1_idx` (`group_id` ASC),
105 INDEX `fk_subject_types1_idx` (`type_id` ASC),
106 CONSTRAINT `fk_subject_groups1`
107 FOREIGN KEY (`group_id`)
108 REFERENCES `web_project`.`groups` (`group_id`)
109 ON DELETE NO ACTION
110 ON UPDATE NO ACTION,
111 CONSTRAINT `fk_subject_types1`
112 FOREIGN KEY (`type_id`)
113 REFERENCES `web_project`.`types` (`type_id`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION)
116ENGINE = InnoDB
117DEFAULT CHARACTER SET = utf8;
118
119
120-- -----------------------------------------------------
121-- Table `web_project`.`subject_has_lecturers`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `web_project`.`subject_has_lecturers` (
124 `subject_id` INT(11) NOT NULL,
125 `leturer_id` INT(11) NOT NULL,
126 PRIMARY KEY (`subject_id`, `leturer_id`),
127 INDEX `fk_subject_has_lecturers_lecturers1_idx` (`leturer_id` ASC),
128 INDEX `fk_subject_has_lecturers_subject1_idx` (`subject_id` ASC),
129 CONSTRAINT `fk_subject_has_lecturers_lecturers1`
130 FOREIGN KEY (`leturer_id`)
131 REFERENCES `web_project`.`lecturers` (`leturer_id`)
132 ON DELETE NO ACTION
133 ON UPDATE NO ACTION,
134 CONSTRAINT `fk_subject_has_lecturers_subject1`
135 FOREIGN KEY (`subject_id`)
136 REFERENCES `web_project`.`subject` (`subject_id`)
137 ON DELETE NO ACTION
138 ON UPDATE NO ACTION)
139ENGINE = InnoDB
140DEFAULT CHARACTER SET = utf8;
141
142
143-- -----------------------------------------------------
144-- Table `web_project`.`subject_has_majors`
145-- -----------------------------------------------------
146CREATE TABLE IF NOT EXISTS `web_project`.`subject_has_majors` (
147 `subject_id` INT(11) NOT NULL,
148 `major_id` INT(11) NOT NULL,
149 `min_course_id` INT(11) NULL DEFAULT NULL,
150 `only_course_id` INT(11) NULL DEFAULT NULL,
151 PRIMARY KEY (`subject_id`, `major_id`),
152 INDEX `fk_subject_has_majors_majors1_idx` (`major_id` ASC),
153 INDEX `fk_subject_has_majors_subject1_idx` (`subject_id` ASC),
154 INDEX `fk_subject_has_majors_courses1_idx` (`min_course_id` ASC),
155 INDEX `fk_subject_has_majors_courses2_idx` (`only_course_id` ASC),
156 CONSTRAINT `fk_subject_has_majors_courses1`
157 FOREIGN KEY (`min_course_id`)
158 REFERENCES `web_project`.`courses` (`course_id`)
159 ON DELETE NO ACTION
160 ON UPDATE NO ACTION,
161 CONSTRAINT `fk_subject_has_majors_courses2`
162 FOREIGN KEY (`only_course_id`)
163 REFERENCES `web_project`.`courses` (`course_id`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION,
166 CONSTRAINT `fk_subject_has_majors_majors1`
167 FOREIGN KEY (`major_id`)
168 REFERENCES `web_project`.`majors` (`major_id`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION,
171 CONSTRAINT `fk_subject_has_majors_subject1`
172 FOREIGN KEY (`subject_id`)
173 REFERENCES `web_project`.`subject` (`subject_id`)
174 ON DELETE NO ACTION
175 ON UPDATE NO ACTION)
176ENGINE = InnoDB
177DEFAULT CHARACTER SET = utf8;
178
179
180-- -----------------------------------------------------
181-- Table `web_project`.`workloads`
182-- -----------------------------------------------------
183CREATE TABLE IF NOT EXISTS `web_project`.`workloads` (
184 `workload_id` INT(11) NOT NULL AUTO_INCREMENT,
185 `lectures` INT(11) NOT NULL,
186 `seminars` INT(11) NOT NULL,
187 `practices` INT(11) NOT NULL,
188 `subject_id` INT(11) NOT NULL,
189 PRIMARY KEY (`workload_id`, `subject_id`),
190 INDEX `fk_workloads_subject1_idx` (`subject_id` ASC),
191 CONSTRAINT `fk_workloads_subject1`
192 FOREIGN KEY (`subject_id`)
193 REFERENCES `web_project`.`subject` (`subject_id`)
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB
197DEFAULT CHARACTER SET = utf8;
198
199
200SET SQL_MODE=@OLD_SQL_MODE;
201SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
202SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;