· 5 years ago · Feb 14, 2020, 12:54 PM
1-- MySQL Workbench Synchronization
2-- Generated: 2020-02-14 15:47
3-- Model: New Model
4-- Version: 1.0
5-- Project: Name of the project
6-- Author: denek
7
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @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';
11
12CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
13
14CREATE TABLE IF NOT EXISTS `mydb`.`pasport` (
15 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
16 `id_country` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
17 `series` CHAR(2) NULL DEFAULT NULL,
18 `number` INT(10) UNSIGNED NULL DEFAULT NULL,
19 `gender` ENUM('Мужской', 'Женский') NULL DEFAULT NULL,
20 `date_experation` DATE NULL DEFAULT NULL,
21 `date_receiving` DATE NULL DEFAULT NULL,
22 `identification_number` VARCHAR(16) NULL DEFAULT NULL,
23 `id_name1` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
24 `id_name2` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
25 `id_name3` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
26 PRIMARY KEY (`id`),
27 INDEX `tocountry_idx` (`id_country` ASC) VISIBLE,
28 INDEX `to_name2_idx` (`id_name2` ASC) VISIBLE,
29 INDEX `to_name1_idx` (`id_name1` ASC) VISIBLE,
30 INDEX `to_name3_idx` (`id_name3` ASC) VISIBLE,
31 CONSTRAINT `tocountry`
32 FOREIGN KEY (`id_country`)
33 REFERENCES `mydb`.`country` (`id`)
34 ON DELETE NO ACTION
35 ON UPDATE NO ACTION,
36 CONSTRAINT `to_name1`
37 FOREIGN KEY (`id_name1`)
38 REFERENCES `mydb`.`name1` (`id`)
39 ON DELETE NO ACTION
40 ON UPDATE NO ACTION,
41 CONSTRAINT `to_name2`
42 FOREIGN KEY (`id_name2`)
43 REFERENCES `mydb`.`name2` (`id`)
44 ON DELETE NO ACTION
45 ON UPDATE NO ACTION,
46 CONSTRAINT `to_name3`
47 FOREIGN KEY (`id_name3`)
48 REFERENCES `mydb`.`name3` (`id`)
49 ON DELETE NO ACTION
50 ON UPDATE NO ACTION)
51ENGINE = InnoDB
52DEFAULT CHARACTER SET = utf8;
53
54CREATE TABLE IF NOT EXISTS `mydb`.`storage` (
55 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
56 `id_car` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
57 `date` DATE NULL DEFAULT NULL,
58 PRIMARY KEY (`id`))
59ENGINE = InnoDB
60DEFAULT CHARACTER SET = utf8;
61
62CREATE TABLE IF NOT EXISTS `mydb`.`bank_account` (
63 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
64 `id_owner` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
65 `account_number` VARCHAR(45) NULL DEFAULT NULL,
66 `id_bank` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
67 `UNN` CHAR(9) NULL DEFAULT NULL,
68 PRIMARY KEY (`id`),
69 INDEX `toowner_idx` (`id_owner` ASC) VISIBLE,
70 INDEX `tobank_idx` (`id_bank` ASC) VISIBLE,
71 CONSTRAINT `toowner`
72 FOREIGN KEY (`id_owner`)
73 REFERENCES `mydb`.`customer` (`id`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION,
76 CONSTRAINT `tobank`
77 FOREIGN KEY (`id_bank`)
78 REFERENCES `mydb`.`bank` (`id`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION)
81ENGINE = InnoDB
82DEFAULT CHARACTER SET = utf8;
83
84CREATE TABLE IF NOT EXISTS `mydb`.`tarif` (
85 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
86 `id_storage` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
87 `city` DOUBLE UNSIGNED NULL DEFAULT NULL,
88 `no_city` DOUBLE UNSIGNED NULL DEFAULT NULL,
89 PRIMARY KEY (`id`),
90 INDEX `tostorage_idx` (`id_storage` ASC) VISIBLE,
91 CONSTRAINT `tostorage`
92 FOREIGN KEY (`id_storage`)
93 REFERENCES `mydb`.`storage` (`id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8;
98
99CREATE TABLE IF NOT EXISTS `mydb`.`car` (
100 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
101 `id_provider` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
102 `model` VARCHAR(50) NULL DEFAULT NULL,
103 `number` VARCHAR(10) NULL DEFAULT NULL,
104 PRIMARY KEY (`id`))
105ENGINE = InnoDB
106DEFAULT CHARACTER SET = utf8;
107
108CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
109 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
110 `id_name1` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
111 `id_name2` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
112 `id_name3` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
113 `id_passport` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
114 `id_discount_card` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
115 `id_credential` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
116 PRIMARY KEY (`id`),
117 INDEX `toname1_idx` (`id_name1` ASC) VISIBLE,
118 INDEX `toname2_idx` (`id_name2` ASC) VISIBLE,
119 INDEX `toname3_idx` (`id_name3` ASC) VISIBLE,
120 INDEX `topassport_idx` (`id_passport` ASC) VISIBLE,
121 INDEX `todiscount_idx` (`id_discount_card` ASC) VISIBLE,
122 INDEX `to_customer_credential_idx` (`id_credential` ASC) VISIBLE,
123 CONSTRAINT `toname1`
124 FOREIGN KEY (`id_name1`)
125 REFERENCES `mydb`.`name1` (`id`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION,
128 CONSTRAINT `toname2`
129 FOREIGN KEY (`id_name2`)
130 REFERENCES `mydb`.`name2` (`id`)
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION,
133 CONSTRAINT `toname3`
134 FOREIGN KEY (`id_name3`)
135 REFERENCES `mydb`.`name3` (`id`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION,
138 CONSTRAINT `topassport`
139 FOREIGN KEY (`id_passport`)
140 REFERENCES `mydb`.`pasport` (`id`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION,
143 CONSTRAINT `todiscount`
144 FOREIGN KEY (`id_discount_card`)
145 REFERENCES `mydb`.`discount_card` (`id`)
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION,
148 CONSTRAINT `to_customer_credential`
149 FOREIGN KEY (`id_credential`)
150 REFERENCES `mydb`.`credentials` (`id`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION)
153ENGINE = InnoDB
154DEFAULT CHARACTER SET = utf8;
155
156CREATE TABLE IF NOT EXISTS `mydb`.`sales` (
157 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
158 `id_customer` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
159 `id_storage` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
160 `id_payment` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
161 `ride_status` VARCHAR(45) NULL DEFAULT NULL,
162 `comment` VARCHAR(250) NULL DEFAULT NULL,
163 `date` TIMESTAMP NULL DEFAULT NULL,
164 `date1` TIMESTAMP NULL DEFAULT NULL,
165 PRIMARY KEY (`id`),
166 INDEX `tocustomer_idx` (`id_customer` ASC) VISIBLE,
167 INDEX `tostorage_idx` (`id_storage` ASC) VISIBLE,
168 INDEX `topayment_idx` (`id_payment` ASC) VISIBLE,
169 CONSTRAINT `tocustomer`
170 FOREIGN KEY (`id_customer`)
171 REFERENCES `mydb`.`customer` (`id`)
172 ON DELETE NO ACTION
173 ON UPDATE NO ACTION,
174 CONSTRAINT `tostorage`
175 FOREIGN KEY (`id_storage`)
176 REFERENCES `mydb`.`storage` (`id`)
177 ON DELETE NO ACTION
178 ON UPDATE NO ACTION,
179 CONSTRAINT `topayment`
180 FOREIGN KEY (`id_payment`)
181 REFERENCES `mydb`.`payment` (`id`)
182 ON DELETE NO ACTION
183 ON UPDATE NO ACTION)
184ENGINE = InnoDB
185DEFAULT CHARACTER SET = utf8;
186
187CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
188 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
189 `number` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
190 `cost` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
191 `date` TIMESTAMP NULL DEFAULT NULL,
192 PRIMARY KEY (`id`))
193ENGINE = InnoDB
194DEFAULT CHARACTER SET = utf8;
195
196CREATE TABLE IF NOT EXISTS `mydb`.`country` (
197 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
198 `country` VARCHAR(45) NULL DEFAULT NULL,
199 PRIMARY KEY (`id`))
200ENGINE = InnoDB
201DEFAULT CHARACTER SET = utf8;
202
203CREATE TABLE IF NOT EXISTS `mydb`.`address` (
204 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
205 `id_country` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
206 `id_region` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
207 `id_area` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
208 `is_street` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
209 `id_city` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
210 `street_type` ENUM('Улица', 'Переулок') NULL DEFAULT NULL,
211 `house` CHAR(5) NULL DEFAULT NULL,
212 `building` CHAR(1) NULL DEFAULT NULL,
213 `flat` CHAR(5) NULL DEFAULT NULL,
214 `phone_code` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
215 `phone` BIGINT(19) UNSIGNED NULL DEFAULT NULL,
216 PRIMARY KEY (`id`),
217 INDEX `tocountry_idx` (`id_country` ASC) VISIBLE,
218 INDEX `toregion_idx` (`id_region` ASC) VISIBLE,
219 INDEX `toarea_idx` (`id_area` ASC) VISIBLE,
220 INDEX `tostreet_idx` (`is_street` ASC) VISIBLE,
221 INDEX `tocity_idx` (`id_city` ASC) VISIBLE,
222 CONSTRAINT `tocountry`
223 FOREIGN KEY (`id_country`)
224 REFERENCES `mydb`.`country` (`id`)
225 ON DELETE NO ACTION
226 ON UPDATE NO ACTION,
227 CONSTRAINT `toregion`
228 FOREIGN KEY (`id_region`)
229 REFERENCES `mydb`.`region` (`id`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION,
232 CONSTRAINT `toarea`
233 FOREIGN KEY (`id_area`)
234 REFERENCES `mydb`.`arear` (`id`)
235 ON DELETE NO ACTION
236 ON UPDATE NO ACTION,
237 CONSTRAINT `tostreet`
238 FOREIGN KEY (`is_street`)
239 REFERENCES `mydb`.`street` (`id`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION,
242 CONSTRAINT `tocity`
243 FOREIGN KEY (`id_city`)
244 REFERENCES `mydb`.`city` (`id`)
245 ON DELETE NO ACTION
246 ON UPDATE NO ACTION)
247ENGINE = InnoDB
248DEFAULT CHARACTER SET = utf8;
249
250CREATE TABLE IF NOT EXISTS `mydb`.`region` (
251 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
252 `region` VARCHAR(45) NULL DEFAULT NULL,
253 PRIMARY KEY (`id`))
254ENGINE = InnoDB
255DEFAULT CHARACTER SET = utf8;
256
257CREATE TABLE IF NOT EXISTS `mydb`.`arear` (
258 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
259 `area` VARCHAR(45) NULL DEFAULT NULL,
260 PRIMARY KEY (`id`))
261ENGINE = InnoDB
262DEFAULT CHARACTER SET = utf8;
263
264CREATE TABLE IF NOT EXISTS `mydb`.`driver_rating` (
265 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
266 `description` VARCHAR(250) NULL DEFAULT NULL,
267 `rating` DOUBLE UNSIGNED NULL DEFAULT NULL,
268 PRIMARY KEY (`id`))
269ENGINE = InnoDB
270DEFAULT CHARACTER SET = utf8;
271
272CREATE TABLE IF NOT EXISTS `mydb`.`driver` (
273 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
274 `id_passport` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
275 `id_driver_rating` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
276 `id_car` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
277 `id_credential` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
278 PRIMARY KEY (`id`),
279 INDEX `topassport_idx` (`id_passport` ASC) VISIBLE,
280 INDEX `todriverrating_idx` (`id_driver_rating` ASC) VISIBLE,
281 INDEX `tocar_idx` (`id_car` ASC) VISIBLE,
282 INDEX `to_driver_credential_idx` (`id_credential` ASC) VISIBLE,
283 CONSTRAINT `topassport`
284 FOREIGN KEY (`id_passport`)
285 REFERENCES `mydb`.`pasport` (`id`)
286 ON DELETE NO ACTION
287 ON UPDATE NO ACTION,
288 CONSTRAINT `todriverrating`
289 FOREIGN KEY (`id_driver_rating`)
290 REFERENCES `mydb`.`driver_rating` (`id`)
291 ON DELETE NO ACTION
292 ON UPDATE NO ACTION,
293 CONSTRAINT `tocar`
294 FOREIGN KEY (`id_car`)
295 REFERENCES `mydb`.`car` (`id`)
296 ON DELETE NO ACTION
297 ON UPDATE NO ACTION,
298 CONSTRAINT `to_driver_credential`
299 FOREIGN KEY (`id_credential`)
300 REFERENCES `mydb`.`credentials` (`id`)
301 ON DELETE NO ACTION
302 ON UPDATE NO ACTION)
303ENGINE = InnoDB
304DEFAULT CHARACTER SET = utf8;
305
306CREATE TABLE IF NOT EXISTS `mydb`.`discount_card` (
307 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
308 `card_number` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
309 `discount_number` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
310 PRIMARY KEY (`id`))
311ENGINE = InnoDB
312DEFAULT CHARACTER SET = utf8;
313
314CREATE TABLE IF NOT EXISTS `mydb`.`street` (
315 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
316 `street` VARCHAR(45) NULL DEFAULT NULL,
317 PRIMARY KEY (`id`))
318ENGINE = InnoDB
319DEFAULT CHARACTER SET = utf8;
320
321CREATE TABLE IF NOT EXISTS `mydb`.`city` (
322 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
323 `city` VARCHAR(45) NULL DEFAULT NULL,
324 PRIMARY KEY (`id`))
325ENGINE = InnoDB
326DEFAULT CHARACTER SET = utf8;
327
328CREATE TABLE IF NOT EXISTS `mydb`.`name1` (
329 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
330 `name1` VARCHAR(80) NULL DEFAULT NULL,
331 PRIMARY KEY (`id`))
332ENGINE = InnoDB
333DEFAULT CHARACTER SET = utf8;
334
335CREATE TABLE IF NOT EXISTS `mydb`.`name2` (
336 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
337 `name2` VARCHAR(80) NULL DEFAULT NULL,
338 PRIMARY KEY (`id`))
339ENGINE = InnoDB
340DEFAULT CHARACTER SET = utf8;
341
342CREATE TABLE IF NOT EXISTS `mydb`.`name3` (
343 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
344 `name3` VARCHAR(80) NULL DEFAULT NULL,
345 PRIMARY KEY (`id`))
346ENGINE = InnoDB
347DEFAULT CHARACTER SET = utf8;
348
349CREATE TABLE IF NOT EXISTS `mydb`.`bank` (
350 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
351 `bank_name` VARCHAR(45) NULL DEFAULT NULL,
352 PRIMARY KEY (`id`))
353ENGINE = InnoDB
354DEFAULT CHARACTER SET = utf8;
355
356CREATE TABLE IF NOT EXISTS `mydb`.`credentials` (
357 `id` MEDIUMINT(8) UNSIGNED NOT NULL,
358 `login` VARCHAR(45) NULL DEFAULT NULL,
359 `pass` VARCHAR(45) NULL DEFAULT NULL,
360 `role` VARCHAR(45) NULL DEFAULT NULL,
361 PRIMARY KEY (`id`))
362ENGINE = InnoDB
363DEFAULT CHARACTER SET = utf8;
364
365
366SET SQL_MODE=@OLD_SQL_MODE;
367SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
368SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;