Atividade Supervisionada

Prévia do material em texto

��Disciplina: Banco de Dados II�Documento:�ASPV���Professor: Alberto Tavares da Silva�Versão :�01��ATIVIDADE PRÁTICA SUPERVISIONADA – 2016.2��
IDENTIFICAÇÃO DO PROJETO
	TÍTULO DO PROJETO
Locadora de carro
	ALIAS DO PROJETO
Locadora
	DATA INÍCIO
	
	DATA DE ENTREGA DO PROJETO
	
DEFINIÇÃO DA EQUIPE
	Membro
	Matrícula 
	Charle de Amorim Silva
	2015201211
	Eliel Rocha Mororo
	2013101877
	Priscila da Silva Vianna
	2013000222
	Tiago da Silva Sousa
	2014201015
	
	
	
	
	
	
	
	
DESCRIÇÃO DO MINIMUNDO
	Cliente solicita aluguel e escolhe carro através de site da locadora onde um funcionário realiza o aluguel do carro, os dados que o funcionário solicita ao cliente é o modelo do carro forma de pagamento, data da retirada e a data da devolução. 
MODELO CONCEITUAL
	
5. MODELO LÓGICO
	
 6. SCRIPT DE CRIAÇÃO DAS TABELAS
	CREATE TABLE funcionario (
 matricula int NOT NULL AUTO_INCREMENT,
 cpf_funcionarios varchar(11) DEFAULT NULL,
 nome varchar(45) DEFAULT NULL,
 salario_fixo float DEFAULT NULL,
 PRIMARY KEY (matricula)
 ) DEFAULT CHARSET=utf8;
	CREATE TABLE cliente (
 cod_cliente int NOT NULL AUTO_INCREMENT,
 nome varchar(30) NOT NULL,
 cpf_cliente varchar(11) DEFAULT NULL,
 telefone varchar(9) NOT NULL,
 email varchar(45) DEFAULT NULL,
 endereco varchar(45) DEFAULT NULL,
 cidade varchar(30) DEFAULT NULL,
 genero enum('M','F') DEFAULT NULL,
 PRIMARY KEY (cod_cliente)
) DEFAULT CHARSET=utf8;
	CREATE TABLE carro (
 cod_carro int NOT NULL AUTO_INCREMENT,
 modelo varchar(45) DEFAULT NULL,
 chassi varchar(45) DEFAULT NULL,
 ano_fabrica year(4) DEFAULT NULL,
 cor varchar(20) DEFAULT NULL,
 PRIMARY KEY (cod_carro)
 ) DEFAULT CHARSET=utf8;
	CREATE TABLE aluguel (
 cod_aluguel int NOT NULL AUTO_INCREMENT,
 data_aluguel date DEFAULT NULL,
 data_devolucao date DEFAULT NULL,
 forma_pagamento varchar(20) DEFAULT NULL,
 valor_aluguel float DEFAULT NULL,
 matricula int not null,
 cod_cliente int not null,
 PRIMARY KEY (cod_aluguel),
 FOREIGN KEY (matricula)
 REFERENCES funcionario (matricula),
 FOREIGN KEY (cod_cliente)
 REFERENCES cliente (cod_cliente)
 ) DEFAULT CHARSET=utf8;
	CREATE TABLE escolhe (
 cod_carro int NOT NULL,
 cod_aluguel int NOT NULL,
 FOREIGN KEY (cod_carro) 
 REFERENCES carro (cod_carro),
 FOREIGN KEY (cod_aluguel)
 REFERENCES aluguel (cod_aluguel)
) DEFAULT CHARSET=utf8;
7. SCRIPT DE INSERÇÃO DE REGISTROS
	INSERT INTO funcionario 
VALUES (1,'8349087623','Beatriz Matos',2500),
(2,'93409218934','Lucas Lopes',4900),
(3,'8349087625','Marcos Antônio',1500),
(4,'8349087654','Ângela Neves',3500),
(5,'8349087123','Amanda Nascimento',4200),
(6,'8312387623','Carla Cristina',2600),
(7,'7049087654','Barbara Silva',1500);
	INSERT INTO cliente 
VALUES (1,'Ana Beatriz ','21487634895','984584324','abeatriz@gmail.com','Rua da luz 46','Rio','F'),
(2,'Luis Eduardo','73649837266','994568732','luis@gmail.com','Rua do Sol 102','Rio','M'),
(3,'Alan Silva','74859387488','993212348','alan@gmail.com','Rua da Alegria 12','São Paulo','M'),
(4,'Carlos Neves','85732987466','994532178','carlosn@gmail.com','Rua do Sol 212','São Paulo','M'),
(5,'Ana Paula','87656789543', '997654568','ana@gmail.com','Rua da Alegria 44','Rio','F'),
(6,'Elena ','22287634895','704584324','elena@gmail.com','Rua do luar 78','Rio','F'),
(7,'Carlos Augusto','95487634895','994564324','carlos@gmail.com','Rua da Paz 12','Rio','M');
	INSERT INTO carro 
VALUES (1,'Gol','9BWZZZ377VT004251',2010,'Preto'),
(2,'Uno','7BWAAA746VT005675',2010,'Azul'),
(3,'Uno','8BWBBB839VT002986',2011,'Azul'),
(4,'Palio','6BWCCC789VT003897',2011,'Vermelho'),
(5,'Fox','5BWDDD890VT007897',2012,'Prata'),
(6,'Fiesta','4BWEEE937VT004890',2013,'Preto'),
(7,'Palio','3BWFFF856VT006346',2013,'Preto'),
(8,'Gol','3BWFFF889VT006346',2014,'Prata'),
(9,'Fox','2BWFFF889VT007846',2014,'Preto'),
(10,'Picasso','1BWFFF779VT006346',2015,'Vermelho'),
(11,'Gol','5BWFFF867VT006346',2013,'Verde');
	INSERT INTO aluguel 
VALUES (1,'2000-05-07','2000-05-17','cartao debito','1500', 3,1),
(2,'2000-07-22','2000-08-03','cartao credito','2500', 5,2),
(3,'2000-07-22','2000-08-04','cartao credito','3000', 7,3),
(4,'2000-06-21','2000-07-31','cartao debito','2500',2 ,7),
(5,'2001-06-12','2001-06-30','cheque','3500',6 ,4),
(6,'2002-12-01','2003-01-12','catao debito','5000',6 ,5),
(7,'2010-05-07','2010-05-17','dinheiro','1500',1 ,6),
(8,'2016-09-10','2016-10-10','Cheque','3000',4 ,2);
	INSERT INTO escolhe VALUES (1,1), (8,2),(11,3),(2,4),(3,5),(10,6),(5,7),(6,2);
8. COMANDOS SQL 
	
	Elaborar quatro consultas com duas tabelas usando group by com funções de agregação
	CONSULTA 01
	Quantos carros cada funcionário alugaram?
	SQL
	select f.nome, count(cod_aluguel) as quantidade
from funcionario f join aluguel a
using(matricula)
group by f.nome;
	Resultado da consulta
	
	CONSULTA 02
	Quais os clientes que devolveram o carro no ano 2000?
	SQL
	select c.nome, year(data_aluguel) as ano
from cliente c join aluguel a
using(cod_cliente)
group by c.nome
having ano = 2000;
	Resultado da consulta
	
	CONSULTA 03
	Quais foram as formas de pagamento de cada cliente? 
	SQL
	select c.nome as Cliente, a.forma_pagamento as 'Forma de pagamento'
from cliente c join aluguel a
using(cod_cliente)
group by c.nome;
	Resultado da consulta
	
	CONSULTA 04
	Quantos carros de cada modelo foram alugados?
	SQL
	select count(cod_carro) as 'Quantidade',c.modelo as Modelo 
from carro c join escolhe e
using(cod_carro)
group by c.cor;
	Resultado da consulta
	
	8.2
	Elaborar três consultas com três tabelas usando group by com funções de agregação
	CONSULTA 05
	Quais funcionários atenderem clientes que moram no Rio? 
	SQL
	select f.nome as 'Funcionário',c.nome as Cliente, c.cidade as Cidade 
from funcionario f join aluguel a
using(matricula)
join cliente c
using(cod_cliente)
where c.cidade= 'Rio'
group by f.nome;
	Resultado da consulta
	
	CONSULTA 06
	Quais carros foram alugados com cartão de debito? 
	SQL
	select c.modelo as 'Modelo', a.forma_pagamento as 'Forma de Pagamento' 
from carro c join escolhe e
on c.cod_carro = e.cod_carro
join aluguel a
on a.cod_aluguel = e.cod_aluguel
where a.forma_pagamento= 'Cartao debito'
group by c.modelo;
	Resultado da consulta
	
	CONSULTA 07
	Quantos carros foram alugados por homens?
	SQL
	select c.nome as 'Nome', count(e.cod_carro) as 'Quantidade de Carro' 
from cliente c join aluguel a
on c.cod_cliente = a.cod_cliente
join escolhe e
on a.cod_aluguel = e.cod_aluguel
where c.genero= 'M'
group by c.nome;
	Resultado da consulta
	
	8.3
	Elaborar uma consulta com subconsulta
	CONSULTA 08
	Qual funcionário alugou carro na data 10/09/2016?
	SQL
	select nome as 'Nome Funcionário'
from funcionario
where matricula in 
(
select matricula 
from aluguel
where data_aluguel = '2016-09-10'
);
	Resultado da consulta
	
	8.4
	Elaborar uma exclusão
	CONSULTA 09
	Excluir o modelo Palio
	SQL
	delete from carro
where cod_carro = 4;
	Resultado da consulta
	
	8.5
	Elaborar uma atualização
	CONSULTA 10
	Aumento de R$ 1500 para R$ 2000 funcionário Marcos Antônio
	SQL
	update funcionario
set salario_fixo = 2000
where matricula = 3;
	Resultado da consulta
	
� FILENAME \* MERGEFORMAT �PTR Plano de Trabalho.doc�					� PAGE �1�/� NUMPAGES �8�