· 6 years ago · May 15, 2019, 08:46 AM
1--
2-- Script was generated by Devart dbForge Studio 2019 for MySQL, Version 8.1.45.0
3-- Product home page: http://www.devart.com/dbforge/mysql/studio
4-- Script date 5/15/2019 11:34:20 AM
5-- Server version: 5.5.5-10.1.39-MariaDB
6-- Client version: 4.1
7--
8
9--
10-- Disable foreign keys
11--
12/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13
14--
15-- Set SQL mode
16--
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18
19--
20-- Set character set the client will use to send SQL statements to the server
21--
22SET NAMES 'utf8';
23
24--
25-- Set default database
26--
27USE sport_db_test;
28
29--
30-- Drop table `bill`
31--
32DROP TABLE IF EXISTS bill;
33
34--
35-- Drop table `bank_card`
36--
37DROP TABLE IF EXISTS bank_card;
38
39--
40-- Drop table `bank`
41--
42DROP TABLE IF EXISTS bank;
43
44--
45-- Drop table `user_information`
46--
47DROP TABLE IF EXISTS user_information;
48
49--
50-- Drop table `gender`
51--
52DROP TABLE IF EXISTS gender;
53
54--
55-- Drop table `order_lessons`
56--
57DROP TABLE IF EXISTS order_lessons;
58
59--
60-- Drop table `order`
61--
62DROP TABLE IF EXISTS `order`;
63
64--
65-- Drop table `order_status`
66--
67DROP TABLE IF EXISTS order_status;
68
69--
70-- Drop table `certificate`
71--
72DROP TABLE IF EXISTS certificate;
73
74--
75-- Drop table `user`
76--
77DROP TABLE IF EXISTS user;
78
79--
80-- Drop table `role`
81--
82DROP TABLE IF EXISTS role;
83
84--
85-- Drop table `ordered_lesson`
86--
87DROP TABLE IF EXISTS ordered_lesson;
88
89--
90-- Drop table `lesson`
91--
92DROP TABLE IF EXISTS lesson;
93
94--
95-- Drop table `lesson_category`
96--
97DROP TABLE IF EXISTS lesson_category;
98
99--
100-- Drop table `company`
101--
102DROP TABLE IF EXISTS company;
103
104--
105-- Drop table `address`
106--
107DROP TABLE IF EXISTS address;
108
109--
110-- Drop table `street`
111--
112DROP TABLE IF EXISTS street;
113
114--
115-- Drop table `city`
116--
117DROP TABLE IF EXISTS city;
118
119--
120-- Drop table `country`
121--
122DROP TABLE IF EXISTS country;
123
124--
125-- Set default database
126--
127USE sport_db_test;
128
129--
130-- Create table `country`
131--
132CREATE TABLE country (
133 id INT(11) NOT NULL AUTO_INCREMENT,
134 name VARCHAR(255) NOT NULL,
135 code VARCHAR(255) NOT NULL,
136 PRIMARY KEY (id)
137)
138ENGINE = INNODB,
139AUTO_INCREMENT = 3,
140AVG_ROW_LENGTH = 8192,
141CHARACTER SET utf8,
142COLLATE utf8_unicode_ci;
143
144--
145-- Create table `city`
146--
147CREATE TABLE city (
148 id INT(11) NOT NULL AUTO_INCREMENT,
149 name VARCHAR(255) NOT NULL,
150 countryId INT(11) NOT NULL,
151 PRIMARY KEY (id)
152)
153ENGINE = INNODB,
154AUTO_INCREMENT = 2,
155AVG_ROW_LENGTH = 16384,
156CHARACTER SET utf8,
157COLLATE utf8_unicode_ci;
158
159--
160-- Create foreign key
161--
162ALTER TABLE city
163 ADD CONSTRAINT FK_city_countryId FOREIGN KEY (countryId)
164 REFERENCES country(id) ON DELETE NO ACTION;
165
166--
167-- Create table `street`
168--
169CREATE TABLE street (
170 id INT(11) NOT NULL AUTO_INCREMENT,
171 name VARCHAR(255) NOT NULL,
172 cityId INT(11) NOT NULL,
173 PRIMARY KEY (id)
174)
175ENGINE = INNODB,
176AUTO_INCREMENT = 3,
177AVG_ROW_LENGTH = 16384,
178CHARACTER SET utf8,
179COLLATE utf8_unicode_ci;
180
181--
182-- Create foreign key
183--
184ALTER TABLE street
185 ADD CONSTRAINT FK_street_cityId FOREIGN KEY (cityId)
186 REFERENCES city(id) ON DELETE NO ACTION;
187
188--
189-- Create table `address`
190--
191CREATE TABLE address (
192 id INT(11) NOT NULL AUTO_INCREMENT,
193 street_id INT(11) NOT NULL,
194 number INT(11) NOT NULL,
195 PRIMARY KEY (id)
196)
197ENGINE = INNODB,
198AUTO_INCREMENT = 3,
199AVG_ROW_LENGTH = 16384,
200CHARACTER SET utf8,
201COLLATE utf8_unicode_ci;
202
203--
204-- Create foreign key
205--
206ALTER TABLE address
207 ADD CONSTRAINT FK_address_street_id FOREIGN KEY (street_id)
208 REFERENCES street(id) ON DELETE NO ACTION;
209
210--
211-- Create table `company`
212--
213CREATE TABLE company (
214 id INT(11) NOT NULL AUTO_INCREMENT,
215 Title VARCHAR(255) NOT NULL,
216 CompanyCode VARCHAR(255) NOT NULL,
217 AddressId INT(11) NOT NULL,
218 PRIMARY KEY (id)
219)
220ENGINE = INNODB,
221AUTO_INCREMENT = 2,
222AVG_ROW_LENGTH = 16384,
223CHARACTER SET utf8,
224COLLATE utf8_unicode_ci;
225
226--
227-- Create foreign key
228--
229ALTER TABLE company
230 ADD CONSTRAINT FK_company_AddressId FOREIGN KEY (AddressId)
231 REFERENCES address(id) ON DELETE NO ACTION;
232
233--
234-- Create table `lesson_category`
235--
236CREATE TABLE lesson_category (
237 id INT(11) NOT NULL AUTO_INCREMENT,
238 Title VARCHAR(255) NOT NULL,
239 PRIMARY KEY (id)
240)
241ENGINE = INNODB,
242AUTO_INCREMENT = 3,
243AVG_ROW_LENGTH = 8192,
244CHARACTER SET utf8,
245COLLATE utf8_unicode_ci;
246
247--
248-- Create table `lesson`
249--
250CREATE TABLE lesson (
251 id INT(11) NOT NULL AUTO_INCREMENT,
252 Title VARCHAR(255) NOT NULL,
253 AvailableDate DATE NOT NULL,
254 Price DECIMAL(19, 2) NOT NULL,
255 AddressId INT(11) NOT NULL,
256 LessonCategoryId INT(11) NOT NULL,
257 PRIMARY KEY (id)
258)
259ENGINE = INNODB,
260CHARACTER SET utf8,
261COLLATE utf8_unicode_ci;
262
263--
264-- Create foreign key
265--
266ALTER TABLE lesson
267 ADD CONSTRAINT FK_lesson_AddressId FOREIGN KEY (AddressId)
268 REFERENCES address(id) ON DELETE NO ACTION;
269
270--
271-- Create foreign key
272--
273ALTER TABLE lesson
274 ADD CONSTRAINT FK_lesson_LessonCategoryId FOREIGN KEY (LessonCategoryId)
275 REFERENCES lesson_category(id) ON DELETE NO ACTION;
276
277--
278-- Create table `ordered_lesson`
279--
280CREATE TABLE ordered_lesson (
281 id INT(11) NOT NULL AUTO_INCREMENT,
282 ArrivalDateTime DATETIME NOT NULL,
283 LeavingDateTime DATETIME NOT NULL,
284 LessonId INT(11) NOT NULL,
285 PRIMARY KEY (id)
286)
287ENGINE = INNODB,
288CHARACTER SET utf8,
289COLLATE utf8_unicode_ci;
290
291--
292-- Create foreign key
293--
294ALTER TABLE ordered_lesson
295 ADD CONSTRAINT FK_ordered_lesson_LessonId FOREIGN KEY (LessonId)
296 REFERENCES lesson(id) ON DELETE NO ACTION;
297
298--
299-- Create table `role`
300--
301CREATE TABLE role (
302 id INT(11) NOT NULL AUTO_INCREMENT,
303 title VARCHAR(255) NOT NULL,
304 PRIMARY KEY (id)
305)
306ENGINE = INNODB,
307AUTO_INCREMENT = 4,
308AVG_ROW_LENGTH = 5461,
309CHARACTER SET utf8,
310COLLATE utf8_unicode_ci;
311
312--
313-- Create table `user`
314--
315CREATE TABLE user (
316 id INT(11) NOT NULL AUTO_INCREMENT,
317 Username VARCHAR(255) NOT NULL,
318 Password VARCHAR(255) NOT NULL,
319 role_id INT(11) NOT NULL,
320 PRIMARY KEY (id)
321)
322ENGINE = INNODB,
323AUTO_INCREMENT = 6,
324AVG_ROW_LENGTH = 16384,
325CHARACTER SET utf8,
326COLLATE utf8_unicode_ci;
327
328--
329-- Create index `Username` on table `user`
330--
331ALTER TABLE user
332 ADD UNIQUE INDEX Username(Username);
333
334--
335-- Create foreign key
336--
337ALTER TABLE user
338 ADD CONSTRAINT FK_user_role_id FOREIGN KEY (role_id)
339 REFERENCES role(id) ON DELETE NO ACTION;
340
341--
342-- Create table `certificate`
343--
344CREATE TABLE certificate (
345 id INT(11) NOT NULL AUTO_INCREMENT,
346 Title VARCHAR(255) NOT NULL,
347 CertificateNr VARCHAR(255) NOT NULL,
348 IssueDate DATE NOT NULL,
349 UserId INT(11) NOT NULL,
350 CompanyId INT(11) NOT NULL,
351 PRIMARY KEY (id)
352)
353ENGINE = INNODB,
354CHARACTER SET utf8,
355COLLATE utf8_unicode_ci;
356
357--
358-- Create foreign key
359--
360ALTER TABLE certificate
361 ADD CONSTRAINT FK_certificate_CompanyId FOREIGN KEY (CompanyId)
362 REFERENCES company(id) ON DELETE NO ACTION;
363
364--
365-- Create foreign key
366--
367ALTER TABLE certificate
368 ADD CONSTRAINT FK_certificate_UserId FOREIGN KEY (UserId)
369 REFERENCES user(id) ON DELETE NO ACTION;
370
371--
372-- Create table `order_status`
373--
374CREATE TABLE order_status (
375 id INT(11) NOT NULL AUTO_INCREMENT,
376 Title VARCHAR(255) NOT NULL,
377 Comment VARCHAR(255) NOT NULL,
378 PRIMARY KEY (id)
379)
380ENGINE = INNODB,
381CHARACTER SET utf8,
382COLLATE utf8_unicode_ci;
383
384--
385-- Create table `order`
386--
387CREATE TABLE `order` (
388 id INT(11) NOT NULL,
389 OrderNumber VARCHAR(255) NOT NULL,
390 OrderDate DATETIME NOT NULL,
391 OrderStatusId INT(11) NOT NULL,
392 UserId INT(11) NOT NULL
393)
394ENGINE = INNODB,
395CHARACTER SET utf8,
396COLLATE utf8_unicode_ci;
397
398--
399-- Create index `UK_order_id` on table `order`
400--
401ALTER TABLE `order`
402 ADD UNIQUE INDEX UK_order_id(id);
403
404--
405-- Create foreign key
406--
407ALTER TABLE `order`
408 ADD CONSTRAINT FK_order_OrderStatusId FOREIGN KEY (OrderStatusId)
409 REFERENCES order_status(id) ON DELETE NO ACTION;
410
411--
412-- Create foreign key
413--
414ALTER TABLE `order`
415 ADD CONSTRAINT FK_order_UserId FOREIGN KEY (UserId)
416 REFERENCES user(id) ON DELETE NO ACTION;
417
418--
419-- Create table `order_lessons`
420--
421CREATE TABLE order_lessons (
422 id INT(11) NOT NULL AUTO_INCREMENT,
423 OrderId INT(11) NOT NULL,
424 OrderedLessonId INT(11) NOT NULL,
425 PRIMARY KEY (id)
426)
427ENGINE = INNODB,
428CHARACTER SET utf8,
429COLLATE utf8_unicode_ci;
430
431--
432-- Create foreign key
433--
434ALTER TABLE order_lessons
435 ADD CONSTRAINT FK_order_lessons_OrderId FOREIGN KEY (OrderId)
436 REFERENCES `order`(id) ON DELETE NO ACTION;
437
438--
439-- Create foreign key
440--
441ALTER TABLE order_lessons
442 ADD CONSTRAINT FK_order_lessons_OrderedLessonId FOREIGN KEY (OrderedLessonId)
443 REFERENCES ordered_lesson(id) ON DELETE NO ACTION;
444
445--
446-- Create table `gender`
447--
448CREATE TABLE gender (
449 id INT(11) NOT NULL AUTO_INCREMENT,
450 title VARCHAR(255) NOT NULL,
451 PRIMARY KEY (id)
452)
453ENGINE = INNODB,
454AUTO_INCREMENT = 4,
455AVG_ROW_LENGTH = 5461,
456CHARACTER SET utf8,
457COLLATE utf8_unicode_ci;
458
459--
460-- Create table `user_information`
461--
462CREATE TABLE user_information (
463 UserId INT(11) NOT NULL,
464 FirstName VARCHAR(255) NOT NULL,
465 LastName VARCHAR(255) NOT NULL,
466 BirthDate DATE DEFAULT NULL,
467 Email VARCHAR(50) NOT NULL,
468 PhoneNumber VARCHAR(255) DEFAULT NULL,
469 genderId INT(11) NOT NULL,
470 PRIMARY KEY (UserId)
471)
472ENGINE = INNODB,
473AVG_ROW_LENGTH = 16384,
474CHARACTER SET utf8,
475COLLATE utf8_unicode_ci;
476
477--
478-- Create foreign key
479--
480ALTER TABLE user_information
481 ADD CONSTRAINT FK_user_information_genderId FOREIGN KEY (genderId)
482 REFERENCES gender(id) ON DELETE NO ACTION;
483
484--
485-- Create foreign key
486--
487ALTER TABLE user_information
488 ADD CONSTRAINT FK_userinformation_UserId FOREIGN KEY (UserId)
489 REFERENCES user(id) ON DELETE NO ACTION;
490
491--
492-- Create table `bank`
493--
494CREATE TABLE bank (
495 id INT(11) NOT NULL AUTO_INCREMENT,
496 Title VARCHAR(255) NOT NULL,
497 SWITFT VARCHAR(255) NOT NULL,
498 PRIMARY KEY (id)
499)
500ENGINE = INNODB,
501AUTO_INCREMENT = 2,
502AVG_ROW_LENGTH = 16384,
503CHARACTER SET utf8,
504COLLATE utf8_unicode_ci;
505
506--
507-- Create table `bank_card`
508--
509CREATE TABLE bank_card (
510 id INT(11) NOT NULL AUTO_INCREMENT,
511 CardNr VARCHAR(255) NOT NULL,
512 ValidFrom DATE NOT NULL,
513 ExpreEnd DATE NOT NULL,
514 CVV TINYINT(4) NOT NULL,
515 BankId INT(11) NOT NULL,
516 UserId INT(11) NOT NULL,
517 PRIMARY KEY (id)
518)
519ENGINE = INNODB,
520CHARACTER SET utf8,
521COLLATE utf8_unicode_ci;
522
523--
524-- Create foreign key
525--
526ALTER TABLE bank_card
527 ADD CONSTRAINT FK_bank_card_BankId FOREIGN KEY (BankId)
528 REFERENCES bank(id) ON DELETE NO ACTION;
529
530--
531-- Create foreign key
532--
533ALTER TABLE bank_card
534 ADD CONSTRAINT FK_bank_card_UserId FOREIGN KEY (UserId)
535 REFERENCES user(id) ON DELETE NO ACTION;
536
537--
538-- Create table `bill`
539--
540CREATE TABLE bill (
541 id INT(11) NOT NULL,
542 CreationDate DATETIME NOT NULL,
543 BillCode VARCHAR(255) NOT NULL,
544 OrderId INT(11) NOT NULL
545)
546ENGINE = INNODB,
547CHARACTER SET utf8,
548COLLATE utf8_unicode_ci;
549
550--
551-- Dumping data for table country
552--
553INSERT INTO country VALUES
554(1, 'Lithuania', 'LT'),
555(2, 'Sweeden', 'SE');
556
557--
558-- Dumping data for table city
559--
560INSERT INTO city VALUES
561(1, 'Vilnius', 1);
562
563--
564-- Dumping data for table street
565--
566INSERT INTO street VALUES
567(2, 'Å vitrigailos', 1);
568
569--
570-- Dumping data for table lesson_category
571--
572INSERT INTO lesson_category VALUES
573(1, 'Basketball'),
574(2, 'Football');
575
576--
577-- Dumping data for table address
578--
579INSERT INTO address VALUES
580(2, 2, 15);
581
582--
583-- Dumping data for table role
584--
585INSERT INTO role VALUES
586(1, 'customer'),
587(2, 'admin'),
588(3, 'trainer');
589
590--
591-- Dumping data for table lesson
592--
593-- Table sport_db_test.lesson does not contain any data (it is empty)
594
595--
596-- Dumping data for table user
597--
598INSERT INTO user VALUES
599(5, 'admin', 'admin', 2);
600
601--
602-- Dumping data for table order_status
603--
604-- Table sport_db_test.order_status does not contain any data (it is empty)
605
606--
607-- Dumping data for table gender
608--
609INSERT INTO gender VALUES
610(1, 'male'),
611(2, 'female'),
612(3, 'other');
613
614--
615-- Dumping data for table ordered_lesson
616--
617-- Table sport_db_test.ordered_lesson does not contain any data (it is empty)
618
619--
620-- Dumping data for table `order`
621--
622-- Table sport_db_test.`order` does not contain any data (it is empty)
623
624--
625-- Dumping data for table company
626--
627INSERT INTO company VALUES
628(1, 'Udemy', 'UDEM123', 2);
629
630--
631-- Dumping data for table bank
632--
633INSERT INTO bank VALUES
634(1, 'Swedbank', 'SW123434');
635
636--
637-- Dumping data for table user_information
638--
639INSERT INTO user_information VALUES
640(5, 'Lukas', 'Pabijonavicius', '2019-05-08', 'sadsada', 'sadsadasd', 1);
641
642--
643-- Dumping data for table order_lessons
644--
645-- Table sport_db_test.order_lessons does not contain any data (it is empty)
646
647--
648-- Dumping data for table certificate
649--
650-- Table sport_db_test.certificate does not contain any data (it is empty)
651
652--
653-- Dumping data for table bill
654--
655-- Table sport_db_test.bill does not contain any data (it is empty)
656
657--
658-- Dumping data for table bank_card
659--
660-- Table sport_db_test.bank_card does not contain any data (it is empty)
661
662--
663-- Restore previous SQL mode
664--
665/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
666
667--
668-- Enable foreign keys
669--
670/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;