· 7 years ago · Dec 04, 2018, 07:20 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Dec 1 12:52:27 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema BudgetCarRental
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema BudgetCarRental
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `BudgetCarRental` DEFAULT CHARACTER SET utf8 ;
18USE `BudgetCarRental` ;
19
20-- -----------------------------------------------------
21-- Table `BudgetCarRental`.`Marca`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `BudgetCarRental`.`Marca` ;
24
25CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Marca` (
26 `idMarca` INT NOT NULL AUTO_INCREMENT,
27 `fabricante` VARCHAR(45) NOT NULL,
28 PRIMARY KEY (`idMarca`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `BudgetCarRental`.`Modelo`
34-- -----------------------------------------------------
35DROP TABLE IF EXISTS `BudgetCarRental`.`Modelo` ;
36
37CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Modelo` (
38 `idModelo` INT NOT NULL AUTO_INCREMENT,
39 `nome` VARCHAR(45) NOT NULL,
40 `descricao` VARCHAR(45) NOT NULL,
41 `ano` DATE NOT NULL,
42 `valorLocacao` FLOAT NOT NULL,
43 `qtdTotal` INT NOT NULL,
44 `qtdLocado` INT NOT NULL,
45 `Marca_idMarca` INT NOT NULL,
46 PRIMARY KEY (`idModelo`),
47 INDEX `fk_Modelo_Marca_idx` (`Marca_idMarca` ASC) VISIBLE,
48 CONSTRAINT `fk_Modelo_Marca`
49 FOREIGN KEY (`Marca_idMarca`)
50 REFERENCES `BudgetCarRental`.`Marca` (`idMarca`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION)
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table `BudgetCarRental`.`Veiculo`
58-- -----------------------------------------------------
59DROP TABLE IF EXISTS `BudgetCarRental`.`Veiculo` ;
60
61CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Veiculo` (
62 `idVeiculo` INT NOT NULL AUTO_INCREMENT,
63 `chassi` VARCHAR(45) NOT NULL,
64 `placa` VARCHAR(45) NOT NULL,
65 `anoFabricacao` DATE NOT NULL,
66 `cor` VARCHAR(45) NOT NULL,
67 `qtdPortas` INT NOT NULL,
68 `Modelo_idModelo` INT NOT NULL,
69 PRIMARY KEY (`idVeiculo`),
70 INDEX `fk_Veiculo_Modelo1_idx` (`Modelo_idModelo` ASC) VISIBLE,
71 CONSTRAINT `fk_Veiculo_Modelo1`
72 FOREIGN KEY (`Modelo_idModelo`)
73 REFERENCES `BudgetCarRental`.`Modelo` (`idModelo`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `BudgetCarRental`.`Acessorio`
81-- -----------------------------------------------------
82DROP TABLE IF EXISTS `BudgetCarRental`.`Acessorio` ;
83
84CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Acessorio` (
85 `idAcessorio` INT NOT NULL AUTO_INCREMENT,
86 `descricao` VARCHAR(45) NOT NULL,
87 `taxa` FLOAT NOT NULL,
88 PRIMARY KEY (`idAcessorio`))
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `BudgetCarRental`.`Acessorio_has_Veiculo`
94-- -----------------------------------------------------
95DROP TABLE IF EXISTS `BudgetCarRental`.`Acessorio_has_Veiculo` ;
96
97CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Acessorio_has_Veiculo` (
98 `Acessorio_idAcessorio` INT NOT NULL,
99 `Veiculo_idVeiculo` INT NOT NULL,
100 INDEX `fk_Acessorio_has_Veiculo_Veiculo1_idx` (`Veiculo_idVeiculo` ASC) VISIBLE,
101 INDEX `fk_Acessorio_has_Veiculo_Acessorio1_idx` (`Acessorio_idAcessorio` ASC) VISIBLE,
102 CONSTRAINT `fk_Acessorio_has_Veiculo_Acessorio1`
103 FOREIGN KEY (`Acessorio_idAcessorio`)
104 REFERENCES `BudgetCarRental`.`Acessorio` (`idAcessorio`)
105 ON DELETE NO ACTION
106 ON UPDATE NO ACTION,
107 CONSTRAINT `fk_Acessorio_has_Veiculo_Veiculo1`
108 FOREIGN KEY (`Veiculo_idVeiculo`)
109 REFERENCES `BudgetCarRental`.`Veiculo` (`idVeiculo`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `BudgetCarRental`.`Estado`
117-- -----------------------------------------------------
118DROP TABLE IF EXISTS `BudgetCarRental`.`Estado` ;
119
120CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Estado` (
121 `idEstado` INT NOT NULL AUTO_INCREMENT,
122 `estado` VARCHAR(45) NULL,
123 PRIMARY KEY (`idEstado`))
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `BudgetCarRental`.`Itinerario`
129-- -----------------------------------------------------
130DROP TABLE IF EXISTS `BudgetCarRental`.`Itinerario` ;
131
132CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Itinerario` (
133 `idItinerario` INT NOT NULL AUTO_INCREMENT,
134 `cidadeOrigem` VARCHAR(45) NOT NULL,
135 `cidadeDestino` VARCHAR(45) NULL,
136 `Estado_idEstado` INT NOT NULL,
137 PRIMARY KEY (`idItinerario`),
138 INDEX `fk_Itinerario_Estado1_idx` (`Estado_idEstado` ASC) VISIBLE,
139 CONSTRAINT `fk_Itinerario_Estado1`
140 FOREIGN KEY (`Estado_idEstado`)
141 REFERENCES `BudgetCarRental`.`Estado` (`idEstado`)
142 ON DELETE NO ACTION
143 ON UPDATE NO ACTION)
144ENGINE = InnoDB;
145
146
147-- -----------------------------------------------------
148-- Table `BudgetCarRental`.`Cliente`
149-- -----------------------------------------------------
150DROP TABLE IF EXISTS `BudgetCarRental`.`Cliente` ;
151
152CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Cliente` (
153 `idCliente` INT NOT NULL AUTO_INCREMENT,
154 `nome` VARCHAR(45) NOT NULL,
155 `cpf` VARCHAR(45) NOT NULL,
156 PRIMARY KEY (`idCliente`))
157ENGINE = InnoDB;
158
159
160-- -----------------------------------------------------
161-- Table `BudgetCarRental`.`Funcionario`
162-- -----------------------------------------------------
163DROP TABLE IF EXISTS `BudgetCarRental`.`Funcionario` ;
164
165CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Funcionario` (
166 `idFuncionario` INT NOT NULL AUTO_INCREMENT,
167 `nome` VARCHAR(50) NOT NULL,
168 `email` VARCHAR(50) NOT NULL,
169 `login` VARCHAR(15) NOT NULL,
170 `senha` VARCHAR(15) NOT NULL,
171 PRIMARY KEY (`idFuncionario`),
172 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE,
173 UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
174ENGINE = InnoDB;
175
176insert into funcionario(idFuncionario, nome, email, login, senha, nivelAcesso)
177values(1, 'Administrador', 'administrador@BudgetCarRental.com.br', 'local', 'local');
178
179-- -----------------------------------------------------
180-- Table `BudgetCarRental`.`Pedido`
181-- -----------------------------------------------------
182DROP TABLE IF EXISTS `BudgetCarRental`.`Pedido` ;
183
184CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Pedido` (
185 `idPedido` INT NOT NULL AUTO_INCREMENT,
186 `dataPedido` DATE NOT NULL,
187 `valorTotal` FLOAT NOT NULL,
188 `valorCaucao` FLOAT NOT NULL,
189 `taxaSeguro` FLOAT NOT NULL,
190 `Cliente_idCliente` INT NOT NULL,
191 `Funcionario_idFuncionario` INT NOT NULL,
192 PRIMARY KEY (`idPedido`),
193 INDEX `fk_Pedido_Cliente1_idx` (`Cliente_idCliente` ASC) VISIBLE,
194 INDEX `fk_Pedido_Funcionario1_idx` (`Funcionario_idFuncionario` ASC) VISIBLE,
195 CONSTRAINT `fk_Pedido_Cliente1`
196 FOREIGN KEY (`Cliente_idCliente`)
197 REFERENCES `BudgetCarRental`.`Cliente` (`idCliente`)
198 ON DELETE NO ACTION
199 ON UPDATE NO ACTION,
200 CONSTRAINT `fk_Pedido_Funcionario1`
201 FOREIGN KEY (`Funcionario_idFuncionario`)
202 REFERENCES `BudgetCarRental`.`Funcionario` (`idFuncionario`)
203 ON DELETE NO ACTION
204 ON UPDATE NO ACTION)
205ENGINE = InnoDB;
206
207
208-- -----------------------------------------------------
209-- Table `BudgetCarRental`.`Locacao`
210-- -----------------------------------------------------
211DROP TABLE IF EXISTS `BudgetCarRental`.`Locacao` ;
212
213CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Locacao` (
214 `idLocacao` INT NOT NULL AUTO_INCREMENT,
215 `dataLocacao` DATE NOT NULL,
216 `dataDevolucao` DATE NOT NULL,
217 `kmSaida` FLOAT NOT NULL,
218 `kmChegada` FLOAT NOT NULL,
219 `kmRodado` FLOAT NOT NULL,
220 `Veiculo_idVeiculo` INT NOT NULL,
221 `Itinerario_idItinerario` INT NOT NULL,
222 `Pedido_idPedido` INT NOT NULL,
223 PRIMARY KEY (`idLocacao`),
224 INDEX `fk_Locacao_Veiculo1_idx` (`Veiculo_idVeiculo` ASC) VISIBLE,
225 INDEX `fk_Locacao_Itinerario1_idx` (`Itinerario_idItinerario` ASC) VISIBLE,
226 INDEX `fk_Locacao_Pedido1_idx` (`Pedido_idPedido` ASC) VISIBLE,
227 CONSTRAINT `fk_Locacao_Veiculo1`
228 FOREIGN KEY (`Veiculo_idVeiculo`)
229 REFERENCES `BudgetCarRental`.`Veiculo` (`idVeiculo`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION,
232 CONSTRAINT `fk_Locacao_Itinerario1`
233 FOREIGN KEY (`Itinerario_idItinerario`)
234 REFERENCES `BudgetCarRental`.`Itinerario` (`idItinerario`)
235 ON DELETE NO ACTION
236 ON UPDATE NO ACTION,
237 CONSTRAINT `fk_Locacao_Pedido1`
238 FOREIGN KEY (`Pedido_idPedido`)
239 REFERENCES `BudgetCarRental`.`Pedido` (`idPedido`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION)
242ENGINE = InnoDB;
243
244
245-- -----------------------------------------------------
246-- Table `BudgetCarRental`.`Ocorrencia`
247-- -----------------------------------------------------
248DROP TABLE IF EXISTS `BudgetCarRental`.`Ocorrencia` ;
249
250CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Ocorrencia` (
251 `idOcorrencia` INT NOT NULL AUTO_INCREMENT,
252 `descricao` VARCHAR(45) NOT NULL,
253 `taxa` FLOAT NOT NULL,
254 `foto` VARCHAR(45) NOT NULL,
255 `Locacao_idLocacao` INT NOT NULL,
256 PRIMARY KEY (`idOcorrencia`),
257 INDEX `fk_Ocorrencia_Locacao1_idx` (`Locacao_idLocacao` ASC) VISIBLE,
258 CONSTRAINT `fk_Ocorrencia_Locacao1`
259 FOREIGN KEY (`Locacao_idLocacao`)
260 REFERENCES `BudgetCarRental`.`Locacao` (`idLocacao`)
261 ON DELETE NO ACTION
262 ON UPDATE NO ACTION)
263ENGINE = InnoDB;
264
265
266-- -----------------------------------------------------
267-- Table `BudgetCarRental`.`Cidade`
268-- -----------------------------------------------------
269DROP TABLE IF EXISTS `BudgetCarRental`.`Cidade` ;
270
271CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Cidade` (
272 `idCidade` INT NOT NULL AUTO_INCREMENT,
273 `cidade` VARCHAR(45) NULL,
274 `Estado_idEstado` INT NOT NULL,
275 PRIMARY KEY (`idCidade`),
276 INDEX `fk_Cidade_Estado1_idx` (`Estado_idEstado` ASC) VISIBLE,
277 CONSTRAINT `fk_Cidade_Estado1`
278 FOREIGN KEY (`Estado_idEstado`)
279 REFERENCES `BudgetCarRental`.`Estado` (`idEstado`)
280 ON DELETE NO ACTION
281 ON UPDATE NO ACTION)
282ENGINE = InnoDB;
283
284
285-- -----------------------------------------------------
286-- Table `BudgetCarRental`.`Endereco`
287-- -----------------------------------------------------
288DROP TABLE IF EXISTS `BudgetCarRental`.`Endereco` ;
289
290CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Endereco` (
291 `idEndereco` INT NOT NULL AUTO_INCREMENT,
292 `cep` VARCHAR(45) NOT NULL,
293 `logradouro` VARCHAR(45) NOT NULL,
294 `complemento` VARCHAR(45) NOT NULL,
295 `bairro` VARCHAR(45) NOT NULL,
296 `Estado_idEstado` INT NOT NULL,
297 `Cliente_idCliente` INT NOT NULL,
298 PRIMARY KEY (`idEndereco`),
299 INDEX `fk_Endereco_Estado1_idx` (`Estado_idEstado` ASC) VISIBLE,
300 INDEX `fk_Endereco_Cliente1_idx` (`Cliente_idCliente` ASC) VISIBLE,
301 CONSTRAINT `fk_Endereco_Estado1`
302 FOREIGN KEY (`Estado_idEstado`)
303 REFERENCES `BudgetCarRental`.`Estado` (`idEstado`)
304 ON DELETE NO ACTION
305 ON UPDATE NO ACTION,
306 CONSTRAINT `fk_Endereco_Cliente1`
307 FOREIGN KEY (`Cliente_idCliente`)
308 REFERENCES `BudgetCarRental`.`Cliente` (`idCliente`)
309 ON DELETE NO ACTION
310 ON UPDATE NO ACTION)
311ENGINE = InnoDB;
312
313
314-- -----------------------------------------------------
315-- Table `BudgetCarRental`.`Caixa`
316-- -----------------------------------------------------
317DROP TABLE IF EXISTS `BudgetCarRental`.`Caixa` ;
318
319CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Caixa` (
320 `idCaixa` INT NOT NULL AUTO_INCREMENT,
321 `saldo` FLOAT NOT NULL,
322 `dataAbertura` DATE NOT NULL,
323 `dataFechamento` DATE NOT NULL,
324 PRIMARY KEY (`idCaixa`))
325ENGINE = InnoDB;
326
327
328-- -----------------------------------------------------
329-- Table `BudgetCarRental`.`Pagamento`
330-- -----------------------------------------------------
331DROP TABLE IF EXISTS `BudgetCarRental`.`Pagamento` ;
332
333CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Pagamento` (
334 `idPagamento` INT NOT NULL AUTO_INCREMENT,
335 `dataPagamento` DATE NOT NULL,
336 `valor` FLOAT NOT NULL,
337 `Pedido_idPedido` INT NOT NULL,
338 `Caixa_idCaixa` INT NOT NULL,
339 PRIMARY KEY (`idPagamento`),
340 INDEX `fk_Pagamento_Pedido1_idx` (`Pedido_idPedido` ASC) VISIBLE,
341 INDEX `fk_Pagamento_Caixa1_idx` (`Caixa_idCaixa` ASC) VISIBLE,
342 CONSTRAINT `fk_Pagamento_Pedido1`
343 FOREIGN KEY (`Pedido_idPedido`)
344 REFERENCES `BudgetCarRental`.`Pedido` (`idPedido`)
345 ON DELETE NO ACTION
346 ON UPDATE NO ACTION,
347 CONSTRAINT `fk_Pagamento_Caixa1`
348 FOREIGN KEY (`Caixa_idCaixa`)
349 REFERENCES `BudgetCarRental`.`Caixa` (`idCaixa`)
350 ON DELETE NO ACTION
351 ON UPDATE NO ACTION)
352ENGINE = InnoDB;
353
354
355-- -----------------------------------------------------
356-- Table `BudgetCarRental`.`Contato`
357-- -----------------------------------------------------
358DROP TABLE IF EXISTS `BudgetCarRental`.`Contato` ;
359
360CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Contato` (
361 `idContato` INT NOT NULL,
362 `celular` VARCHAR(45) NULL,
363 `residencial` VARCHAR(45) NULL,
364 `email` VARCHAR(45) NULL,
365 `Cliente_idCliente` INT NOT NULL AUTO_INCREMENT,
366 PRIMARY KEY (`idContato`),
367 INDEX `fk_Contato_Cliente1_idx` (`Cliente_idCliente` ASC) VISIBLE,
368 CONSTRAINT `fk_Contato_Cliente1`
369 FOREIGN KEY (`Cliente_idCliente`)
370 REFERENCES `BudgetCarRental`.`Cliente` (`idCliente`)
371 ON DELETE NO ACTION
372 ON UPDATE NO ACTION)
373ENGINE = InnoDB;
374
375
376-- -----------------------------------------------------
377-- Table `BudgetCarRental`.`Habilitacao`
378-- -----------------------------------------------------
379DROP TABLE IF EXISTS `BudgetCarRental`.`Habilitacao` ;
380
381CREATE TABLE IF NOT EXISTS `BudgetCarRental`.`Habilitacao` (
382 `idHabilitacao` INT NOT NULL,
383 `numero` VARCHAR(45) NOT NULL,
384 `categoria` VARCHAR(45) NOT NULL,
385 `validade` DATE NOT NULL,
386 `Cliente_idCliente` INT NOT NULL AUTO_INCREMENT,
387 PRIMARY KEY (`idHabilitacao`),
388 INDEX `fk_Habilitacao_Cliente1_idx` (`Cliente_idCliente` ASC) VISIBLE,
389 CONSTRAINT `fk_Habilitacao_Cliente1`
390 FOREIGN KEY (`Cliente_idCliente`)
391 REFERENCES `BudgetCarRental`.`Cliente` (`idCliente`)
392 ON DELETE NO ACTION
393 ON UPDATE NO ACTION)
394ENGINE = InnoDB;
395
396
397SET SQL_MODE=@OLD_SQL_MODE;
398SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
399SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;