· 4 years ago · Feb 05, 2021, 01:28 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 sport_model
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema sport_model
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `sport_model` DEFAULT CHARACTER SET utf8 ;
15USE `sport_model` ;
16
17-- -----------------------------------------------------
18-- Table `sport_model`.`sports`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `sport_model`.`sports` (
21 `id` INT NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(45) NOT NULL,
23 PRIMARY KEY (`id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `sport_model`.`leagues`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `sport_model`.`leagues` (
31 `id` INT NOT NULL AUTO_INCREMENT,
32 `name` VARCHAR(45) NOT NULL,
33 `sport_id` INT NOT NULL,
34 PRIMARY KEY (`id`),
35 UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE,
36 INDEX `sportid_idx` (`sport_id` ASC) VISIBLE,
37 CONSTRAINT `sportid`
38 FOREIGN KEY (`sport_id`)
39 REFERENCES `sport_model`.`sports` (`id`)
40 ON DELETE NO ACTION
41 ON UPDATE NO ACTION)
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `sport_model`.`seasons`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `sport_model`.`seasons` (
49 `id` INT NOT NULL AUTO_INCREMENT,
50 `league_id` INT NOT NULL,
51 `number` INT NOT NULL,
52 PRIMARY KEY (`id`),
53 INDEX `league_id_idx` (`league_id` ASC) VISIBLE,
54 CONSTRAINT `league`
55 FOREIGN KEY (`league_id`)
56 REFERENCES `sport_model`.`leagues` (`id`)
57 ON DELETE NO ACTION
58 ON UPDATE NO ACTION)
59ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63-- Table `sport_model`.`teams`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `sport_model`.`teams` (
66 `id` INT NOT NULL AUTO_INCREMENT,
67 `season_id` INT NOT NULL,
68 `name` VARCHAR(45) NOT NULL,
69 PRIMARY KEY (`id`),
70 INDEX `season_id_idx` (`season_id` ASC) VISIBLE,
71 CONSTRAINT `season_id`
72 FOREIGN KEY (`season_id`)
73 REFERENCES `sport_model`.`seasons` (`id`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `sport_model`.`games`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `sport_model`.`games` (
83 `id` INT NOT NULL AUTO_INCREMENT,
84 `season_id` INT NOT NULL,
85 `date` DATE NOT NULL,
86 `round` INT NOT NULL,
87 `finished` TINYINT NOT NULL,
88 `home_team` INT NOT NULL,
89 `away_team` INT NOT NULL,
90 `home_result` INT NOT NULL,
91 `away_result` INT NOT NULL,
92 PRIMARY KEY (`id`),
93 INDEX `season_id_idx` (`season_id` ASC) VISIBLE,
94 INDEX `home_team_idx` (`home_team` ASC) VISIBLE,
95 INDEX `away_team_idx` (`away_team` ASC) VISIBLE,
96 CONSTRAINT `home_team`
97 FOREIGN KEY (`home_team`)
98 REFERENCES `sport_model`.`teams` (`id`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION,
101 CONSTRAINT `away_team`
102 FOREIGN KEY (`away_team`)
103 REFERENCES `sport_model`.`teams` (`id`)
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION,
106 CONSTRAINT `season_id`
107 FOREIGN KEY (`season_id`)
108 REFERENCES `sport_model`.`seasons` (`id`)
109 ON DELETE NO ACTION
110 ON UPDATE NO ACTION)
111ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `sport_model`.`goals`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `sport_model`.`goals` (
118 `id` INT NOT NULL AUTO_INCREMENT,
119 `game_id` INT NOT NULL,
120 `period` INT NOT NULL,
121 `scoring_team` INT NOT NULL,
122 PRIMARY KEY (`id`, `game_id`),
123 INDEX `match_id_idx` (`game_id` ASC) VISIBLE,
124 INDEX `scoring_team_idx` (`scoring_team` ASC) VISIBLE,
125 CONSTRAINT `game_id`
126 FOREIGN KEY (`game_id`)
127 REFERENCES `sport_model`.`games` (`id`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `scoring_team`
131 FOREIGN KEY (`scoring_team`)
132 REFERENCES `sport_model`.`teams` (`id`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION)
135ENGINE = InnoDB;
136
137
138SET SQL_MODE=@OLD_SQL_MODE;
139SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
140SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
141