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