· 6 years ago · Oct 31, 2019, 04:14 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Oct 31 13:10:55 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 NHL
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `NHL` ;
14
15-- -----------------------------------------------------
16-- Schema NHL
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `NHL` DEFAULT CHARACTER SET utf8 ;
19USE `NHL` ;
20
21-- -----------------------------------------------------
22-- Table `NHL`.`Captains`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `NHL`.`Captains` ;
25
26CREATE TABLE IF NOT EXISTS `NHL`.`Captains` (
27 `idCaptains` INT NOT NULL,
28 `first_name` VARCHAR(45) NULL,
29 `last_name` VARCHAR(45) NULL,
30 PRIMARY KEY (`idCaptains`),
31 CONSTRAINT `player`
32 FOREIGN KEY (`idCaptains`)
33 REFERENCES `NHL`.`Players` (`idPlayers`)
34 ON DELETE NO ACTION
35 ON UPDATE NO ACTION)
36ENGINE = InnoDB;
37
38CREATE UNIQUE INDEX `idCaptains_UNIQUE` ON `NHL`.`Captains` (`idCaptains` ASC) VISIBLE;
39
40
41-- -----------------------------------------------------
42-- Table `NHL`.`Games`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `NHL`.`Games` ;
45
46CREATE TABLE IF NOT EXISTS `NHL`.`Games` (
47 `idGames` INT NOT NULL,
48 `home_team` VARCHAR(45) NULL,
49 `road_team` VARCHAR(45) NULL,
50 `home_score` VARCHAR(45) NULL,
51 `road_score` VARCHAR(45) NULL,
52 `winner` VARCHAR(45) NULL,
53 `date` DATE NULL,
54 PRIMARY KEY (`idGames`),
55 CONSTRAINT `home`
56 FOREIGN KEY (`home_score`)
57 REFERENCES `NHL`.`Teams` (`name`)
58 ON DELETE NO ACTION
59 ON UPDATE NO ACTION,
60 CONSTRAINT `road`
61 FOREIGN KEY (`road_team`)
62 REFERENCES `NHL`.`Teams` (`name`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67CREATE INDEX `home_idx` ON `NHL`.`Games` (`home_score` ASC) VISIBLE;
68
69CREATE INDEX `road_idx` ON `NHL`.`Games` (`road_team` ASC) VISIBLE;
70
71
72-- -----------------------------------------------------
73-- Table `NHL`.`Players`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `NHL`.`Players` ;
76
77CREATE TABLE IF NOT EXISTS `NHL`.`Players` (
78 `idPlayers` INT GENERATED ALWAYS AS () VIRTUAL,
79 `first_name` VARCHAR(45) NULL,
80 `last_name` VARCHAR(45) NULL,
81 `position` SET('centre', 'left_wing', 'right_wing', 'defense', 'goalie') NULL,
82 `skill_level` DECIMAL NULL,
83 `team` VARCHAR(45) NULL,
84 PRIMARY KEY (`idPlayers`),
85 CONSTRAINT `name`
86 FOREIGN KEY (`team`)
87 REFERENCES `NHL`.`Teams` (`name`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB
91ROW_FORMAT = DYNAMIC;
92
93CREATE INDEX `name_idx` ON `NHL`.`Players` (`team` ASC) VISIBLE;
94
95
96-- -----------------------------------------------------
97-- Table `NHL`.`Teams`
98-- -----------------------------------------------------
99DROP TABLE IF EXISTS `NHL`.`Teams` ;
100
101CREATE TABLE IF NOT EXISTS `NHL`.`Teams` (
102 `idTeams` INT GENERATED ALWAYS AS () VIRTUAL,
103 `name` VARCHAR(45) NOT NULL,
104 `city` VARCHAR(45) NOT NULL,
105 `coach` VARCHAR(45) CHARACTER SET 'armscii8' NULL,
106 PRIMARY KEY (`idTeams`))
107ENGINE = InnoDB;
108
109CREATE UNIQUE INDEX `name_UNIQUE` ON `NHL`.`Teams` (`name` ASC) VISIBLE;
110
111GRANT ALL ON `NHL`.* TO 'jerbear';
112
113SET SQL_MODE=@OLD_SQL_MODE;
114SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
115SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;