· 5 years ago · Mar 22, 2020, 05:14 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`customer`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
21 `id` INT NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(45) NOT NULL,
23 `birth_date` DATE NOT NULL,
24 `driving_license_number` VARCHAR(45) NOT NULL,
25 `driving_license_issue_dt` DATE NOT NULL,
26 PRIMARY KEY (`id`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`location`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`location` (
34 `id` INT NOT NULL AUTO_INCREMENT,
35 `city` VARCHAR(45) NOT NULL,
36 `voivodeship` VARCHAR(45) NOT NULL,
37 `zip` VARCHAR(45) NOT NULL,
38 `street_address` VARCHAR(45) NOT NULL,
39 PRIMARY KEY (`id`))
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `mydb`.`car_category`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `mydb`.`car_category` (
47 `id` INT NOT NULL,
48 `name` VARCHAR(45) NOT NULL,
49 `rental_value` DECIMAL NOT NULL,
50 PRIMARY KEY (`id`))
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `mydb`.`car`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `mydb`.`car` (
58 `id` INT NOT NULL,
59 `brand` VARCHAR(45) NOT NULL,
60 `model` VARCHAR(45) NOT NULL,
61 `production_year` INT NOT NULL,
62 `mileage` INT NOT NULL,
63 `color` VARCHAR(45) NOT NULL,
64 `car_category_id` INT NOT NULL,
65 `location_id` INT NOT NULL,
66 PRIMARY KEY (`id`, `car_category_id`, `location_id`),
67 INDEX `fk_car_car_category1_idx` (`car_category_id` ASC) ,
68 INDEX `fk_car_location1_idx` (`location_id` ASC) ,
69 CONSTRAINT `fk_car_car_category1`
70 FOREIGN KEY (`car_category_id`)
71 REFERENCES `mydb`.`car_category` (`id`)
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION,
74 CONSTRAINT `fk_car_location1`
75 FOREIGN KEY (`location_id`)
76 REFERENCES `mydb`.`location` (`id`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `mydb`.`fuel_option`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `mydb`.`fuel_option` (
86 `id` INT NOT NULL,
87 `description` VARCHAR(45) NOT NULL,
88 PRIMARY KEY (`id`))
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `mydb`.`insurance`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `mydb`.`insurance` (
96 `id` INT NOT NULL,
97 `name` VARCHAR(45) NOT NULL,
98 `description` TEXT NOT NULL,
99 `cost` DECIMAL NOT NULL,
100 PRIMARY KEY (`id`))
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `mydb`.`rental`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `mydb`.`rental` (
108 `id` INT NOT NULL,
109 `customer_id` INT NOT NULL,
110 `car_id` INT NOT NULL,
111 `fuel_option_id` INT NOT NULL,
112 `pick_up_location_id` INT NOT NULL,
113 `drop_off_location_id` INT NOT NULL,
114 `start_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIME,
115 `end_date` TIMESTAMP NULL,
116 `remarks` TEXT NULL,
117 PRIMARY KEY (`id`, `customer_id`, `car_id`, `fuel_option_id`, `pick_up_location_id`, `drop_off_location_id`),
118 INDEX `fk_rental_customer_idx` (`customer_id` ASC) ,
119 INDEX `fk_rental_car1_idx` (`car_id` ASC) ,
120 INDEX `fk_rental_fuel_option1_idx` (`fuel_option_id` ASC) ,
121 INDEX `fk_rental_location1_idx` (`pick_up_location_id` ASC) ,
122 INDEX `fk_rental_location2_idx` (`drop_off_location_id` ASC),
123 CONSTRAINT `fk_rental_customer`
124 FOREIGN KEY (`customer_id`)
125 REFERENCES `mydb`.`customer` (`id`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION,
128 CONSTRAINT `fk_rental_car1`
129 FOREIGN KEY (`car_id`)
130 REFERENCES `mydb`.`car` (`id`)
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION,
133 CONSTRAINT `fk_rental_fuel_option1`
134 FOREIGN KEY (`fuel_option_id`)
135 REFERENCES `mydb`.`fuel_option` (`id`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION,
138 CONSTRAINT `fk_rental_location1`
139 FOREIGN KEY (`pick_up_location_id`)
140 REFERENCES `mydb`.`location` (`id`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION,
143 CONSTRAINT `fk_rental_location2`
144 FOREIGN KEY (`drop_off_location_id`)
145 REFERENCES `mydb`.`location` (`id`)
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `mydb`.`rental_invoice`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `mydb`.`rental_invoice` (
155 `id` INT NOT NULL AUTO_INCREMENT,
156 `car_rent` DECIMAL NOT NULL,
157 `insurance_cost_total` DECIMAL NULL,
158 `VAT` DECIMAL NOT NULL,
159 `total_amount_payable` DECIMAL NOT NULL,
160 `discount_amount` DECIMAL NULL,
161 `rental_id` INT NOT NULL,
162 PRIMARY KEY (`id`, `rental_id`),
163 INDEX `fk_rental_invoice_rental1_idx` (`rental_id` ASC) ,
164 CONSTRAINT `fk_rental_invoice_rental1`
165 FOREIGN KEY (`rental_id`)
166 REFERENCES `mydb`.`rental` (`id`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB;
170
171
172-- -----------------------------------------------------
173-- Table `mydb`.`insurance_has_rental`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `mydb`.`insurance_has_rental` (
176 `insurance_id` INT NOT NULL,
177 `rental_id` INT NOT NULL,
178 PRIMARY KEY (`insurance_id`, `rental_id`),
179 INDEX `fk_insurance_has_rental_rental1_idx` (`rental_id` ASC) ,
180 INDEX `fk_insurance_has_rental_insurance1_idx` (`insurance_id` ASC) ,
181 CONSTRAINT `fk_insurance_has_rental_insurance1`
182 FOREIGN KEY (`insurance_id`)
183 REFERENCES `mydb`.`insurance` (`id`)
184 ON DELETE NO ACTION
185 ON UPDATE NO ACTION,
186 CONSTRAINT `fk_insurance_has_rental_rental1`
187 FOREIGN KEY (`rental_id`)
188 REFERENCES `mydb`.`rental` (`id`)
189 ON DELETE NO ACTION
190 ON UPDATE NO ACTION)
191ENGINE = InnoDB;
192
193
194SET SQL_MODE=@OLD_SQL_MODE;
195SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
196SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;