5xNZyJ5K

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 biker
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema biker
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `biker` DEFAULT CHARACTER SET utf8 ;
15USE `biker` ;
16
17-- -----------------------------------------------------
18-- Table `biker`.`addresses`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `biker`.`addresses` (
21  `id` INT NOT NULL AUTO_INCREMENT,
22  `street` VARCHAR(255) NOT NULL,
23  `number` INT NOT NULL,
24  `suffix` VARCHAR(6) NULL,
25  `postal_code` VARCHAR(6) NOT NULL,
26  `city` VARCHAR(255) NOT NULL,
27  `country` VARCHAR(255) NOT NULL DEFAULT 'Nederland',
28  PRIMARY KEY (`id`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `biker`.`users`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `biker`.`users` (
36  `id` INT NOT NULL AUTO_INCREMENT,
37  `username` VARCHAR(255) NOT NULL,
38  `password` VARCHAR(255) NOT NULL,
39  `firstname` VARCHAR(255) NOT NULL,
40  `lastname` VARCHAR(255) NOT NULL,
41  `sex` CHAR(1) NULL DEFAULT 'M',
42  `date_of_birth` DATE NULL,
43  `phonenumber` VARCHAR(20) NULL,
44  `addresses_id` INT NULL,
45  PRIMARY KEY (`id`),
46  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
47  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
48  INDEX `fk_users_addresses1_idx` (`addresses_id` ASC),
49  CONSTRAINT `fk_users_addresses1`
50    FOREIGN KEY (`addresses_id`)
51    REFERENCES `biker`.`addresses` (`id`)
52    ON DELETE SET NULL
53    ON UPDATE NO ACTION)
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `biker`.`roles`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `biker`.`roles` (
61  `id` INT NOT NULL AUTO_INCREMENT,
62  `name` VARCHAR(45) NOT NULL,
63  `description` VARCHAR(45) NULL,
64  PRIMARY KEY (`id`))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `biker`.`brands`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `biker`.`brands` (
72  `id` INT NOT NULL AUTO_INCREMENT,
73  `name` VARCHAR(255) NOT NULL,
74  `description` VARCHAR(255) NULL,
75  PRIMARY KEY (`id`))
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `biker`.`bike_types`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `biker`.`bike_types` (
83  `id` INT NOT NULL AUTO_INCREMENT,
84  `name` VARCHAR(255) NOT NULL,
85  `description` VARCHAR(255) NULL,
86  PRIMARY KEY (`id`))
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `biker`.`bikes`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `biker`.`bikes` (
94  `id` INT NOT NULL AUTO_INCREMENT,
95  `brands_id` INT NOT NULL,
96  `bike_types_id` INT NOT NULL,
97  `status` CHAR(1) NOT NULL DEFAULT 'A',
98  `version` CHAR(1) NOT NULL DEFAULT 'M',
99  `current_price` FLOAT NOT NULL,
100  `buy_price` FLOAT NOT NULL,
101  `repair_date` DATETIME NULL,
102  PRIMARY KEY (`id`),
103  INDEX `fk_bikes_brands_idx` (`brands_id` ASC),
104  INDEX `fk_bikes_bike_types1_idx` (`bike_types_id` ASC),
105  CONSTRAINT `fk_bikes_brands`
106    FOREIGN KEY (`brands_id`)
107    REFERENCES `biker`.`brands` (`id`)
108    ON DELETE CASCADE
109    ON UPDATE NO ACTION,
110  CONSTRAINT `fk_bikes_bike_types1`
111    FOREIGN KEY (`bike_types_id`)
112    REFERENCES `biker`.`bike_types` (`id`)
113    ON DELETE CASCADE
114    ON UPDATE NO ACTION)
115ENGINE = InnoDB;
116
117
118-- -----------------------------------------------------
119-- Table `biker`.`users_has_roles`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `biker`.`users_has_roles` (
122  `users_id` INT NOT NULL,
123  `roles_id` INT NOT NULL,
124  PRIMARY KEY (`users_id`, `roles_id`),
125  INDEX `fk_users_has_roles_roles1_idx` (`roles_id` ASC),
126  INDEX `fk_users_has_roles_users1_idx` (`users_id` ASC),
127  CONSTRAINT `fk_users_has_roles_users1`
128    FOREIGN KEY (`users_id`)
129    REFERENCES `biker`.`users` (`id`)
130    ON DELETE CASCADE
131    ON UPDATE NO ACTION,
132  CONSTRAINT `fk_users_has_roles_roles1`
133    FOREIGN KEY (`roles_id`)
134    REFERENCES `biker`.`roles` (`id`)
135    ON DELETE NO ACTION
136    ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `biker`.`accessories_types`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `biker`.`accessories_types` (
144  `id` INT NOT NULL AUTO_INCREMENT,
145  `name` VARCHAR(255) NOT NULL,
146  `description` VARCHAR(255) NULL,
147  PRIMARY KEY (`id`))
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `biker`.`accessories`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `biker`.`accessories` (
155  `id` INT NOT NULL AUTO_INCREMENT,
156  `brands_id` INT NOT NULL,
157  `accessories_types_id` INT NOT NULL,
158  `name` VARCHAR(255) NOT NULL,
159  `current_price` FLOAT NOT NULL,
160  `buy_price` FLOAT NOT NULL,
161  PRIMARY KEY (`id`),
162  INDEX `fk_accessories_brands1_idx` (`brands_id` ASC),
163  INDEX `fk_accessories_accessories_types1_idx` (`accessories_types_id` ASC),
164  CONSTRAINT `fk_accessories_brands1`
165    FOREIGN KEY (`brands_id`)
166    REFERENCES `biker`.`brands` (`id`)
167    ON DELETE NO ACTION
168    ON UPDATE NO ACTION,
169  CONSTRAINT `fk_accessories_accessories_types1`
170    FOREIGN KEY (`accessories_types_id`)
171    REFERENCES `biker`.`accessories_types` (`id`)
172    ON DELETE NO ACTION
173    ON UPDATE NO ACTION)
174ENGINE = InnoDB;
175
176
177-- -----------------------------------------------------
178-- Table `biker`.`payment_methods`
179-- -----------------------------------------------------
180CREATE TABLE IF NOT EXISTS `biker`.`payment_methods` (
181  `id` INT NOT NULL AUTO_INCREMENT,
182  `name` VARCHAR(255) NOT NULL,
183  PRIMARY KEY (`id`))
184ENGINE = InnoDB;
185
186
187-- -----------------------------------------------------
188-- Table `biker`.`payments`
189-- -----------------------------------------------------
190CREATE TABLE IF NOT EXISTS `biker`.`payments` (
191  `id` INT NOT NULL AUTO_INCREMENT,
192  `payment_methods_id` INT NOT NULL,
193  `amount` FLOAT NOT NULL,
194  PRIMARY KEY (`id`),
195  INDEX `fk_payments_payment_methods1_idx` (`payment_methods_id` ASC),
196  CONSTRAINT `fk_payments_payment_methods1`
197    FOREIGN KEY (`payment_methods_id`)
198    REFERENCES `biker`.`payment_methods` (`id`)
199    ON DELETE NO ACTION
200    ON UPDATE NO ACTION)
201ENGINE = InnoDB;
202
203
204-- -----------------------------------------------------
205-- Table `biker`.`rent_agreements`
206-- -----------------------------------------------------
207CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements` (
208  `id` INT NOT NULL AUTO_INCREMENT,
209  `users_id` INT NOT NULL,
210  `agreement_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
211  `status` CHAR(1) NOT NULL DEFAULT 'R',
212  `policy_number` VARCHAR(255) NOT NULL,
213  `start_date` DATETIME NOT NULL,
214  `end_date` DATETIME NOT NULL,
215  `price` FLOAT NOT NULL,
216  `deposit` FLOAT NOT NULL,
217  `payments_id` INT NULL,
218  PRIMARY KEY (`id`, `users_id`),
219  INDEX `fk_rent_agreements_payments1_idx` (`payments_id` ASC),
220  INDEX `fk_rent_agreements_users1_idx` (`users_id` ASC),
221  CONSTRAINT `fk_rent_agreements_payments1`
222    FOREIGN KEY (`payments_id`)
223    REFERENCES `biker`.`payments` (`id`)
224    ON DELETE NO ACTION
225    ON UPDATE NO ACTION,
226  CONSTRAINT `fk_rent_agreements_users1`
227    FOREIGN KEY (`users_id`)
228    REFERENCES `biker`.`users` (`id`)
229    ON DELETE NO ACTION
230    ON UPDATE NO ACTION)
231ENGINE = InnoDB;
232
233
234-- -----------------------------------------------------
235-- Table `biker`.`rent_agreements_has_bikes`
236-- -----------------------------------------------------
237CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements_has_bikes` (
238  `rent_agreements_id` INT NOT NULL,
239  `bikes_id` INT NOT NULL,
240  PRIMARY KEY (`rent_agreements_id`, `bikes_id`),
241  INDEX `fk_rent_agreements_has_bikes_bikes1_idx` (`bikes_id` ASC),
242  INDEX `fk_rent_agreements_has_bikes_rent_agreements1_idx` (`rent_agreements_id` ASC),
243  CONSTRAINT `fk_rent_agreements_has_bikes_rent_agreements1`
244    FOREIGN KEY (`rent_agreements_id`)
245    REFERENCES `biker`.`rent_agreements` (`id`)
246    ON DELETE NO ACTION
247    ON UPDATE NO ACTION,
248  CONSTRAINT `fk_rent_agreements_has_bikes_bikes1`
249    FOREIGN KEY (`bikes_id`)
250    REFERENCES `biker`.`bikes` (`id`)
251    ON DELETE NO ACTION
252    ON UPDATE NO ACTION)
253ENGINE = InnoDB;
254
255
256-- -----------------------------------------------------
257-- Table `biker`.`rent_agreements_has_accessories`
258-- -----------------------------------------------------
259CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements_has_accessories` (
260  `rent_agreements_id` INT NOT NULL,
261  `accessories_id` INT NOT NULL,
262  PRIMARY KEY (`rent_agreements_id`, `accessories_id`),
263  INDEX `fk_rent_agreements_has_accessories_accessories1_idx` (`accessories_id` ASC),
264  INDEX `fk_rent_agreements_has_accessories_rent_agreements1_idx` (`rent_agreements_id` ASC),
265  CONSTRAINT `fk_rent_agreements_has_accessories_rent_agreements1`
266    FOREIGN KEY (`rent_agreements_id`)
267    REFERENCES `biker`.`rent_agreements` (`id`)
268    ON DELETE CASCADE
269    ON UPDATE NO ACTION,
270  CONSTRAINT `fk_rent_agreements_has_accessories_accessories1`
271    FOREIGN KEY (`accessories_id`)
272    REFERENCES `biker`.`accessories` (`id`)
273    ON DELETE CASCADE
274    ON UPDATE NO ACTION)
275ENGINE = InnoDB;
276
277
278SET SQL_MODE=@OLD_SQL_MODE;
279SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
280SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;