· 5 years ago · Sep 06, 2020, 08:24 PM
1CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
2USE `mydb` ;
3
4-- -----------------------------------------------------
5-- Table `mydb`.`chess_player`
6-- -----------------------------------------------------
7
8CREATE TABLE IF NOT EXISTS `mydb`.`chess_player` (
9 `id` INT NOT NULL,
10 `full_name` VARCHAR(60) NOT NULL,
11 `rating` INT NOT NULL,
12 `nation` VARCHAR(45) NOT NULL,
13 `age` INT NOT NULL,
14 PRIMARY KEY (`id`),
15 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
16ENGINE = InnoDB;
17
18
19-- -----------------------------------------------------
20-- Table `mydb`.`tournament`
21-- -----------------------------------------------------
22
23CREATE TABLE IF NOT EXISTS `mydb`.`tournament` (
24 `id` INT NOT NULL,
25 `fund` INT NOT NULL,
26 `start_date` DATETIME NOT NULL,
27 `end_date` DATETIME NOT NULL,
28 `place` VARCHAR(45) NOT NULL,
29 `name` VARCHAR(45) NOT NULL,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `mydb`.`time_control`
37-- -----------------------------------------------------
38
39CREATE TABLE IF NOT EXISTS `mydb`.`time_control` (
40 `id` INT NOT NULL,
41 `category` VARCHAR(45) NOT NULL,
42 PRIMARY KEY (`id`))
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`game`
48-- -----------------------------------------------------
49
50CREATE TABLE IF NOT EXISTS `mydb`.`game` (
51 `id` INT NOT NULL,
52 `result` INT NOT NULL,
53 `date` DATETIME NOT NULL,
54 `white_pieces` INT NOT NULL,
55 `black_pieces` INT NOT NULL,
56 `time_control_id` INT NOT NULL,
57 PRIMARY KEY (`id`, `black_pieces`, `white_pieces`, `time_control_id`),
58 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
59 INDEX `white_pieces_idx` (`white_pieces` ASC) VISIBLE,
60 INDEX `black_pieces_idx` (`black_pieces` ASC) VISIBLE,
61 INDEX `time_control_id_idx` (`time_control_id` ASC) VISIBLE,
62 CONSTRAINT `white_pieces`
63 FOREIGN KEY (`white_pieces`)
64 REFERENCES `mydb`.`chess_player` (`id`)
65 ON DELETE NO ACTION
66 ON UPDATE NO ACTION,
67 CONSTRAINT `black_pieces`
68 FOREIGN KEY (`black_pieces`)
69 REFERENCES `mydb`.`chess_player` (`id`)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION,
72 CONSTRAINT `time_control_id`
73 FOREIGN KEY (`time_control_id`)
74 REFERENCES `mydb`.`time_control` (`id`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION)
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `mydb`.`tournament_game`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `mydb`.`tournament_game` (
84 `game_id` INT NOT NULL,
85 `tournament_id` INT NOT NULL,
86 `round` INT NOT NULL,
87 PRIMARY KEY (`game_id`, `tournament_id`),
88 INDEX `tournament_id_idx` (`tournament_id` ASC) VISIBLE,
89 CONSTRAINT `game_id`
90 FOREIGN KEY (`game_id`)
91 REFERENCES `mydb`.`game` (`id`)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION,
94 CONSTRAINT `tournament_id`
95 FOREIGN KEY (`tournament_id`)
96 REFERENCES `mydb`.`tournament` (`id`)
97 ON DELETE NO ACTION
98 ON UPDATE NO ACTION)
99ENGINE = InnoDB;
100
101
102-- -----------------------------------------------------
103-- Table `mydb`.`player_x_tournament`
104-- -----------------------------------------------------
105
106CREATE TABLE IF NOT EXISTS `mydb`.`player_x_tournament` (
107 `chess_player_id` INT NOT NULL,
108 `tournament_id` INT NOT NULL,
109 `score` INT NOT NULL,
110 `place` INT NOT NULL,
111 PRIMARY KEY (`chess_player_id`, `tournament_id`),
112 INDEX `tournament_id_idx` (`tournament_id` ASC) VISIBLE,
113 CONSTRAINT `chess_player_id`
114 FOREIGN KEY (`chess_player_id`)
115 REFERENCES `mydb`.`chess_player` (`id`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION,
118 CONSTRAINT `tournament_id`
119 FOREIGN KEY (`tournament_id`)
120 REFERENCES `mydb`.`tournament` (`id`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `mydb`.`chess_club`
128-- -----------------------------------------------------
129
130CREATE TABLE IF NOT EXISTS `mydb`.`chess_club` (
131 `id` INT NOT NULL,
132 `name` VARCHAR(45) NOT NULL,
133 `adress` VARCHAR(45) NOT NULL,
134 PRIMARY KEY (`id`))
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table `mydb`.`club_member`
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS `mydb`.`club_member` (
142 `chess_club_id` INT NOT NULL,
143 `chess_player_id` INT NOT NULL,
144 PRIMARY KEY (`chess_club_id`, `chess_player_id`),
145 INDEX `chess_player_id_idx` (`chess_player_id` ASC) VISIBLE,
146 CONSTRAINT `chess_club_id`
147 FOREIGN KEY (`chess_club_id`)
148 REFERENCES `mydb`.`chess_club` (`id`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION,
151 CONSTRAINT `chess_player_id`
152 FOREIGN KEY (`chess_player_id`)
153 REFERENCES `mydb`.`chess_player` (`id`)
154 ON DELETE NO ACTION
155 ON UPDATE NO ACTION)
156ENGINE = InnoDB;
157