-- Создание базы данных 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 );
-- Таблица Shifts CREATE TABLE Shifts ( shift_id INT AUTO_INCREMENT PRIMARY KEY, shift_name VARCHAR(50) NOT NULL UNIQUE, start_time TIME NOT NULL, end_time TIME NOT NULL );
-- Вставка фиксированных смен INSERT INTO Shifts (shift_name, start_time, end_time) VALUES ('Ночь', '00:00:00', '08:00:00'), ('Утро', '08:00:00', '16:00:00'), ('Вечер', '16:00:00', '00:00:00');
-- Таблица 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', shift_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (shift_id) REFERENCES Shifts(shift_id) ON DELETE RESTRICT ON UPDATE CASCADE );
-- Таблица Rooms CREATE TABLE Rooms ( room_id INT AUTO_INCREMENT PRIMARY KEY, room_number INT UNIQUE NOT NULL, available BOOLEAN DEFAULT TRUE, status ENUM('Доступен', 'Недоступен') DEFAULT 'Доступен', price DECIMAL(10,2) NOT NULL, bed_type ENUM('Одноместный', 'Двухместный', 'Трехместный', 'Четырехместный') NOT NULL, room_type ENUM('Стандарт', 'Люкс', 'Делюкс') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
-- Таблица 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('Назначено', 'В процессе', 'Завершено') DEFAULT 'Назначено', 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('Мужской', 'Женский'), 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('Наличные', 'Кредитная карта', 'Дебетовая карта', 'Онлайн перевод') 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 );
DELIMITER $$
CREATE TRIGGER trg_update_room_availability AFTER UPDATE ON Reservations FOR EACH ROW BEGIN IF NEW.status = 'Booked' OR NEW.status = 'Checked-in' THEN UPDATE Rooms SET available = FALSE, status = 'Occupied' WHERE room_id = NEW.room_id; ELSEIF NEW.status = 'Cancelled' OR NEW.status = 'Checked-out' THEN UPDATE Rooms SET available = TRUE, status = 'Available' WHERE room_id = NEW.room_id; END IF; END$$
DELIMITER ;
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 ;
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 ;
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; ELSEIF NEW.status = 'In Progress' THEN UPDATE Cleaners SET status = 'Busy' WHERE cleaner_id = NEW.cleaner_id; END IF; END$$
DELIMITER ;
правильно переведи все оставшиеся значений enum которые я еще не перевел на русский язык
- Assistant
Конечно! Я перевёл оставшиеся значения
ENUMна русский язык. Вот обновлённый SQL-скрипт с переведёнными значениямиENUM:-- Создание базы данных 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 ); -- Таблица Shifts CREATE TABLE Shifts ( shift_id INT AUTO_INCREMENT PRIMARY KEY, shift_name VARCHAR(50) NOT NULL UNIQUE, start_time TIME NOT NULL, end_time TIME NOT NULL ); -- Вставка фиксированных смен INSERT INTO Shifts (shift_name, start_time, end_time) VALUES ('Ночь', '00:00:00', '08:00:00'), ('Утро', '08:00:00', '16:00:00'), ('Вечер', '16:00:00', '00:00:00'); -- Таблица 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('Свободен', 'Занят') DEFAULT 'Свободен', shift_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (shift_id) REFERENCES Shifts(shift_id) ON DELETE RESTRICT ON UPDATE CASCADE ); -- Таблица Rooms CREATE TABLE Rooms ( room_id INT AUTO_INCREMENT PRIMARY KEY, room_number INT UNIQUE NOT NULL, available BOOLEAN DEFAULT TRUE, status ENUM('Доступен', 'Недоступен') DEFAULT 'Доступен', price DECIMAL(10,2) NOT NULL, bed_type ENUM('Одноместный', 'Двухместный', 'Трёхместный', 'Четырёхместный') NOT NULL, room_type ENUM('Стандарт', 'Люкс', 'Делюкс') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- Таблица 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('Назначено', 'В процессе', 'Завершено') DEFAULT 'Назначено', 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('Мужской', 'Женский'), 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('Забронирован', 'Заселен', 'Выселен', 'Отменён') DEFAULT 'Забронирован', 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('Наличные', 'Кредитная карта', 'Дебетовая карта', 'Онлайн перевод') 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('Ручное назначение', 'Автоматическое назначение') NOT NULL, scheduled_time DATETIME NOT NULL, status ENUM('Запланировано', 'В процессе', 'Завершено') DEFAULT 'Запланировано', 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 ); DELIMITER $$ CREATE TRIGGER trg_update_room_availability AFTER UPDATE ON Reservations FOR EACH ROW BEGIN IF NEW.status = 'Забронирован' OR NEW.status = 'Заселен' THEN UPDATE Rooms SET available = FALSE, status = 'Недоступен' WHERE room_id = NEW.room_id; ELSEIF NEW.status = 'Отменён' OR NEW.status = 'Выселен' THEN UPDATE Rooms SET available = TRUE, status = 'Доступен' WHERE room_id = NEW.room_id; END IF; END$$ DELIMITER ; 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 ('Отменён') 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 ; 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 ; DELIMITER $$ CREATE TRIGGER trg_update_cleaner_status_after_action_complete AFTER UPDATE ON Cleaning_Actions FOR EACH ROW BEGIN IF NEW.status = 'Завершено' THEN UPDATE Cleaners SET status = 'Свободен' WHERE cleaner_id = NEW.cleaner_id; ELSEIF NEW.status = 'В процессе' THEN UPDATE Cleaners SET status = 'Занят' WHERE cleaner_id = NEW.cleaner_id; END IF; END$$ DELIMITER ;Изменения в ENUM:
-
Таблица
Cleaners:status ENUM('Свободен', 'Занят') DEFAULT 'Свободен',Free→'Свободен'Busy→'Занят'
-
Таблица
Reservations:status ENUM('Забронирован', 'Заселен', 'Выселен', 'Отменён') DEFAULT 'Забронирован',Booked→'Забронирован'Checked-in→'Заселен'Checked-out→'Выселен'Cancelled→'Отменён'
-
Таблица
Cleaning_Actions:action_type ENUM('Ручное назначение', 'Автоматическое назначение') NOT NULL, status ENUM('Запланировано', 'В процессе', 'Завершено') DEFAULT 'Запланировано',Manual Assignment→'Ручное назначение'Auto Assignment→'Автоматическое назначение'Scheduled→'Запланировано'In Progress→'В процессе'Completed→'Завершено'
Изменения в триггерах:
В триггерах были также обновлены условия проверки статусов с учётом новых русских значений.
Например, в триггере
trg_update_room_availability:IF NEW.status = 'Забронирован' OR NEW.status = 'Заселен' THEN ... ELSEIF NEW.status = 'Отменён' OR NEW.status = 'Выселен' THEN ...И в триггере
trg_update_cleaner_status_after_action_complete:IF NEW.status = 'Завершено' THEN ... ELSEIF NEW.status = 'В процессе' THEN ...Примечания:
- Убедитесь, что все приложения или интерфейсы, взаимодействующие с этой базой данных, также обновлены для использования новых значений
ENUMна русском языке. - Также рекомендуется использовать транслитерацию или согласовать формы при необходимости (например,
'Свободна'vs'Свободен'), в зависимости от контекста и требований к родам в русском языке.
Если у вас есть дополнительные вопросы или нужны дальнейшие изменения, пожалуйста, дайте знать!
-