Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
BANCO DE DADOS
Trabalho – Relatório
Curso: Tecnologia em Análise e Desenvolvimento de Sistemas
Aluno(a): Gabriele Lauriane Fiduniv
RU: 4023482
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 Entidade-Relacionamento (MER), isto é, o modelo
conceitual.
O Modelo Entidade-Relacionamento (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,
e-mail, 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, e-mail 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 Entidade-Relacionamento (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 (1,1), a chave estrangeira deve fazer parte da
entidade que possui o maior número de chaves estrangeiras.
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:
Banco de Dados: Trabalho - Relatório
3
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
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).
create database LocadoraVeiculos;
use LocadoraVeiculos;
create table Cliente (
Banco de Dados: Trabalho - Relatório
4
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
idCliente int auto_increment primary key,
CPF varchar(20),
nome varchar(50),
telefone varchar(20),
email varchar(50),
endereco varchar(100)
);
create table Veiculo (
idVeiculo int auto_increment primary key,
modelo varchar(50),
marca varchar(50),
ano int,
placa varchar(10),
valorDiaria decimal(7,2),
estado enum('Disponível', 'Alugado', 'Manutenção')
);
create table Pagamento (
idPagamento int auto_increment primary key,
forma enum('Cartão', 'Pix', 'Dinheiro'),
dataPagamento date,
valorTotal decimal(7,2),
estado enum('Pago', 'Pendente')
);
create table Locacao (
Banco de Dados: Trabalho - Relatório
5
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
idLocacao int auto_increment primary key,
idCliente int,
idPagamento int,
dataInicio date,
dataFim date,
constraint fk_Locacao_Cliente foreign key (idCliente) references Cliente(idCliente),
Constraint fk_Locacao_Pagamento foreign key (idPagamento) references
Pagamento(idPagamento
);
create table LocacaoVeiculo (
idLocacao int,
idVeiculo int,
primary key (idLocacao, idVeiculo),
constraint fk_LocacaoVeiculo_Locacao foreign key (idLocacao) references
Locacao(idLocacao),
constraint fk_LocacaoVeiculo_Veiculo foreign key (idVeiculo) references
Veiculo(idVeiculo)
);
create table Manutencao (
idManutencao int auto_increment primary key,
idVeiculo int,
descricao varchar(100),
dataManutencao date,
custo decimal(7,2),
constraint fk_Manutencao_Veiculo foreign key (idVeiculo) references Veiculo(idVeiculo)
);
Banco de Dados: Trabalho - Relatório
6
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
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.
select descricao Descrição, dataManutencao Data, Custo
from Manutencao;
Pontuação: 10 pontos.
3. Implemente uma consulta para listar o valor total arrecadado pela locadora.
Lembre-se que pagamentos “pendentes” não fazem parte da soma.
select
sum(valorTotal) 'Total arrecadado'
from Pagamento
where estado = 'Pago'
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.
Banco de Dados: Trabalho - Relatório
7
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
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
8
