Buscar

Dump20151103

Banco de Dados I

•

UNINTER

0
Dislike0
0
Dislike0
Comment0
User badge image

M@yl@ Phyph3

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

CREATE DATABASE IF NOT EXISTS `locadora` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `locadora`;
-- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: locadora
-- ------------------------------------------------------
-- Server version	5.6.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `locacao`
--
DROP TABLE IF EXISTS `locacao`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `locacao` (
 `idlocacao` int(11) NOT NULL AUTO_INCREMENT,
 `dtlocacao` datetime DEFAULT NULL,
 `dtdevolucao_prev` datetime DEFAULT NULL,
 `dtdevolucao_evetiva` datetime DEFAULT NULL,
 `kminicial` int(11) DEFAULT NULL,
 `kmfinal` int(11) DEFAULT NULL,
 `vlr_diaria` double DEFAULT NULL,
 `observacores` longtext,
 `locatario_idlocatario` int(11) NOT NULL,
 `veiculos_idveiculos` int(11) NOT NULL,
 PRIMARY KEY (`idlocacao`,`locatario_idlocatario`,`veiculos_idveiculos`),
 KEY `fk_locacao_locatario1_idx` (`locatario_idlocatario`),
 KEY `fk_locacao_veiculos1_idx` (`veiculos_idveiculos`),
 CONSTRAINT `fk_locacao_locatario1` FOREIGN KEY (`locatario_idlocatario`) REFERENCES `locatario` (`idlocatario`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_locacao_veiculos1` FOREIGN KEY (`veiculos_idveiculos`) REFERENCES `veiculos` (`idveiculos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `locacao`
--
LOCK TABLES `locacao` WRITE;
/*!40000 ALTER TABLE `locacao` DISABLE KEYS */;
INSERT INTO `locacao` VALUES (1,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,1),(2,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,2),(3,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,3),(4,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,4),(5,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,5),(6,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,6),(7,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,7),(8,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,8),(9,'2015-02-02 12:00:00','2015-02-10 12:00:00','2015-02-12 15:20:00',12000,12890,200.5,'',1,9),(10,'2015-02-15 12:00:00','2015-03-10 12:00:00',NULL,12000,12890,200.5,'',2,1),(11,'2015-02-15 12:00:00','2015-03-10 12:00:00',NULL,12000,12890,200.5,'',16,1),(12,'2015-02-15 12:00:00','2015-03-10 12:00:00',NULL,12000,12890,150,'',16,3);
/*!40000 ALTER TABLE `locacao` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `locatario`
--
DROP TABLE IF EXISTS `locatario`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `locatario` (
 `idlocatario` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(80) NOT NULL,
 `tipo` enum('PJ','PF') NOT NULL,
 `RG` varchar(45) NOT NULL,
 `CPG_CNPJ` varchar(45) NOT NULL,
 `rua` varchar(45) DEFAULT NULL,
 `numero` varchar(45) DEFAULT NULL,
 `complemento` varchar(45) DEFAULT NULL,
 `cep` varchar(45) DEFAULT NULL,
 `bairro` varchar(45) DEFAULT NULL,
 `cidade` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idlocatario`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `locatario`
--
LOCK TABLES `locatario` WRITE;
/*!40000 ALTER TABLE `locatario` DISABLE KEYS */;
INSERT INTO `locatario` VALUES (1,'a','PF','5','5','5','5','5','5','5','5'),(2,'a','PJ','a','a','a','a','a','a','a','a'),(16,'b','PF','b','b','b','b','b','b','b','b'),(17,'d','PF','d','d','d','d','d','d','d','d'),(18,'e','PF','e','e','e','e','e','e','e','e'),(19,'f','PF','f','f','f','f','f','f','f','f'),(20,'g','PF','g','g','g','g','g','g','g','g'),(21,'aa','PJ','aa','aa','aa','aa','aa','aa','aa','aa'),(22,'aa','PJ','aa','aa','aa','aa','aa','aa','aa','aa'),(23,'bb','PJ','bb','bb','bb','bb','bb','bb','bb','bb'),(24,'cc','PJ','cc','cc','cc','cc','cc','cc','cc','cc'),(25,'cc','PJ','cc','cc','cc','cc','cc','cc','cc','cc'),(26,'dd','PJ','dd','dd','dd','dd','dd','dd','dd','dd'),(27,'ee','PJ','ee','ee','ee','ee','ee','ee','ee','ee'),(28,'ff','PJ','ff','ff','ff','ff','ff','ff','ff','ff'),(29,'gg','PJ','gg','gg','gg','gg','gg','gg','gg','gg');
/*!40000 ALTER TABLE `locatario` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `manutencao`
--
DROP TABLE IF EXISTS `manutencao`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `manutencao` (
 `idmanutencao` int(11) NOT NULL AUTO_INCREMENT,
 `tipo` enum('PREVENTIVA','CORRETIVA') DEFAULT NULL,
 `km` int(11) DEFAULT NULL,
 `data` date DEFAULT NULL,
 `descricao` varchar(250) DEFAULT NULL,
 `manutencaocol` varchar(45) DEFAULT NULL,
 `veiculos_idveiculos` int(11) NOT NULL,
 PRIMARY KEY (`idmanutencao`,`veiculos_idveiculos`),
 KEY `fk_manutencao_veiculos1_idx` (`veiculos_idveiculos`),
 CONSTRAINT `fk_manutencao_veiculos1` FOREIGN KEY (`veiculos_idveiculos`) REFERENCES `veiculos` (`idveiculos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `manutencao`
--
LOCK TABLES `manutencao` WRITE;
/*!40000 ALTER TABLE `manutencao` DISABLE KEYS */;
INSERT INTO `manutencao` VALUES (1,'PREVENTIVA',10000,'2015-02-05','Geral',NULL,1),(2,'CORRETIVA',10300,'2015-02-10','Freios',NULL,1),(3,'PREVENTIVA',11700,'2015-04-11','Elétrica e geral',NULL,1),(4,'PREVENTIVA',4000,'2015-03-05','GERAL',NULL,2),(5,'PREVENTIVA',10000,'2015-06-10','GERAL',NULL,2),(6,'CORRETIVA',30700,'2015-06-02','Troca de pneus',NULL,2);
/*!40000 ALTER TABLE `manutencao` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `marca`
--
DROP TABLE IF EXISTS `marca`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `marca` (
 `idmarca` int(11) NOT NULL AUTO_INCREMENT,
 `descricao` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idmarca`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `marca`
--
LOCK TABLES `marca` WRITE;
/*!40000 ALTER TABLE `marca` DISABLE KEYS */;
INSERT INTO `marca` VALUES (1,'VW'),(2,'Ford'),(3,'H'),(4,'P'),(5,'R');
/*!40000 ALTER TABLE `marca` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `modelo`
--
DROP TABLE IF EXISTS `modelo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `modelo` (
 `idmodelo` int(11) NOT NULL AUTO_INCREMENT,
 `descricao` varchar(45) DEFAULT NULL,
 `marca_idmarca` int(11) NOT NULL,
 PRIMARY KEY (`idmodelo`,`marca_idmarca`),
 KEY `fk_modelo_marca1_idx` (`marca_idmarca`),
 CONSTRAINT `fk_modelo_marca1` FOREIGN KEY (`marca_idmarca`) REFERENCES `marca` (`idmarca`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=19
DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `modelo`
--
LOCK TABLES `modelo` WRITE;
/*!40000 ALTER TABLE `modelo` DISABLE KEYS */;
INSERT INTO `modelo` VALUES (1,'as',1),(2,'ax',1),(3,'ar',1),(4,'bq',2),(5,'bt',2),(6,'by',2),(7,'bb',2),(8,'za',3),(9,'zx',3),(10,'zw',3),(11,'z5',3),(12,'zv',3),(13,'xa',4),(14,'xc',4),(15,'xb',4),(16,'xh',4),(17,'dv',5),(18,'dc',5);
/*!40000 ALTER TABLE `modelo` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `realizado`
--
DROP TABLE IF EXISTS `realizado`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `realizado` (
 `idrealizado` int(11) NOT NULL AUTO_INCREMENT,
 `descricao` varchar(45) DEFAULT NULL,
 `valor` double DEFAULT NULL,
 `manutencao_idmanutencao` int(11) NOT NULL,
 PRIMARY KEY (`idrealizado`,`manutencao_idmanutencao`),
 KEY `fk_realizado_manutencao1_idx` (`manutencao_idmanutencao`),
 CONSTRAINT `fk_realizado_manutencao1` FOREIGN KEY (`manutencao_idmanutencao`) REFERENCES `manutencao` (`idmanutencao`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `realizado`
--
LOCK TABLES `realizado` WRITE;
/*!40000 ALTER TABLE `realizado` DISABLE KEYS */;
INSERT INTO `realizado` VALUES (1,'porcars e parafusos',20,1),(2,'troca das lonas de freios',350,1),(3,'regulagem dos freios',200,2),(4,'regulagem de faróis',160,3),(5,'troca de lampadas',90,3);
/*!40000 ALTER TABLE `realizado` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `seguro`
--
DROP TABLE IF EXISTS `seguro`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `seguro` (
 `idseguro` int(11) NOT NULL AUTO_INCREMENT,
 `apolice` varchar(45) NOT NULL,
 `valor` double NOT NULL,
 `vencimento` date NOT NULL,
 `veiculos_idveiculos` int(11) NOT NULL,
 PRIMARY KEY (`idseguro`,`veiculos_idveiculos`),
 KEY `fk_seguro_veiculos1_idx` (`veiculos_idveiculos`),
 CONSTRAINT `fk_seguro_veiculos1` FOREIGN KEY (`veiculos_idveiculos`) REFERENCES `veiculos` (`idveiculos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `seguro`
--
LOCK TABLES `seguro` WRITE;
/*!40000 ALTER TABLE `seguro` DISABLE KEYS */;
INSERT INTO `seguro` VALUES (1,'432342',1500,'2015-07-01',1),(2,'546546',1200,'2016-02-10',2),(3,'122331',980,'2014-07-01',1),(4,'655443',1150,'2013-06-29',1),(5,'988776',1766,'2017-03-20',3),(6,'467912',2100,'2015-12-02',5);
/*!40000 ALTER TABLE `seguro` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `telefones`
--
DROP TABLE IF EXISTS `telefones`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `telefones` (
 `idtelefones` int(11) NOT NULL AUTO_INCREMENT,
 `tipo` varchar(45) DEFAULT NULL,
 `ddd` varchar(45) DEFAULT NULL,
 `numero` varchar(45) DEFAULT NULL,
 `locatario_idlocatario` int(11) NOT NULL,
 PRIMARY KEY (`idtelefones`,`locatario_idlocatario`),
 KEY `fk_telefones_locatario1_idx` (`locatario_idlocatario`),
 CONSTRAINT `fk_telefones_locatario1` FOREIGN KEY (`locatario_idlocatario`) REFERENCES `locatario` (`idlocatario`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `telefones`
--
LOCK TABLES `telefones` WRITE;
/*!40000 ALTER TABLE `telefones` DISABLE KEYS */;
/*!40000 ALTER TABLE `telefones` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `veiculos`
--
DROP TABLE IF EXISTS `veiculos`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `veiculos` (
 `idveiculos` int(11) NOT NULL AUTO_INCREMENT,
 `placa` varchar(45) DEFAULT NULL,
 `chassi` varchar(45) DEFAULT NULL,
 `renavan` varchar(45) DEFAULT NULL,
 `cor` varchar(45) DEFAULT NULL,
 `ano` year(4) DEFAULT NULL,
 `modelo_idmodelo` int(11) NOT NULL,
 PRIMARY KEY (`idveiculos`,`modelo_idmodelo`),
 KEY `fk_veiculos_modelo1_idx` (`modelo_idmodelo`),
 CONSTRAINT `fk_veiculos_modelo1` FOREIGN KEY (`modelo_idmodelo`) REFERENCES `modelo` (`idmodelo`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `veiculos`
--
LOCK TABLES `veiculos` WRITE;
/*!40000 ALTER TABLE `veiculos` DISABLE KEYS */;
INSERT INTO `veiculos` VALUES (1,'xxx4444','xxx','xxx','xxx',2010,1),(2,'ccc2222','ccc','ccc','ccc',2015,1),(3,'xxx1111','qaz','qaz','qaz',2014,1),(4,'aaa1111','123','123','123',2011,2),(5,'ddd4444','edc','edc','edc',2012,2),(6,'sss4444','wsx','wsx','wsx',2013,4),(7,'azq111','azq','azq','azq',2013,5),(8,'swx2222','swx','swx','swx',2014,18),(9,'gfd4444','gfd','gfd','gfd',2015,17),(10,'dsa2222','dsa','dsa','dsa',2015,17);
/*!40000 ALTER TABLE `veiculos` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-11-03 16:10:12

Teste o Premium para desbloquear

Aproveite todos os benefícios por 3 dias sem pagar! 😉
Já tem cadastro?

E aí, curtiu este material?

Ajude a incentivar outros estudantes a melhorar o conteúdo

Gostou desse material? Compartilhe! 🧡

LikeFooter
DislikeFooter

Banco de Dados I

27.694 Materiais compartilhados

mobile

Baixe o app para aproveitar ainda mais

Leia os materiais offline, sem usar a internet. Além de vários outros recursos!

Outros materiais

Perguntas relacionadas

Question Icon

Construa um diagrama E-R para uma empresa que vende e aluga equipamentos para construção civil. Descrição do problema: A empresa possui uma rede d...

User badge image

Willian Martins

Question Icon

Qual o comando no sistema gerenciador de banco de dados que possibilita excluir uma tabela?

User badge image

Ton Cabral do Nascimento

Question Icon

O que descontinuidade na matemática

User badge image

Drica silva

Question Icon

O diagrama a seguir representa uma modelagem ER para bandas de rock. Descrição acessível: Figura possui dois retângulos: “Banda de rock” (esquerda...

User badge image

Testando o Conhecimento

Outros materiais