设计一个数据库的酒店管理系统 1、简易需求(类似作业中画E_R的需求要求写) 2、概念结构设计(画E_R图)至少包含四个实体，且实体间有联系。 3、设计表的结构 4、创建数据库 具体要求如下:系统最少要有四个实体，且实体与实体之间必须有联系。 最少包含一个触发器。 表要有完整性约束。 数据库初始化程序，自动完成建库、建表(包含必要的约束)、基础数据添加、触发器器和存储过程的建立。 数据库建立以后，并执行相关SQL语句，展示数据库功能，附适当截图。 5、验证数据库的功能，利用SQL语句执行，并附适当截图。

1、简易需求：
酒店管理系统的主要功能是管理酒店所有客房的预订、入住和退房，以及客人的信息。系统需要记录每个客人的基本信息，例如姓名、联系方式、身份证号码等。同时，系统还需要记录每个客房的基本信息，例如房间类型、房间号码、价格等。客房可以被预订、入住和退房，每个客房在同一时间只能有一个客人使用。系统还需要提供查询客房和客人信息的功能。

2、概念结构设计：

3、设计表的结构：
- 客人表(guest)：包含客人的基本信息，如姓名、联系方式、身份证号码等。
- id：客人ID，主键
- name：客人姓名
- phone：客人联系方式
- id_card：客人身份证号码

- 房间表(room)：包含客房的基本信息，如房间类型、房间号码、价格等。
- id：房间ID，主键
- room_type：房间类型
- room_no：房间号码
- price：房间价格
- status：房间状态，0表示空闲，1表示已预订，2表示已入住

- 预订表(reserve)：记录客人预订房间的信息。
- id：预订ID，主键
- guest_id：客人ID，外键
- room_id：房间ID，外键
- reserve_time：预订时间
- status：预订状态，0表示已取消，1表示未取消

- 入住表(checkin)：记录客人入住房间的信息。
- id：入住ID，主键
- guest_id：客人ID，外键
- room_id：房间ID，外键
- checkin_time：入住时间
- checkout_time：离店时间

4、创建数据库：

-- 创建数据库
CREATE DATABASE hotel_management;

-- 切换到数据库
USE hotel_management;

-- 创建客人表
CREATE TABLE guest (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    id_card VARCHAR(18) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建房间表
CREATE TABLE room (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_type VARCHAR(20) NOT NULL,
    room_no VARCHAR(10) NOT NULL,
    price DECIMAL(8, 2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建预订表
CREATE TABLE reserve (
    id INT PRIMARY KEY AUTO_INCREMENT,
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    reserve_time DATETIME NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_guest_id FOREIGN KEY (guest_id) REFERENCES guest(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_room_id FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建入住表
CREATE TABLE checkin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    checkin_time DATETIME NOT NULL,
    checkout_time DATETIME,
    CONSTRAINT fk_guest_id FOREIGN KEY (guest_id) REFERENCES guest(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_room_id FOREIGN KEY (room_id) REFERENCES room(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建触发器，确保同一时间只能有一个客人使用一间客房
DELIMITER //
CREATE TRIGGER room_status BEFORE INSERT ON checkin
FOR EACH ROW
BEGIN
    IF EXISTS(SELECT * FROM checkin WHERE room_id = NEW.room_id AND checkout_time IS NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该房间已被占用';
    ELSE
        UPDATE room SET status = 2 WHERE id = NEW.room_id;
    END IF;
END //
DELIMITER ;

-- 创建完整性约束，确保客房号码和房间类型的唯一性
ALTER TABLE room ADD CONSTRAINT unique_room_no UNIQUE (room_no);
ALTER TABLE room ADD CONSTRAINT unique_room_type UNIQUE (room_type);

5、验证数据库的功能：
- 查询客人信息

SELECT * FROM guest;


- 添加客人信息

INSERT INTO guest (name, phone, id_card) VALUES ('张三', '13812345678', '310110199001010001');


- 查询客房信息

SELECT * FROM room;


- 添加客房信息

INSERT INTO room (room_type, room_no, price) VALUES ('标准间', '101', 200);


- 预订房间

INSERT INTO reserve (guest_id, room_id, reserve_time) VALUES (1, 1, NOW());


- 入住房间

INSERT INTO checkin (guest_id, room_id, checkin_time) VALUES (1, 1, NOW());


- 查询入住信息

SELECT * FROM checkin;


- 退房

UPDATE checkin SET checkout_time = NOW() WHERE id = 1;