Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
BANCO DE DADOS
Trabalho – Relatório
Curso: BACHARELADO EM ENGENHARIA DE SOFTWARE - DISTÂNCIA
Aluno(a): Cássio Montenegro Marques
RU: 5297183
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.
Modelo Entidade-Relacionamento (MER):
Banco de Dados: Trabalho - Relatório
2
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
2. 2ª Etapa – Implementação
Banco de Dados: Trabalho - Relatório
3
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
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).
create database LocadoraVeiculos;
Banco de Dados: Trabalho - Relatório
4
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
use LocadoraVeiculos;
create table Cliente(
idCliente int primary key,
CPF varchar (20) not null,
nome varchar (50) not null,
telefone varchar (20) not null,
email varchar (50) not null,
endereco varchar (100) not null
);
create table Pagamento(
idPagamento int primary key,
forma enum('Cartão', 'Pix', 'Dinheiro') not null,
dataPagamento date not null,
valorTotal decimal (7,2) not null,
estado enum('Pago', 'Pendente') not null
);
create table Veiculo(
idVeiculo int primary key,
modelo varchar (50) not null,
marca varchar (50) not null,
ano int not null,
placa varchar (10) not null,
valorDiaria decimal (7,2) not null,
estado enum ('Disponível', 'Alugado', 'Manutenção') not null
);
create table Locacao(
Banco de Dados: Trabalho - Relatório
5
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
idLocacao int primary key,
idCliente int not null,
idPagamento int not null,
dataInicio date,
dataFim date,
constraint fkLocacaoCliente foreign key(idCliente) references Cliente(idCliente),
constraint fkLocacaoPagamento foreign key(idPagamento) references
Pagamento(idPagamento)
);
create table LocacaoVeiculo(
idLocacao int not null,
idVeiculo int not null,
PRIMARY KEY (idLocacao, idVeiculo),
FOREIGN KEY (idLocacao) REFERENCES Locacao(idLocacao),
FOREIGN KEY (idVeiculo) REFERENCES Veiculo(idVeiculo)
);
create table Manutencao(
idManutencao int primary key,
idVeiculo int not null,
descricao varchar (100) not null,
dataManutencao date not null,
custo decimal (7,2) not null,
constraint fkManutencaoVeiculo 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.
Código:
select descricao, dataManutencao, 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) AS Valor_Total_Arrecadado from Pagamento
where estado = 'Pago';
Banco de Dados: Trabalho - Relatório
7
Centro Universitário Internacional UNINTER
Escola Superior Politécnica – ESP
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.
select modelo, marca, COUNT([Link]) AS quantidade_alugueis
from Veiculo
inner join LocacaoVeiculo
on [Link] = [Link]
group by modelo, marca
order by quantidade_alugueis desc;
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.
select nome, sum([Link]) AS valor_devido
from Cliente
inner Join Locacao on [Link] = [Link]
inner join Pagamento on [Link] = [Link]
WHERE [Link] = 'Pendente'
group by [Link]
ORDER BY [Link] ASC;
Banco de Dados: Trabalho - Relatório
8
