Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

-- Создание базы данных CREATE DATABASE IF NOT EXISTS hotel; USE hotel; -- Таблица…

  1. 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 );

    добавь триггера которые необходимы

  2. Assistant

    Добавление триггеров может помочь автоматизировать различные бизнес-логики и обеспечить целостность данных в вашей базе данных гостиницы. Ниже представлены несколько необходимых триггеров, которые могут улучшить функциональность вашей системы:

    1. Обновление статуса номера при изменении статуса бронирования
    2. Предотвращение перекрывающихся бронирований для одного и того же номера
    3. Автоматический расчет общей суммы бронирования при вставке или обновлении записи
    4. Обновление статуса уборщика при завершении действия уборки

    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 позволяет возвращать понятные сообщения об ошибках, которые можно обработать на уровне приложения.

    Эти триггеры помогут автоматизировать основные бизнес-процессы в системе управления гостиницей и обеспечить целостность данных. При необходимости вы можете добавлять дополнительные триггеры в соответствии с другими требованиями вашей системы.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.