-- Создание базы данных CREATE DATABASE IF NOT EXISTS hotel; USE hotel; -- Таблица…
- User
-- Создание базы данных CREATE DATABASE IF NOT EXISTS hotel; USE hotel;
-- Таблица Administrators CREATE TABLE Administrators ( admin_id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, passport_number VARCHAR(20) UNIQUE NOT NULL, address TEXT NOT NULL, phone_number VARCHAR(15) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, login VARCHAR(20) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, registration_code VARCHAR(50) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
-- Таблица Receptionists CREATE TABLE Receptionists ( receptionist_id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, passport_number VARCHAR(20) UNIQUE NOT NULL, address TEXT NOT NULL, phone_number VARCHAR(15) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, login VARCHAR(20) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, admin_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (admin_id) REFERENCES Administrators(admin_id) ON DELETE CASCADE );
-- Таблица Cleaners CREATE TABLE Cleaners ( cleaner_id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, phone_number VARCHAR(15) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, status ENUM('Free', 'Busy') DEFAULT 'Free', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
-- Таблица Rooms CREATE TABLE Rooms ( room_id INT AUTO_INCREMENT PRIMARY KEY, room_number INT UNIQUE NOT NULL, available BOOLEAN DEFAULT TRUE, status ENUM('Available', 'Occupied', 'Maintenance') DEFAULT 'Available', price DECIMAL(10,2) NOT NULL, bed_type ENUM('Single', 'Double', 'Queen', 'King', 'Twin') NOT NULL, room_type VARCHAR(50), admin_id INT, -- Changed to allow NULL receptionist_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (admin_id) REFERENCES Administrators(admin_id) ON DELETE SET NULL, FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL );
-- Таблица Cleaning_Schedules CREATE TABLE Cleaning_Schedules ( schedule_id INT AUTO_INCREMENT PRIMARY KEY, room_id INT NOT NULL, cleaner_id INT NOT NULL, scheduled_time DATETIME NOT NULL, status ENUM('Scheduled', 'In Progress', 'Completed') DEFAULT 'Scheduled', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE, FOREIGN KEY (cleaner_id) REFERENCES Cleaners(cleaner_id) ON DELETE CASCADE );
-- Таблица Room_Images CREATE TABLE Room_Images ( image_id INT AUTO_INCREMENT PRIMARY KEY, room_id INT NOT NULL, image_url VARCHAR(255) NOT NULL, is_primary BOOLEAN DEFAULT FALSE, FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE );
-- Таблица Customers CREATE TABLE Customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(100) NOT NULL, phone VARCHAR(15) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE, gender ENUM('Male', 'Female', 'Other'), country VARCHAR(50), address TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, receptionist_id INT, FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL );
-- Таблица Reservations CREATE TABLE Reservations ( reservation_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, room_id INT NOT NULL, check_in DATE NOT NULL, check_out DATE NOT NULL, status ENUM('Booked', 'Checked-in', 'Checked-out', 'Cancelled') DEFAULT 'Booked', total_amount DECIMAL(10,2), deposit DECIMAL(10,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, receptionist_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE, FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE, FOREIGN KEY (receptionist_id) REFERENCES Receptionists(receptionist_id) ON DELETE SET NULL );
-- Таблица Payments CREATE TABLE Payments ( payment_id INT AUTO_INCREMENT PRIMARY KEY, reservation_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, payment_date DATE NOT NULL, payment_method ENUM('Cash', 'Credit Card', 'Debit Card', 'Online Transfer') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE );
-- Таблица Cleaning_Actions CREATE TABLE Cleaning_Actions ( action_id INT AUTO_INCREMENT PRIMARY KEY, reservation_id INT NOT NULL, room_id INT NOT NULL, cleaner_id INT NOT NULL, action_type ENUM('Manual Assignment', 'Auto Assignment') NOT NULL, scheduled_time DATETIME NOT NULL, status ENUM('Scheduled', 'In Progress', 'Completed') DEFAULT 'Scheduled', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id) ON DELETE CASCADE, FOREIGN KEY (room_id) REFERENCES Rooms(room_id) ON DELETE CASCADE, FOREIGN KEY (cleaner_id) REFERENCES Cleaners(cleaner_id) ON DELETE CASCADE );
добавь триггера которые необходимы
- Assistant
Добавление триггеров может помочь автоматизировать различные бизнес-логики и обеспечить целостность данных в вашей базе данных гостиницы. Ниже представлены несколько необходимых триггеров, которые могут улучшить функциональность вашей системы:
- Обновление статуса номера при изменении статуса бронирования
- Предотвращение перекрывающихся бронирований для одного и того же номера
- Автоматический расчет общей суммы бронирования при вставке или обновлении записи
- Обновление статуса уборщика при завершении действия уборки
1. Триггер для обновления статуса номера при изменении статуса бронирования
Этот триггер автоматически обновляет статус номера в таблице
Roomsв зависимости от статуса бронирования в таблицеReservations.DELIMITER $$ CREATE TRIGGER trg_update_room_status_after_reservation_update AFTER UPDATE ON Reservations FOR EACH ROW BEGIN IF NEW.status = 'Booked' THEN UPDATE Rooms SET status = 'Occupied', available = FALSE WHERE room_id = NEW.room_id; ELSIF NEW.status IN ('Checked-out', 'Cancelled') THEN UPDATE Rooms SET status = 'Available', available = TRUE WHERE room_id = NEW.room_id; END IF; END$$ DELIMITER ;2. Триггер для предотвращения перекрывающихся бронирований
Этот триггер предотвращает создание или обновление бронирования, если выбранный номер уже забронирован на указанные даты.
DELIMITER $$ CREATE TRIGGER trg_prevent_overlapping_reservations BEFORE INSERT ON Reservations FOR EACH ROW BEGIN IF EXISTS ( SELECT 1 FROM Reservations WHERE room_id = NEW.room_id AND status NOT IN ('Cancelled') AND ( (NEW.check_in BETWEEN check_in AND check_out) OR (NEW.check_out BETWEEN check_in AND check_out) OR (check_in BETWEEN NEW.check_in AND NEW.check_out) ) ) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Номер уже забронирован на указанные даты.'; END IF; END$$ DELIMITER ;Триггер для обновлений аналогичен триггеру
BEFORE INSERTи может быть создан аналогично, изменив событие наBEFORE UPDATE.3. Триггер для автоматического расчета общей суммы бронирования
Этот триггер автоматически рассчитывает общую сумму бронирования (
total_amount) на основе цены номера и длительности пребывания при вставке или обновлении бронирования.DELIMITER $$ CREATE TRIGGER trg_calculate_total_amount_before_insert BEFORE INSERT ON Reservations FOR EACH ROW BEGIN DECLARE roomPrice DECIMAL(10,2); DECLARE days INT; SELECT price INTO roomPrice FROM Rooms WHERE room_id = NEW.room_id; SET days = DATEDIFF(NEW.check_out, NEW.check_in); IF days <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Дата выезда должна быть позже даты заезда.'; END IF; SET NEW.total_amount = roomPrice * days; END$$ CREATE TRIGGER trg_calculate_total_amount_before_update BEFORE UPDATE ON Reservations FOR EACH ROW BEGIN DECLARE roomPrice DECIMAL(10,2); DECLARE days INT; SELECT price INTO roomPrice FROM Rooms WHERE room_id = NEW.room_id; SET days = DATEDIFF(NEW.check_out, NEW.check_in); IF days <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Дата выезда должна быть позже даты заезда.'; END IF; SET NEW.total_amount = roomPrice * days; END$$ DELIMITER ;4. Триггер для обновления статуса уборщика при завершении действия уборки
Этот триггер автоматически обновляет статус уборщика в таблице
Cleaners, когда действие уборки завершается.DELIMITER $$ CREATE TRIGGER trg_update_cleaner_status_after_action_complete AFTER UPDATE ON Cleaning_Actions FOR EACH ROW BEGIN IF NEW.status = 'Completed' THEN UPDATE Cleaners SET status = 'Free' WHERE cleaner_id = NEW.cleaner_id; ELSIF NEW.status = 'In Progress' THEN UPDATE Cleaners SET status = 'Busy' WHERE cleaner_id = NEW.cleaner_id; END IF; END$$ DELIMITER ;Примечания
-
DELIMITER: В MySQL по умолчанию точка с запятой (
;) используется как разделитель команд. Однако, при создании триггеров, которые содержат несколько SQL-команд внутри, необходимо временно изменить разделитель на другой, например,$$, чтобы избежать преждевременного завершения триггера. -
Проверки и ограничения: Триггеры содержат проверки, такие как обеспечение корректности дат бронирования и предотвращение перекрытия бронирований. Это помогает поддерживать целостность данных на уровне базы.
-
Сообщения об ошибках: Использование
SIGNAL SQLSTATEпозволяет возвращать понятные сообщения об ошибках, которые можно обработать на уровне приложения.
Эти триггеры помогут автоматизировать основные бизнес-процессы в системе управления гостиницей и обеспечить целостность данных. При необходимости вы можете добавлять дополнительные триггеры в соответствии с другими требованиями вашей системы.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.