· 5 years ago · Jun 02, 2020, 03:04 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 sportcity
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema sportcity
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `sportcity` ;
15USE `sportcity` ;
16
17-- -----------------------------------------------------
18-- Table `sportcity`.`sportsman`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `sportcity`.`sportsman` (
21 `id` INT NOT NULL,
22 `name` VARCHAR(100) NOT NULL,
23 `club_name` VARCHAR(100) NOT NULL,
24 PRIMARY KEY (`id`))
25 ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `sportcity`.`coach`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `sportcity`.`coach` (
32 `id` INT NOT NULL,
33 `name` VARCHAR(100) NOT NULL,
34 `sport` ENUM('football', 'tennis', 'hockey', 'volleyball') NOT NULL,
35 PRIMARY KEY (`id`))
36 ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `sportcity`.`abilities`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `sportcity`.`abilities` (
43 `id` INT NOT NULL,
44 `sportsman_id` INT NOT NULL,
45 `category` INT NOT NULL,
46 `sport` ENUM('football', 'tennis', 'hockey', 'volleyball') NOT NULL,
47 PRIMARY KEY (`id`),
48 INDEX `fk_abilities_sportsman1_idx` (`sportsman_id` ASC) ,
49 CONSTRAINT `fk_abilities_sportsman1`
50 FOREIGN KEY (`sportsman_id`)
51 REFERENCES `sportcity`.`sportsman` (`id`)
52 ON DELETE CASCADE
53 ON UPDATE CASCADE)
54 ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `sportcity`.`competition`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `sportcity`.`competition` (
61 `id` INT NOT NULL,
62 `c_date` TIMESTAMP NOT NULL,
63 `sport` ENUM('football', 'tennis', 'hockey', 'volleyball') NOT NULL,
64 PRIMARY KEY (`id`))
65 ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `sportcity`.`participation`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `sportcity`.`participation` (
72 `sportsman_id` INT NOT NULL,
73 `competition_id` INT NOT NULL,
74 PRIMARY KEY (`sportsman_id`, `competition_id`),
75 INDEX `fk_participation_competition1_idx` (`competition_id` ASC) ,
76 CONSTRAINT `fk_participation_sportsman1`
77 FOREIGN KEY (`sportsman_id`)
78 REFERENCES `sportcity`.`sportsman` (`id`)
79 ON DELETE CASCADE
80 ON UPDATE CASCADE,
81 CONSTRAINT `fk_participation_competition1`
82 FOREIGN KEY (`competition_id`)
83 REFERENCES `sportcity`.`competition` (`id`)
84 ON DELETE CASCADE
85 ON UPDATE CASCADE)
86 ENGINE = InnoDB;
87
88
89-- -----------------------------------------------------
90-- Table `sportcity`.`organizer`
91-- -----------------------------------------------------
92CREATE TABLE IF NOT EXISTS `sportcity`.`organizer` (
93 `id` INT NOT NULL,
94 `name` VARCHAR(100) NOT NULL,
95 PRIMARY KEY (`id`))
96 ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `sportcity`.`arrangement`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `sportcity`.`arrangement` (
103 `competition_id` INT NOT NULL,
104 `organizer_id` INT NOT NULL,
105 PRIMARY KEY (`competition_id`, `organizer_id`),
106 INDEX `fk_arrangement_organizer1_idx` (`organizer_id` ASC) ,
107 CONSTRAINT `fk_arrangement_competition1`
108 FOREIGN KEY (`competition_id`)
109 REFERENCES `sportcity`.`competition` (`id`)
110 ON DELETE CASCADE
111 ON UPDATE CASCADE,
112 CONSTRAINT `fk_arrangement_organizer1`
113 FOREIGN KEY (`organizer_id`)
114 REFERENCES `sportcity`.`organizer` (`id`)
115 ON DELETE CASCADE
116 ON UPDATE CASCADE)
117 ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `sportcity`.`sports_facility`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `sportcity`.`sports_facility` (
124 `facility_id` INT NOT NULL,
125 PRIMARY KEY (`facility_id`))
126 ENGINE = InnoDB;
127
128
129-- -----------------------------------------------------
130-- Table `sportcity`.`location`
131-- -----------------------------------------------------
132CREATE TABLE IF NOT EXISTS `sportcity`.`location` (
133 `competition_id` INT NOT NULL,
134 `sports_facility_id` INT NOT NULL,
135 PRIMARY KEY (`competition_id`, `sports_facility_id`),
136 INDEX `fk_location_sports_facility1_idx` (`sports_facility_id` ASC) ,
137 CONSTRAINT `fk_location_competition1`
138 FOREIGN KEY (`competition_id`)
139 REFERENCES `sportcity`.`competition` (`id`)
140 ON DELETE CASCADE
141 ON UPDATE CASCADE,
142 CONSTRAINT `fk_location_sports_facility1`
143 FOREIGN KEY (`sports_facility_id`)
144 REFERENCES `sportcity`.`sports_facility` (`facility_id`)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE)
147 ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `sportcity`.`stadium`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `sportcity`.`stadium` (
154 `stadium_id` INT NOT NULL,
155 `capacity` INT NOT NULL,
156 PRIMARY KEY (`stadium_id`),
157 CONSTRAINT `fk_stadium_sports_facility1`
158 FOREIGN KEY (`stadium_id`)
159 REFERENCES `sportcity`.`sports_facility` (`facility_id`)
160 ON DELETE CASCADE
161 ON UPDATE CASCADE)
162 ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `sportcity`.`court`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `sportcity`.`court` (
169 `court_id` INT NOT NULL,
170 `coverage_type` ENUM('grass', 'clay') NOT NULL,
171 PRIMARY KEY (`court_id`),
172 CONSTRAINT `fk_court_sports_facility1`
173 FOREIGN KEY (`court_id`)
174 REFERENCES `sportcity`.`sports_facility` (`facility_id`)
175 ON DELETE CASCADE
176 ON UPDATE CASCADE)
177 ENGINE = InnoDB;
178
179
180-- -----------------------------------------------------
181-- Table `sportcity`.`mentoring`
182-- -----------------------------------------------------
183CREATE TABLE IF NOT EXISTS `sportcity`.`mentoring` (
184 `sportsman_id` INT NOT NULL,
185 `coach_id` INT NOT NULL,
186 PRIMARY KEY (`sportsman_id`, `coach_id`),
187 INDEX `fk_mentoring_coach1_idx` (`coach_id` ASC) ,
188 CONSTRAINT `fk_mentoring_sportsman1`
189 FOREIGN KEY (`sportsman_id`)
190 REFERENCES `sportcity`.`sportsman` (`id`)
191 ON DELETE CASCADE
192 ON UPDATE CASCADE,
193 CONSTRAINT `fk_mentoring_coach1`
194 FOREIGN KEY (`coach_id`)
195 REFERENCES `sportcity`.`coach` (`id`)
196 ON DELETE CASCADE
197 ON UPDATE CASCADE)
198 ENGINE = InnoDB;
199
200
201-- -----------------------------------------------------
202-- Table `sportcity`.`ice_arena`
203-- -----------------------------------------------------
204CREATE TABLE IF NOT EXISTS `sportcity`.`ice_arena` (
205 `ice_arena_id` INT NOT NULL,
206 `square` INT NOT NULL,
207 PRIMARY KEY (`ice_arena_id`),
208 CONSTRAINT `fk_ice_arena_sports_facility1`
209 FOREIGN KEY (`ice_arena_id`)
210 REFERENCES `sportcity`.`sports_facility` (`facility_id`)
211 ON DELETE CASCADE
212 ON UPDATE CASCADE)
213 ENGINE = InnoDB;
214
215
216-- -----------------------------------------------------
217-- Table `sportcity`.`volleyball_arena`
218-- -----------------------------------------------------
219CREATE TABLE IF NOT EXISTS `sportcity`.`volleyball_arena` (
220 `volleyball_arena_id` INT NOT NULL,
221 `net_height` INT NOT NULL,
222 `net_width` INT NOT NULL,
223 PRIMARY KEY (`volleyball_arena_id`),
224 CONSTRAINT `fk_volleyball_arena_sports_facility1`
225 FOREIGN KEY (`volleyball_arena_id`)
226 REFERENCES `sportcity`.`sports_facility` (`facility_id`)
227 ON DELETE CASCADE
228 ON UPDATE CASCADE)
229 ENGINE = InnoDB;
230
231
232SET SQL_MODE=@OLD_SQL_MODE;
233SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
234SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;