· 6 years ago · Jan 06, 2020, 04:27 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='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 mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17
18-- -----------------------------------------------------
19-- Table `mydb`.`users`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `mydb`.`users` (
22 `id` INT NOT NULL AUTO_INCREMENT,
23 `username` VARCHAR(16) NOT NULL,
24 `email` VARCHAR(255) NULL,
25 `password` VARCHAR(32) NOT NULL,
26 `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
27 `first_name` VARCHAR(45) NOT NULL,
28 `second_name` VARCHAR(45) NOT NULL,
29 `last_name` VARCHAR(45) NOT NULL,
30 `city_id` INT NOT NULL,
31 `post_code` INT NOT NULL,
32 `address` VARCHAR(45) NOT NULL,
33 `persenal_infocol` DATE NOT NULL,
34 `education` VARCHAR(45) NOT NULL,
35 `job` VARCHAR(45) NOT NULL,
36 PRIMARY KEY (`id`),
37 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
38 ON DELETE NO ACTION
39 ON UPDATE NO ACTION)
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `mydb`.`cinemas`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `mydb`.`cinemas` (
47 `id` INT NOT NULL AUTO_INCREMENT,
48 `cinema_name` VARCHAR(45) NOT NULL,
49 `address` VARCHAR(45) NOT NULL,
50 `telephone_number` VARCHAR(45) NOT NULL,
51 `cinema_info_id` INT NOT NULL,
52 PRIMARY KEY (`id`),
53 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `mydb`.`cinema_halls_type`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `mydb`.`cinema_halls_type` (
61 `id` INT NOT NULL AUTO_INCREMENT,
62 `type_name` VARCHAR(45) NOT NULL,
63 PRIMARY KEY (`id`),
64 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `mydb`.`cinema_halls`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `mydb`.`cinema_halls` (
72 `id` INT NOT NULL AUTO_INCREMENT,
73 `cinema_hall_type_id` INT NOT NULL,
74 `cinema_id` INT NOT NULL,
75 `number_of_seats` INT NOT NULL,
76 PRIMARY KEY (`id`),
77 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
78 INDEX `id_idx` (`cinema_hall_type_id` ASC) VISIBLE,
79 INDEX `cinema_id_idx` (`cinema_id` ASC) VISIBLE,
80 CONSTRAINT `cinema_hall_type_id`
81 FOREIGN KEY (`cinema_hall_type_id`)
82 REFERENCES `mydb`.`cinema_halls_type` (`id`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION,
85 CONSTRAINT `cinema_id`
86 FOREIGN KEY (`cinema_id`)
87 REFERENCES `mydb`.`cinemas` (`id`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `mydb`.`directors`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `mydb`.`directors` (
97 `id` INT NOT NULL AUTO_INCREMENT,
98 `full_name` VARCHAR(45) NOT NULL,
99 PRIMARY KEY (`id`),
100 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `mydb`.`ganre`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `mydb`.`ganre` (
108 `id` INT NOT NULL AUTO_INCREMENT,
109 `ganre_name` VARCHAR(45) NOT NULL,
110 PRIMARY KEY (`id`),
111 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `mydb`.`restrictions`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `mydb`.`restrictions` (
119 `id` INT NOT NULL AUTO_INCREMENT,
120 `restriction_name` VARCHAR(45) NOT NULL,
121 `max_age` INT NULL,
122 PRIMARY KEY (`id`),
123 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
124 UNIQUE INDEX `max_age_UNIQUE` (`max_age` ASC) VISIBLE)
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `mydb`.`video_formats`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `mydb`.`video_formats` (
132 `id` INT NOT NULL AUTO_INCREMENT,
133 `name` VARCHAR(45) NOT NULL,
134 PRIMARY KEY (`id`),
135 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `mydb`.`movies`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `mydb`.`movies` (
143 `id` INT NOT NULL AUTO_INCREMENT,
144 `movie_name` VARCHAR(45) NOT NULL,
145 `description` VARCHAR(200) NOT NULL,
146 `runtime_in_min` INT NOT NULL,
147 `premiere` DATE NOT NULL,
148 `director_id` INT NOT NULL,
149 `genre_id` INT NOT NULL,
150 `restriction_id` INT NOT NULL,
151 `rating` INT NOT NULL,
152 `is_dubbed` TINYINT NOT NULL,
153 `video_fomat_id` INT NOT NULL,
154 PRIMARY KEY (`id`),
155 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
156 INDEX `directod_id_idx` (`director_id` ASC) VISIBLE,
157 INDEX `genre_id_idx` (`genre_id` ASC) VISIBLE,
158 INDEX `restriction_id_idx` (`restriction_id` ASC) VISIBLE,
159 INDEX `video_format_id_idx` (`video_fomat_id` ASC) VISIBLE,
160 CONSTRAINT `directod_id`
161 FOREIGN KEY (`director_id`)
162 REFERENCES `mydb`.`directors` (`id`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION,
165 CONSTRAINT `genre_id`
166 FOREIGN KEY (`genre_id`)
167 REFERENCES `mydb`.`ganre` (`id`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION,
170 CONSTRAINT `restriction_id`
171 FOREIGN KEY (`restriction_id`)
172 REFERENCES `mydb`.`restrictions` (`id`)
173 ON DELETE NO ACTION
174 ON UPDATE NO ACTION,
175 CONSTRAINT `video_format_id`
176 FOREIGN KEY (`video_fomat_id`)
177 REFERENCES `mydb`.`video_formats` (`id`)
178 ON DELETE NO ACTION
179 ON UPDATE NO ACTION)
180ENGINE = InnoDB;
181
182
183-- -----------------------------------------------------
184-- Table `mydb`.`actors`
185-- -----------------------------------------------------
186CREATE TABLE IF NOT EXISTS `mydb`.`actors` (
187 `id` INT NOT NULL AUTO_INCREMENT,
188 `full_name` VARCHAR(45) NOT NULL,
189 PRIMARY KEY (`id`),
190 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
191ENGINE = InnoDB;
192
193
194-- -----------------------------------------------------
195-- Table `mydb`.`cast`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `mydb`.`cast` (
198 `movie_id` INT NOT NULL,
199 `actor_id` INT NOT NULL,
200 PRIMARY KEY (`actor_id`, `movie_id`),
201 INDEX `movie_id_idx` (`movie_id` ASC) VISIBLE,
202 CONSTRAINT `movie_id`
203 FOREIGN KEY (`movie_id`)
204 REFERENCES `mydb`.`movies` (`id`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION,
207 CONSTRAINT `actor_id`
208 FOREIGN KEY (`actor_id`)
209 REFERENCES `mydb`.`actors` (`id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION)
212ENGINE = InnoDB;
213
214
215-- -----------------------------------------------------
216-- Table `mydb`.`projection`
217-- -----------------------------------------------------
218CREATE TABLE IF NOT EXISTS `mydb`.`projection` (
219 `id` INT NOT NULL AUTO_INCREMENT,
220 `movie_id` INT NOT NULL,
221 `hall_id` INT NOT NULL,
222 `projection_date` DATE NOT NULL,
223 `projection_time` TIME NOT NULL,
224 PRIMARY KEY (`id`),
225 INDEX `hall_id_idx` (`hall_id` ASC) VISIBLE,
226 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
227 CONSTRAINT `movie_id`
228 FOREIGN KEY (`movie_id`)
229 REFERENCES `mydb`.`movies` (`id`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION,
232 CONSTRAINT `hall_id`
233 FOREIGN KEY (`hall_id`)
234 REFERENCES `mydb`.`cinema_halls` (`id`)
235 ON DELETE NO ACTION
236 ON UPDATE NO ACTION)
237ENGINE = InnoDB;
238
239
240-- -----------------------------------------------------
241-- Table `mydb`.`tickets`
242-- -----------------------------------------------------
243CREATE TABLE IF NOT EXISTS `mydb`.`tickets` (
244 `id` INT NOT NULL AUTO_INCREMENT,
245 `user_id` INT NOT NULL,
246 `projection_id` INT NOT NULL,
247 `seat_number` INT NOT NULL,
248 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
249 PRIMARY KEY (`id`),
250 INDEX `user_id_idx` (`user_id` ASC) VISIBLE,
251 INDEX `projection_id_idx` (`projection_id` ASC) VISIBLE,
252 CONSTRAINT `user_id`
253 FOREIGN KEY (`user_id`)
254 REFERENCES `mydb`.`users` (`id`)
255 ON DELETE NO ACTION
256 ON UPDATE NO ACTION,
257 CONSTRAINT `projection_id`
258 FOREIGN KEY (`projection_id`)
259 REFERENCES `mydb`.`projection` (`id`)
260 ON DELETE NO ACTION
261 ON UPDATE NO ACTION)
262ENGINE = InnoDB;
263
264
265SET SQL_MODE=@OLD_SQL_MODE;
266SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
267SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;