· 6 years ago · Apr 04, 2019, 08:12 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Apr 5 02:01:03 2019
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`PERSON`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `mydb`.`PERSON` ;
24
25CREATE TABLE IF NOT EXISTS `mydb`.`PERSON` (
26 `person_id` INT NOT NULL AUTO_INCREMENT,
27 `birthdate` INT NOT NULL,
28 `birthplace` VARCHAR(45) NOT NULL,
29 `name` VARCHAR(45) NOT NULL,
30 `film_id` INT NOT NULL,
31 PRIMARY KEY (`person_id`),
32 INDEX `fk_PERSON_FILM1_idx` (`film_id` ASC),
33 CONSTRAINT `fk_PERSON_FILM1`
34 FOREIGN KEY (`film_id`)
35 REFERENCES `mydb`.`FILM` (`film_id`)
36 ON DELETE NO ACTION
37 ON UPDATE NO ACTION)
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`FILM`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `mydb`.`FILM` ;
45
46CREATE TABLE IF NOT EXISTS `mydb`.`FILM` (
47 `film_id` INT NOT NULL AUTO_INCREMENT,
48 `name` VARCHAR(45) NOT NULL,
49 `year` INT(4) NOT NULL,
50 `studio_id` INT NOT NULL,
51 `country` VARCHAR(45) NOT NULL,
52 `person_id` INT NOT NULL,
53 `publisher_id` INT NOT NULL,
54 PRIMARY KEY (`film_id`, `publisher_id`),
55 INDEX `fk_FILM_PERSON1_idx` (`person_id` ASC),
56 CONSTRAINT `fk_FILM_PERSON1`
57 FOREIGN KEY (`person_id`)
58 REFERENCES `mydb`.`PERSON` (`person_id`)
59 ON DELETE NO ACTION
60 ON UPDATE NO ACTION)
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `mydb`.`STUDIO`
66-- -----------------------------------------------------
67DROP TABLE IF EXISTS `mydb`.`STUDIO` ;
68
69CREATE TABLE IF NOT EXISTS `mydb`.`STUDIO` (
70 `studio_id` INT NOT NULL AUTO_INCREMENT,
71 `name` VARCHAR(45) NOT NULL,
72 `film_id` INT NOT NULL,
73 PRIMARY KEY (`studio_id`),
74 INDEX `fk_STUDIO_FILM1_idx` (`film_id` ASC),
75 CONSTRAINT `fk_STUDIO_FILM1`
76 FOREIGN KEY (`film_id`)
77 REFERENCES `mydb`.`FILM` (`film_id`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION)
80ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Table `mydb`.`PUBLISHER`
85-- -----------------------------------------------------
86DROP TABLE IF EXISTS `mydb`.`PUBLISHER` ;
87
88CREATE TABLE IF NOT EXISTS `mydb`.`PUBLISHER` (
89 `publisher_id` INT NOT NULL AUTO_INCREMENT,
90 `country` VARCHAR(45) NOT NULL,
91 `publisher_name` VARCHAR(45) NOT NULL,
92 `film_id` INT NOT NULL,
93 PRIMARY KEY (`publisher_id`, `film_id`),
94 INDEX `fk_PUBLISHER_FILM1_idx` (`film_id` ASC, `publisher_id` ASC),
95 CONSTRAINT `fk_PUBLISHER_FILM1`
96 FOREIGN KEY (`film_id` , `publisher_id`)
97 REFERENCES `mydb`.`FILM` (`film_id` , `publisher_id`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION)
100ENGINE = InnoDB;
101
102
103SET SQL_MODE=@OLD_SQL_MODE;
104SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
105SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
106
107-- -----------------------------------------------------
108-- Data for table `mydb`.`PERSON`
109-- -----------------------------------------------------
110START TRANSACTION;
111USE `mydb`;
112INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (1, 1964, 'Beirut', 'Keanu Reeves', 1);
113INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (1, 1964, 'Beirut ', 'Keanu Reeves', 2);
114INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (1, 1964, 'Beirut', 'Keanu Reeves', 3);
115INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (2, 1955, 'Appleton', 'Willem Dafoe', 1);
116INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (2, 1955, 'Appleton', 'Willem Dafoe', 4);
117INSERT INTO `mydb`.`PERSON` (`person_id`, `birthdate`, `birthplace`, `name`, `film_id`) VALUES (1, 1964, 'Beirut', 'Keanu Reeves', 5);
118
119COMMIT;
120
121
122-- -----------------------------------------------------
123-- Data for table `mydb`.`FILM`
124-- -----------------------------------------------------
125START TRANSACTION;
126USE `mydb`;
127INSERT INTO `mydb`.`FILM` (`film_id`, `name`, `year`, `studio_id`, `country`, `person_id`, `publisher_id`) VALUES (1, 'John Wick', 2014, 1, 'USA', 1, 1);
128INSERT INTO `mydb`.`FILM` (`film_id`, `name`, `year`, `studio_id`, `country`, `person_id`, `publisher_id`) VALUES (2, 'John Wick 2', 2017, 1, 'USA', 1, 2);
129INSERT INTO `mydb`.`FILM` (`film_id`, `name`, `year`, `studio_id`, `country`, `person_id`, `publisher_id`) VALUES (3, 'John Wick 3', 2019, 1, 'USA', 1, 2);
130INSERT INTO `mydb`.`FILM` (`film_id`, `name`, `year`, `studio_id`, `country`, `person_id`, `publisher_id`) VALUES (4, 'The Boondock Saints', 1999, 2, 'USA', 2, 3);
131INSERT INTO `mydb`.`FILM` (`film_id`, `name`, `year`, `studio_id`, `country`, `person_id`, `publisher_id`) VALUES (5, 'Matrix', 1999, 3, 'Russia', 1, 4);
132
133COMMIT;
134
135
136-- -----------------------------------------------------
137-- Data for table `mydb`.`STUDIO`
138-- -----------------------------------------------------
139START TRANSACTION;
140USE `mydb`;
141INSERT INTO `mydb`.`STUDIO` (`studio_id`, `name`, `film_id`) VALUES (1, 'JW Studio', 1);
142INSERT INTO `mydb`.`STUDIO` (`studio_id`, `name`, `film_id`) VALUES (1, 'JW Studio', 2);
143INSERT INTO `mydb`.`STUDIO` (`studio_id`, `name`, `film_id`) VALUES (1, 'JW Studio', 3);
144INSERT INTO `mydb`.`STUDIO` (`studio_id`, `name`, `film_id`) VALUES (2, 'CB Prod', 4);
145INSERT INTO `mydb`.`STUDIO` (`studio_id`, `name`, `film_id`) VALUES (3, 'Universal', 5);
146
147COMMIT;
148
149
150-- -----------------------------------------------------
151-- Data for table `mydb`.`PUBLISHER`
152-- -----------------------------------------------------
153START TRANSACTION;
154USE `mydb`;
155INSERT INTO `mydb`.`PUBLISHER` (`publisher_id`, `country`, `publisher_name`, `film_id`) VALUES (1, 'Rusiia', 'West', 1);
156INSERT INTO `mydb`.`PUBLISHER` (`publisher_id`, `country`, `publisher_name`, `film_id`) VALUES (2, 'Russia', 'Central', 2);
157INSERT INTO `mydb`.`PUBLISHER` (`publisher_id`, `country`, `publisher_name`, `film_id`) VALUES (2, 'Russia', 'Central', 3);
158INSERT INTO `mydb`.`PUBLISHER` (`publisher_id`, `country`, `publisher_name`, `film_id`) VALUES (3, 'Russia', 'Union', 4);
159
160COMMIT;