SQL_Atividade_Avaliativa_2019-2_EaD

Prévia do material em texto

Banco de Dados I – EAD – 2019/2
SQL - Atividade Avaliativa
Professora: Denise Bandeira
Leia este documento na íntegra antes de iniciar os exercícios
Edite o presente documento e insira as suas respostas para cada questão logo após o enunciado da mesma. 
Salve este arquivo com o seu nome e envie por meio da tarefa criada no Moodle para este propósito.
Nome do Aluno: Jeferson Vieira Bitencourt
Descrição do banco de dados
Considere o banco de dados sobre um sistema para registro das corridas de taxi realizadas por passageiros. O sistema mantém cadastro dos passageiros e dos taxis, com seus respectivos motoristas. E também o registro das corridas realizadas pelos taxis e com que passageiros.
-- DROP TABLE – dica: use este conjunto de instruções caso tenha que recriar todo o banco de dados. Crie um script que possa executar a qualquer momento, sempre que necessário.
DROP TABLE Taxi CASCADE CONSTRAINTS;
DROP TABLE Passageiro CASCADE CONSTRAINTS;
DROP TABLE Corrida CASCADE CONSTRAINTS;
-- CREATE TABLE
CREATE TABLE Taxi (
	Prefixo CHAR(4) PRIMARY KEY,
	Placa CHAR(7) NOT NULL,
	Marca CHAR(30),
	Modelo CHAR(30),
	NomeMotorista CHAR(30) NOT NULL,
	Ano NUMBER(4) NOT NULL,
);
CREATE TABLE Passageiro (
	CPF NUMBER(8),
	Nome CHAR(30),
	email CHAR(30),
	telefone NUMBER(8),
	BairroDoPAssageiro CHAR(30)
);
CREATE TABLE Corrida (
	Id NUMBER(6) PRIMARY KEY,
	BairroDaPartida CHAR(30),
	Valor NUMBER(5),
	Kms NUMBER(5),
	Prefixo CHAR(4),
	CPF NUMBER(8),
	CONSTRAINT FkCorridaPassageiro FOREIGN KEY (CPF) REFERENCES Passageiro
);
Questões da tarefa
Criação do esquema do banco de dados. Note que as instruções CREATE TABLE de todas as tabelas que fazem parte deste projeto já foram dadas acima, mas você deve verificar as necessidades do projeto e alterar o código de criação das tabelas antes de criar o esquema no banco de dados. Abaixo, algumas questões que você deve verificar a fim de criar o projeto corretamente:
Verifique se todas as chaves (primárias e estrangeiras) foram criadas. Se não foram criadas, altere o código acima antes de criar o esquema no banco de dados.
restrições de integridade 
É importante definir algumas restrições de integridade para que os dados inseridos respeitem as regras de validação e, consequentemente, o banco de dados permaneça sempre íntegro, com os dados corretos segundo o seu contexto de aplicação. O conceito de restrição de integridade, apresentado no livro da disciplina é: “restrições de integridade, ou seja, regras que são definidas sobre os dados para regular as operações que podem ser realizadas sobre eles”. No livro, são apresentados os tipos de restrições que podem ser especificadas no momento da criação das tabelas.
Uma breve revisão e exemplo de como definir restrições de integridade (cláusula CHECK):
As restrições podem ser criadas através de uma CONSTRAINT da seguinte forma:
CONSTRAINT <nome da constraint> CHECK (<expressão>)
onde <expressão> é alguma expressão envolvendo o atributo relacionado na restrição. Ela pode envolver os operadores IN e BETWEEN.
Exemplos:
CONSTRAINT ck_idade CHECK (idade > 18) /* regra que valida se a idade é maior que 18 */
ou
CONSTRAINT ck_ramo CHECK (ramo IN (‘Atacado’, ‘Varejo’, ‘Serviços’)) /* regra que valida se o valor informado para ramo é um dos três listados */
Considerando o exposto acima, para criar o banco de dados de corridas de taxi, algumas restrições de integridade são sugeridas abaixo.
	Tabela
	Restrições de integridade
	Taxi
	Ano > 2008
	Corrida
	Bairros aonde os taxis deste sistema buscam passageiros: 'Floresta', 'Sao Joao', 'Higienopolis', ou 'Auxiliadora'
Insira AQUI a resposta para esta questão, que deve ser o conjunto de instruções CREATE que definem as tabelas deste projeto corretamente. 
CREATE TABLE Taxi (
	Prefixo CHAR(4) NOT NULL,
	Placa CHAR(7) NOT NULL,
	Marca CHAR(30)NOT NULL,
	Modelo CHAR(30) NOT NULL,
	NomeMotorista CHAR(30) NOT NULL,
	Ano NUMBER(4) NOT NULL CHECK(ano>2008),
 CONSTRAINT PKTaxi PRIMARY KEY (Prefixo)
);
CREATE TABLE Passageiro (
	CPF NUMBER(8) NOT NULL,
	Nome CHAR(30)NOT NULL,
	email CHAR(30)NOT NULL,
	telefone NUMBER(8) NOT NULL,
	BairroDoPassageiro CHAR(30)NOT NULL,
 CONSTRAINT PKPassageiro PRIMARY KEY (CPF)
);
CREATE TABLE Corrida (
	Id NUMBER(6) NOT NULL,
	BairroDaPartida CHAR(30) NOT NULL CHECK (BairroDaPartida IN('Floresta', 'Sao Joao', 'Higienopolis', 'Auxiliadora')),
	Valor NUMBER(5)NOT NULL,
	Kms NUMBER(5)NOT NULL,
 PrefixoTaxi CHAR(4) NOT NULL,
	CPFPassageiro NUMBER(8)NOT NULL,
 CONSTRAINT PKCorrida PRIMARY KEY (Id),
 CONSTRAINT FKCorridaMotorista FOREIGN KEY (PrefixoTaxi) REFERENCES Taxi(Prefixo) ,
	CONSTRAINT FkCorridaPassageiro FOREIGN KEY (CPFPassageiro) REFERENCES Passageiro(CPF)
);
 
Popule o banco de dados acima. Note que as instruções já foram dadas, mas você deve verificar se geram erros. Execute o conjunto de instruções apresentado abaixo para a inserção dos dados no banco de dados. As instruções que gerarem erro devem ser comentadas na resposta; os erros NÃO devem ser corrigidos, mas COMENTADOS NA RESPOSTA. Atenção para o caracter ' é o apóstrofe simples, não use o ‘ que é o acento agúdo.
INSERT INTO Taxi VALUES ('1111', 'VBH8875', 'Ford', 'Fiesta', 'Luciano', 2009);
INSERT INTO Taxi VALUES ('2222', 'HGT6890', 'Renault', 'Sandero', 'Denise', 2010);
INSERT INTO Taxi VALUES ('3333', 'WOL8877', 'Renault', 'Duster', 'Eduardo', 2015);
INSERT INTO Taxi VALUES ('4444', 'HJO3899', 'Ford', 'Ka', 'Jair', 1998);
INSERT INTO Passageiro VALUES (55555555,'Carla', 'carla@carla', 51515151, 'Floresta');
INSERT INTO Passageiro VALUES (66666666,'Ana', 'ana@ana', 61616161,'Higienopolis'); 
INSERT INTO Passageiro VALUES (77777777,'Pedro','pedro@pedro', 71717171,'Higienopolis');
INSERT INTO Corrida VALUES (202020, 'Floresta', 16, 10, '1111', 55555555);
INSERT INTO Corrida VALUES (212121, 'Floresta', 19, 13, '2222', 66666666);
INSERT INTO Corrida VALUES (313131, 'Higienopolis', 15, 9, '1111', 55555555);
INSERT INTO Corrida VALUES (414141, 'Rio Branco', 40, 70, '3333', 66666666); 
INSERT INTO Corrida VALUES (313131, 'Sao Joao', 8, 10, '1111', 55555555); 
INSERT INTO Corrida VALUES (515151, 'Higienopolis', 12, 8, '3333', 77777777);
INSERT INTO Corrida VALUES (616161, 'Auxiliadora', 40, 70, '4444', 88888888); 
INSERT INTO Corrida VALUES (717171, 'Sao Joao', 22, 15, '1111', 66666666);
INSERT INTO Corrida VALUES (818181, 'Auxiliadora', 30, 25, '3333', 66666666);
Insira AQUI a resposta para esta questão, que deve conter os itens abaixo:
o conjunto FINAL de instruções INSERT realizado em todas as tabelas.
os comentários sobre as instruções que geraram erro e qual o motivo do erro. O erro NÃO DEVE SER ARRUMADO na inserção.
uma listagem dos dados de cada tabela após a finalização de todos os INSERTS; por exemplo, pode ser um printscreen da tela do ambiente Oracle (ou outro SGBD que esteja utilizando), ou pode ser a “cópia e cola” do resultado emitido por uma instrução SELECT * FROM <tabela> para cada tabela do banco de dados.
INSERT INTO Taxi VALUES ('1111', 'VBH8875', 'Ford', 'Fiesta', 'Luciano', 2009);
INSERT INTO Taxi VALUES ('2222', 'HGT6890', 'Renault', 'Sandero', 'Denise', 2010);
INSERT INTO Taxi VALUES ('3333', 'WOL8877', 'Renault', 'Duster', 'Eduardo', 2015);
INSERT INTO Taxi VALUES ('4444', 'HJO3899', 'Ford', 'Ka', 'Jair', 1998);
-Erro ao inserir por causa do CHECK do ano > 1998
check constraint (UNISINOS.SYS_C0082299682) violated
INSERT INTO Passageiro VALUES (55555555,'Carla', 'carla@carla', 51515151, 'Floresta');
INSERT INTO Passageiro VALUES (66666666,'Ana', 'ana@ana', 61616161,'Higienopolis');
INSERT INTO Passageiro VALUES (77777777,'Pedro','pedro@pedro', 71717171,'Higienopolis');
INSERT INTO Corrida VALUES (202020, 'Floresta', 16, 10, '1111', 55555555);
INSERT INTO Corrida VALUES (212121, 'Floresta', 19, 13, '2222', 66666666);
INSERT INTO Corrida VALUES (313131, 'Higienopolis', 15, 9,'1111', 55555555);
INSERT INTO Corrida VALUES (414141, 'Rio Branco', 40, 70, '3333', 66666666); 
-Erro ao inserir por causa do CHECK BairroDaPartida
check constraint (UNISINOS.SYS_C0082299708) violated
INSERT INTO Corrida VALUES (313131, 'Sao Joao', 8, 10, '1111', 55555555);
-Erro de chave primaria
unique constraint (UNISINOS.PKCORRIDA) violated
INSERT INTO Corrida VALUES (515151, 'Higienopolis', 12, 8, '3333', 77777777);
INSERT INTO Corrida VALUES (616161, 'Auxiliadora', 40, 70, '4444', 88888888); 
-Erro de chave extrangeira
integrity constraint (UNISINOS.FKCORRIDAPASSAGEIRO) violated - parent key not found
INSERT INTO Corrida VALUES (717171, 'Sao Joao', 22, 15, '1111', 66666666);
INSERT INTO Corrida VALUES (818181, 'Auxiliadora', 30, 25, '3333', 66666666);
Realize as seguintes consultas em SQL. 
A resposta para esta questão deve conter (para cada um dos exercícios sugeridos nos itens abaixo):
a instrução SQL que resolve o problema.
uma listagem dos dados retornados pelo SGBD após a execução da instrução SQL; por exemplo, pode ser um printscreen da tela do ambiente Oracle (ou outro SGBD que esteja utilizando), ou pode ser a “cópia e cola” do resultado emitido pela instrução. 
Listar os Prefixos dos taxis que realizaram corridas que partiram do bairro 'Floresta'.
SELECT Prefixo FROM Taxi,Corrida
WHERE PrefixoTaxi = Prefixo AND BairroDaPartida = 'Floresta';
	
Listar o nome dos passageiros e o prefixo dos taxis que fizeram corridas com esses passageiros, mas somente para as corridas que tem o mesmo bairro de partida do cadastrado para o passageiro.
SELECT Nome,Prefixo FROM Taxi,Passageiro,corrida
WHERE PrefixoTaxi = Prefixo AND BairroDoPassageiro = BairroDaPartida AND CPF = CPFPassageiro;
Listar o CPF e nome dos passageiros e a quantidade total de quilômetros percorridos por cada um deles em suas corridas de taxi.
SELECT CPF,Nome,SUM (kms) AS Total_de_Km from Passageiro,Corrida
WHERE CPFPassageiro = CPF
GROUP BY CPF,Nome
Listar os prefixos dos taxis que não realizaram corridas.
SELECT Prefixo FROM Taxi
WHERE Prefixo NOT IN(SELECT PrefixoTaxi FROM Corrida)
Nessa questão como o resultado foi “no data found” , inseri mais uma linha apenas na tabela taxi sem nenhuma corrida apenas para testar,e o resultado :
INSERT INTO taxi VALUES('5555','IJK3939','Fiat','Siena','Manoel',2010)
Listar os nomes dos passageiros e dos motoristas de taxis que realizaram corridas mais de uma vez juntos.
SELECT Nome,NomeMotorista
FROM Taxi,Passageiro,Corrida
WHERE Prefixotaxi = Prefixo AND CPFPassageiro = CPF
GROUP BY Nome,NomeMotorista
HAVING COUNT(*) > 1 ;
Após editar o arquivo, ESTE EXERCÍCIO DEVE SER ENCAMINHADO PELO Moodle, conforme PRAZO indicado na tarefa. A entrega deve ser feita neste mesmo arquivo, editado conforme orientações ao longo do texto.
Observação sobre a avaliação: 
este trabalho é individual; as questões somente serão consideradas totalmente corretas se as respostas forem apresentadas conforme as orientações ao longo do texto acima.