Trabalho de Banco de Dados

Centro Universitário Internacional UNINTER Escola Superior Politécnica ESP BANCO DE DADOS Trabalho Relatório Curso Alunoa RU 1 1ª Etapa Modelagem Pontuação 30 pontos Dadas as regras de negócio abaixo listadas referentes ao estudo de caso de uma Rede de Hotéis elabore o Modelo EntidadeRelacionamento MER isto é o modelo conceitual O Modelo EntidadeRelacionamento MER deve contemplar os seguintes itens Entidades Atributos Relacionamentos Cardinalidades Chaves primárias Chaves estrangeiras Uma Rede de Hotéis necessita controlar os dados dos funcionários das unidades dos quartos dos hóspedes das reservas e dos pagamentos Para isso contratou um profissional de Banco de Dados a fim de modelar o Banco de Dados que armazenará todos os dados As regras de negócio são Funcionário Deverão ser armazenados os seguintes dados CPF nome telefone email login e senha Banco de Dados Trabalho Relatório 1 Centro Universitário Internacional UNINTER Escola Superior Politécnica ESP Hotel Deverão ser armazenados os seguintes dados identificação do hotel nome categoria telefone email e endereço sendo o endereço composto por rua número complemento bairro CEP cidade e estado Quarto Deverão ser armazenados os seguintes dados identificação do quarto número de leitos tipo standard luxo ou suíte preço da diária e status disponível ocupado ou manutenção Hóspede Deverão ser armazenados os seguintes dados CPF nome telefone e mail e endereço sendo o endereço composto por rua número complemento bairro CEP cidade e estado Reserva Deverão ser armazenados os seguintes dados identificação da reserva data de entrada data de saída e status ativa cancelada ou concluída Pagamento Deverão ser armazenados os seguintes dados identificação do pagamento forma de pagamento cartão pix ou dinheiro data do pagamento valor total e status pago ou pendente Um hotel possui um ou vários quartos Um ou vários funcionários trabalham em um hotel Um funcionário realiza uma ou várias reservas Um ou vários quartos fazem parte de uma ou várias reservas Um hóspede pode fazer uma ou várias reservas Uma reserva gera um pagamento Importante O Modelo EntidadeRelacionamento MER deve considerar somente as regras de negócio dadas não podendo ser criada nenhuma outra entidade ou atributo que não estejam nas regras de negócio Em caso de haver entidade associativa a mesma deve ser representada pela Representação 1 texto da Aula 1 Fundamentos de Banco de Dados Figura 25 Em caso de haver cardinalidade 11 a chave estrangeira deve fazer parte da entidade que possui o maior número de chaves estrangeiras Cole o Modelo EntidadeRelacionamento MER aqui Banco de Dados Trabalho Relatório 2 Centro Universitário Internacional UNINTER Escola Superior Politécnica ESP 2 2ª Etapa Implementação Considere o seguinte Modelo Relacional modelo lógico referente ao estudo de caso de uma Locadora de Veículos Com base no Modelo Relacional dado e utilizando a Structured Query Language SQL no MySQL Workbench implemente o que se pede Importante Para testar o Banco de Dados após a implementação utilize os comandos contidos no arquivo Trabalho Populando o Banco de Dados para popular as tabelas Tal arquivo contém todos os comandos de inserção dos dados fictícios necessários para a realização dos testes Pontuação 30 pontos 1 Implemente um Banco de Dados chamado LocadoraVeiculos Após implemente as tabelas conforme o Modelo Relacional dado observando as chaves primárias e as chaves estrangeiras Todos os campos de todas as tabelas não podem ser nulos not null Banco de Dados Trabalho Relatório 3 Centro Universitário Internacional UNINTER Escola Superior Politécnica ESP Cole o código aqui Pontuação 10 pontos 2 Implemente uma consulta para listar a descrição a data e o custo de todas as manutenções realizadas nos veículos Cole o código e o print resultante da consulta aqui Pontuação 10 pontos 3 Implemente uma consulta para listar o valor total arrecadado pela locadora Lembrese que pagamentos pendentes não fazem parte da soma Cole o código e o print resultante da consulta aqui Pontuação 10 pontos 4 Implemente uma consulta para listar o modelo e a marca dos veículos bem como o número de vezes que cada um foi locado A listagem deve ser mostrada em ordem decrescente pelo número de aluguéis Dica Utilize a cláusula group by Cole o código e o print resultante da consulta aqui Pontuação 10 pontos 5 Implemente uma consulta para listar o nome dos clientes que possuem pagamento pendente bem como o valor devido por eles A listagem deve ser mostrada em ordem alfabética crescente pelo nome dos clientes Dica Utilize a cláusula group by Cole o código e o print resultante da consulta aqui Banco de Dados Trabalho Relatório 4 BANCO DE DADOS Trabalho Relatório Curso Alunoa Diego RU 1 1ª Etapa Modelagem Pontuação 30 pontos Dadas as regras de negócio abaixo listadas referentes ao estudo de caso de uma Rede de Hotéis elabore o Modelo EntidadeRelacionamento MER isto é o modelo conceitual O Modelo EntidadeRelacionamento MER deve contemplar os seguintes itens Entidades Atributos Relacionamentos Cardinalidades Chaves primárias Chaves estrangeiras Uma Rede de Hotéis necessita controlar os dados dos funcionários das unidades dos quartos dos hóspedes das reservas e dos pagamentos Para isso contratou um profissional de Banco de Dados a fim de modelar o Banco de Dados que armazenará todos os dados As regras de negócio são Funcionário Deverão ser armazenados os seguintes dados CPF nome telefone email login e senha Banco de Dados Trabalho Relatório 1 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Hotel Deverão ser armazenados os seguintes dados identificação do hotel nome categoria telefone email e endereço sendo o endereço composto por rua número complemento bairro CEP cidade e estado Quarto Deverão ser armazenados os seguintes dados identificação do quarto número de leitos tipo standard luxo ou suíte preço da diária e status disponível ocupado ou manutenção Hóspede Deverão ser armazenados os seguintes dados CPF nome telefone e mail e endereço sendo o endereço composto por rua número complemento bairro CEP cidade e estado Reserva Deverão ser armazenados os seguintes dados identificação da reserva data de entrada data de saída e status ativa cancelada ou concluída Pagamento Deverão ser armazenados os seguintes dados identificação do pagamento forma de pagamento cartão pix ou dinheiro data do pagamento valor total e status pago ou pendente Um hotel possui um ou vários quartos Um ou vários funcionários trabalham em um hotel Um funcionário realiza uma ou várias reservas Um ou vários quartos fazem parte de uma ou várias reservas Um hóspede pode fazer uma ou várias reservas Uma reserva gera um pagamento Importante O Modelo EntidadeRelacionamento MER deve considerar somente as regras de negócio dadas não podendo ser criada nenhuma outra entidade ou atributo que não estejam nas regras de negócio Em caso de haver entidade associativa a mesma deve ser representada pela Representação 1 texto da Aula 1 Fundamentos de Banco de Dados Figura 25 Em caso de haver cardinalidade 11 a chave estrangeira deve fazer parte da entidade que possui o maior número de chaves estrangeiras Modelo EntidadeRelacionamento Banco de Dados Trabalho Relatório 2 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER 2 2ª Etapa Implementação Considere o seguinte Modelo Relacional modelo lógico referente ao estudo de caso de uma Locadora de Veículos Banco de Dados Trabalho Relatório 3 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Com base no Modelo Relacional dado e utilizando a Structured Query Language SQL no MySQL Workbench implemente o que se pede Importante Para testar o Banco de Dados após a implementação utilize os comandos contidos no arquivo Trabalho Populando o Banco de Dados para popular as tabelas Tal arquivo contém todos os comandos de inserção dos dados fictícios necessários para a realização dos testes Pontuação 30 pontos 1 Implemente um Banco de Dados chamado LocadoraVeiculos Após implemente as tabelas conforme o Modelo Relacional dado observando as chaves primárias e as chaves estrangeiras Todos os campos de todas as tabelas não podem ser nulos not null Código CREATE DATABASE LocadoraVeiculos USE LocadoraVeiculos Banco de Dados Trabalho Relatório 4 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER CREATE TABLE Cliente idCliente INT PRIMARY KEY CPF VARCHAR14 NOT NULL nome VARCHAR100 NOT NULL telefone VARCHAR15 NOT NULL email VARCHAR100 NOT NULL endereco VARCHAR200 NOT NULL CREATE TABLE Veiculo idVeiculo INT PRIMARY KEY modelo VARCHAR50 NOT NULL marca VARCHAR50 NOT NULL ano INT NOT NULL placa VARCHAR8 NOT NULL valorDiaria DECIMAL102 NOT NULL estado VARCHAR20 NOT NULL CREATE TABLE Manutencao idManutencao INT PRIMARY KEY idVeiculo INT NOT NULL descricao VARCHAR200 NOT NULL data DATE NOT NULL custo DECIMAL102 NOT NULL FOREIGN KEY idVeiculo REFERENCES VeiculoidVeiculo CREATE TABLE Locacao idLocacao INT PRIMARY KEY idCliente INT NOT NULL idVeiculo INT NOT NULL Banco de Dados Trabalho Relatório 5 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER dataInicio DATE NOT NULL dataFim DATE NOT NULL FOREIGN KEY idCliente REFERENCES ClienteidCliente FOREIGN KEY idVeiculo REFERENCES VeiculoidVeiculo CREATE TABLE Pagamento idPagamento INT PRIMARY KEY idLocacao INT NOT NULL valor DECIMAL102 NOT NULL status VARCHAR20 NOT NULL FOREIGN KEY idLocacao REFERENCES LocacaoidLocacao Pontuação 10 pontos 2 Implemente uma consulta para listar a descrição a data e o custo de todas as manutenções realizadas nos veículos Código e o print SELECT mdescricao mdata mcusto FROM Manutencao m Banco de Dados Trabalho Relatório 6 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Pontuação 10 pontos 3 Implemente uma consulta para listar o valor total arrecadado pela locadora Lembrese que pagamentos pendentes não fazem parte da soma Código e o print SELECT SUMpvalorTotal AS valortotalarrecadado FROM Pagamento p WHERE pestado Pago Banco de Dados Trabalho Relatório 7 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Pontuação 10 pontos 4 Implemente uma consulta para listar o modelo e a marca dos veículos bem como o número de vezes que cada um foi locado A listagem deve ser mostrada em ordem decrescente pelo número de aluguéis Dica Utilize a cláusula group by Código e o print SELECT vmodelo vmarca COUNTlvidLocacao AS numerolocacoes FROM Veiculo v LEFT JOIN LocacaoVeiculo lv ON vidVeiculo lvidVeiculo GROUP BY vidVeiculo vmodelo vmarca ORDER BY numerolocacoes DESC Banco de Dados Trabalho Relatório 8 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Pontuação 10 pontos 5 Implemente uma consulta para listar o nome dos clientes que possuem pagamento pendente bem como o valor devido por eles A listagem deve ser mostrada em ordem alfabética crescente pelo nome dos clientes Dica Utilize a cláusula group by Código e o print SELECT cnome SUMpvalorTotal AS valordevido FROM Cliente c JOIN Locacao l ON cidCliente lidCliente JOIN Pagamento p ON lidPagamento pidPagamento WHERE pestado Pendente GROUP BY cidCliente cnome ORDER BY cnome ASC Banco de Dados Trabalho Relatório 9 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER Banco de Dados Trabalho Relatório 10 Escola Superior Politécnica ESP Centro Universitário Internacional UNINTER