MAPA – Material de Avaliação Prática da Aprendizagem

Acadêmico: Joice Oliveira Mota R.A. 22025417-5

Curso: Engenharia de Software

Disciplina: Banco de Dados II

Valor da atividade: 3,50 Prazo: 30/06/2024

Instruções para Realização da Atividade

1. Todos os campos acima deverão ser devidamente preenchidos;

2. É obrigatória a utilização deste formulário para a realização do MAPA;
3. Esta é uma atividade individual. Caso identificado cópia de colegas, o trabalho de
ambos sofrerá decréscimo de nota;
4. Utilizando este formulário, realize sua atividade, salve em seu computador, renomeie e
envie em forma de anexo no campo de resposta da atividade MAPA;
5. Formatação exigida para esta atividade: documento Word, Fonte Arial ou Times New
Roman tamanho 12, Espaçamento entre linhas 1,5, texto justificado;
6. Ao utilizar quaisquer materiais de pesquisa referência conforme as normas da ABNT;
7. No campo “Material da disciplina”, no ambiente virtual da disciplina, você encontrará
orientações importantes para elaboração desta atividade. Confira!
8. Critérios de avaliação: Utilização do template; atendimento ao Tema; Constituição dos
argumentos e organização das Ideias; Correção Gramatical e atendimento às normas
ABNT.
9. Procure argumentar claramente e objetiva, conforme o conteúdo da disciplina.

Em caso de dúvidas, entre em contato com seu Professor Mediador.

Bons estudos!
1. Crie o esquema de banco de dados para o sistema de gerenciamento e hospedagens hoteleiras
nomeado “hospedar_db”.

CREATE DATABASE IF NOT EXISTS hospedar_db;

USE hospedar_db;

2. Crie as tabelas "Hotel", "Quarto", "Cliente" e "Hospedagem" com as colunas especificadas

anteriormente.
CREATE TABLE Hotel (
hotel_id INT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
cidade VARCHAR(100) NOT NULL,
uf VARCHAR(2) NOT NULL,
ratting INT NOT NULL
);

CREATE TABLE Quarto (

quarto_id INT PRIMARY KEY,
hotel_id INT NOT NULL,
numero INT NOT NULL,
tipo VARCHAR(50) NOT NULL,
preco_diaria DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
);

CREATE TABLE Cliente (

cliente_id INT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
telefone VARCHAR(20) NOT NULL,
cpf VARCHAR(14) NOT NULL UNIQUE
);
 CREATE TABLE Hospedagem (
hospedagem_id INT PRIMARY KEY,
cliente_id INT NOT NULL,
quarto_id INT NOT NULL,
dt_checkin DATE NOT NULL,
dt_checkout DATE NOT NULL,
valor_total_hosp FLOAT NOT NULL,
status_hosp VARCHAR(20) NOT NULL,
checkin_realizado BOOLEAN,
FOREIGN KEY (cliente_id) REFERENCES Cliente(cliente_id),
FOREIGN KEY (quarto_id) REFERENCES Quarto(quarto_id)
);

3. Insira dados artificiais nas tabelas "Hotel" (2 hotéis), "Quarto"(5 para cada hotel),
"Cliente"(3 clientes) e "Hospedagem" (20 hospedagens, 5 para cada um dos “Status_hosp”)
para simular hotéis, quartos, clientes e hospedagens.
INSERT INTO Hotel (hotel_id, nome, cidade, uf, ratting)
VALUES
(1, 'Hotel A', 'Cidade A', 'UF', 4),
(2, 'Hotel B', 'Cidade B', 'UF', 3);

INSERT INTO Quarto (quarto_id, hotel_id, numero, tipo, preco_diaria)

VALUES
(1, 1, 101, 'Standard', 100.00),
(2, 1, 102, 'Deluxe', 150.00),
(3, 1, 103, 'Suíte', 250.00),
(4, 1, 104, 'Standard', 100.00),
(5, 1, 105, 'Deluxe', 150.00),
(6, 2, 201, 'Standard', 120.00),
(7, 2, 202, 'Deluxe', 180.00),
(8, 2, 203, 'Suíte', 280.00),
(9, 2, 204, 'Standard', 120.00),
(10, 2, 205, 'Deluxe', 180.00);

INSERT INTO Cliente (cliente_id, nome, email, telefone, cpf)

VALUES
(1, 'Cliente A', '[email protected]', '(00) 0000-0000', '111.111.111-
11'),
(2, 'Cliente B', '[email protected]', '(11) 1111-1111', '222.222.222-
22'),
(3, 'Cliente C', '[email protected]', '(22) 2222-2222', '333.333.333-
33');
 INSERT INTO Hospedagem (hospedagem_id, cliente_id, quarto_id, dt_checkin,
dt_checkout, valor_total_hosp, status_hosp, checkin_realizado)
VALUES
(1, 1, 1, '2023-01-01', '2023-01-05', 500.00, 'finalizada', true),
(2, 2, 6, '2023-02-01', '2023-02-03', 240.00, 'finalizada', true),
(3, 3, 10, '2023-02-15', '2023-02-17', 360.00, 'finalizada', true),
(4, 1, 3, '2023-03-10', '2023-03-12', 500.00, 'finalizada', true),
(5, 2, 8, '2023-04-05', '2023-04-10', 900.00, 'finalizada', true),
(6, 3, 2, '2023-04-20', '2023-04-25', 750.00, 'reserva', false),
(7, 1, 5, '2023-05-01', '2023-05-03', 300.00, 'reserva', false),
(8, 2, 9, '2023-06-10', '2023-06-12', 360.00, 'reserva', false),
(9, 3, 4, '2023-07-15', '2023-07-17', 200.00, 'cancelada', false),
(10, 1, 1, '2023-08-01', '2023-08-05', 500.00, 'hospedado', true),
(11, 2, 6, '2023-09-01', '2023-09-05', 480.00, 'hospedado', true),
(12, 3, 7, '2023-10-10', '2023-10-15', 900.00, 'hospedado', true),
(13, 1, 2, '2023-11-15', '2023-11-18', 450.00, 'reserva', false),
(14, 2, 8, '2023-12-20', '2023-12-22', 360.00, 'reserva', false),
(15, 3, 9, '2024-01-05', '2024-01-07', 240.00, 'cancelada', false),
(16, 1, 3, '2024-02-10', '2024-02-12', 500.00, 'hospedado', true),
(17, 2, 7, '2024-03-05', '2024-03-10', 900.00, 'hospedado', true),
(18, 3, 5, '2024-04-01', '2024-04-05', 600.00, 'hospedado', true),
(19, 1, 4, '2024-05-15', '2024-05-17', 200.00, 'reserva', false),
(20, 2, 10, '2024-06-20', '2024-06-22', 360.00, 'reserva', false);

4. Escreva as seguintes consultas e comandos SQL:

a. Listar todos os hotéis e seus respectivos quartos, apresentando os seguintes campos: para
hotel, nome e cidade; para quarto, tipo e preco_diaria;
SELECT h.nome AS hotel_nome, h.cidade, q.tipo, q.preco_diaria
FROM Hotel h
JOIN Quarto q ON h.hotel_id = q.hotel_id;

b. Listar todos os clientes que já realizaram hospedagens (status_hosp igual á “finalizada”), e

os respectivos quartos e hotéis;
 SELECT c.nome AS cliente_nome, h.nome AS hotel_nome, q.numero AS
numero_quarto
FROM Cliente c
JOIN Hospedagem hos ON c.cliente_id = hos.cliente_id
JOIN Quarto q ON hos.quarto_id = q.quarto_id
JOIN Hotel h ON q.hotel_id = h.hotel_id
WHERE hos.status_hosp = 'finalizada';

c. Mostrar o histórico de hospedagens em ordem cronológica de um determinado cliente;

SELECT h.dt_checkin, h.dt_checkout, h.status_hosp, ho.nome AS hotel_nome,
q.numero AS numero_quarto
FROM Hospedagem h
JOIN Cliente c ON h.cliente_id = c.cliente_id
JOIN Quarto q ON h.quarto_id = q.quarto_id
JOIN Hotel ho ON q.hotel_id = ho.hotel_id
WHERE c.nome = 'Cliente A'
ORDER BY h.dt_checkin;

d. Apresentar o cliente com maior número de hospedagens (não importando o tempo em que
ficou hospedado);

SELECT c.nome AS cliente_nome, COUNT(*) AS total_hospedagens

FROM Cliente c
JOIN Hospedagem h ON c.cliente_id = h.cliente_id
GROUP BY c.nome
ORDER BY total_hospedagens DESC
LIMIT 1;

e. Apresentar clientes que tiveram hospedagem “cancelada”, os respectivos quartos e hotéis.

SELECT c
f. Calcular a receita de todos os hotéis (hospedagem com status_hosp igual a “finalizada”),
ordenado de forma decrescente;
SELECT H.nome AS hotel_nome, SUM(Hs.valor_total_hosp) AS receita_total
FROM Hospedagem Hs
JOIN Quarto Q ON Hs.quarto_id = Q.quarto_id
JOIN Hotel H ON Q.hotel_id = H.hotel_id
WHERE Hs.status_hosp = 'finalizada'
GROUP BY H.nome
ORDER BY receita_total DESC;

g. Listar todos os clientes que já fizeram uma reserva em um hotel específico;

SELECT C.nome
FROM Hospedagem Hs
JOIN Cliente C ON Hs.cliente_id = C.cliente_id
JOIN Quarto Q ON Hs.quarto_id = Q.quarto_id
WHERE Q.hotel_id = 1;

h. Listar o quanto cada cliente que gastou em hospedagens (status_hosp igual a “finalizada”),
em ordem decrescente por valor gasto.
SELECT C.nome, SUM(Hs.valor_total_hosp) AS total_gasto
FROM Hospedagem Hs
JOIN Cliente C ON Hs.cliente_id = C.cliente_id
WHERE Hs.status_hosp = 'finalizada'
GROUP BY C.nome
ORDER BY total_gasto DESC;

i. Listar todos os quartos que ainda não receberam hóspedes.

SELECT Q.quarto_id, Q.numero
FROM Quarto Q
LEFT JOIN Hospedagem Hs ON Q.quarto_id = Hs.quarto_id
WHERE Hs.quarto_id IS NULL;
j. Apresentar a média de preços de diárias em todos os hotéis, por tipos de quarto.
SELECT Q.tipo, AVG(Q.preco_diaria) AS media_preco
FROM Quarto Q
GROUP BY Q.tipo;

l. Criar a coluna checkin_realizado do tipo booleano na tabela Hospedagem (via código). E

atribuir verdadeiro para as Hospedagens com status_hosp “finalizada” e “hospedado”, e como
falso para Hospedagens com status_hosp “reserva” e “cancelada”.
ALTER TABLE Hospedagem ADD COLUMN checkin_realizado BOOLEAN;
UPDATE Hospedagem
SET checkin_realizado = CASE
WHEN status_hosp IN ('finalizada', 'hospedado') THEN TRUE
WHEN status_hosp IN ('reserva', 'cancelada') THEN FALSE
ELSE NULL
END;
SELECT hospedagem_id, status_hosp, checkin_realizado
FROM Hospedagem;

m. Mudar o nome da coluna “classificacao” da tabela Hotel para “ratting” (via código).
ALTER TABLE Hotel RENAME COLUMN classificacao TO ratting;

5. Efetue a criação dos seguintes procedimentos usando PL/MySQL:

a. Criar uma procedure chamada "RegistrarCheckIn" que aceita hospedagem_id e data_checkin
como parâmetros. A procedure deve atualizar a data de check-in na tabela "Hospedagem" e
mudar o status_hosp para "hospedado".
DELIMITER //
CREATE PROCEDURE RegistrarCheckIn(IN p_hospedagem_id INT, IN p_data_checkin
DATE)
BEGIN
UPDATE Hospedagem
SET dt_checkin = p_data_checkin, status_hosp = 'hospedado'
WHERE hospedagem_id = p_hospedagem_id

b. Criar uma procedure chamada "CalcularTotalHospedagem" que aceita hospedagem_id

como parâmetro. A procedure deve calcular o valor total da hospedagem com base na diferença
de dias entre check-in e check-out e o preço da diária do quarto reservado. O valor deve ser
atualizado na coluna valor_total_hosp.
CREATE PROCEDURE CalcularTotalHospedagem(IN p_hospedagem_id INT)
BEGIN
DECLARE v_dt_checkin DATE;
DECLARE v_dt_checkout DATE;
DECLARE v_preco_diaria DECIMAL(10, 2);
DECLARE v_dias INT;
DECLARE v_valor_total FLOAT;

SELECT H.dt_checkin, H.dt_checkout, Q.preco_diaria

INTO v_dt_checkin, v_dt_checkout, v_preco_diaria
FROM Hospedagem H
JOIN Quarto Q ON H.quarto_id = Q.quarto_id
WHERE H.hospedagem_id = p_hospedagem_id;

SET v_dias = DATEDIFF(v_dt_checkout, v_dt_checkin);

SET v_valor_total = v_dias * v_preco_diaria;

UPDATE Hospedagem
SET valor_total_hosp = v_valor_total
WHERE hospedagem_id = p_hospedagem_id;
 END

c. Criar uma procedure chamada "RegistrarCheckout" que aceita hospedagem_id e

data_checkout como parâmetros. A procedure deve atualizar a data de check-out na tabela
"Hospedagem" e mudar o status_hosp para "finalizada".
CREATE PROCEDURE RegistrarCheckout(IN p_hospedagem_id INT, IN
p_data_checkout DATE)
BEGIN

UPDATE Hospedagem
SET dt_checkout = p_data_checkout, status_hosp = 'finalizada'
WHERE hospedagem_id = p_hospedagem_id;
END

6. Efetue a criação das seguintes funções utilizando PL/MySQL:

a. Criar uma function chamada "TotalHospedagensHotel" que aceita hotel_id como parâmetro.
A função deve retornar o número total de hospedagens realizadas em um determinado hotel.
CREATE FUNCTION TotalHospedagensHotel(p_hotel_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v_total_hospedagens INT;

SELECT COUNT(*)
INTO v_total_hospedagens
FROM Hospedagem Hs
JOIN Quarto Q ON Hs.quarto_id = Q.quarto_id
WHERE Q.hotel_id = p_hotel_id;

RETURN v_total_hospedagens;
END
b. Criar uma function chamada "ValorMedioDiariasHotel" que aceita hotel_id como
parâmetro. A função deve calcular e retornar o valor médio das diárias dos quartos deste hotel.
CREATE FUNCTION ValorMedioDiariasHotel(p_hotel_id INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
DECLARE v_valor_medio DECIMAL(10, 2);

-- Calcular o valor médio das diárias dos quartos de um determinado

hotel
SELECT AVG(preco_diaria)
INTO v_valor_medio
FROM Quarto
WHERE hotel_id = p_hotel_id;

RETURN v_valor_medio;
END

c. Criar uma function chamada "VerificarDisponibilidadeQuarto" que aceita quarto_id e data

como parâmetros. A função deve retornar um valor booleano indicando se o quarto está
disponível ou não para reserva na data especificada.
CREATE FUNCTION VerificarDisponibilidadeQuarto(p_quarto_id INT, p_data DATE)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE v_disponivel BOOLEAN;

IF EXISTS (
SELECT 1
FROM Hospedagem
WHERE quarto_id = p_quarto_id
AND p_data BETWEEN dt_checkin AND dt_checkout
AND status_hosp IN ('reserva', 'hospedado')
) THEN
 SET v_disponivel = FALSE;
ELSE
SET v_disponivel = TRUE;
END IF;

RETURN v_disponivel;
END

7. Efetue a criação das seguintes triggers utilizando PL/MySQL:

a. Criar um trigger chamado "AntesDeInserirHospedagem" que é acionado antes de uma

inserção na tabela "Hospedagem". O trigger deve verificar se o quarto está disponível na data
de check-in. Se não estiver, a inserção deve ser cancelada.
CREATE TRIGGER AntesDeInserirHospedagem
BEFORE INSERT ON Hospedagem
FOR EACH ROW
BEGIN
DECLARE v_quarto_disponivel BOOLEAN;

-- Verificar se o quarto está disponível na data de check-in

SET v_quarto_disponivel = (SELECT NOT EXISTS (
SELECT 1
FROM Hospedagem
WHERE quarto_id = NEW.quarto_id
AND NEW.dt_checkin BETWEEN dt_checkin AND dt_checkout
AND status_hosp IN ('reserva', 'hospedado')
));

-- Se o quarto não estiver disponível, cancelar a inserção

IF v_quarto_disponivel = FALSE THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'O quarto não está disponível na data de check-in
especificada.';
END IF;
END

b. Cria um trigger chamado "AposDeletarCliente" que é acionado após a exclusão de um cliente

na tabela "Cliente". O trigger deve registrar a exclusão em uma tabela de log.
CREATE TABLE LogExclusaoCliente (
log_id INT AUTO_INCREMENT PRIMARY KEY,
cliente_id INT NOT NULL,
nome VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
telefone VARCHAR(50) NOT NULL,
cpf VARCHAR(20) NOT NULL,
data_exclusao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER AposDeletarCliente

AFTER DELETE ON Cliente
FOR EACH ROW
BEGIN
INSERT INTO LogExclusaoCliente (cliente_id, nome, email, telefone, cpf)
VALUES (OLD.cliente_id, OLD.nome, OLD.email, OLD.telefone, OLD.cpf);
END