· 7 years ago · Jan 04, 2019, 04:40 PM
1-- MySQL Script generated by MySQL Workbench
2-- Пт 04 Ñнв 2019 21:35:57
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema sakila
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema sakila
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `sakila` ;
18USE `sakila` ;
19
20-- -----------------------------------------------------
21-- Table `sakila`.`actor`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
24 `actor_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
25 `first_name` VARCHAR(45) NOT NULL,
26 `last_name` VARCHAR(45) NOT NULL,
27 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 PRIMARY KEY (`actor_id`),
29 INDEX `idx_actor_last_name` (`last_name` ASC))
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `sakila`.`country`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `sakila`.`country` (
38 `country_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
39 `country` VARCHAR(50) NOT NULL,
40 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
41 PRIMARY KEY (`country_id`))
42ENGINE = InnoDB
43DEFAULT CHARACTER SET = utf8;
44
45
46-- -----------------------------------------------------
47-- Table `sakila`.`city`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `sakila`.`city` (
50 `city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
51 `city` VARCHAR(50) NOT NULL,
52 `country_id` SMALLINT UNSIGNED NOT NULL,
53 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 PRIMARY KEY (`city_id`),
55 INDEX `idx_fk_country_id` (`country_id` ASC),
56 CONSTRAINT `fk_city_country`
57 FOREIGN KEY (`country_id`)
58 REFERENCES `sakila`.`country` (`country_id`)
59 ON DELETE RESTRICT
60 ON UPDATE CASCADE)
61ENGINE = InnoDB
62DEFAULT CHARACTER SET = utf8;
63
64
65-- -----------------------------------------------------
66-- Table `sakila`.`address`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `sakila`.`address` (
69 `address_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
70 `address` VARCHAR(50) NOT NULL,
71 `address2` VARCHAR(50) NULL DEFAULT NULL,
72 `district` VARCHAR(20) NOT NULL,
73 `city_id` SMALLINT UNSIGNED NOT NULL,
74 `postal_code` VARCHAR(10) NULL DEFAULT NULL,
75 `phone` VARCHAR(20) NOT NULL,
76 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
77 PRIMARY KEY (`address_id`),
78 INDEX `idx_fk_city_id` (`city_id` ASC),
79 CONSTRAINT `fk_address_city`
80 FOREIGN KEY (`city_id`)
81 REFERENCES `sakila`.`city` (`city_id`)
82 ON DELETE RESTRICT
83 ON UPDATE CASCADE)
84ENGINE = InnoDB
85DEFAULT CHARACTER SET = utf8;
86
87
88-- -----------------------------------------------------
89-- Table `sakila`.`category`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `sakila`.`category` (
92 `category_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
93 `name` VARCHAR(25) NOT NULL,
94 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
95 PRIMARY KEY (`category_id`))
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8;
98
99
100-- -----------------------------------------------------
101-- Table `sakila`.`staff`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `sakila`.`staff` (
104 `staff_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
105 `first_name` VARCHAR(45) NOT NULL,
106 `last_name` VARCHAR(45) NOT NULL,
107 `address_id` SMALLINT UNSIGNED NOT NULL,
108 `picture` BLOB NULL,
109 `email` VARCHAR(50) NULL DEFAULT NULL,
110 `store_id` TINYINT UNSIGNED NOT NULL,
111 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
112 `username` VARCHAR(16) NOT NULL,
113 `password` VARCHAR(40) BINARY NULL DEFAULT NULL,
114 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
115 PRIMARY KEY (`staff_id`),
116 INDEX `idx_fk_store_id` (`store_id` ASC),
117 INDEX `idx_fk_address_id` (`address_id` ASC),
118 CONSTRAINT `fk_staff_store`
119 FOREIGN KEY (`store_id`)
120 REFERENCES `sakila`.`store` (`store_id`)
121 ON DELETE RESTRICT
122 ON UPDATE CASCADE,
123 CONSTRAINT `fk_staff_address`
124 FOREIGN KEY (`address_id`)
125 REFERENCES `sakila`.`address` (`address_id`)
126 ON DELETE RESTRICT
127 ON UPDATE CASCADE)
128ENGINE = InnoDB
129DEFAULT CHARACTER SET = utf8;
130
131
132-- -----------------------------------------------------
133-- Table `sakila`.`store`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `sakila`.`store` (
136 `store_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
137 `manager_staff_id` TINYINT UNSIGNED NOT NULL,
138 `address_id` SMALLINT UNSIGNED NOT NULL,
139 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
140 PRIMARY KEY (`store_id`),
141 UNIQUE INDEX `idx_unique_manager` (`manager_staff_id` ASC),
142 INDEX `idx_fk_address_id` (`address_id` ASC),
143 CONSTRAINT `fk_store_staff`
144 FOREIGN KEY (`manager_staff_id`)
145 REFERENCES `sakila`.`staff` (`staff_id`)
146 ON DELETE RESTRICT
147 ON UPDATE CASCADE,
148 CONSTRAINT `fk_store_address`
149 FOREIGN KEY (`address_id`)
150 REFERENCES `sakila`.`address` (`address_id`)
151 ON DELETE RESTRICT
152 ON UPDATE CASCADE)
153ENGINE = InnoDB
154DEFAULT CHARACTER SET = utf8;
155
156
157-- -----------------------------------------------------
158-- Table `sakila`.`customer`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `sakila`.`customer` (
161 `customer_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
162 `store_id` TINYINT UNSIGNED NOT NULL,
163 `first_name` VARCHAR(45) NOT NULL,
164 `last_name` VARCHAR(45) NOT NULL,
165 `email` VARCHAR(50) NULL DEFAULT NULL,
166 `address_id` SMALLINT UNSIGNED NOT NULL,
167 `active` TINYINT(1) NOT NULL DEFAULT TRUE,
168 `create_date` DATETIME NOT NULL,
169 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
170 PRIMARY KEY (`customer_id`),
171 INDEX `idx_fk_store_id` (`store_id` ASC),
172 INDEX `idx_fk_address_id` (`address_id` ASC),
173 INDEX `idx_last_name` (`last_name` ASC),
174 CONSTRAINT `fk_customer_address`
175 FOREIGN KEY (`address_id`)
176 REFERENCES `sakila`.`address` (`address_id`)
177 ON DELETE RESTRICT
178 ON UPDATE CASCADE,
179 CONSTRAINT `fk_customer_store`
180 FOREIGN KEY (`store_id`)
181 REFERENCES `sakila`.`store` (`store_id`)
182 ON DELETE RESTRICT
183 ON UPDATE CASCADE)
184ENGINE = InnoDB
185DEFAULT CHARACTER SET = utf8
186COMMENT = 'Table storing all customers. Holds foreign keys to the addre' /* comment truncated */ /*s table and the store table where this customer is registered.
187
188Basic information about the customer like first and last name are stored in the table itself. Same for the date the record was created and when the information was last updated.*/;
189
190
191-- -----------------------------------------------------
192-- Table `sakila`.`language`
193-- -----------------------------------------------------
194CREATE TABLE IF NOT EXISTS `sakila`.`language` (
195 `language_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
196 `name` CHAR(20) NOT NULL,
197 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
198 PRIMARY KEY (`language_id`))
199ENGINE = InnoDB
200DEFAULT CHARACTER SET = utf8;
201
202
203-- -----------------------------------------------------
204-- Table `sakila`.`film`
205-- -----------------------------------------------------
206CREATE TABLE IF NOT EXISTS `sakila`.`film` (
207 `film_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
208 `title` VARCHAR(255) NOT NULL,
209 `description` TEXT NULL,
210 `release_year` YEAR NULL,
211 `language_id` TINYINT UNSIGNED NOT NULL,
212 `original_language_id` TINYINT UNSIGNED NULL DEFAULT NULL,
213 `rental_duration` TINYINT UNSIGNED NOT NULL DEFAULT 3,
214 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT 4.99,
215 `length` SMALLINT UNSIGNED NULL DEFAULT NULL,
216 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT 19.99,
217 `rating` ENUM('G','PG','PG-13','R','NC-17') NULL DEFAULT 'G',
218 `special_features` SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') NULL,
219 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
220 INDEX `idx_title` (`title` ASC),
221 INDEX `idx_fk_language_id` (`language_id` ASC),
222 INDEX `idx_fk_original_language_id` (`original_language_id` ASC),
223 PRIMARY KEY (`film_id`),
224 CONSTRAINT `fk_film_language`
225 FOREIGN KEY (`language_id`)
226 REFERENCES `sakila`.`language` (`language_id`)
227 ON DELETE RESTRICT
228 ON UPDATE CASCADE,
229 CONSTRAINT `fk_film_language_original`
230 FOREIGN KEY (`original_language_id`)
231 REFERENCES `sakila`.`language` (`language_id`)
232 ON DELETE RESTRICT
233 ON UPDATE CASCADE)
234ENGINE = InnoDB
235DEFAULT CHARACTER SET = utf8;
236
237
238-- -----------------------------------------------------
239-- Table `sakila`.`film_actor`
240-- -----------------------------------------------------
241CREATE TABLE IF NOT EXISTS `sakila`.`film_actor` (
242 `actor_id` SMALLINT UNSIGNED NOT NULL,
243 `film_id` SMALLINT UNSIGNED NOT NULL,
244 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
245 PRIMARY KEY (`actor_id`, `film_id`),
246 INDEX `idx_fk_film_id` (`film_id` ASC),
247 INDEX `fk_film_actor_actor_idx` (`actor_id` ASC),
248 CONSTRAINT `fk_film_actor_actor`
249 FOREIGN KEY (`actor_id`)
250 REFERENCES `sakila`.`actor` (`actor_id`)
251 ON DELETE RESTRICT
252 ON UPDATE CASCADE,
253 CONSTRAINT `fk_film_actor_film`
254 FOREIGN KEY (`film_id`)
255 REFERENCES `sakila`.`film` (`film_id`)
256 ON DELETE RESTRICT
257 ON UPDATE CASCADE)
258ENGINE = InnoDB
259DEFAULT CHARACTER SET = utf8;
260
261
262-- -----------------------------------------------------
263-- Table `sakila`.`film_category`
264-- -----------------------------------------------------
265CREATE TABLE IF NOT EXISTS `sakila`.`film_category` (
266 `film_id` SMALLINT UNSIGNED NOT NULL,
267 `category_id` TINYINT UNSIGNED NOT NULL,
268 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
269 PRIMARY KEY (`film_id`, `category_id`),
270 INDEX `fk_film_category_category_idx` (`category_id` ASC),
271 INDEX `fk_film_category_film_idx` (`film_id` ASC),
272 CONSTRAINT `fk_film_category_film`
273 FOREIGN KEY (`film_id`)
274 REFERENCES `sakila`.`film` (`film_id`)
275 ON DELETE RESTRICT
276 ON UPDATE CASCADE,
277 CONSTRAINT `fk_film_category_category`
278 FOREIGN KEY (`category_id`)
279 REFERENCES `sakila`.`category` (`category_id`)
280 ON DELETE RESTRICT
281 ON UPDATE CASCADE)
282ENGINE = InnoDB
283DEFAULT CHARACTER SET = utf8;
284
285
286-- -----------------------------------------------------
287-- Table `sakila`.`film_text`
288-- -----------------------------------------------------
289CREATE TABLE IF NOT EXISTS `sakila`.`film_text` (
290 `film_id` SMALLINT UNSIGNED NOT NULL,
291 `title` VARCHAR(255) NOT NULL,
292 `description` TEXT NULL,
293 PRIMARY KEY (`film_id`),
294 FULLTEXT INDEX `idx_title_description` (`title` ASC, `description` ASC))
295ENGINE = InnoDB;
296
297
298-- -----------------------------------------------------
299-- Table `sakila`.`inventory`
300-- -----------------------------------------------------
301CREATE TABLE IF NOT EXISTS `sakila`.`inventory` (
302 `inventory_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
303 `film_id` SMALLINT UNSIGNED NOT NULL,
304 `store_id` TINYINT UNSIGNED NOT NULL,
305 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
306 PRIMARY KEY (`inventory_id`),
307 INDEX `idx_fk_film_id` (`film_id` ASC),
308 INDEX `idx_store_id_film_id` (`store_id` ASC, `film_id` ASC),
309 INDEX `fk_inventory_store_idx` (`store_id` ASC),
310 CONSTRAINT `fk_inventory_store`
311 FOREIGN KEY (`store_id`)
312 REFERENCES `sakila`.`store` (`store_id`)
313 ON DELETE RESTRICT
314 ON UPDATE CASCADE,
315 CONSTRAINT `fk_inventory_film`
316 FOREIGN KEY (`film_id`)
317 REFERENCES `sakila`.`film` (`film_id`)
318 ON DELETE RESTRICT
319 ON UPDATE CASCADE)
320ENGINE = InnoDB
321DEFAULT CHARACTER SET = utf8;
322
323
324-- -----------------------------------------------------
325-- Table `sakila`.`rental`
326-- -----------------------------------------------------
327CREATE TABLE IF NOT EXISTS `sakila`.`rental` (
328 `rental_id` INT NOT NULL AUTO_INCREMENT,
329 `rental_date` DATETIME NOT NULL,
330 `inventory_id` MEDIUMINT UNSIGNED NOT NULL,
331 `customer_id` SMALLINT UNSIGNED NOT NULL,
332 `return_date` DATETIME NULL,
333 `staff_id` TINYINT UNSIGNED NOT NULL,
334 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
335 PRIMARY KEY (`rental_id`),
336 UNIQUE INDEX `idx_rental` (`rental_date` ASC, `inventory_id` ASC, `customer_id` ASC),
337 INDEX `idx_fk_inventory_id` (`inventory_id` ASC),
338 INDEX `idx_fk_customer_id` (`customer_id` ASC),
339 INDEX `idx_fk_staff_id` (`staff_id` ASC),
340 CONSTRAINT `fk_rental_staff`
341 FOREIGN KEY (`staff_id`)
342 REFERENCES `sakila`.`staff` (`staff_id`)
343 ON DELETE RESTRICT
344 ON UPDATE CASCADE,
345 CONSTRAINT `fk_rental_inventory`
346 FOREIGN KEY (`inventory_id`)
347 REFERENCES `sakila`.`inventory` (`inventory_id`)
348 ON DELETE RESTRICT
349 ON UPDATE CASCADE,
350 CONSTRAINT `fk_rental_customer`
351 FOREIGN KEY (`customer_id`)
352 REFERENCES `sakila`.`customer` (`customer_id`)
353 ON DELETE RESTRICT
354 ON UPDATE CASCADE)
355ENGINE = InnoDB
356DEFAULT CHARACTER SET = utf8;
357
358
359-- -----------------------------------------------------
360-- Table `sakila`.`payment`
361-- -----------------------------------------------------
362CREATE TABLE IF NOT EXISTS `sakila`.`payment` (
363 `payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
364 `customer_id` SMALLINT UNSIGNED NOT NULL,
365 `staff_id` TINYINT UNSIGNED NOT NULL,
366 `rental_id` INT NULL DEFAULT NULL,
367 `amount` DECIMAL(5,2) NOT NULL,
368 `payment_date` DATETIME NOT NULL,
369 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
370 PRIMARY KEY (`payment_id`),
371 INDEX `idx_fk_staff_id` (`staff_id` ASC),
372 INDEX `idx_fk_customer_id` (`customer_id` ASC),
373 INDEX `fk_payment_rental_idx` (`rental_id` ASC),
374 CONSTRAINT `fk_payment_rental`
375 FOREIGN KEY (`rental_id`)
376 REFERENCES `sakila`.`rental` (`rental_id`)
377 ON DELETE SET NULL
378 ON UPDATE CASCADE,
379 CONSTRAINT `fk_payment_customer`
380 FOREIGN KEY (`customer_id`)
381 REFERENCES `sakila`.`customer` (`customer_id`)
382 ON DELETE RESTRICT
383 ON UPDATE CASCADE,
384 CONSTRAINT `fk_payment_staff`
385 FOREIGN KEY (`staff_id`)
386 REFERENCES `sakila`.`staff` (`staff_id`)
387 ON DELETE RESTRICT
388 ON UPDATE CASCADE)
389ENGINE = InnoDB
390DEFAULT CHARACTER SET = utf8;
391
392
393-- -----------------------------------------------------
394-- Table `sakila`.`permisions`
395-- -----------------------------------------------------
396CREATE TABLE IF NOT EXISTS `sakila`.`permisions` (
397 `id` INT NOT NULL AUTO_INCREMENT,
398 `name` VARCHAR(45) NOT NULL,
399 PRIMARY KEY (`id`))
400ENGINE = InnoDB;
401
402
403-- -----------------------------------------------------
404-- Table `sakila`.`users`
405-- -----------------------------------------------------
406CREATE TABLE IF NOT EXISTS `sakila`.`users` (
407 `id` INT NOT NULL AUTO_INCREMENT,
408 `permisions_id` INT NOT NULL,
409 PRIMARY KEY (`id`),
410 INDEX `fk_users_permisions1_idx` (`permisions_id` ASC),
411 CONSTRAINT `fk_users_permisions1`
412 FOREIGN KEY (`permisions_id`)
413 REFERENCES `sakila`.`permisions` (`id`)
414 ON DELETE NO ACTION
415 ON UPDATE NO ACTION)
416ENGINE = InnoDB;
417
418
419-- -----------------------------------------------------
420-- Table `sakila`.`courses`
421-- -----------------------------------------------------
422CREATE TABLE IF NOT EXISTS `sakila`.`courses` (
423 `id` INT NOT NULL AUTO_INCREMENT,
424 `owner_id` INT NOT NULL,
425 PRIMARY KEY (`id`),
426 INDEX `fk_courses_users1_idx` (`owner_id` ASC),
427 CONSTRAINT `fk_courses_users1`
428 FOREIGN KEY (`owner_id`)
429 REFERENCES `sakila`.`users` (`id`)
430 ON DELETE NO ACTION
431 ON UPDATE NO ACTION)
432ENGINE = InnoDB;
433
434
435-- -----------------------------------------------------
436-- Table `sakila`.`videos`
437-- -----------------------------------------------------
438CREATE TABLE IF NOT EXISTS `sakila`.`videos` (
439 `id` INT NOT NULL AUTO_INCREMENT,
440 PRIMARY KEY (`id`))
441ENGINE = InnoDB;
442
443
444-- -----------------------------------------------------
445-- Table `sakila`.`course_subscribes`
446-- -----------------------------------------------------
447CREATE TABLE IF NOT EXISTS `sakila`.`course_subscribes` (
448 `users_id` INT NOT NULL,
449 `courses_id` INT NOT NULL,
450 PRIMARY KEY (`users_id`, `courses_id`),
451 INDEX `fk_users_has_courses_courses1_idx` (`courses_id` ASC),
452 INDEX `fk_users_has_courses_users1_idx` (`users_id` ASC),
453 CONSTRAINT `fk_users_has_courses_users1`
454 FOREIGN KEY (`users_id`)
455 REFERENCES `sakila`.`users` (`id`)
456 ON DELETE NO ACTION
457 ON UPDATE NO ACTION,
458 CONSTRAINT `fk_users_has_courses_courses1`
459 FOREIGN KEY (`courses_id`)
460 REFERENCES `sakila`.`courses` (`id`)
461 ON DELETE NO ACTION
462 ON UPDATE NO ACTION)
463ENGINE = InnoDB;
464
465
466-- -----------------------------------------------------
467-- Table `sakila`.`course_frames`
468-- -----------------------------------------------------
469CREATE TABLE IF NOT EXISTS `sakila`.`course_frames` (
470 `id` INT NOT NULL AUTO_INCREMENT,
471 `courses_id` INT NOT NULL,
472 PRIMARY KEY (`id`),
473 INDEX `fk_course_frames_courses1_idx` (`courses_id` ASC),
474 CONSTRAINT `fk_course_frames_courses1`
475 FOREIGN KEY (`courses_id`)
476 REFERENCES `sakila`.`courses` (`id`)
477 ON DELETE NO ACTION
478 ON UPDATE NO ACTION)
479ENGINE = InnoDB;
480
481
482-- -----------------------------------------------------
483-- Table `sakila`.`images`
484-- -----------------------------------------------------
485CREATE TABLE IF NOT EXISTS `sakila`.`images` (
486 `id` INT NOT NULL,
487 PRIMARY KEY (`id`))
488ENGINE = InnoDB;
489
490
491-- -----------------------------------------------------
492-- Table `sakila`.`texts`
493-- -----------------------------------------------------
494CREATE TABLE IF NOT EXISTS `sakila`.`texts` (
495 `id` INT NOT NULL,
496 PRIMARY KEY (`id`))
497ENGINE = InnoDB;
498
499
500-- -----------------------------------------------------
501-- Table `sakila`.`problems`
502-- -----------------------------------------------------
503CREATE TABLE IF NOT EXISTS `sakila`.`problems` (
504 `id` INT NOT NULL AUTO_INCREMENT,
505 PRIMARY KEY (`id`))
506ENGINE = InnoDB;
507
508
509-- -----------------------------------------------------
510-- Table `sakila`.`course_frame_elements`
511-- -----------------------------------------------------
512CREATE TABLE IF NOT EXISTS `sakila`.`course_frame_elements` (
513 `id` INT NOT NULL AUTO_INCREMENT,
514 `course_frames_id` INT NOT NULL,
515 `order` INT NOT NULL,
516 `videos_id` INT NULL,
517 `images_id` INT NULL,
518 `texts_id` INT NULL,
519 PRIMARY KEY (`id`),
520 INDEX `fk_course_frame_elements_course_frames1_idx` (`course_frames_id` ASC),
521 INDEX `fk_course_frame_elements_videos1_idx` (`videos_id` ASC),
522 INDEX `fk_course_frame_elements_images1_idx` (`images_id` ASC),
523 INDEX `fk_course_frame_elements_texts1_idx` (`texts_id` ASC),
524 CONSTRAINT `fk_course_frame_elements_course_frames1`
525 FOREIGN KEY (`course_frames_id`)
526 REFERENCES `sakila`.`course_frames` (`id`)
527 ON DELETE NO ACTION
528 ON UPDATE NO ACTION,
529 CONSTRAINT `fk_course_frame_elements_videos1`
530 FOREIGN KEY (`videos_id`)
531 REFERENCES `sakila`.`videos` (`id`)
532 ON DELETE NO ACTION
533 ON UPDATE NO ACTION,
534 CONSTRAINT `fk_course_frame_elements_images1`
535 FOREIGN KEY (`images_id`)
536 REFERENCES `sakila`.`images` (`id`)
537 ON DELETE NO ACTION
538 ON UPDATE NO ACTION,
539 CONSTRAINT `fk_course_frame_elements_texts1`
540 FOREIGN KEY (`texts_id`)
541 REFERENCES `sakila`.`texts` (`id`)
542 ON DELETE NO ACTION
543 ON UPDATE NO ACTION)
544ENGINE = InnoDB;
545
546
547-- -----------------------------------------------------
548-- Table `sakila`.`course_frames_has_problems`
549-- -----------------------------------------------------
550CREATE TABLE IF NOT EXISTS `sakila`.`course_frames_has_problems` (
551 `course_frames_id` INT NOT NULL,
552 `problems_id` INT NOT NULL,
553 PRIMARY KEY (`course_frames_id`, `problems_id`),
554 INDEX `fk_course_frames_has_problems_problems1_idx` (`problems_id` ASC),
555 INDEX `fk_course_frames_has_problems_course_frames1_idx` (`course_frames_id` ASC),
556 CONSTRAINT `fk_course_frames_has_problems_course_frames1`
557 FOREIGN KEY (`course_frames_id`)
558 REFERENCES `sakila`.`course_frames` (`id`)
559 ON DELETE NO ACTION
560 ON UPDATE NO ACTION,
561 CONSTRAINT `fk_course_frames_has_problems_problems1`
562 FOREIGN KEY (`problems_id`)
563 REFERENCES `sakila`.`problems` (`id`)
564 ON DELETE NO ACTION
565 ON UPDATE NO ACTION)
566ENGINE = InnoDB;
567
568USE `sakila` ;
569
570-- -----------------------------------------------------
571-- Placeholder table for view `sakila`.`customer_list`
572-- -----------------------------------------------------
573CREATE TABLE IF NOT EXISTS `sakila`.`customer_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `notes` INT, `SID` INT);
574
575-- -----------------------------------------------------
576-- Placeholder table for view `sakila`.`film_list`
577-- -----------------------------------------------------
578CREATE TABLE IF NOT EXISTS `sakila`.`film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
579
580-- -----------------------------------------------------
581-- Placeholder table for view `sakila`.`nicer_but_slower_film_list`
582-- -----------------------------------------------------
583CREATE TABLE IF NOT EXISTS `sakila`.`nicer_but_slower_film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
584
585-- -----------------------------------------------------
586-- Placeholder table for view `sakila`.`staff_list`
587-- -----------------------------------------------------
588CREATE TABLE IF NOT EXISTS `sakila`.`staff_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `SID` INT);
589
590-- -----------------------------------------------------
591-- Placeholder table for view `sakila`.`sales_by_store`
592-- -----------------------------------------------------
593CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_store` (`store` INT, `manager` INT, `total_sales` INT);
594
595-- -----------------------------------------------------
596-- Placeholder table for view `sakila`.`sales_by_film_category`
597-- -----------------------------------------------------
598CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_film_category` (`category` INT, `total_sales` INT);
599
600-- -----------------------------------------------------
601-- Placeholder table for view `sakila`.`actor_info`
602-- -----------------------------------------------------
603CREATE TABLE IF NOT EXISTS `sakila`.`actor_info` (`actor_id` INT, `first_name` INT, `last_name` INT, `film_info` INT);
604
605-- -----------------------------------------------------
606-- procedure rewards_report
607-- -----------------------------------------------------
608
609DELIMITER $$
610USE `sakila`$$
611
612
613CREATE PROCEDURE `sakila`.`rewards_report` (
614 IN min_monthly_purchases TINYINT UNSIGNED
615 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
616 , OUT count_rewardees INT
617)
618LANGUAGE SQL
619NOT DETERMINISTIC
620READS SQL DATA
621SQL SECURITY DEFINER
622COMMENT 'Provides a customizable report on best customers'
623proc: BEGIN
624
625 DECLARE last_month_start DATE;
626 DECLARE last_month_end DATE;
627
628 /* Some sanity checks... */
629 IF min_monthly_purchases = 0 THEN
630 SELECT 'Minimum monthly purchases parameter must be > 0';
631 LEAVE proc;
632 END IF;
633 IF min_dollar_amount_purchased = 0.00 THEN
634 SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
635 LEAVE proc;
636 END IF;
637
638 /* Determine start and end time periods */
639 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
640 SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
641 SET last_month_end = LAST_DAY(last_month_start);
642
643 /*
644 Create a temporary storage area for
645 Customer IDs.
646 */
647 CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
648
649 /*
650 Find all customers meeting the
651 monthly purchase requirements
652 */
653 INSERT INTO tmpCustomer (customer_id)
654 SELECT p.customer_id
655 FROM payment AS p
656 WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
657 GROUP BY customer_id
658 HAVING SUM(p.amount) > min_dollar_amount_purchased
659 AND COUNT(customer_id) > min_monthly_purchases;
660
661 /* Populate OUT parameter with count of found customers */
662 SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
663
664 /*
665 Output ALL customer information of matching rewardees.
666 Customize output as needed.
667 */
668 SELECT c.*
669 FROM tmpCustomer AS t
670 INNER JOIN customer AS c ON t.customer_id = c.customer_id;
671
672 /* Clean up */
673 DROP TABLE tmpCustomer;
674END$$
675
676DELIMITER ;
677
678-- -----------------------------------------------------
679-- function get_customer_balance
680-- -----------------------------------------------------
681
682DELIMITER $$
683USE `sakila`$$
684
685
686CREATE FUNCTION `sakila`.`get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
687 DETERMINISTIC
688 READS SQL DATA
689BEGIN
690
691 #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
692 #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
693 # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
694 # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
695 # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
696 # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
697
698 DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
699 DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
700 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
701
702 SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
703 FROM film, inventory, rental
704 WHERE film.film_id = inventory.film_id
705 AND inventory.inventory_id = rental.inventory_id
706 AND rental.rental_date <= p_effective_date
707 AND rental.customer_id = p_customer_id;
708
709 SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
710 ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
711 FROM rental, inventory, film
712 WHERE film.film_id = inventory.film_id
713 AND inventory.inventory_id = rental.inventory_id
714 AND rental.rental_date <= p_effective_date
715 AND rental.customer_id = p_customer_id;
716
717
718 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
719 FROM payment
720
721 WHERE payment.payment_date <= p_effective_date
722 AND payment.customer_id = p_customer_id;
723
724 RETURN v_rentfees + v_overfees - v_payments;
725END$$
726
727DELIMITER ;
728
729-- -----------------------------------------------------
730-- procedure film_in_stock
731-- -----------------------------------------------------
732
733DELIMITER $$
734USE `sakila`$$
735
736
737CREATE PROCEDURE `sakila`.`film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
738READS SQL DATA
739BEGIN
740 SELECT inventory_id
741 FROM inventory
742 WHERE film_id = p_film_id
743 AND store_id = p_store_id
744 AND inventory_in_stock(inventory_id);
745
746 SELECT FOUND_ROWS() INTO p_film_count;
747END$$
748
749DELIMITER ;
750
751-- -----------------------------------------------------
752-- procedure film_not_in_stock
753-- -----------------------------------------------------
754
755DELIMITER $$
756USE `sakila`$$
757
758
759CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
760READS SQL DATA
761BEGIN
762 SELECT inventory_id
763 FROM inventory
764 WHERE film_id = p_film_id
765 AND store_id = p_store_id
766 AND NOT inventory_in_stock(inventory_id);
767
768 SELECT FOUND_ROWS() INTO p_film_count;
769END$$
770
771DELIMITER ;
772
773-- -----------------------------------------------------
774-- function inventory_held_by_customer
775-- -----------------------------------------------------
776
777DELIMITER $$
778USE `sakila`$$
779
780
781CREATE FUNCTION `sakila`.`inventory_held_by_customer`(p_inventory_id INT) RETURNS INT
782READS SQL DATA
783BEGIN
784 DECLARE v_customer_id INT;
785 DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
786
787 SELECT customer_id INTO v_customer_id
788 FROM rental
789 WHERE return_date IS NULL
790 AND inventory_id = p_inventory_id;
791
792 RETURN v_customer_id;
793END$$
794
795DELIMITER ;
796
797-- -----------------------------------------------------
798-- function inventory_in_stock
799-- -----------------------------------------------------
800
801DELIMITER $$
802USE `sakila`$$
803
804
805CREATE FUNCTION `sakila`.`inventory_in_stock`(p_inventory_id INT) RETURNS BOOLEAN
806READS SQL DATA
807BEGIN
808 DECLARE v_rentals INT;
809 DECLARE v_out INT;
810
811 #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
812 #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
813
814 SELECT COUNT(*) INTO v_rentals
815 FROM rental
816 WHERE inventory_id = p_inventory_id;
817
818 IF v_rentals = 0 THEN
819 RETURN TRUE;
820 END IF;
821
822 SELECT COUNT(rental_id) INTO v_out
823 FROM inventory LEFT JOIN rental USING(inventory_id)
824 WHERE inventory.inventory_id = p_inventory_id
825 AND rental.return_date IS NULL;
826
827 IF v_out > 0 THEN
828 RETURN FALSE;
829 ELSE
830 RETURN TRUE;
831 END IF;
832END$$
833
834DELIMITER ;
835
836-- -----------------------------------------------------
837-- View `sakila`.`customer_list`
838-- -----------------------------------------------------
839DROP TABLE IF EXISTS `sakila`.`customer_list`;
840USE `sakila`;
841--
842-- View structure for view `customer_list`
843--
844
845CREATE OR REPLACE VIEW customer_list
846AS
847SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
848 a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
849FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
850 JOIN country ON city.country_id = country.country_id;
851
852-- -----------------------------------------------------
853-- View `sakila`.`film_list`
854-- -----------------------------------------------------
855DROP TABLE IF EXISTS `sakila`.`film_list`;
856USE `sakila`;
857--
858-- View structure for view `film_list`
859--
860
861CREATE OR REPLACE VIEW film_list
862AS
863SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
864 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
865FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
866 JOIN film_actor ON film.film_id = film_actor.film_id
867 JOIN actor ON film_actor.actor_id = actor.actor_id
868GROUP BY film.film_id, category.name;
869
870-- -----------------------------------------------------
871-- View `sakila`.`nicer_but_slower_film_list`
872-- -----------------------------------------------------
873DROP TABLE IF EXISTS `sakila`.`nicer_but_slower_film_list`;
874USE `sakila`;
875--
876-- View structure for view `nicer_but_slower_film_list`
877--
878
879CREATE OR REPLACE VIEW nicer_but_slower_film_list
880AS
881SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
882 film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
883 LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
884 LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
885FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
886 JOIN film_actor ON film.film_id = film_actor.film_id
887 JOIN actor ON film_actor.actor_id = actor.actor_id
888GROUP BY film.film_id, category.name;
889
890-- -----------------------------------------------------
891-- View `sakila`.`staff_list`
892-- -----------------------------------------------------
893DROP TABLE IF EXISTS `sakila`.`staff_list`;
894USE `sakila`;
895--
896-- View structure for view `staff_list`
897--
898
899CREATE OR REPLACE VIEW staff_list
900AS
901SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.last_name) AS name, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone,
902 city.city AS city, country.country AS country, s.store_id AS SID
903FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
904 JOIN country ON city.country_id = country.country_id;
905
906-- -----------------------------------------------------
907-- View `sakila`.`sales_by_store`
908-- -----------------------------------------------------
909DROP TABLE IF EXISTS `sakila`.`sales_by_store`;
910USE `sakila`;
911--
912-- View structure for view `sales_by_store`
913--
914
915CREATE OR REPLACE VIEW sales_by_store
916AS
917SELECT
918CONCAT(c.city, _utf8',', cy.country) AS store
919, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
920, SUM(p.amount) AS total_sales
921FROM payment AS p
922INNER JOIN rental AS r ON p.rental_id = r.rental_id
923INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
924INNER JOIN store AS s ON i.store_id = s.store_id
925INNER JOIN address AS a ON s.address_id = a.address_id
926INNER JOIN city AS c ON a.city_id = c.city_id
927INNER JOIN country AS cy ON c.country_id = cy.country_id
928INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
929GROUP BY s.store_id
930ORDER BY cy.country, c.city;
931
932-- -----------------------------------------------------
933-- View `sakila`.`sales_by_film_category`
934-- -----------------------------------------------------
935DROP TABLE IF EXISTS `sakila`.`sales_by_film_category`;
936USE `sakila`;
937--
938-- View structure for view `sales_by_film_category`
939--
940-- Note that total sales will add up to >100% because
941-- some titles belong to more than 1 category
942--
943
944CREATE OR REPLACE VIEW sales_by_film_category
945AS
946SELECT
947c.name AS category
948, SUM(p.amount) AS total_sales
949FROM payment AS p
950INNER JOIN rental AS r ON p.rental_id = r.rental_id
951INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
952INNER JOIN film AS f ON i.film_id = f.film_id
953INNER JOIN film_category AS fc ON f.film_id = fc.film_id
954INNER JOIN category AS c ON fc.category_id = c.category_id
955GROUP BY c.name
956ORDER BY total_sales DESC;
957
958-- -----------------------------------------------------
959-- View `sakila`.`actor_info`
960-- -----------------------------------------------------
961DROP TABLE IF EXISTS `sakila`.`actor_info`;
962USE `sakila`;
963--
964-- View structure for view `actor_info`
965--
966
967CREATE OR REPLACE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
968AS
969SELECT
970a.actor_id,
971a.first_name,
972a.last_name,
973GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
974 (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
975 FROM sakila.film f
976 INNER JOIN sakila.film_category fc
977 ON f.film_id = fc.film_id
978 INNER JOIN sakila.film_actor fa
979 ON f.film_id = fa.film_id
980 WHERE fc.category_id = c.category_id
981 AND fa.actor_id = a.actor_id
982 )
983 )
984 ORDER BY c.name SEPARATOR '; ')
985AS film_info
986FROM sakila.actor a
987LEFT JOIN sakila.film_actor fa
988 ON a.actor_id = fa.actor_id
989LEFT JOIN sakila.film_category fc
990 ON fa.film_id = fc.film_id
991LEFT JOIN sakila.category c
992 ON fc.category_id = c.category_id
993GROUP BY a.actor_id, a.first_name, a.last_name;
994USE `sakila`;
995
996DELIMITER $$
997USE `sakila`$$
998
999CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
1000 INSERT INTO film_text (film_id, title, description)
1001 VALUES (new.film_id, new.title, new.description);
1002 END$$
1003
1004USE `sakila`$$
1005
1006CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
1007 IF (old.title != new.title) or (old.description != new.description)
1008 THEN
1009 UPDATE film_text
1010 SET title=new.title,
1011 description=new.description,
1012 film_id=new.film_id
1013 WHERE film_id=old.film_id;
1014 END IF;
1015 END$$
1016
1017USE `sakila`$$
1018
1019CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
1020 DELETE FROM film_text WHERE film_id = old.film_id;
1021 END$$
1022
1023
1024DELIMITER ;
1025
1026SET SQL_MODE=@OLD_SQL_MODE;
1027SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
1028SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;