· 8 years ago · Feb 12, 2018, 05: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='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema gym_schema
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema gym_schema
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `gym_schema` DEFAULT CHARACTER SET utf8 ;
18USE `gym_schema` ;
19
20-- -----------------------------------------------------
21-- Table `gym_schema`.`credentials`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `gym_schema`.`credentials` (
24 `credentials_id` INT(11) NOT NULL,
25 `username` VARCHAR(45) NOT NULL,
26 `password` VARCHAR(45) NOT NULL,
27 `discarded` TINYINT(4) NOT NULL,
28 PRIMARY KEY (`credentials_id`))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `gym_schema`.`administrator`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `gym_schema`.`administrator` (
37 `administrator_id` INT(11) NOT NULL,
38 `credentials_id` INT(11) NOT NULL,
39 PRIMARY KEY (`administrator_id`),
40 INDEX `credentials_id_idx` (`credentials_id` ASC),
41 CONSTRAINT `credentials_id`
42 FOREIGN KEY (`credentials_id`)
43 REFERENCES `gym_schema`.`credentials` (`credentials_id`)
44 ON DELETE NO ACTION
45 ON UPDATE NO ACTION)
46ENGINE = InnoDB
47DEFAULT CHARACTER SET = utf8;
48
49
50-- -----------------------------------------------------
51-- Table `gym_schema`.`gym_chain`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `gym_schema`.`gym_chain` (
54 `gym_chain_id` INT(11) NOT NULL,
55 `name` VARCHAR(100) NOT NULL,
56 `logo` LONGBLOB NULL DEFAULT NULL,
57 `discarded` TINYINT(4) NOT NULL,
58 PRIMARY KEY (`gym_chain_id`))
59ENGINE = InnoDB
60DEFAULT CHARACTER SET = utf8;
61
62
63-- -----------------------------------------------------
64-- Table `gym_schema`.`coach`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `gym_schema`.`coach` (
67 `coach_id` INT(11) NOT NULL AUTO_INCREMENT,
68 `credentials_id` INT(11) NOT NULL,
69 `gym_chain_id` INT(11) NOT NULL,
70 `name` VARCHAR(45) NOT NULL,
71 `surname` VARCHAR(45) NOT NULL,
72 `education` VARCHAR(45) NOT NULL,
73 `born` DATETIME NOT NULL,
74 `discarded` TINYINT(4) NOT NULL,
75 PRIMARY KEY (`coach_id`),
76 INDEX `credentials_id_idx` (`credentials_id` ASC),
77 INDEX `gym_chain_id_idx` (`gym_chain_id` ASC),
78 CONSTRAINT `coach_credentials`
79 FOREIGN KEY (`credentials_id`)
80 REFERENCES `gym_schema`.`credentials` (`credentials_id`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION,
83 CONSTRAINT `coaches_at_gym_chain`
84 FOREIGN KEY (`gym_chain_id`)
85 REFERENCES `gym_schema`.`gym_chain` (`gym_chain_id`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION)
88ENGINE = InnoDB
89DEFAULT CHARACTER SET = utf8;
90
91
92-- -----------------------------------------------------
93-- Table `gym_schema`.`trainee`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `gym_schema`.`trainee` (
96 `trainee_id` INT(11) NOT NULL,
97 `gym_chain_id` INT(11) NOT NULL,
98 `name` VARCHAR(45) NULL DEFAULT NULL,
99 `surname` VARCHAR(45) NULL DEFAULT NULL,
100 `gender` TINYINT(4) NULL DEFAULT NULL,
101 `email` VARCHAR(45) NULL DEFAULT NULL,
102 `born` VARCHAR(45) NULL DEFAULT NULL,
103 `date_created` DATETIME NULL DEFAULT NULL,
104 `valid_until` DATETIME NULL DEFAULT NULL,
105 `image` LONGBLOB NULL DEFAULT NULL,
106 `discarded` VARCHAR(45) NULL DEFAULT NULL,
107 `notes` VARCHAR(300) NULL DEFAULT NULL,
108 PRIMARY KEY (`trainee_id`),
109 INDEX `gym_chain_id_idx` (`gym_chain_id` ASC),
110 CONSTRAINT `member_of_gym_chain`
111 FOREIGN KEY (`gym_chain_id`)
112 REFERENCES `gym_schema`.`gym_chain` (`gym_chain_id`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = utf8;
117
118
119-- -----------------------------------------------------
120-- Table `gym_schema`.`contract`
121-- -----------------------------------------------------
122CREATE TABLE IF NOT EXISTS `gym_schema`.`contract` (
123 `contract_id` INT(11) NOT NULL,
124 `coach_id` INT(11) NOT NULL,
125 `trainee_id` INT(11) NOT NULL,
126 `creation_date` DATETIME NOT NULL,
127 `expiry_date` DATETIME NOT NULL,
128 `discarded` VARCHAR(45) NULL DEFAULT NULL,
129 PRIMARY KEY (`contract_id`),
130 INDEX `coach_id_idx` (`coach_id` ASC),
131 INDEX `trainee_id_idx` (`trainee_id` ASC),
132 CONSTRAINT `coach_in_contract`
133 FOREIGN KEY (`coach_id`)
134 REFERENCES `gym_schema`.`coach` (`coach_id`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION,
137 CONSTRAINT `trainee_in_contact`
138 FOREIGN KEY (`trainee_id`)
139 REFERENCES `gym_schema`.`trainee` (`trainee_id`)
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION)
142ENGINE = InnoDB
143DEFAULT CHARACTER SET = utf8;
144
145
146-- -----------------------------------------------------
147-- Table `gym_schema`.`employee`
148-- -----------------------------------------------------
149CREATE TABLE IF NOT EXISTS `gym_schema`.`employee` (
150 `employee_id` INT(11) NOT NULL AUTO_INCREMENT,
151 `credentials_id` INT(11) NOT NULL,
152 `gym_chain_id` INT(11) NOT NULL,
153 `name` VARCHAR(45) NOT NULL,
154 `surname` VARCHAR(45) NOT NULL,
155 `discarded` TINYINT(4) NOT NULL,
156 PRIMARY KEY (`employee_id`),
157 INDEX `credentials_id_idx` (`credentials_id` ASC),
158 INDEX `gym_chain_id_idx` (`gym_chain_id` ASC),
159 CONSTRAINT `employee_credentials`
160 FOREIGN KEY (`credentials_id`)
161 REFERENCES `gym_schema`.`credentials` (`credentials_id`)
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION,
164 CONSTRAINT `works_at_gym_chain`
165 FOREIGN KEY (`gym_chain_id`)
166 REFERENCES `gym_schema`.`gym_chain` (`gym_chain_id`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB
170DEFAULT CHARACTER SET = utf8;
171
172
173-- -----------------------------------------------------
174-- Table `gym_schema`.`gym`
175-- -----------------------------------------------------
176CREATE TABLE IF NOT EXISTS `gym_schema`.`gym` (
177 `gym_id` INT(11) NOT NULL,
178 `gym_chain_id` INT(11) NOT NULL,
179 `location_latitude` DOUBLE NOT NULL,
180 `location_longitude` DOUBLE NOT NULL,
181 `street_address` VARCHAR(100) NOT NULL,
182 `discarded` TINYINT(4) NOT NULL,
183 PRIMARY KEY (`gym_id`),
184 INDEX `gym_chain_id_idx` (`gym_chain_id` ASC),
185 CONSTRAINT `gym_chain_id`
186 FOREIGN KEY (`gym_chain_id`)
187 REFERENCES `gym_schema`.`gym_chain` (`gym_chain_id`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION)
190ENGINE = InnoDB
191DEFAULT CHARACTER SET = utf8;
192
193
194-- -----------------------------------------------------
195-- Table `gym_schema`.`gym_entry`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `gym_schema`.`gym_entry` (
198 `gym_id` INT(11) NOT NULL,
199 `trainee_id` INT(11) NOT NULL,
200 `entry_time` DATETIME NULL DEFAULT NULL,
201 `exit_time` DATETIME NULL DEFAULT NULL,
202 INDEX `gym_id_idx` (`gym_id` ASC),
203 INDEX `trainee_id_idx` (`trainee_id` ASC),
204 CONSTRAINT `entry_by_trainee`
205 FOREIGN KEY (`trainee_id`)
206 REFERENCES `gym_schema`.`trainee` (`trainee_id`)
207 ON DELETE NO ACTION
208 ON UPDATE NO ACTION,
209 CONSTRAINT `entry_in_gym`
210 FOREIGN KEY (`gym_id`)
211 REFERENCES `gym_schema`.`gym` (`gym_id`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION)
214ENGINE = InnoDB
215DEFAULT CHARACTER SET = utf8;
216
217
218-- -----------------------------------------------------
219-- Table `gym_schema`.`training`
220-- -----------------------------------------------------
221CREATE TABLE IF NOT EXISTS `gym_schema`.`training` (
222 `trainining_id` INT(11) NOT NULL,
223 `contract_id` INT(11) NOT NULL,
224 `notes` VARCHAR(500) NULL DEFAULT NULL,
225 `date` DATETIME NULL DEFAULT NULL,
226 PRIMARY KEY (`trainining_id`),
227 INDEX `contract_id_idx` (`contract_id` ASC),
228 CONSTRAINT `belongs_to_contract`
229 FOREIGN KEY (`contract_id`)
230 REFERENCES `gym_schema`.`contract` (`contract_id`)
231 ON DELETE NO ACTION
232 ON UPDATE NO ACTION)
233ENGINE = InnoDB
234DEFAULT CHARACTER SET = utf8;
235
236
237SET SQL_MODE=@OLD_SQL_MODE;
238SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
239SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;