Contoh ERD Pemesanan Reservasi Kamar Hotel – Tutorial Lengkap Database Design

— Membuat database hotel
CREATE DATABASE hotel_reservation_system;
USE hotel_reservation_system;– Tabel GUEST
CREATE TABLE guest (
guest_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
address TEXT,
id_number VARCHAR(50),
nationality VARCHAR(50),
date_of_birth DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

— Tabel ROOM_TYPE
CREATE TABLE room_type (
room_type_id INT PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(50) NOT NULL,
description TEXT,
base_price DECIMAL(10,2) NOT NULL,
max_occupancy INT NOT NULL,
bed_type VARCHAR(50),
room_size DECIMAL(5,2),
amenities TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— Tabel ROOM
CREATE TABLE room (
room_id INT PRIMARY KEY AUTO_INCREMENT,
room_type_id INT NOT NULL,
room_number VARCHAR(10) UNIQUE NOT NULL,
floor INT,
status ENUM(‘Available’, ‘Occupied’, ‘Maintenance’, ‘Out of Order’) DEFAULT ‘Available’,
view_type VARCHAR(50),
last_cleaned TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_type_id) REFERENCES room_type(room_type_id)
);

— Tabel RESERVATION
CREATE TABLE reservation (
reservation_id INT PRIMARY KEY AUTO_INCREMENT,
guest_id INT NOT NULL,
room_id INT NOT NULL,
check_in_date DATE NOT NULL,
check_out_date DATE NOT NULL,
adults INT NOT NULL DEFAULT 1,
children INT DEFAULT 0,
total_amount DECIMAL(10,2) NOT NULL,
status ENUM(‘Pending’, ‘Confirmed’, ‘Checked-in’, ‘Checked-out’, ‘Cancelled’) DEFAULT ‘Pending’,
special_requests TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (guest_id) REFERENCES guest(guest_id),
FOREIGN KEY (room_id) REFERENCES room(room_id)
);

— Tabel PAYMENT
CREATE TABLE payment (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
reservation_id INT NOT NULL,
payment_method ENUM(‘Cash’, ‘Credit Card’, ‘Debit Card’, ‘Bank Transfer’, ‘E-Wallet’) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
transaction_id VARCHAR(100),
status ENUM(‘Pending’, ‘Completed’, ‘Failed’, ‘Refunded’) DEFAULT ‘Pending’,
notes TEXT,
FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
);

— Tabel EMPLOYEE
CREATE TABLE employee (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
employee_code VARCHAR(20) UNIQUE NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
hire_date DATE NOT NULL,
salary DECIMAL(10,2),
status ENUM(‘Active’, ‘Inactive’, ‘Terminated’) DEFAULT ‘Active’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— Tabel SERVICE
CREATE TABLE service (
service_id INT PRIMARY KEY AUTO_INCREMENT,
service_name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(50),
availability BOOLEAN DEFAULT TRUE,
duration INT, — dalam menit
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— Tabel RESERVATION_SERVICE (Many-to-Many relationship)
CREATE TABLE reservation_service (
reservation_service_id INT PRIMARY KEY AUTO_INCREMENT,
reservation_id INT NOT NULL,
service_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
service_date DATE,
total_price DECIMAL(10,2) NOT NULL,
status ENUM(‘Requested’, ‘Confirmed’, ‘Completed’, ‘Cancelled’) DEFAULT ‘Requested’,
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id),
FOREIGN KEY (service_id) REFERENCES service(service_id)
);

— Tabel HOUSEKEEPING
CREATE TABLE housekeeping (
housekeeping_id INT PRIMARY KEY AUTO_INCREMENT,
room_id INT NOT NULL,
employee_id INT NOT NULL,
cleaning_date DATE NOT NULL,
cleaning_type ENUM(‘Checkout Cleaning’, ‘Maintenance Cleaning’, ‘Deep Cleaning’) NOT NULL,
start_time TIME,
end_time TIME,
status ENUM(‘Scheduled’, ‘In Progress’, ‘Completed’) DEFAULT ‘Scheduled’,
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_id) REFERENCES room(room_id),
FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

— Tabel RATE
CREATE TABLE rate (
rate_id INT PRIMARY KEY AUTO_INCREMENT,
room_type_id INT NOT NULL,
rate_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
day_type ENUM(‘Weekday’, ‘Weekend’, ‘Holiday’) DEFAULT ‘Weekday’,
season ENUM(‘Low’, ‘High’, ‘Peak’) DEFAULT ‘Low’,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_type_id) REFERENCES room_type(room_type_id)
);

— Membuat index untuk optimasi query
CREATE INDEX idx_guest_email ON guest(email);
CREATE INDEX idx_room_number ON room(room_number);
CREATE INDEX idx_reservation_dates ON reservation(check_in_date, check_out_date);
CREATE INDEX idx_reservation_status ON reservation(status);
CREATE INDEX idx_payment_date ON payment(payment_date);
CREATE INDEX idx_rate_dates ON rate(valid_from, valid_to);
CREATE INDEX idx_housekeeping_date ON housekeeping(cleaning_date);– Composite index untuk query kompleks
CREATE INDEX idx_room_status_type ON room(status, room_type_id);
CREATE INDEX idx_reservation_guest_dates ON reservation(guest_id, check_in_date, check_out_date);

— Insert sample room types
INSERT INTO room_type (type_name, description, base_price, max_occupancy, bed_type, room_size, amenities) VALUES
(‘Standard’, ‘Kamar standar dengan fasilitas dasar’, 500000, 2, ‘Double Bed’, 25.00, ‘AC, TV, WiFi, Kamar Mandi Dalam’),
(‘Deluxe’, ‘Kamar deluxe dengan fasilitas lengkap’, 750000, 2, ‘Queen Bed’, 35.00, ‘AC, TV LCD, WiFi, Mini Bar, Balkon’),
(‘Suite’, ‘Suite mewah dengan ruang tamu terpisah’, 1200000, 4, ‘King Bed + Sofa Bed’, 50.00, ‘AC, Smart TV, WiFi, Mini Bar, Jacuzzi, Balkon’),
(‘Family’, ‘Kamar keluarga dengan 2 tempat tidur’, 900000, 4, ‘2 Double Beds’, 40.00, ‘AC, TV, WiFi, Mini Fridge, Connecting Door’);– Insert sample rooms
INSERT INTO room (room_type_id, room_number, floor, status, view_type) VALUES
(1, ‘101’, 1, ‘Available’, ‘Garden View’),
(1, ‘102’, 1, ‘Available’, ‘Garden View’),
(1, ‘103’, 1, ‘Occupied’, ‘Garden View’),
(2, ‘201’, 2, ‘Available’, ‘City View’),
(2, ‘202’, 2, ‘Available’, ‘City View’),
(2, ‘203’, 2, ‘Maintenance’, ‘City View’),
(3, ‘301’, 3, ‘Available’, ‘Ocean View’),
(3, ‘302’, 3, ‘Available’, ‘Ocean View’),
(4, ‘401’, 4, ‘Available’, ‘Mountain View’),
(4, ‘402’, 4, ‘Occupied’, ‘Mountain View’);

— Insert sample guests
INSERT INTO guest (first_name, last_name, email, phone, address, id_number, nationality, date_of_birth) VALUES
(‘John’, ‘Doe’, ‘john.doe@email.com’, ‘+62812345678’, ‘Jl. Sudirman No. 123, Jakarta’, ‘3171234567890123’, ‘Indonesian’, ‘1985-05-15’),
(‘Jane’, ‘Smith’, ‘jane.smith@email.com’, ‘+62823456789’, ‘Jl. Thamrin No. 456, Jakarta’, ‘3171234567890124’, ‘Indonesian’, ‘1990-08-22’),
(‘Michael’, ‘Johnson’, ‘michael.j@email.com’, ‘+1234567890’, ‘123 Main St, New York, USA’, ‘P123456789’, ‘American’, ‘1982-12-10’),
(‘Sarah’, ‘Wilson’, ‘sarah.w@email.com’, ‘+447123456789’, ‘456 Oxford St, London, UK’, ‘UK987654321’, ‘British’, ‘1988-03-18’);

— Insert sample reservations
INSERT INTO reservation (guest_id, room_id, check_in_date, check_out_date, adults, children, total_amount, status, special_requests) VALUES
(1, 1, ‘2024-02-15’, ‘2024-02-18’, 2, 0, 1500000, ‘Confirmed’, ‘Late check-in around 10 PM’),
(2, 4, ‘2024-02-20’, ‘2024-02-23’, 2, 1, 2250000, ‘Confirmed’, ‘Extra bed for child’),
(3, 7, ‘2024-02-25’, ‘2024-02-28’, 2, 0, 3600000, ‘Pending’, ‘Honeymoon package’),
(4, 9, ‘2024-03-01’, ‘2024-03-05’, 4, 2, 3600000, ‘Confirmed’, ‘Connecting rooms if available’);

— Insert sample payments
INSERT INTO payment (reservation_id, payment_method, amount, transaction_id, status, notes) VALUES
(1, ‘Credit Card’, 750000, ‘TXN001234567’, ‘Completed’, ‘Down payment 50%’),
(2, ‘Bank Transfer’, 1125000, ‘TXN001234568’, ‘Completed’, ‘Down payment 50%’),
(3, ‘Credit Card’, 1800000, ‘TXN001234569’, ‘Completed’, ‘Down payment 50%’),
(4, ‘Cash’, 3600000, ‘TXN001234570’, ‘Completed’, ‘Full payment’);

— Insert sample services
INSERT INTO service (service_name, description, price, category, availability, duration) VALUES
(‘Room Service – Breakfast’, ‘Sarapan diantar ke kamar’, 150000, ‘Food & Beverage’, TRUE, 30),
(‘Laundry Service’, ‘Layanan cuci dan setrika pakaian’, 50000, ‘Laundry’, TRUE, 1440),
(‘Spa Treatment’, ‘Perawatan spa dan massage’, 300000, ‘Spa & Wellness’, TRUE, 90),
(‘Airport Transfer’, ‘Antar jemput bandara’, 200000, ‘Transportation’, TRUE, 60),
(‘Extra Bed’, ‘Tempat tidur tambahan’, 100000, ‘Room Amenity’, TRUE, 0);

— Insert sample employees
INSERT INTO employee (employee_code, first_name, last_name, position, department, email, phone, hire_date, salary) VALUES
(‘EMP001’, ‘Ahmad’, ‘Santoso’, ‘Front Office Manager’, ‘Front Office’, ‘ahmad.s@hotel.com’, ‘+62811111111’, ‘2020-01-15’, 8000000),
(‘EMP002’, ‘Siti’, ‘Nurhaliza’, ‘Receptionist’, ‘Front Office’, ‘siti.n@hotel.com’, ‘+62822222222’, ‘2021-03-10’, 4500000),
(‘EMP003’, ‘Budi’, ‘Prasetyo’, ‘Housekeeping Supervisor’, ‘Housekeeping’, ‘budi.p@hotel.com’, ‘+62833333333’, ‘2019-06-20’, 6000000),
(‘EMP004’, ‘Rina’, ‘Wati’, ‘Room Attendant’, ‘Housekeeping’, ‘rina.w@hotel.com’, ‘+62844444444’, ‘2022-01-05’, 3500000);

— Primary indexes (sudah otomatis)
— Secondary indexes untuk query optimization
CREATE INDEX idx_reservation_checkin ON reservation(check_in_date);
CREATE INDEX idx_reservation_checkout ON reservation(check_out_date);
CREATE INDEX idx_reservation_status ON reservation(status);
CREATE INDEX idx_room_status ON room(status);
CREATE INDEX idx_payment_status ON payment(status);
CREATE INDEX idx_guest_email ON guest(email);– Composite indexes untuk complex queries
CREATE INDEX idx_reservation_dates_status ON reservation(check_in_date, check_out_date, status);
CREATE INDEX idx_room_type_status ON room(room_type_id, status);
CREATE INDEX idx_payment_reservation_status ON payment(reservation_id, status);

— Partitioning tabel reservation berdasarkan tahun
ALTER TABLE reservation
PARTITION BY RANGE (YEAR(check_in_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);– Partitioning tabel payment berdasarkan bulan
ALTER TABLE payment
PARTITION BY RANGE (YEAR(payment_date) * 100 + MONTH(payment_date)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
PARTITION p_current VALUES LESS THAN MAXVALUE
);

— Constraint untuk memastikan check-out setelah check-in
ALTER TABLE reservation
ADD CONSTRAINT chk_checkout_after_checkin
CHECK (check_out_date > check_in_date);– Constraint untuk memastikan adults minimal 1
ALTER TABLE reservation
ADD CONSTRAINT chk_adults_minimum
CHECK (adults >= 1);

— Constraint untuk memastikan children tidak negatif
ALTER TABLE reservation
ADD CONSTRAINT chk_children_positive
CHECK (children >= 0);

— Constraint untuk memastikan total amount positif
ALTER TABLE reservation
ADD CONSTRAINT chk_total_amount_positive
CHECK (total_amount > 0);

— Constraint untuk memastikan payment amount positif
ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount_positive
CHECK (amount > 0);

— Constraint untuk memastikan room number unik per floor
ALTER TABLE room
ADD CONSTRAINT uk_room_number_floor
UNIQUE (room_number, floor);

— Trigger untuk update room status saat check-in
DELIMITER //
CREATE TRIGGER tr_reservation_checkin
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN
IF NEW.status = ‘Checked-in’ AND OLD.status != ‘Checked-in’ THEN
UPDATE room
SET status = ‘Occupied’
WHERE room_id = NEW.room_id;
END IF;
END//– Trigger untuk update room status saat check-out
CREATE TRIGGER tr_reservation_checkout
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN
IF NEW.status = ‘Checked-out’ AND OLD.status != ‘Checked-out’ THEN
UPDATE room
SET status = ‘Available’
WHERE room_id = NEW.room_id;

— Schedule housekeeping
INSERT INTO housekeeping (room_id, employee_id, cleaning_date, cleaning_type, status)
SELECT NEW.room_id,
(SELECT employee_id FROM employee WHERE department = ‘Housekeeping’ AND status = ‘Active’ LIMIT 1),
CURDATE(),
‘Checkout Cleaning’,
‘Scheduled’;
END IF;
END//

— Trigger untuk calculate total amount
CREATE TRIGGER tr_calculate_total_amount
BEFORE INSERT ON reservation
FOR EACH ROW
BEGIN
DECLARE room_price DECIMAL(10,2);
DECLARE nights INT;

SELECT rt.base_price INTO room_price
FROM room r
JOIN room_type rt ON r.room_type_id = rt.room_type_id
WHERE r.room_id = NEW.room_id;

SET nights = DATEDIFF(NEW.check_out_date, NEW.check_in_date);
SET NEW.total_amount = room_price * nights;
END//

— Trigger untuk prevent double booking
CREATE TRIGGER tr_prevent_double_booking
BEFORE INSERT ON reservation
FOR EACH ROW
BEGIN
DECLARE booking_count INT;

SELECT COUNT(*) INTO booking_count
FROM reservation
WHERE room_id = NEW.room_id
AND status IN (‘Confirmed’, ‘Checked-in’)
AND (
(NEW.check_in_date BETWEEN check_in_date AND check_out_date) OR
(NEW.check_out_date BETWEEN check_in_date AND check_out_date) OR
(check_in_date BETWEEN NEW.check_in_date AND NEW.check_out_date)
);

IF booking_count > 0 THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Room is already booked for the selected dates’;
END IF;
END//

DELIMITER ;

— Stored procedure untuk check room availability
DELIMITER //
CREATE PROCEDURE sp_check_room_availability(
IN p_check_in DATE,
IN p_check_out DATE,
IN p_room_type_id INT
)
BEGIN
SELECT
r.room_id,
r.room_number,
rt.type_name,
rt.base_price,
r.view_type
FROM room r
JOIN room_type rt ON r.room_type_id = rt.room_type_id
WHERE r.status = ‘Available’
AND (p_room_type_id IS NULL OR r.room_type_id = p_room_type_id)
AND r.room_id NOT IN (
SELECT room_id
FROM reservation
WHERE status IN (‘Confirmed’, ‘Checked-in’)
AND (
(p_check_in BETWEEN check_in_date AND check_out_date) OR
(p_check_out BETWEEN check_in_date AND check_out_date) OR
(check_in_date BETWEEN p_check_in AND p_check_out)
)
)
ORDER BY rt.base_price;
END//– Stored procedure untuk create reservation
CREATE PROCEDURE sp_create_reservation(
IN p_guest_id INT,
IN p_room_id INT,
IN p_check_in DATE,
IN p_check_out DATE,
IN p_adults INT,
IN p_children INT,
IN p_special_requests TEXT,
OUT p_reservation_id INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;

— Insert reservation
INSERT INTO reservation (
guest_id, room_id, check_in_date, check_out_date,
adults, children, special_requests, status
) VALUES (
p_guest_id, p_room_id, p_check_in, p_check_out,
p_adults, p_children, p_special_requests, ‘Pending’
);

SET p_reservation_id = LAST_INSERT_ID();

COMMIT;
END//

— Stored procedure untuk generate daily report
CREATE PROCEDURE sp_daily_report(IN p_date DATE)
BEGIN
— Arrivals
SELECT ‘ARRIVALS’ as report_type,
COUNT(*) as count,
SUM(total_amount) as revenue
FROM reservation
WHERE check_in_date = p_date
AND status IN (‘Confirmed’, ‘Checked-in’);

— Departures
SELECT ‘DEPARTURES’ as report_type,
COUNT(*) as count,
SUM(total_amount) as revenue
FROM reservation
WHERE check_out_date = p_date
AND status = ‘Checked-out’;

— In-house guests
SELECT ‘IN_HOUSE’ as report_type,
COUNT(*) as count,
SUM(total_amount) as revenue
FROM reservation
WHERE p_date BETWEEN check_in_date AND check_out_date
AND status = ‘Checked-in’;

— Room occupancy by type
SELECT rt.type_name,
COUNT(r.room_id) as total_rooms,
COUNT(res.room_id) as occupied_rooms,
ROUND((COUNT(res.room_id) * 100.0 / COUNT(r.room_id)), 2) as occupancy_rate
FROM room_type rt
LEFT JOIN room r ON rt.room_type_id = r.room_type_id
LEFT JOIN reservation res ON r.room_id = res.room_id
AND p_date BETWEEN res.check_in_date AND res.check_out_date
AND res.status = ‘Checked-in’
GROUP BY rt.room_type_id, rt.type_name;
END//

DELIMITER ;

— Create database users dengan role berbeda
CREATE USER ‘hotel_admin’@’localhost’ IDENTIFIED BY ‘StrongPassword123!’;
CREATE USER ‘front_desk’@’localhost’ IDENTIFIED BY ‘FrontDesk456!’;
CREATE USER ‘housekeeping’@’localhost’ IDENTIFIED BY ‘Housekeep789!’;
CREATE USER ‘finance’@’localhost’ IDENTIFIED BY ‘Finance012!’;
CREATE USER ‘readonly’@’localhost’ IDENTIFIED BY ‘ReadOnly345!’;– Grant privileges untuk admin (full access)
GRANT ALL PRIVILEGES ON hotel_reservation_system.* TO ‘hotel_admin’@’localhost’;

— Grant privileges untuk front desk (reservations, guests, payments)
GRANT SELECT, INSERT, UPDATE ON hotel_reservation_system.guest TO ‘front_desk’@’localhost’;
GRANT SELECT, INSERT, UPDATE ON hotel_reservation_system.reservation TO ‘front_desk’@’localhost’;
GRANT SELECT, INSERT, UPDATE ON hotel_reservation_system.payment TO ‘front_desk’@’localhost’;
GRANT SELECT ON hotel_reservation_system.room TO ‘front_desk’@’localhost’;
GRANT SELECT ON hotel_reservation_system.room_type TO ‘front_desk’@’localhost’;
GRANT SELECT ON hotel_reservation_system.service TO ‘front_desk’@’localhost’;
GRANT SELECT, INSERT, UPDATE ON hotel_reservation_system.reservation_service TO ‘front_desk’@’localhost’;

— Grant privileges untuk housekeeping (rooms, housekeeping)
GRANT SELECT, UPDATE ON hotel_reservation_system.room TO ‘housekeeping’@’localhost’;
GRANT SELECT, INSERT, UPDATE ON hotel_reservation_system.housekeeping TO ‘housekeeping’@’localhost’;
GRANT SELECT ON hotel_reservation_system.reservation TO ‘housekeeping’@’localhost’;

— Grant privileges untuk finance (payments, reports)
GRANT SELECT ON hotel_reservation_system.* TO ‘finance’@’localhost’;
GRANT INSERT, UPDATE ON hotel_reservation_system.payment TO ‘finance’@’localhost’;

— Grant privileges untuk readonly (reports only)
GRANT SELECT ON hotel_reservation_system.* TO ‘readonly’@’localhost’;

FLUSH PRIVILEGES;

— Encrypt sensitive data (contoh untuk credit card info jika ada)
ALTER TABLE payment ADD COLUMN encrypted_card_info VARBINARY(255);– Function untuk encrypt data
DELIMITER //
CREATE FUNCTION encrypt_sensitive_data(data TEXT)
RETURNS VARBINARY(255)
READS SQL DATA
DETERMINISTIC
BEGIN
RETURN AES_ENCRYPT(data, ‘hotel_encryption_key_2024’);
END//

— Function untuk decrypt data
CREATE FUNCTION decrypt_sensitive_data(encrypted_data VARBINARY(255))
RETURNS TEXT
READS SQL DATA
DETERMINISTIC
BEGIN
RETURN AES_DECRYPT(encrypted_data, ‘hotel_encryption_key_2024’);
END//

DELIMITER ;

— Hash password untuk employee login (jika ada sistem login)
ALTER TABLE employee ADD COLUMN password_hash VARCHAR(255);
ALTER TABLE employee ADD COLUMN salt VARCHAR(32);

— Trigger untuk hash password
DELIMITER //
CREATE TRIGGER tr_hash_employee_password
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.password_hash IS NOT NULL THEN
SET NEW.salt = SUBSTRING(MD5(RAND()), 1, 32);
SET NEW.password_hash = SHA2(CONCAT(NEW.password_hash, NEW.salt), 256);
END IF;
END//
DELIMITER ;

— Tabel audit untuk tracking changes
CREATE TABLE audit_log (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
table_name VARCHAR(50) NOT NULL,
operation_type ENUM(‘INSERT’, ‘UPDATE’, ‘DELETE’) NOT NULL,
record_id INT NOT NULL,
old_values JSON,
new_values JSON,
user_id VARCHAR(50),
ip_address VARCHAR(45),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);– Trigger audit untuk reservation
DELIMITER //
CREATE TRIGGER tr_audit_reservation_update
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation_type, record_id, old_values, new_values, user_id)
VALUES (
‘reservation’,
‘UPDATE’,
NEW.reservation_id,
JSON_OBJECT(
‘status’, OLD.status,
‘total_amount’, OLD.total_amount,
‘check_in_date’, OLD.check_in_date,
‘check_out_date’, OLD.check_out_date
),
JSON_OBJECT(
‘status’, NEW.status,
‘total_amount’, NEW.total_amount,
‘check_in_date’, NEW.check_in_date,
‘check_out_date’, NEW.check_out_date
),
USER()
);
END//

— Trigger audit untuk payment
CREATE TRIGGER tr_audit_payment_insert
AFTER INSERT ON payment
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation_type, record_id, new_values, user_id)
VALUES (
‘payment’,
‘INSERT’,
NEW.payment_id,
JSON_OBJECT(
‘reservation_id’, NEW.reservation_id,
‘amount’, NEW.amount,
‘payment_method’, NEW.payment_method,
‘status’, NEW.status
),
USER()
);
END//

DELIMITER ;

— Tabel untuk dynamic pricing rules
CREATE TABLE pricing_rule (
rule_id INT PRIMARY KEY AUTO_INCREMENT,
rule_name VARCHAR(100) NOT NULL,
room_type_id INT,
season_type ENUM(‘Low’, ‘High’, ‘Peak’) NOT NULL,
day_type ENUM(‘Weekday’, ‘Weekend’, ‘Holiday’) NOT NULL,
occupancy_threshold DECIMAL(5,2), — percentage
price_modifier DECIMAL(5,2) NOT NULL, — multiplier
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_type_id) REFERENCES room_type(room_type_id)
);– Function untuk calculate dynamic price
DELIMITER //
CREATE FUNCTION calculate_dynamic_price(
p_room_type_id INT,
p_check_in_date DATE,
p_check_out_date DATE
) RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE base_price DECIMAL(10,2);
DECLARE final_price DECIMAL(10,2);
DECLARE current_occupancy DECIMAL(5,2);
DECLARE price_modifier DECIMAL(5,2) DEFAULT 1.0;

— Get base price
SELECT rt.base_price INTO base_price
FROM room_type rt
WHERE rt.room_type_id = p_room_type_id;

— Calculate current occupancy
SELECT
COALESCE(
(COUNT(res.room_id) * 100.0) /
(SELECT COUNT(*) FROM room WHERE room_type_id = p_room_type_id),
0
) INTO current_occupancy
FROM room r
LEFT JOIN reservation res ON r.room_id = res.room_id
AND p_check_in_date BETWEEN res.check_in_date AND res.check_out_date
AND res.status IN (‘Confirmed’, ‘Checked-in’)
WHERE r.room_type_id = p_room_type_id;

— Get price modifier based on rules
SELECT pr.price_modifier INTO price_modifier
FROM pricing_rule pr
WHERE pr.room_type_id = p_room_type_id
AND p_check_in_date BETWEEN pr.valid_from AND pr.valid_to
AND pr.is_active = TRUE
AND (pr.occupancy_threshold IS NULL OR current_occupancy >= pr.occupancy_threshold)
ORDER BY pr.occupancy_threshold DESC
LIMIT 1;

SET final_price = base_price * COALESCE(price_modifier, 1.0);

RETURN final_price;
END//
DELIMITER ;

— Tabel loyalty program
CREATE TABLE loyalty_program (
program_id INT PRIMARY KEY AUTO_INCREMENT,
program_name VARCHAR(100) NOT NULL,
tier_name VARCHAR(50) NOT NULL,
min_points INT NOT NULL,
discount_percentage DECIMAL(5,2),
benefits TEXT,
is_active BOOLEAN DEFAULT TRUE
);– Tabel guest loyalty
CREATE TABLE guest_loyalty (
guest_loyalty_id INT PRIMARY KEY AUTO_INCREMENT,
guest_id INT NOT NULL,
program_id INT NOT NULL,
current_points INT DEFAULT 0,
total_earned_points INT DEFAULT 0,
tier_level VARCHAR(50),
member_since DATE DEFAULT (CURDATE()),
last_activity DATE,
FOREIGN KEY (guest_id) REFERENCES guest(guest_id),
FOREIGN KEY (program_id) REFERENCES loyalty_program(program_id)
);

— Tabel point transactions
CREATE TABLE point_transaction (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
guest_loyalty_id INT NOT NULL,
reservation_id INT,
transaction_type ENUM(‘Earned’, ‘Redeemed’, ‘Expired’, ‘Bonus’) NOT NULL,
points INT NOT NULL,
description TEXT,
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (guest_loyalty_id) REFERENCES guest_loyalty(guest_loyalty_id),
FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
);

— Trigger untuk earn points dari reservation
DELIMITER //
CREATE TRIGGER tr_earn_loyalty_points
AFTER UPDATE ON reservation
FOR EACH ROW
BEGIN
DECLARE points_earned INT;
DECLARE guest_loyalty_id INT;

IF NEW.status = ‘Checked-out’ AND OLD.status != ‘Checked-out’ THEN
— Calculate points (1 point per 10,000 spent)
SET points_earned = FLOOR(NEW.total_amount / 10000);

— Get or create guest loyalty record
SELECT gl.guest_loyalty_id INTO guest_loyalty_id
FROM guest_loyalty gl
WHERE gl.guest_id = NEW.guest_id
LIMIT 1;

IF guest_loyalty_id IS NULL THEN
INSERT INTO guest_loyalty (guest_id, program_id, current_points, total_earned_points)
VALUES (NEW.guest_id, 1, points_earned, points_earned);
SET guest_loyalty_id = LAST_INSERT_ID();
ELSE
UPDATE guest_loyalty
SET current_points = current_points + points_earned,
total_earned_points = total_earned_points + points_earned,
last_activity = CURDATE()
WHERE guest_loyalty_id = guest_loyalty_id;
END IF;

— Record point transaction
INSERT INTO point_transaction (guest_loyalty_id, reservation_id, transaction_type, points, description)
VALUES (guest_loyalty_id, NEW.reservation_id, ‘Earned’, points_earned,
CONCAT(‘Points earned from reservation #’, NEW.reservation_id));
END IF;
END//
DELIMITER ;

— Tabel inventory items
CREATE TABLE inventory_item (
item_id INT PRIMARY KEY AUTO_INCREMENT,
item_code VARCHAR(50) UNIQUE NOT NULL,
item_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
unit_of_measure VARCHAR(20),
current_stock INT DEFAULT 0,
minimum_stock INT DEFAULT 0,
maximum_stock INT DEFAULT 0,
unit_cost DECIMAL(10,2),
supplier_info TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);– Tabel room inventory (items in each room)
CREATE TABLE room_inventory (
room_inventory_id INT PRIMARY KEY AUTO_INCREMENT,
room_id INT NOT NULL,
item_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 0,
last_checked DATE,
condition_status ENUM(‘Good’, ‘Fair’, ‘Poor’, ‘Damaged’) DEFAULT ‘Good’,
notes TEXT,
FOREIGN KEY (room_id) REFERENCES room(room_id),
FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
UNIQUE KEY uk_room_item (room_id, item_id)
);

— Tabel inventory transactions
CREATE TABLE inventory_transaction (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
item_id INT NOT NULL,
room_id INT,
transaction_type ENUM(‘Stock In’, ‘Stock Out’, ‘Transfer’, ‘Adjustment’, ‘Damaged’) NOT NULL,
quantity INT NOT NULL,
reference_number VARCHAR(50),
notes TEXT,
employee_id INT,
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
FOREIGN KEY (room_id) REFERENCES room(room_id),
FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

— View untuk low stock alert
CREATE VIEW v_low_stock_alert AS
SELECT
ii.item_id,
ii.item_code,
ii.item_name,
ii.current_stock,
ii.minimum_stock,
(ii.minimum_stock – ii.current_stock) AS shortage_quantity
FROM inventory_item ii
WHERE ii.current_stock <= ii.minimum_stock
AND ii.is_active = TRUE;