K8VtC1Tx

1-- -----------------------------------------------------
2-- Schema tennis_schema
3-- -----------------------------------------------------
4CREATE SCHEMA IF NOT EXISTS `tennis_schema` DEFAULT CHARACTER SET utf8 ;
5USE `tennis_schema` ;
6
7-- Table `tennis_schema`.`country`
8CREATE TABLE IF NOT EXISTS `tennis_schema`.`country` (
9  `id` INT NOT NULL AUTO_INCREMENT,
10  `name` VARCHAR(45) NOT NULL,
11  PRIMARY KEY (`id`),
12  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
13ENGINE = InnoDB
14DEFAULT CHARACTER SET = utf8;
15
16
17-- Table `tennis_schema`.`coach`
18CREATE TABLE IF NOT EXISTS `tennis_schema`.`coach` (
19  `id` INT NOT NULL AUTO_INCREMENT,
20  `first_name` VARCHAR(45) NOT NULL,
21  `second_name` VARCHAR(45) NOT NULL,
22  `gender` ENUM('F', 'M', 'O') NOT NULL,
23  `birth_date` DATE NOT NULL,
24  `country_id` INT NOT NULL,
25  CONSTRAINT `fk_coach_country`
26    FOREIGN KEY (`country_id`)
27    REFERENCES `tennis_schema`.`country` (`id`),
28  `is_professional_career` TINYINT NULL,
29  PRIMARY KEY (`id`),
30  INDEX `fk_coach_country_idx` (`country_id` ASC) INVISIBLE,
31  INDEX `fk_coach_first_second_name_idx` (`first_name` ASC, `second_name` ASC))
32ENGINE = InnoDB;
33
34
35-- Table `tennis_schema`.`player`
36CREATE TABLE IF NOT EXISTS `tennis_schema`.`player` (
37  `id` INT NOT NULL AUTO_INCREMENT,
38  `first_name` VARCHAR(45) NOT NULL,
39  `second_name` VARCHAR(45) NOT NULL,
40  `gender` ENUM('F', 'M', 'O') NOT NULL,
41  `working_hand` ENUM('L', 'R', 'B') NULL DEFAULT NULL,
42  `birth_date` DATE NOT NULL,
43  `country_id` INT NOT NULL,
44  CONSTRAINT `fk_tennis_player_country`
45    FOREIGN KEY (`country_id`)
46    REFERENCES `tennis_schema`.`country` (`id`),
47  `coach_id` INT NULL,
48  CONSTRAINT `fk_tennis_player_coach`
49    FOREIGN KEY (`coach_id`)
50    REFERENCES `tennis_schema`.`coach` (`id`),
51  PRIMARY KEY (`id`),
52  INDEX `fk_player_country_id_idx` (`country_id` ASC) INVISIBLE,
53  INDEX `fk_player_coach_id_idx` (`coach_id` ASC) INVISIBLE,
54  INDEX `fk_player_first_second_name` (`first_name` ASC, `second_name` ASC))
55ENGINE = InnoDB
56DEFAULT CHARACTER SET = utf8;
57
58
59-- Table `tennis_schema`.`referee`
60CREATE TABLE IF NOT EXISTS `tennis_schema`.`referee` (
61  `id` INT NOT NULL AUTO_INCREMENT,
62  `first_name` VARCHAR(45) NOT NULL,
63  `second_name` VARCHAR(45) NOT NULL,
64  `gender` ENUM('F', 'M', 'O') NOT NULL,
65  `birth_date` DATE NOT NULL,
66  `country_id` INT NOT NULL,
67  CONSTRAINT `fk_referee_country`
68    FOREIGN KEY (`country_id`)
69    REFERENCES `tennis_schema`.`country` (`id`),
70  PRIMARY KEY (`id`),
71  INDEX `fk_referee_country_idx` (`country_id` ASC) INVISIBLE,
72  INDEX `fk_feferee_first_second_name_idx` (`first_name` ASC, `second_name` ASC))
73ENGINE = InnoDB;
74
75
76-- Table `tennis_schema`.`type_of_court`
77CREATE TABLE IF NOT EXISTS `tennis_schema`.`type_of_court` (
78  `id` INT NOT NULL AUTO_INCREMENT,
79  `name` VARCHAR(45) NOT NULL,
80  PRIMARY KEY (`id`))
81ENGINE = InnoDB;
82
83
84-- Table `tennis_schema`.`title`
85CREATE TABLE IF NOT EXISTS `tennis_schema`.`title` (
86  `id` INT NOT NULL AUTO_INCREMENT,
87  `name` VARCHAR(45) NOT NULL,
88  PRIMARY KEY (`id`))
89ENGINE = InnoDB;
90
91
92-- Table `tennis_schema`.`tournament`
93CREATE TABLE IF NOT EXISTS `tennis_schema`.`tournament` (
94  `id` INT NOT NULL,
95  `name` VARCHAR(45) NOT NULL,
96  `founded_date` YEAR NOT NULL,
97  `website` VARCHAR(45) NULL,
98  PRIMARY KEY (`id`))
99ENGINE = InnoDB;
100
101
102-- Table `tennis_schema`.`season`
103CREATE TABLE IF NOT EXISTS `tennis_schema`.`season` (
104  `id` INT NOT NULL AUTO_INCREMENT,
105  `tournament_id` INT NOT NULL,
106  CONSTRAINT `fk_season_tournament_id`
107    FOREIGN KEY (`tournament_id`)
108    REFERENCES `tennis_schema`.`tournament` (`id`),
109  `country_id` INT NOT NULL,
110  CONSTRAINT `fk_season_country_id`
111    FOREIGN KEY (`country_id`)
112    REFERENCES `tennis_schema`.`country` (`id`),
113  `type_of_court_id` INT NOT NULL,
114  CONSTRAINT `fk_season_type_of_court_id`
115    FOREIGN KEY (`type_of_court_id`)
116    REFERENCES `tennis_schema`.`type_of_court` (`id`),
117  `title_id` INT NOT NULL,
118  CONSTRAINT `fk_season_title_id`
119    FOREIGN KEY (`title_id`)
120    REFERENCES `tennis_schema`.`title` (`id`),
121  `price` INT NOT NULL,
122  `start_date` DATE NOT NULL,
123  `end_date` DATE NOT NULL,
124  `winner_id` INT NOT NULL DEFAULT 0,
125  CONSTRAINT `fk_season_winner_id`
126    FOREIGN KEY (`winner_id`)
127    REFERENCES `tennis_schema`.`player` (`id`),
128  PRIMARY KEY (`id`),
129  INDEX `fk_season_type_of_court_id_idx` (`type_of_court_id` ASC) INVISIBLE,
130  INDEX `fk_season_title_id_idx` (`title_id` ASC) INVISIBLE,
131  INDEX `fk_season_country_id_idx` (`country_id` ASC) INVISIBLE,
132  INDEX `fk_season_winner_id_idx` (`winner_id` ASC) INVISIBLE,
133  INDEX `fk_season_tournament_id_idx` (`tournament_id` ASC),
134  INDEX `start_end_date_idx` (`start_date` ASC, `end_date` ASC))
135ENGINE = InnoDB;
136
137
138-- Table `tennis_schema`.`stage`
139CREATE TABLE IF NOT EXISTS `tennis_schema`.`stage` (
140  `id` INT NOT NULL,
141  `name` VARCHAR(45) NOT NULL,
142  PRIMARY KEY (`id`))
143ENGINE = InnoDB;
144
145
146-- Table `tennis_schema`.`match`
147CREATE TABLE IF NOT EXISTS `tennis_schema`.`match` (
148  `id` INT NOT NULL AUTO_INCREMENT,
149  `first_player_id` INT NOT NULL,
150  CONSTRAINT `fk_match_first_player_id`
151    FOREIGN KEY (`first_player_id`)
152    REFERENCES `tennis_schema`.`player` (`id`),
153  `second_player_id` INT NOT NULL,
154  CONSTRAINT `fk_match_second_player_id`
155    FOREIGN KEY (`second_player_id`)
156    REFERENCES `tennis_schema`.`player` (`id`),
157  `winner_player_id` INT NOT NULL DEFAULT 0,
158  CONSTRAINT `fk_match_winner_player_id`
159    FOREIGN KEY (`winner_player_id`)
160    REFERENCES `tennis_schema`.`player` (`id`),
161  `first_num_sets` INT NOT NULL,
162  `second_num_sets` INT NOT NULL,
163  `referee_id` INT NOT NULL,
164  CONSTRAINT `fk_match_referee_id`
165    FOREIGN KEY (`referee_id`)
166    REFERENCES `tennis_schema`.`referee` (`id`),
167  `start_date` DATE NOT NULL,
168  `end_date` DATE NOT NULL,
169  `season_id` INT NOT NULL,
170  CONSTRAINT `fk_match_season`
171    FOREIGN KEY (`season_id`)
172    REFERENCES `tennis_schema`.`season` (`id`),
173  `stage_id` INT NOT NULL,
174  CONSTRAINT `fk_match_stage_id`
175    FOREIGN KEY (`stage_id`)
176    REFERENCES `tennis_schema`.`stage` (`id`),
177  PRIMARY KEY (`id`),
178  INDEX `fk_match_winner_player_id_idx` (`winner_player_id` ASC) INVISIBLE,
179  INDEX `fk_match_season_idx` (`season_id` ASC) INVISIBLE,
180  INDEX `fk_match_second_player_id_idx` (`second_player_id` ASC) INVISIBLE,
181  INDEX `fk_match_first_player_id_idx` (`first_player_id` ASC) INVISIBLE,
182  INDEX `fk_match_referee_id_idx` (`referee_id` ASC) INVISIBLE,
183  INDEX `fk_match_stage_id_idx` (`stage_id` ASC) INVISIBLE,
184  INDEX `fk_start_end_date_idx` (`start_date` ASC, `end_date` ASC),
185  INDEX `fk_match_first_second_player_idx` (`first_player_id` ASC, `second_player_id` ASC))
186ENGINE = InnoDB;
187
188
189-- Table `tennis_schema`.`season_history`
190CREATE TABLE IF NOT EXISTS `tennis_schema`.`season_history` (
191  `season_id` INT NOT NULL,
192  CONSTRAINT `fk_season_history_season`
193    FOREIGN KEY (`season_id`)
194    REFERENCES `tennis_schema`.`season` (`id`),
195  `player_id` INT NOT NULL,
196  CONSTRAINT `fk_season_history_player`
197    FOREIGN KEY (`player_id`)
198    REFERENCES `tennis_schema`.`player` (`id`),
199  PRIMARY KEY (`player_id`, `season_id`),
200  INDEX `fk_season_history_player_id_idx` (`player_id` ASC),
201  INDEX `fk_season_history_season_id_idx` (`season_id` ASC))
202ENGINE = InnoDB;