· 6 years ago · Dec 18, 2019, 07:26 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 ;
15-- -----------------------------------------------------
16-- Schema new_schema
17-- -----------------------------------------------------
18
19-- -----------------------------------------------------
20-- Schema new_schema
21-- -----------------------------------------------------
22CREATE SCHEMA IF NOT EXISTS `new_schema` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
23-- -----------------------------------------------------
24-- Schema qwerty
25-- -----------------------------------------------------
26
27-- -----------------------------------------------------
28-- Schema qwerty
29-- -----------------------------------------------------
30CREATE SCHEMA IF NOT EXISTS `qwerty` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
31-- -----------------------------------------------------
32-- Schema sakila
33-- -----------------------------------------------------
34
35-- -----------------------------------------------------
36-- Schema sakila
37-- -----------------------------------------------------
38CREATE SCHEMA IF NOT EXISTS `sakila` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
39-- -----------------------------------------------------
40-- Schema world
41-- -----------------------------------------------------
42
43-- -----------------------------------------------------
44-- Schema world
45-- -----------------------------------------------------
46CREATE SCHEMA IF NOT EXISTS `world` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
47-- -----------------------------------------------------
48-- Schema shop
49-- -----------------------------------------------------
50USE `mydb` ;
51
52-- -----------------------------------------------------
53-- Table `mydb`.`Brand`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mydb`.`Brand` (
56 `id` INT NOT NULL,
57 `BrandName` VARCHAR(45) NOT NULL,
58 PRIMARY KEY (`id`),
59 UNIQUE INDEX `idBrand_UNIQUE` (`id` ASC) VISIBLE)
60ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64-- Table `mydb`.`Model`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `mydb`.`Model` (
67 `id` INT NOT NULL,
68 `ModelName` VARCHAR(45) NOT NULL,
69 PRIMARY KEY (`id`),
70 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `mydb`.`Supplier`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `mydb`.`Supplier` (
78 `id` INT NOT NULL,
79 `SupplierName` VARCHAR(45) NOT NULL,
80 PRIMARY KEY (`id`),
81 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
82ENGINE = InnoDB;
83
84
85-- -----------------------------------------------------
86-- Table `mydb`.`Producer`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `mydb`.`Producer` (
89 `id` INT NOT NULL,
90 `ProducerName` VARCHAR(45) NOT NULL,
91 PRIMARY KEY (`id`),
92 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `mydb`.`Category`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `mydb`.`Category` (
100 `id` INT NOT NULL,
101 `CategoryName` VARCHAR(45) NOT NULL,
102 PRIMARY KEY (`id`),
103 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `mydb`.`Review`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `mydb`.`Review` (
111 `id` INT NOT NULL,
112 `ReviewText` VARCHAR(500) NOT NULL,
113 PRIMARY KEY (`id`),
114 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
115ENGINE = InnoDB;
116
117
118-- -----------------------------------------------------
119-- Table `mydb`.`Product`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
122 `id` INT NOT NULL,
123 `Brand_id` INT NOT NULL,
124 `Model_id` INT NOT NULL,
125 `Supplier_id` INT NOT NULL,
126 `Producer_id` INT NOT NULL,
127 `Category_id` INT NOT NULL,
128 `Review_id` INT NOT NULL,
129 `ProductName` VARCHAR(100) NOT NULL,
130 `TopRated` TINYINT NULL,
131 `Bestseller` TINYINT NULL,
132 PRIMARY KEY (`id`),
133 UNIQUE INDEX `idProduct_UNIQUE` (`id` ASC) VISIBLE,
134 INDEX `fk_Product_Brand_idx` (`Brand_id` ASC) VISIBLE,
135 INDEX `fk_Product_Model1_idx` (`Model_id` ASC) VISIBLE,
136 INDEX `fk_Product_Supplier1_idx` (`Supplier_id` ASC) VISIBLE,
137 INDEX `fk_Product_Producer1_idx` (`Producer_id` ASC) VISIBLE,
138 INDEX `fk_Product_Category1_idx` (`Category_id` ASC) VISIBLE,
139 INDEX `fk_Product_Review1_idx` (`Review_id` ASC) VISIBLE,
140 CONSTRAINT `fk_Product_Brand`
141 FOREIGN KEY (`Brand_id`)
142 REFERENCES `mydb`.`Brand` (`id`)
143 ON DELETE NO ACTION
144 ON UPDATE NO ACTION,
145 CONSTRAINT `fk_Product_Model1`
146 FOREIGN KEY (`Model_id`)
147 REFERENCES `mydb`.`Model` (`id`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION,
150 CONSTRAINT `fk_Product_Supplier1`
151 FOREIGN KEY (`Supplier_id`)
152 REFERENCES `mydb`.`Supplier` (`id`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION,
155 CONSTRAINT `fk_Product_Producer1`
156 FOREIGN KEY (`Producer_id`)
157 REFERENCES `mydb`.`Producer` (`id`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT `fk_Product_Category1`
161 FOREIGN KEY (`Category_id`)
162 REFERENCES `mydb`.`Category` (`id`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION,
165 CONSTRAINT `fk_Product_Review1`
166 FOREIGN KEY (`Review_id`)
167 REFERENCES `mydb`.`Review` (`id`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB;
171
172
173-- -----------------------------------------------------
174-- Table `mydb`.`OrderStatus`
175-- -----------------------------------------------------
176CREATE TABLE IF NOT EXISTS `mydb`.`OrderStatus` (
177 `id` INT NOT NULL,
178 `OrderStatus` VARCHAR(45) NOT NULL,
179 PRIMARY KEY (`id`),
180 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
181ENGINE = InnoDB;
182
183
184-- -----------------------------------------------------
185-- Table `mydb`.`Tax`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `mydb`.`Tax` (
188 `id` INT NOT NULL,
189 `TaxValue` INT NOT NULL,
190 `TaxName` VARCHAR(150) NULL,
191 PRIMARY KEY (`id`),
192 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
193ENGINE = InnoDB;
194
195
196-- -----------------------------------------------------
197-- Table `mydb`.`Sale`
198-- -----------------------------------------------------
199CREATE TABLE IF NOT EXISTS `mydb`.`Sale` (
200 `id` INT NOT NULL,
201 `SaleValue` INT NOT NULL,
202 `SaleName` VARCHAR(100) NULL,
203 PRIMARY KEY (`id`),
204 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
205ENGINE = InnoDB;
206
207
208-- -----------------------------------------------------
209-- Table `mydb`.`Purchase`
210-- -----------------------------------------------------
211CREATE TABLE IF NOT EXISTS `mydb`.`Purchase` (
212 `id` INT NOT NULL,
213 `Tax_id` INT NOT NULL,
214 `Sale_id` INT NOT NULL,
215 PRIMARY KEY (`id`, `Tax_id`, `Sale_id`),
216 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
217 INDEX `fk_Purchase_Tax1_idx` (`Tax_id` ASC) VISIBLE,
218 INDEX `fk_Purchase_Sale1_idx` (`Sale_id` ASC) VISIBLE,
219 CONSTRAINT `fk_Purchase_Tax1`
220 FOREIGN KEY (`Tax_id`)
221 REFERENCES `mydb`.`Tax` (`id`)
222 ON DELETE NO ACTION
223 ON UPDATE NO ACTION,
224 CONSTRAINT `fk_Purchase_Sale1`
225 FOREIGN KEY (`Sale_id`)
226 REFERENCES `mydb`.`Sale` (`id`)
227 ON DELETE NO ACTION
228 ON UPDATE NO ACTION)
229ENGINE = InnoDB;
230
231
232-- -----------------------------------------------------
233-- Table `mydb`.`Zip`
234-- -----------------------------------------------------
235CREATE TABLE IF NOT EXISTS `mydb`.`Zip` (
236 `id` INT NOT NULL,
237 `ZipCode` INT NOT NULL,
238 `Country` VARCHAR(45) NOT NULL,
239 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
240 PRIMARY KEY (`id`))
241ENGINE = InnoDB;
242
243
244-- -----------------------------------------------------
245-- Table `mydb`.`Address`
246-- -----------------------------------------------------
247CREATE TABLE IF NOT EXISTS `mydb`.`Address` (
248 `id` INT NOT NULL,
249 `Zip_id` INT NOT NULL,
250 `Street` VARCHAR(45) NOT NULL,
251 `House` VARCHAR(45) NOT NULL,
252 `Apartment` VARCHAR(45) NULL,
253 PRIMARY KEY (`id`, `Zip_id`),
254 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
255 INDEX `fk_Address_Zip1_idx` (`Zip_id` ASC) VISIBLE,
256 CONSTRAINT `fk_Address_Zip1`
257 FOREIGN KEY (`Zip_id`)
258 REFERENCES `mydb`.`Zip` (`id`)
259 ON DELETE NO ACTION
260 ON UPDATE NO ACTION)
261ENGINE = InnoDB;
262
263
264-- -----------------------------------------------------
265-- Table `mydb`.`Customer`
266-- -----------------------------------------------------
267CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
268 `id` INT NOT NULL,
269 `Address_id` INT NOT NULL,
270 `Address_Zip_id` INT NOT NULL,
271 `Name` VARCHAR(100) NOT NULL,
272 `Email` VARCHAR(100) NOT NULL,
273 `Birthdate` DATE NULL,
274 `Gender` VARCHAR(8) NULL,
275 `Phone` VARCHAR(20) NOT NULL,
276 PRIMARY KEY (`id`, `Address_id`, `Address_Zip_id`),
277 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
278 INDEX `fk_Customer_Address1_idx` (`Address_id` ASC, `Address_Zip_id` ASC) VISIBLE,
279 UNIQUE INDEX `Phone_UNIQUE` (`Phone` ASC) VISIBLE,
280 CONSTRAINT `fk_Customer_Address1`
281 FOREIGN KEY (`Address_id` , `Address_Zip_id`)
282 REFERENCES `mydb`.`Address` (`id` , `Zip_id`)
283 ON DELETE NO ACTION
284 ON UPDATE NO ACTION)
285ENGINE = InnoDB;
286
287
288-- -----------------------------------------------------
289-- Table `mydb`.`Order`
290-- -----------------------------------------------------
291CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
292 `id` INT NOT NULL,
293 `OrderStatus_id` INT NOT NULL,
294 `Purchase_id` INT NOT NULL,
295 `Customer_id` INT NOT NULL,
296 PRIMARY KEY (`id`, `OrderStatus_id`, `Purchase_id`, `Customer_id`),
297 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
298 INDEX `fk_Order_OrderStatus1_idx` (`OrderStatus_id` ASC) VISIBLE,
299 INDEX `fk_Order_Purchase1_idx` (`Purchase_id` ASC) VISIBLE,
300 INDEX `fk_Order_Customer1_idx` (`Customer_id` ASC) VISIBLE,
301 CONSTRAINT `fk_Order_OrderStatus1`
302 FOREIGN KEY (`OrderStatus_id`)
303 REFERENCES `mydb`.`OrderStatus` (`id`)
304 ON DELETE NO ACTION
305 ON UPDATE NO ACTION,
306 CONSTRAINT `fk_Order_Purchase1`
307 FOREIGN KEY (`Purchase_id`)
308 REFERENCES `mydb`.`Purchase` (`id`)
309 ON DELETE NO ACTION
310 ON UPDATE NO ACTION,
311 CONSTRAINT `fk_Order_Customer1`
312 FOREIGN KEY (`Customer_id`)
313 REFERENCES `mydb`.`Customer` (`id`)
314 ON DELETE NO ACTION
315 ON UPDATE NO ACTION)
316ENGINE = InnoDB;
317
318
319-- -----------------------------------------------------
320-- Table `mydb`.`Product_has_Order`
321-- -----------------------------------------------------
322CREATE TABLE IF NOT EXISTS `mydb`.`Product_has_Order` (
323 `Product_id` INT NOT NULL,
324 `Order_id` INT NOT NULL,
325 PRIMARY KEY (`Product_id`, `Order_id`),
326 INDEX `fk_Product_has_Order_Order1_idx` (`Order_id` ASC) VISIBLE,
327 INDEX `fk_Product_has_Order_Product1_idx` (`Product_id` ASC) VISIBLE,
328 CONSTRAINT `fk_Product_has_Order_Product1`
329 FOREIGN KEY (`Product_id`)
330 REFERENCES `mydb`.`Product` (`id`)
331 ON DELETE NO ACTION
332 ON UPDATE NO ACTION,
333 CONSTRAINT `fk_Product_has_Order_Order1`
334 FOREIGN KEY (`Order_id`)
335 REFERENCES `mydb`.`Order` (`id`)
336 ON DELETE NO ACTION
337 ON UPDATE NO ACTION)
338ENGINE = InnoDB;
339
340USE `new_schema` ;
341
342-- -----------------------------------------------------
343-- Table `new_schema`.`patient`
344-- -----------------------------------------------------
345CREATE TABLE IF NOT EXISTS `new_schema`.`patient` (
346 `id_patient` INT(11) NOT NULL,
347 `id_medical_history` INT(11) NOT NULL,
348 `id_district` INT(11) NOT NULL,
349 `id_medical_policy` INT(11) NOT NULL,
350 `name` VARCHAR(80) NOT NULL,
351 `address` VARCHAR(80) NOT NULL,
352 `phone` VARCHAR(20) NOT NULL,
353 PRIMARY KEY (`id_patient`))
354ENGINE = InnoDB
355DEFAULT CHARACTER SET = utf8mb4
356COLLATE = utf8mb4_0900_ai_ci;
357
358
359-- -----------------------------------------------------
360-- Table `new_schema`.`clinic`
361-- -----------------------------------------------------
362CREATE TABLE IF NOT EXISTS `new_schema`.`clinic` (
363 `id_clinic` INT(11) NOT NULL,
364 `id_hospital_room` INT(11) NULL DEFAULT NULL,
365 `id_specialist` INT(11) NULL DEFAULT NULL,
366 PRIMARY KEY (`id_clinic`))
367ENGINE = InnoDB
368DEFAULT CHARACTER SET = utf8mb4
369COLLATE = utf8mb4_0900_ai_ci;
370
371
372-- -----------------------------------------------------
373-- Table `new_schema`.`specialist`
374-- -----------------------------------------------------
375CREATE TABLE IF NOT EXISTS `new_schema`.`specialist` (
376 `id_specialist` INT(11) NOT NULL,
377 `name` VARCHAR(80) NOT NULL,
378 `specialty` VARCHAR(80) NOT NULL,
379 `address` VARCHAR(80) NOT NULL,
380 `phone` VARCHAR(20) NOT NULL,
381 `clinic_id_clinic` INT(11) NOT NULL,
382 PRIMARY KEY (`id_specialist`),
383 INDEX `specialist_clinic_fk` (`clinic_id_clinic` ASC) VISIBLE,
384 CONSTRAINT `specialist_clinic_fk`
385 FOREIGN KEY (`clinic_id_clinic`)
386 REFERENCES `new_schema`.`clinic` (`id_clinic`))
387ENGINE = InnoDB
388DEFAULT CHARACTER SET = utf8mb4
389COLLATE = utf8mb4_0900_ai_ci;
390
391
392-- -----------------------------------------------------
393-- Table `new_schema`.`visit`
394-- -----------------------------------------------------
395CREATE TABLE IF NOT EXISTS `new_schema`.`visit` (
396 `id_visit` INT(11) NOT NULL,
397 `id_patient` INT(11) NOT NULL,
398 `id_specialist` INT(11) NOT NULL,
399 `visit_type` VARCHAR(10) NOT NULL,
400 `visit_date` DATE NOT NULL,
401 `anamnesis` VARCHAR(300) NOT NULL,
402 `diagnosis` VARCHAR(100) NOT NULL,
403 `id_treatment` INT(11) NOT NULL,
404 `id_additional_examination` INT(11) NULL DEFAULT NULL,
405 `patient_id_patient` INT(11) NOT NULL,
406 `specialist_id_specialist` INT(11) NOT NULL,
407 PRIMARY KEY (`id_visit`),
408 INDEX `visit_patient_fk` (`patient_id_patient` ASC) VISIBLE,
409 INDEX `visit_specialist_fk` (`specialist_id_specialist` ASC) VISIBLE,
410 CONSTRAINT `visit_patient_fk`
411 FOREIGN KEY (`patient_id_patient`)
412 REFERENCES `new_schema`.`patient` (`id_patient`),
413 CONSTRAINT `visit_specialist_fk`
414 FOREIGN KEY (`specialist_id_specialist`)
415 REFERENCES `new_schema`.`specialist` (`id_specialist`))
416ENGINE = InnoDB
417DEFAULT CHARACTER SET = utf8mb4
418COLLATE = utf8mb4_0900_ai_ci;
419
420
421-- -----------------------------------------------------
422-- Table `new_schema`.`additional_examination`
423-- -----------------------------------------------------
424CREATE TABLE IF NOT EXISTS `new_schema`.`additional_examination` (
425 `id_additional_examination` INT(11) NOT NULL,
426 `examination_name` VARCHAR(80) NOT NULL,
427 `result` VARCHAR(300) NOT NULL,
428 `visit_id_visit` INT(11) NOT NULL,
429 PRIMARY KEY (`id_additional_examination`),
430 UNIQUE INDEX `additional_examination__idx` (`visit_id_visit` ASC) VISIBLE,
431 CONSTRAINT `additional_examination_visit_fk`
432 FOREIGN KEY (`visit_id_visit`)
433 REFERENCES `new_schema`.`visit` (`id_visit`))
434ENGINE = InnoDB
435DEFAULT CHARACTER SET = utf8mb4
436COLLATE = utf8mb4_0900_ai_ci;
437
438
439-- -----------------------------------------------------
440-- Table `new_schema`.`treatment`
441-- -----------------------------------------------------
442CREATE TABLE IF NOT EXISTS `new_schema`.`treatment` (
443 `id_treatment` INT(11) NOT NULL,
444 `visit_id_visit` INT(11) NOT NULL,
445 `id_hospital_room` INT(11) NULL DEFAULT NULL,
446 `id_procedure` INT(11) NULL DEFAULT NULL,
447 `id_prescription` INT(11) NULL DEFAULT NULL,
448 PRIMARY KEY (`id_treatment`),
449 UNIQUE INDEX `treatment__idx` (`visit_id_visit` ASC) VISIBLE,
450 CONSTRAINT `treatment_visit_fk`
451 FOREIGN KEY (`visit_id_visit`)
452 REFERENCES `new_schema`.`visit` (`id_visit`))
453ENGINE = InnoDB
454DEFAULT CHARACTER SET = utf8mb4
455COLLATE = utf8mb4_0900_ai_ci;
456
457
458-- -----------------------------------------------------
459-- Table `new_schema`.`hospital_room`
460-- -----------------------------------------------------
461CREATE TABLE IF NOT EXISTS `new_schema`.`hospital_room` (
462 `id_hospital_room` INT(11) NOT NULL,
463 `capacity` INT(11) NOT NULL,
464 `treatment_id_treatment` INT(11) NOT NULL,
465 `clinic_id_clinic` INT(11) NOT NULL,
466 PRIMARY KEY (`id_hospital_room`),
467 UNIQUE INDEX `hospital_room__idx` (`treatment_id_treatment` ASC) VISIBLE,
468 INDEX `hospital_room_clinic_fk` (`clinic_id_clinic` ASC) VISIBLE,
469 CONSTRAINT `hospital_room_clinic_fk`
470 FOREIGN KEY (`clinic_id_clinic`)
471 REFERENCES `new_schema`.`clinic` (`id_clinic`),
472 CONSTRAINT `hospital_room_treatment_fk`
473 FOREIGN KEY (`treatment_id_treatment`)
474 REFERENCES `new_schema`.`treatment` (`id_treatment`))
475ENGINE = InnoDB
476DEFAULT CHARACTER SET = utf8mb4
477COLLATE = utf8mb4_0900_ai_ci;
478
479
480-- -----------------------------------------------------
481-- Table `new_schema`.`medical_procedure`
482-- -----------------------------------------------------
483CREATE TABLE IF NOT EXISTS `new_schema`.`medical_procedure` (
484 `id_medical_procedure` INT(11) NOT NULL,
485 `procedure_name` VARCHAR(80) NOT NULL,
486 `treatment_id_treatment` INT(11) NOT NULL,
487 PRIMARY KEY (`id_medical_procedure`),
488 INDEX `procedure_treatment_fk` (`treatment_id_treatment` ASC) VISIBLE,
489 CONSTRAINT `procedure_treatment_fk`
490 FOREIGN KEY (`treatment_id_treatment`)
491 REFERENCES `new_schema`.`treatment` (`id_treatment`))
492ENGINE = InnoDB
493DEFAULT CHARACTER SET = utf8mb4
494COLLATE = utf8mb4_0900_ai_ci;
495
496
497-- -----------------------------------------------------
498-- Table `new_schema`.`prescription`
499-- -----------------------------------------------------
500CREATE TABLE IF NOT EXISTS `new_schema`.`prescription` (
501 `id_prescription` INT(11) NOT NULL,
502 `treatment_id_treatment` INT(11) NOT NULL,
503 `drug_name` VARCHAR(100) NOT NULL,
504 PRIMARY KEY (`id_prescription`),
505 INDEX `prescription_treatment_fk` (`treatment_id_treatment` ASC) VISIBLE,
506 CONSTRAINT `prescription_treatment_fk`
507 FOREIGN KEY (`treatment_id_treatment`)
508 REFERENCES `new_schema`.`treatment` (`id_treatment`))
509ENGINE = InnoDB
510DEFAULT CHARACTER SET = utf8mb4
511COLLATE = utf8mb4_0900_ai_ci;
512
513USE `qwerty` ;
514
515-- -----------------------------------------------------
516-- Table `qwerty`.`asdf`
517-- -----------------------------------------------------
518CREATE TABLE IF NOT EXISTS `qwerty`.`asdf` (
519 `idasdf` INT(11) NOT NULL)
520ENGINE = InnoDB
521DEFAULT CHARACTER SET = utf8mb4
522COLLATE = utf8mb4_0900_ai_ci;
523
524USE `sakila` ;
525
526-- -----------------------------------------------------
527-- Table `sakila`.`actor`
528-- -----------------------------------------------------
529CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
530 `actor_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
531 `first_name` VARCHAR(45) NOT NULL,
532 `last_name` VARCHAR(45) NOT NULL,
533 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
534 PRIMARY KEY (`actor_id`),
535 INDEX `idx_actor_last_name` (`last_name` ASC) VISIBLE)
536ENGINE = InnoDB
537AUTO_INCREMENT = 201
538DEFAULT CHARACTER SET = utf8;
539
540
541-- -----------------------------------------------------
542-- Table `sakila`.`country`
543-- -----------------------------------------------------
544CREATE TABLE IF NOT EXISTS `sakila`.`country` (
545 `country_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
546 `country` VARCHAR(50) NOT NULL,
547 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
548 PRIMARY KEY (`country_id`))
549ENGINE = InnoDB
550AUTO_INCREMENT = 110
551DEFAULT CHARACTER SET = utf8;
552
553
554-- -----------------------------------------------------
555-- Table `sakila`.`city`
556-- -----------------------------------------------------
557CREATE TABLE IF NOT EXISTS `sakila`.`city` (
558 `city_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
559 `city` VARCHAR(50) NOT NULL,
560 `country_id` SMALLINT(5) UNSIGNED NOT NULL,
561 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
562 PRIMARY KEY (`city_id`),
563 INDEX `idx_fk_country_id` (`country_id` ASC) VISIBLE,
564 CONSTRAINT `fk_city_country`
565 FOREIGN KEY (`country_id`)
566 REFERENCES `sakila`.`country` (`country_id`)
567 ON DELETE RESTRICT
568 ON UPDATE CASCADE)
569ENGINE = InnoDB
570AUTO_INCREMENT = 601
571DEFAULT CHARACTER SET = utf8;
572
573
574-- -----------------------------------------------------
575-- Table `sakila`.`address`
576-- -----------------------------------------------------
577CREATE TABLE IF NOT EXISTS `sakila`.`address` (
578 `address_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
579 `address` VARCHAR(50) NOT NULL,
580 `address2` VARCHAR(50) NULL DEFAULT NULL,
581 `district` VARCHAR(20) NOT NULL,
582 `city_id` SMALLINT(5) UNSIGNED NOT NULL,
583 `postal_code` VARCHAR(10) NULL DEFAULT NULL,
584 `phone` VARCHAR(20) NOT NULL,
585 `location` GEOMETRY NOT NULL,
586 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
587 PRIMARY KEY (`address_id`),
588 INDEX `idx_fk_city_id` (`city_id` ASC) VISIBLE,
589 SPATIAL INDEX `idx_location` (`location`) VISIBLE,
590 CONSTRAINT `fk_address_city`
591 FOREIGN KEY (`city_id`)
592 REFERENCES `sakila`.`city` (`city_id`)
593 ON DELETE RESTRICT
594 ON UPDATE CASCADE)
595ENGINE = InnoDB
596AUTO_INCREMENT = 606
597DEFAULT CHARACTER SET = utf8;
598
599
600-- -----------------------------------------------------
601-- Table `sakila`.`category`
602-- -----------------------------------------------------
603CREATE TABLE IF NOT EXISTS `sakila`.`category` (
604 `category_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
605 `name` VARCHAR(25) NOT NULL,
606 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
607 PRIMARY KEY (`category_id`))
608ENGINE = InnoDB
609AUTO_INCREMENT = 17
610DEFAULT CHARACTER SET = utf8;
611
612
613-- -----------------------------------------------------
614-- Table `sakila`.`staff`
615-- -----------------------------------------------------
616CREATE TABLE IF NOT EXISTS `sakila`.`staff` (
617 `staff_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
618 `first_name` VARCHAR(45) NOT NULL,
619 `last_name` VARCHAR(45) NOT NULL,
620 `address_id` SMALLINT(5) UNSIGNED NOT NULL,
621 `picture` BLOB NULL DEFAULT NULL,
622 `email` VARCHAR(50) NULL DEFAULT NULL,
623 `store_id` TINYINT(3) UNSIGNED NOT NULL,
624 `active` TINYINT(1) NOT NULL DEFAULT '1',
625 `username` VARCHAR(16) NOT NULL,
626 `password` VARCHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
627 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
628 PRIMARY KEY (`staff_id`),
629 INDEX `idx_fk_store_id` (`store_id` ASC) VISIBLE,
630 INDEX `idx_fk_address_id` (`address_id` ASC) VISIBLE,
631 CONSTRAINT `fk_staff_address`
632 FOREIGN KEY (`address_id`)
633 REFERENCES `sakila`.`address` (`address_id`)
634 ON DELETE RESTRICT
635 ON UPDATE CASCADE,
636 CONSTRAINT `fk_staff_store`
637 FOREIGN KEY (`store_id`)
638 REFERENCES `sakila`.`store` (`store_id`)
639 ON DELETE RESTRICT
640 ON UPDATE CASCADE)
641ENGINE = InnoDB
642AUTO_INCREMENT = 3
643DEFAULT CHARACTER SET = utf8;
644
645
646-- -----------------------------------------------------
647-- Table `sakila`.`store`
648-- -----------------------------------------------------
649CREATE TABLE IF NOT EXISTS `sakila`.`store` (
650 `store_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
651 `manager_staff_id` TINYINT(3) UNSIGNED NOT NULL,
652 `address_id` SMALLINT(5) UNSIGNED NOT NULL,
653 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
654 PRIMARY KEY (`store_id`),
655 UNIQUE INDEX `idx_unique_manager` (`manager_staff_id` ASC) VISIBLE,
656 INDEX `idx_fk_address_id` (`address_id` ASC) VISIBLE,
657 CONSTRAINT `fk_store_address`
658 FOREIGN KEY (`address_id`)
659 REFERENCES `sakila`.`address` (`address_id`)
660 ON DELETE RESTRICT
661 ON UPDATE CASCADE,
662 CONSTRAINT `fk_store_staff`
663 FOREIGN KEY (`manager_staff_id`)
664 REFERENCES `sakila`.`staff` (`staff_id`)
665 ON DELETE RESTRICT
666 ON UPDATE CASCADE)
667ENGINE = InnoDB
668AUTO_INCREMENT = 3
669DEFAULT CHARACTER SET = utf8;
670
671
672-- -----------------------------------------------------
673-- Table `sakila`.`customer`
674-- -----------------------------------------------------
675CREATE TABLE IF NOT EXISTS `sakila`.`customer` (
676 `customer_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
677 `store_id` TINYINT(3) UNSIGNED NOT NULL,
678 `first_name` VARCHAR(45) NOT NULL,
679 `last_name` VARCHAR(45) NOT NULL,
680 `email` VARCHAR(50) NULL DEFAULT NULL,
681 `address_id` SMALLINT(5) UNSIGNED NOT NULL,
682 `active` TINYINT(1) NOT NULL DEFAULT '1',
683 `create_date` DATETIME NOT NULL,
684 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
685 PRIMARY KEY (`customer_id`),
686 INDEX `idx_fk_store_id` (`store_id` ASC) VISIBLE,
687 INDEX `idx_fk_address_id` (`address_id` ASC) VISIBLE,
688 INDEX `idx_last_name` (`last_name` ASC) VISIBLE,
689 CONSTRAINT `fk_customer_address`
690 FOREIGN KEY (`address_id`)
691 REFERENCES `sakila`.`address` (`address_id`)
692 ON DELETE RESTRICT
693 ON UPDATE CASCADE,
694 CONSTRAINT `fk_customer_store`
695 FOREIGN KEY (`store_id`)
696 REFERENCES `sakila`.`store` (`store_id`)
697 ON DELETE RESTRICT
698 ON UPDATE CASCADE)
699ENGINE = InnoDB
700AUTO_INCREMENT = 600
701DEFAULT CHARACTER SET = utf8;
702
703
704-- -----------------------------------------------------
705-- Table `sakila`.`language`
706-- -----------------------------------------------------
707CREATE TABLE IF NOT EXISTS `sakila`.`language` (
708 `language_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
709 `name` CHAR(20) NOT NULL,
710 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
711 PRIMARY KEY (`language_id`))
712ENGINE = InnoDB
713AUTO_INCREMENT = 7
714DEFAULT CHARACTER SET = utf8;
715
716
717-- -----------------------------------------------------
718-- Table `sakila`.`film`
719-- -----------------------------------------------------
720CREATE TABLE IF NOT EXISTS `sakila`.`film` (
721 `film_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
722 `title` VARCHAR(255) NOT NULL,
723 `description` TEXT NULL DEFAULT NULL,
724 `release_year` YEAR(4) NULL DEFAULT NULL,
725 `language_id` TINYINT(3) UNSIGNED NOT NULL,
726 `original_language_id` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
727 `rental_duration` TINYINT(3) UNSIGNED NOT NULL DEFAULT '3',
728 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT '4.99',
729 `length` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
730 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT '19.99',
731 `rating` ENUM('G', 'PG', 'PG-13', 'R', 'NC-17') NULL DEFAULT 'G',
732 `special_features` SET('Trailers', 'Commentaries', 'Deleted Scenes', 'Behind the Scenes') NULL DEFAULT NULL,
733 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
734 PRIMARY KEY (`film_id`),
735 INDEX `idx_title` (`title` ASC) VISIBLE,
736 INDEX `idx_fk_language_id` (`language_id` ASC) VISIBLE,
737 INDEX `idx_fk_original_language_id` (`original_language_id` ASC) VISIBLE,
738 CONSTRAINT `fk_film_language`
739 FOREIGN KEY (`language_id`)
740 REFERENCES `sakila`.`language` (`language_id`)
741 ON DELETE RESTRICT
742 ON UPDATE CASCADE,
743 CONSTRAINT `fk_film_language_original`
744 FOREIGN KEY (`original_language_id`)
745 REFERENCES `sakila`.`language` (`language_id`)
746 ON DELETE RESTRICT
747 ON UPDATE CASCADE)
748ENGINE = InnoDB
749AUTO_INCREMENT = 1001
750DEFAULT CHARACTER SET = utf8;
751
752
753-- -----------------------------------------------------
754-- Table `sakila`.`film_actor`
755-- -----------------------------------------------------
756CREATE TABLE IF NOT EXISTS `sakila`.`film_actor` (
757 `actor_id` SMALLINT(5) UNSIGNED NOT NULL,
758 `film_id` SMALLINT(5) UNSIGNED NOT NULL,
759 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
760 PRIMARY KEY (`actor_id`, `film_id`),
761 INDEX `idx_fk_film_id` (`film_id` ASC) VISIBLE,
762 CONSTRAINT `fk_film_actor_actor`
763 FOREIGN KEY (`actor_id`)
764 REFERENCES `sakila`.`actor` (`actor_id`)
765 ON DELETE RESTRICT
766 ON UPDATE CASCADE,
767 CONSTRAINT `fk_film_actor_film`
768 FOREIGN KEY (`film_id`)
769 REFERENCES `sakila`.`film` (`film_id`)
770 ON DELETE RESTRICT
771 ON UPDATE CASCADE)
772ENGINE = InnoDB
773DEFAULT CHARACTER SET = utf8;
774
775
776-- -----------------------------------------------------
777-- Table `sakila`.`film_category`
778-- -----------------------------------------------------
779CREATE TABLE IF NOT EXISTS `sakila`.`film_category` (
780 `film_id` SMALLINT(5) UNSIGNED NOT NULL,
781 `category_id` TINYINT(3) UNSIGNED NOT NULL,
782 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
783 PRIMARY KEY (`film_id`, `category_id`),
784 INDEX `fk_film_category_category` (`category_id` ASC) VISIBLE,
785 CONSTRAINT `fk_film_category_category`
786 FOREIGN KEY (`category_id`)
787 REFERENCES `sakila`.`category` (`category_id`)
788 ON DELETE RESTRICT
789 ON UPDATE CASCADE,
790 CONSTRAINT `fk_film_category_film`
791 FOREIGN KEY (`film_id`)
792 REFERENCES `sakila`.`film` (`film_id`)
793 ON DELETE RESTRICT
794 ON UPDATE CASCADE)
795ENGINE = InnoDB
796DEFAULT CHARACTER SET = utf8;
797
798
799-- -----------------------------------------------------
800-- Table `sakila`.`film_text`
801-- -----------------------------------------------------
802CREATE TABLE IF NOT EXISTS `sakila`.`film_text` (
803 `film_id` SMALLINT(6) NOT NULL,
804 `title` VARCHAR(255) NOT NULL,
805 `description` TEXT NULL DEFAULT NULL,
806 PRIMARY KEY (`film_id`),
807 FULLTEXT INDEX `idx_title_description` (`title`, `description`) VISIBLE)
808ENGINE = InnoDB
809DEFAULT CHARACTER SET = utf8;
810
811
812-- -----------------------------------------------------
813-- Table `sakila`.`inventory`
814-- -----------------------------------------------------
815CREATE TABLE IF NOT EXISTS `sakila`.`inventory` (
816 `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
817 `film_id` SMALLINT(5) UNSIGNED NOT NULL,
818 `store_id` TINYINT(3) UNSIGNED NOT NULL,
819 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
820 PRIMARY KEY (`inventory_id`),
821 INDEX `idx_fk_film_id` (`film_id` ASC) VISIBLE,
822 INDEX `idx_store_id_film_id` (`store_id` ASC, `film_id` ASC) VISIBLE,
823 CONSTRAINT `fk_inventory_film`
824 FOREIGN KEY (`film_id`)
825 REFERENCES `sakila`.`film` (`film_id`)
826 ON DELETE RESTRICT
827 ON UPDATE CASCADE,
828 CONSTRAINT `fk_inventory_store`
829 FOREIGN KEY (`store_id`)
830 REFERENCES `sakila`.`store` (`store_id`)
831 ON DELETE RESTRICT
832 ON UPDATE CASCADE)
833ENGINE = InnoDB
834AUTO_INCREMENT = 4582
835DEFAULT CHARACTER SET = utf8;
836
837
838-- -----------------------------------------------------
839-- Table `sakila`.`rental`
840-- -----------------------------------------------------
841CREATE TABLE IF NOT EXISTS `sakila`.`rental` (
842 `rental_id` INT(11) NOT NULL AUTO_INCREMENT,
843 `rental_date` DATETIME NOT NULL,
844 `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL,
845 `customer_id` SMALLINT(5) UNSIGNED NOT NULL,
846 `return_date` DATETIME NULL DEFAULT NULL,
847 `staff_id` TINYINT(3) UNSIGNED NOT NULL,
848 `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
849 PRIMARY KEY (`rental_id`),
850 UNIQUE INDEX `rental_date` (`rental_date` ASC, `inventory_id` ASC, `customer_id` ASC) VISIBLE,
851 INDEX `idx_fk_inventory_id` (`inventory_id` ASC) VISIBLE,
852 INDEX `idx_fk_customer_id` (`customer_id` ASC) VISIBLE,
853 INDEX `idx_fk_staff_id` (`staff_id` ASC) VISIBLE,
854 CONSTRAINT `fk_rental_customer`
855 FOREIGN KEY (`customer_id`)
856 REFERENCES `sakila`.`customer` (`customer_id`)
857 ON DELETE RESTRICT
858 ON UPDATE CASCADE,
859 CONSTRAINT `fk_rental_inventory`
860 FOREIGN KEY (`inventory_id`)
861 REFERENCES `sakila`.`inventory` (`inventory_id`)
862 ON DELETE RESTRICT
863 ON UPDATE CASCADE,
864 CONSTRAINT `fk_rental_staff`
865 FOREIGN KEY (`staff_id`)
866 REFERENCES `sakila`.`staff` (`staff_id`)
867 ON DELETE RESTRICT
868 ON UPDATE CASCADE)
869ENGINE = InnoDB
870AUTO_INCREMENT = 16050
871DEFAULT CHARACTER SET = utf8;
872
873
874-- -----------------------------------------------------
875-- Table `sakila`.`payment`
876-- -----------------------------------------------------
877CREATE TABLE IF NOT EXISTS `sakila`.`payment` (
878 `payment_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
879 `customer_id` SMALLINT(5) UNSIGNED NOT NULL,
880 `staff_id` TINYINT(3) UNSIGNED NOT NULL,
881 `rental_id` INT(11) NULL DEFAULT NULL,
882 `amount` DECIMAL(5,2) NOT NULL,
883 `payment_date` DATETIME NOT NULL,
884 `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
885 PRIMARY KEY (`payment_id`),
886 INDEX `idx_fk_staff_id` (`staff_id` ASC) VISIBLE,
887 INDEX `idx_fk_customer_id` (`customer_id` ASC) VISIBLE,
888 INDEX `fk_payment_rental` (`rental_id` ASC) VISIBLE,
889 CONSTRAINT `fk_payment_customer`
890 FOREIGN KEY (`customer_id`)
891 REFERENCES `sakila`.`customer` (`customer_id`)
892 ON DELETE RESTRICT
893 ON UPDATE CASCADE,
894 CONSTRAINT `fk_payment_rental`
895 FOREIGN KEY (`rental_id`)
896 REFERENCES `sakila`.`rental` (`rental_id`)
897 ON DELETE SET NULL
898 ON UPDATE CASCADE,
899 CONSTRAINT `fk_payment_staff`
900 FOREIGN KEY (`staff_id`)
901 REFERENCES `sakila`.`staff` (`staff_id`)
902 ON DELETE RESTRICT
903 ON UPDATE CASCADE)
904ENGINE = InnoDB
905AUTO_INCREMENT = 16050
906DEFAULT CHARACTER SET = utf8;
907
908USE `world` ;
909
910-- -----------------------------------------------------
911-- Table `world`.`country`
912-- -----------------------------------------------------
913CREATE TABLE IF NOT EXISTS `world`.`country` (
914 `Code` CHAR(3) NOT NULL DEFAULT '',
915 `Name` CHAR(52) NOT NULL DEFAULT '',
916 `Continent` ENUM('Asia', 'Europe', 'North America', 'Africa', 'Oceania', 'Antarctica', 'South America') NOT NULL DEFAULT 'Asia',
917 `Region` CHAR(26) NOT NULL DEFAULT '',
918 `SurfaceArea` FLOAT(10,2) NOT NULL DEFAULT '0.00',
919 `IndepYear` SMALLINT(6) NULL DEFAULT NULL,
920 `Population` INT(11) NOT NULL DEFAULT '0',
921 `LifeExpectancy` FLOAT(3,1) NULL DEFAULT NULL,
922 `GNP` FLOAT(10,2) NULL DEFAULT NULL,
923 `GNPOld` FLOAT(10,2) NULL DEFAULT NULL,
924 `LocalName` CHAR(45) NOT NULL DEFAULT '',
925 `GovernmentForm` CHAR(45) NOT NULL DEFAULT '',
926 `HeadOfState` CHAR(60) NULL DEFAULT NULL,
927 `Capital` INT(11) NULL DEFAULT NULL,
928 `Code2` CHAR(2) NOT NULL DEFAULT '',
929 PRIMARY KEY (`Code`))
930ENGINE = InnoDB
931DEFAULT CHARACTER SET = latin1;
932
933
934-- -----------------------------------------------------
935-- Table `world`.`city`
936-- -----------------------------------------------------
937CREATE TABLE IF NOT EXISTS `world`.`city` (
938 `ID` INT(11) NOT NULL AUTO_INCREMENT,
939 `Name` CHAR(35) NOT NULL DEFAULT '',
940 `CountryCode` CHAR(3) NOT NULL DEFAULT '',
941 `District` CHAR(20) NOT NULL DEFAULT '',
942 `Population` INT(11) NOT NULL DEFAULT '0',
943 PRIMARY KEY (`ID`),
944 INDEX `CountryCode` (`CountryCode` ASC) VISIBLE,
945 CONSTRAINT `city_ibfk_1`
946 FOREIGN KEY (`CountryCode`)
947 REFERENCES `world`.`country` (`Code`))
948ENGINE = InnoDB
949AUTO_INCREMENT = 4080
950DEFAULT CHARACTER SET = latin1;
951
952
953-- -----------------------------------------------------
954-- Table `world`.`countrylanguage`
955-- -----------------------------------------------------
956CREATE TABLE IF NOT EXISTS `world`.`countrylanguage` (
957 `CountryCode` CHAR(3) NOT NULL DEFAULT '',
958 `Language` CHAR(30) NOT NULL DEFAULT '',
959 `IsOfficial` ENUM('T', 'F') NOT NULL DEFAULT 'F',
960 `Percentage` FLOAT(4,1) NOT NULL DEFAULT '0.0',
961 PRIMARY KEY (`CountryCode`, `Language`),
962 INDEX `CountryCode` (`CountryCode` ASC) VISIBLE,
963 CONSTRAINT `countryLanguage_ibfk_1`
964 FOREIGN KEY (`CountryCode`)
965 REFERENCES `world`.`country` (`Code`))
966ENGINE = InnoDB
967DEFAULT CHARACTER SET = latin1;
968
969USE `sakila` ;
970
971-- -----------------------------------------------------
972-- Placeholder table for view `sakila`.`actor_info`
973-- -----------------------------------------------------
974CREATE TABLE IF NOT EXISTS `sakila`.`actor_info` (`actor_id` INT, `first_name` INT, `last_name` INT, `film_info` INT);
975
976-- -----------------------------------------------------
977-- Placeholder table for view `sakila`.`customer_list`
978-- -----------------------------------------------------
979CREATE TABLE IF NOT EXISTS `sakila`.`customer_list` (`id` INT);
980
981-- -----------------------------------------------------
982-- Placeholder table for view `sakila`.`film_list`
983-- -----------------------------------------------------
984CREATE TABLE IF NOT EXISTS `sakila`.`film_list` (`FID` INT, `title` INT, `description` INT, `category` INT, `price` INT, `length` INT, `rating` INT, `actors` INT);
985
986-- -----------------------------------------------------
987-- Placeholder table for view `sakila`.`nicer_but_slower_film_list`
988-- -----------------------------------------------------
989CREATE 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);
990
991-- -----------------------------------------------------
992-- Placeholder table for view `sakila`.`sales_by_film_category`
993-- -----------------------------------------------------
994CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_film_category` (`category` INT, `total_sales` INT);
995
996-- -----------------------------------------------------
997-- Placeholder table for view `sakila`.`sales_by_store`
998-- -----------------------------------------------------
999CREATE TABLE IF NOT EXISTS `sakila`.`sales_by_store` (`store` INT, `manager` INT, `total_sales` INT);
1000
1001-- -----------------------------------------------------
1002-- Placeholder table for view `sakila`.`staff_list`
1003-- -----------------------------------------------------
1004CREATE TABLE IF NOT EXISTS `sakila`.`staff_list` (`ID` INT, `name` INT, `address` INT, `zip code` INT, `phone` INT, `city` INT, `country` INT, `SID` INT);
1005
1006-- -----------------------------------------------------
1007-- procedure film_in_stock
1008-- -----------------------------------------------------
1009
1010DELIMITER $$
1011USE `sakila`$$
1012CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
1013 READS SQL DATA
1014BEGIN
1015 SELECT inventory_id
1016 FROM inventory
1017 WHERE film_id = p_film_id
1018 AND store_id = p_store_id
1019 AND inventory_in_stock(inventory_id);
1020
1021 SELECT FOUND_ROWS() INTO p_film_count;
1022END$$
1023
1024DELIMITER ;
1025
1026-- -----------------------------------------------------
1027-- procedure film_not_in_stock
1028-- -----------------------------------------------------
1029
1030DELIMITER $$
1031USE `sakila`$$
1032CREATE DEFINER=`root`@`localhost` PROCEDURE `film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
1033 READS SQL DATA
1034BEGIN
1035 SELECT inventory_id
1036 FROM inventory
1037 WHERE film_id = p_film_id
1038 AND store_id = p_store_id
1039 AND NOT inventory_in_stock(inventory_id);
1040
1041 SELECT FOUND_ROWS() INTO p_film_count;
1042END$$
1043
1044DELIMITER ;
1045
1046-- -----------------------------------------------------
1047-- function get_customer_balance
1048-- -----------------------------------------------------
1049
1050DELIMITER $$
1051USE `sakila`$$
1052CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)
1053 READS SQL DATA
1054 DETERMINISTIC
1055BEGIN
1056
1057 #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
1058 #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
1059 # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
1060 # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
1061 # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
1062 # 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
1063
1064 DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
1065 DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
1066 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
1067
1068 SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
1069 FROM film, inventory, rental
1070 WHERE film.film_id = inventory.film_id
1071 AND inventory.inventory_id = rental.inventory_id
1072 AND rental.rental_date <= p_effective_date
1073 AND rental.customer_id = p_customer_id;
1074
1075 SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
1076 ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
1077 FROM rental, inventory, film
1078 WHERE film.film_id = inventory.film_id
1079 AND inventory.inventory_id = rental.inventory_id
1080 AND rental.rental_date <= p_effective_date
1081 AND rental.customer_id = p_customer_id;
1082
1083
1084 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
1085 FROM payment
1086
1087 WHERE payment.payment_date <= p_effective_date
1088 AND payment.customer_id = p_customer_id;
1089
1090 RETURN v_rentfees + v_overfees - v_payments;
1091END$$
1092
1093DELIMITER ;
1094
1095-- -----------------------------------------------------
1096-- function inventory_held_by_customer
1097-- -----------------------------------------------------
1098
1099DELIMITER $$
1100USE `sakila`$$
1101CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_held_by_customer`(p_inventory_id INT) RETURNS int(11)
1102 READS SQL DATA
1103BEGIN
1104 DECLARE v_customer_id INT;
1105 DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
1106
1107 SELECT customer_id INTO v_customer_id
1108 FROM rental
1109 WHERE return_date IS NULL
1110 AND inventory_id = p_inventory_id;
1111
1112 RETURN v_customer_id;
1113END$$
1114
1115DELIMITER ;
1116
1117-- -----------------------------------------------------
1118-- function inventory_in_stock
1119-- -----------------------------------------------------
1120
1121DELIMITER $$
1122USE `sakila`$$
1123CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)
1124 READS SQL DATA
1125BEGIN
1126 DECLARE v_rentals INT;
1127 DECLARE v_out INT;
1128
1129 #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
1130 #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
1131
1132 SELECT COUNT(*) INTO v_rentals
1133 FROM rental
1134 WHERE inventory_id = p_inventory_id;
1135
1136 IF v_rentals = 0 THEN
1137 RETURN TRUE;
1138 END IF;
1139
1140 SELECT COUNT(rental_id) INTO v_out
1141 FROM inventory LEFT JOIN rental USING(inventory_id)
1142 WHERE inventory.inventory_id = p_inventory_id
1143 AND rental.return_date IS NULL;
1144
1145 IF v_out > 0 THEN
1146 RETURN FALSE;
1147 ELSE
1148 RETURN TRUE;
1149 END IF;
1150END$$
1151
1152DELIMITER ;
1153
1154-- -----------------------------------------------------
1155-- procedure rewards_report
1156-- -----------------------------------------------------
1157
1158DELIMITER $$
1159USE `sakila`$$
1160CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`(
1161 IN min_monthly_purchases TINYINT UNSIGNED
1162 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
1163 , OUT count_rewardees INT
1164)
1165 READS SQL DATA
1166 COMMENT 'Provides a customizable report on best customers'
1167proc: BEGIN
1168
1169 DECLARE last_month_start DATE;
1170 DECLARE last_month_end DATE;
1171
1172 /* Some sanity checks... */
1173 IF min_monthly_purchases = 0 THEN
1174 SELECT 'Minimum monthly purchases parameter must be > 0';
1175 LEAVE proc;
1176 END IF;
1177 IF min_dollar_amount_purchased = 0.00 THEN
1178 SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
1179 LEAVE proc;
1180 END IF;
1181
1182 /* Determine start and end time periods */
1183 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
1184 SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
1185 SET last_month_end = LAST_DAY(last_month_start);
1186
1187 /*
1188 Create a temporary storage area for
1189 Customer IDs.
1190 */
1191 CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
1192
1193 /*
1194 Find all customers meeting the
1195 monthly purchase requirements
1196 */
1197 INSERT INTO tmpCustomer (customer_id)
1198 SELECT p.customer_id
1199 FROM payment AS p
1200 WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
1201 GROUP BY customer_id
1202 HAVING SUM(p.amount) > min_dollar_amount_purchased
1203 AND COUNT(customer_id) > min_monthly_purchases;
1204
1205 /* Populate OUT parameter with count of found customers */
1206 SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
1207
1208 /*
1209 Output ALL customer information of matching rewardees.
1210 Customize output as needed.
1211 */
1212 SELECT c.*
1213 FROM tmpCustomer AS t
1214 INNER JOIN customer AS c ON t.customer_id = c.customer_id;
1215
1216 /* Clean up */
1217 DROP TABLE tmpCustomer;
1218END$$
1219
1220DELIMITER ;
1221
1222-- -----------------------------------------------------
1223-- View `sakila`.`actor_info`
1224-- -----------------------------------------------------
1225DROP TABLE IF EXISTS `sakila`.`actor_info`;
1226USE `sakila`;
1227CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `sakila`.`actor_info` AS select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`sakila`.`film` `f` join `sakila`.`film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `sakila`.`film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `sakila`.`film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`;
1228
1229-- -----------------------------------------------------
1230-- View `sakila`.`customer_list`
1231-- -----------------------------------------------------
1232DROP TABLE IF EXISTS `sakila`.`customer_list`;
1233USE `sakila`;
1234CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`customer_list` AS select `cu`.`customer_id` AS `ID`,concat(`cu`.`first_name`,_utf8mb3' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,if(`cu`.`active`,_utf8mb3'active',_utf8mb3'') AS `notes`,`cu`.`store_id` AS `SID` from (((`sakila`.`customer` `cu` join `sakila`.`address` `a` on((`cu`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)));
1235
1236-- -----------------------------------------------------
1237-- View `sakila`.`film_list`
1238-- -----------------------------------------------------
1239DROP TABLE IF EXISTS `sakila`.`film_list`;
1240USE `sakila`;
1241CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`film_list` AS select `sakila`.`film`.`film_id` AS `FID`,`sakila`.`film`.`title` AS `title`,`sakila`.`film`.`description` AS `description`,`sakila`.`category`.`name` AS `category`,`sakila`.`film`.`rental_rate` AS `price`,`sakila`.`film`.`length` AS `length`,`sakila`.`film`.`rating` AS `rating`,group_concat(concat(`sakila`.`actor`.`first_name`,_utf8mb3' ',`sakila`.`actor`.`last_name`) separator ', ') AS `actors` from ((((`sakila`.`category` left join `sakila`.`film_category` on((`sakila`.`category`.`category_id` = `sakila`.`film_category`.`category_id`))) left join `sakila`.`film` on((`sakila`.`film_category`.`film_id` = `sakila`.`film`.`film_id`))) join `sakila`.`film_actor` on((`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`))) join `sakila`.`actor` on((`sakila`.`film_actor`.`actor_id` = `sakila`.`actor`.`actor_id`))) group by `sakila`.`film`.`film_id`,`sakila`.`category`.`name`;
1242
1243-- -----------------------------------------------------
1244-- View `sakila`.`nicer_but_slower_film_list`
1245-- -----------------------------------------------------
1246DROP TABLE IF EXISTS `sakila`.`nicer_but_slower_film_list`;
1247USE `sakila`;
1248CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`nicer_but_slower_film_list` AS select `sakila`.`film`.`film_id` AS `FID`,`sakila`.`film`.`title` AS `title`,`sakila`.`film`.`description` AS `description`,`sakila`.`category`.`name` AS `category`,`sakila`.`film`.`rental_rate` AS `price`,`sakila`.`film`.`length` AS `length`,`sakila`.`film`.`rating` AS `rating`,group_concat(concat(concat(upper(substr(`sakila`.`actor`.`first_name`,1,1)),lower(substr(`sakila`.`actor`.`first_name`,2,length(`sakila`.`actor`.`first_name`))),_utf8mb3' ',concat(upper(substr(`sakila`.`actor`.`last_name`,1,1)),lower(substr(`sakila`.`actor`.`last_name`,2,length(`sakila`.`actor`.`last_name`)))))) separator ', ') AS `actors` from ((((`sakila`.`category` left join `sakila`.`film_category` on((`sakila`.`category`.`category_id` = `sakila`.`film_category`.`category_id`))) left join `sakila`.`film` on((`sakila`.`film_category`.`film_id` = `sakila`.`film`.`film_id`))) join `sakila`.`film_actor` on((`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`))) join `sakila`.`actor` on((`sakila`.`film_actor`.`actor_id` = `sakila`.`actor`.`actor_id`))) group by `sakila`.`film`.`film_id`,`sakila`.`category`.`name`;
1249
1250-- -----------------------------------------------------
1251-- View `sakila`.`sales_by_film_category`
1252-- -----------------------------------------------------
1253DROP TABLE IF EXISTS `sakila`.`sales_by_film_category`;
1254USE `sakila`;
1255CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`sales_by_film_category` AS select `c`.`name` AS `category`,sum(`p`.`amount`) AS `total_sales` from (((((`sakila`.`payment` `p` join `sakila`.`rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `sakila`.`inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `sakila`.`film` `f` on((`i`.`film_id` = `f`.`film_id`))) join `sakila`.`film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `c`.`name` order by `total_sales` desc;
1256
1257-- -----------------------------------------------------
1258-- View `sakila`.`sales_by_store`
1259-- -----------------------------------------------------
1260DROP TABLE IF EXISTS `sakila`.`sales_by_store`;
1261USE `sakila`;
1262CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`sales_by_store` AS select concat(`c`.`city`,_utf8mb3',',`cy`.`country`) AS `store`,concat(`m`.`first_name`,_utf8mb3' ',`m`.`last_name`) AS `manager`,sum(`p`.`amount`) AS `total_sales` from (((((((`sakila`.`payment` `p` join `sakila`.`rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `sakila`.`inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `sakila`.`store` `s` on((`i`.`store_id` = `s`.`store_id`))) join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` `c` on((`a`.`city_id` = `c`.`city_id`))) join `sakila`.`country` `cy` on((`c`.`country_id` = `cy`.`country_id`))) join `sakila`.`staff` `m` on((`s`.`manager_staff_id` = `m`.`staff_id`))) group by `s`.`store_id` order by `cy`.`country`,`c`.`city`;
1263
1264-- -----------------------------------------------------
1265-- View `sakila`.`staff_list`
1266-- -----------------------------------------------------
1267DROP TABLE IF EXISTS `sakila`.`staff_list`;
1268USE `sakila`;
1269CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8mb3' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,`s`.`store_id` AS `SID` from (((`sakila`.`staff` `s` join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)));
1270USE `sakila`;
1271
1272DELIMITER $$
1273USE `sakila`$$
1274CREATE
1275DEFINER=`root`@`localhost`
1276TRIGGER `sakila`.`customer_create_date`
1277BEFORE INSERT ON `sakila`.`customer`
1278FOR EACH ROW
1279SET NEW.create_date = NOW()$$
1280
1281USE `sakila`$$
1282CREATE
1283DEFINER=`root`@`localhost`
1284TRIGGER `sakila`.`del_film`
1285AFTER DELETE ON `sakila`.`film`
1286FOR EACH ROW
1287BEGIN
1288 DELETE FROM film_text WHERE film_id = old.film_id;
1289 END$$
1290
1291USE `sakila`$$
1292CREATE
1293DEFINER=`root`@`localhost`
1294TRIGGER `sakila`.`ins_film`
1295AFTER INSERT ON `sakila`.`film`
1296FOR EACH ROW
1297BEGIN
1298 INSERT INTO film_text (film_id, title, description)
1299 VALUES (new.film_id, new.title, new.description);
1300 END$$
1301
1302USE `sakila`$$
1303CREATE
1304DEFINER=`root`@`localhost`
1305TRIGGER `sakila`.`upd_film`
1306AFTER UPDATE ON `sakila`.`film`
1307FOR EACH ROW
1308BEGIN
1309 IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
1310 THEN
1311 UPDATE film_text
1312 SET title=new.title,
1313 description=new.description,
1314 film_id=new.film_id
1315 WHERE film_id=old.film_id;
1316 END IF;
1317 END$$
1318
1319USE `sakila`$$
1320CREATE
1321DEFINER=`root`@`localhost`
1322TRIGGER `sakila`.`rental_date`
1323BEFORE INSERT ON `sakila`.`rental`
1324FOR EACH ROW
1325SET NEW.rental_date = NOW()$$
1326
1327USE `sakila`$$
1328CREATE
1329DEFINER=`root`@`localhost`
1330TRIGGER `sakila`.`payment_date`
1331BEFORE INSERT ON `sakila`.`payment`
1332FOR EACH ROW
1333SET NEW.payment_date = NOW()$$
1334
1335
1336DELIMITER ;
1337
1338SET SQL_MODE=@OLD_SQL_MODE;
1339SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
1340SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;