· 6 years ago · May 27, 2019, 04:14 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu May 23 17:45:27 2019
3-- Model: football-league Version: 1.1
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 ekstraklasa
12--
13-- UTF8 - UTF8_POLISH_CI
14--
15-- Dawid Bitner
16-- Marcin Krupa
17-- Databases
18-- Silesian University of Technology - Faculty of Applied Mathematics
19-- 2018/2019
20-- -----------------------------------------------------
21CREATE SCHEMA IF NOT EXISTS `ekstraklasa` DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci ;
22USE `ekstraklasa` ;
23
24-- -----------------------------------------------------
25-- Table `ekstraklasa`.`clubs`
26-- -----------------------------------------------------
27CREATE TABLE IF NOT EXISTS `ekstraklasa`.`clubs` (
28 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
29 `name` VARCHAR(45) NOT NULL,
30 `city` VARCHAR(30) NOT NULL,
31 `founded` YEAR(4) NULL,
32 `active` ENUM("ekstraklasa", "other league", "doesn't exist") NOT NULL,
33 PRIMARY KEY (`id`),
34 UNIQUE INDEX `id_clubs_UNIQUE` (`id` ASC),
35 UNIQUE INDEX `name_clubs_UNIQUE` (`name` ASC))
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `ekstraklasa`.`clubs_has_stadiums`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `ekstraklasa`.`clubs_has_stadiums` (
43 `clubs_id_clubs` INT UNSIGNED NOT NULL,
44 `stadiums_id_stadiums` INT UNSIGNED NOT NULL,
45 PRIMARY KEY (`clubs_id_clubs`, `stadiums_id_stadiums`),
46 INDEX `fk_clubs_has_stadiums_stadiums1_idx` (`stadiums_id_stadiums` ASC),
47 INDEX `fk_clubs_has_stadiums_clubs1_idx` (`clubs_id_clubs` ASC),
48 CONSTRAINT `fk_clubs_has_stadiums_clubs1`
49 FOREIGN KEY (`clubs_id_clubs`)
50 REFERENCES `ekstraklasa`.`clubs` (`id`),
51 CONSTRAINT `fk_clubs_has_stadiums_stadiums1`
52 FOREIGN KEY (`stadiums_id_stadiums`)
53 REFERENCES `ekstraklasa`.`stadiums` (`id`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `ekstraklasa`.`coaches`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `ekstraklasa`.`coaches` (
61 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
62 `name` VARCHAR(45) NOT NULL,
63 `lastname` VARCHAR(45) NOT NULL,
64 `dateofbirth` DATE NULL,
65 `nationality` VARCHAR(45) NULL,
66 `club` INT UNSIGNED NOT NULL,
67 PRIMARY KEY (`id`),
68 INDEX `fk_coaches_clubs1_idx` (`club` ASC),
69 CONSTRAINT `fk_coaches_clubs1`
70 FOREIGN KEY (`club`)
71 REFERENCES `ekstraklasa`.`clubs` (`id`))
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `ekstraklasa`.`crests`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `ekstraklasa`.`crests` (
79 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
80 `image` LONGBLOB NULL,
81 `club` INT UNSIGNED NOT NULL,
82 PRIMARY KEY (`id`),
83 UNIQUE INDEX `id_crests_UNIQUE` (`id` ASC),
84 INDEX `fk_crests_clubs1_idx` (`club` ASC),
85 CONSTRAINT `fk_crests_clubs1`
86 FOREIGN KEY (`club`)
87 REFERENCES `ekstraklasa`.`clubs` (`id`))
88ENGINE = InnoDB;
89
90
91-- -----------------------------------------------------
92-- Table `ekstraklasa`.`domesticcups`
93-- -----------------------------------------------------
94CREATE TABLE IF NOT EXISTS `ekstraklasa`.`domesticcups` (
95 `id` INT UNSIGNED NOT NULL,
96 `name` VARCHAR(45) NOT NULL,
97 `year` VARCHAR(45) NOT NULL,
98 `winner` INT UNSIGNED NOT NULL,
99 `runnerupo` INT UNSIGNED NOT NULL,
100 PRIMARY KEY (`id`),
101 UNIQUE INDEX `id_domesticcup_UNIQUE` (`id` ASC),
102 INDEX `domesticcup_clubs_idx` (`winner` ASC, `runnerupo` ASC),
103 CONSTRAINT `domesticcup_clubs`
104 FOREIGN KEY (`winner` , `runnerupo`)
105 REFERENCES `ekstraklasa`.`clubs` (`id` , `id`))
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `ekstraklasa`.`kits`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `ekstraklasa`.`kits` (
113 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
114 `home` VARCHAR(45) NULL,
115 `away` VARCHAR(45) NULL,
116 `clubcolours` VARCHAR(45) NULL,
117 `club` INT UNSIGNED NOT NULL,
118 PRIMARY KEY (`id`),
119 UNIQUE INDEX `id_kits_UNIQUE` (`id` ASC),
120 INDEX `fk_kits_clubs1_idx` (`club` ASC),
121 UNIQUE INDEX `club_UNIQUE` (`club` ASC),
122 CONSTRAINT `fk_kits_clubs1`
123 FOREIGN KEY (`club`)
124 REFERENCES `ekstraklasa`.`clubs` (`id`))
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `ekstraklasa`.`players`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `ekstraklasa`.`players` (
132 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
133 `name` VARCHAR(45) NOT NULL,
134 `lastname` VARCHAR(45) NOT NULL,
135 `dateofbirth` DATE NULL,
136 `position` ENUM("goalkeeper", "defender", "midfielder", "striker") NOT NULL,
137 `height` TINYINT UNSIGNED NULL,
138 `weight` TINYINT UNSIGNED NULL,
139 `nationality` VARCHAR(45) NULL,
140 `club` INT UNSIGNED NOT NULL,
141 UNIQUE INDEX `id_currentplayers_UNIQUE` (`id` ASC),
142 PRIMARY KEY (`id`),
143 INDEX `fk_players_clubs1_idx` (`club` ASC),
144 CONSTRAINT `fk_players_clubs1`
145 FOREIGN KEY (`club`)
146 REFERENCES `ekstraklasa`.`clubs` (`id`))
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `ekstraklasa`.`seasons`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `ekstraklasa`.`seasons` (
154 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
155 `leaguename` VARCHAR(45) NOT NULL,
156 `year` VARCHAR(9) NOT NULL,
157 `1stplace` INT UNSIGNED NOT NULL,
158 `2ndplace` INT UNSIGNED NOT NULL,
159 `3thplace` INT UNSIGNED NOT NULL,
160 PRIMARY KEY (`id`),
161 UNIQUE INDEX `idtable1_UNIQUE` (`id` ASC),
162 INDEX `test_idx` (`1stplace` ASC, `2ndplace` ASC, `3thplace` ASC),
163 CONSTRAINT `seasons_clubs`
164 FOREIGN KEY (`1stplace` , `2ndplace` , `3thplace`)
165 REFERENCES `ekstraklasa`.`clubs` (`id` , `id` , `id`))
166ENGINE = InnoDB;
167
168
169-- -----------------------------------------------------
170-- Table `ekstraklasa`.`stadiums`
171-- -----------------------------------------------------
172CREATE TABLE IF NOT EXISTS `ekstraklasa`.`stadiums` (
173 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
174 `name` VARCHAR(100) NOT NULL,
175 `city` VARCHAR(45) NOT NULL,
176 `capacity` MEDIUMINT UNSIGNED NULL,
177 `buildyear` YEAR NULL,
178 PRIMARY KEY (`id`),
179 UNIQUE INDEX `id_stadiums_UNIQUE` (`id` ASC))
180ENGINE = InnoDB;
181
182
183SET SQL_MODE=@OLD_SQL_MODE;
184SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
185SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;