数据库系统课程设计——酒店房间预订系统

需求分析

流程

（1）客户查询指定日期内所有类别的空余房间数，系统显示空房表中的信息。
（2）客户输入预订的起始日期、结束日期、房间类别和数量，并提交
（3）系统将用户提交的信息写入预定表，并修改空房表的相关数据

信息要求

流程中有三个实体：客户、房间、订单。
不同的城市有酒店的分店，不同分店都有不同的管理员和该分店的情况。
所以该系统可以分为五个实体：客户、房间、订单、酒店、管理员。
客户查询空余房间，系统根据输入的起始日期和结束日期筛选出房间状态为空的房间。
一个客户可以在一个订单中预订多间房型。

处理要求

1. 用户登录和注册功能涉及到用户信息表。
2. 用户查询房间信息涉及到房间信息表。
3. 用户预定房间，系统应创建相应的订单并更新该酒店的房间信息表

安全性与完整性要求

安全性

1. 用户身份鉴别：用户注册时给每个用户分配一个salt，这样在登陆时通过用户的账号（唯一标识）来获取该用户的salt，然后把该用户的密码进行加密，检查加密后的结果和数据库中存储的内容是否一样。
2. 存取控制：对于酒店管理员，不能对用户列表进行插入和删除操作，酒店管理员可以通过图形化界面帮助用户取消订单；对于用户，可以通过注册和注销实现对用户表的插入和删除，可以通过预订酒店实现对订单表的插入操作；二者都没有数据库和数据表的创建和删除权限。对于最高层的酒店管理员，可以分配权限给低级的管理员，对管理员表有修改权限，而低级的管理员没有此权限。
3. 视图：对于各酒店分店的管理员只能检索客户的信息和订单的信息，但是可以修改房间信息；而订单表和客户表的修改权限只有最高层管理员拥有。
4. 审计：最高层的管理员可以对所有表进行系统级的审计，而较低层的管理员只能对订单表、房间信息表进行用户级的审计。

完整性

实体完整性

客户信息表定义主码客户id；
酒店信息表定义主码酒店id；
管理员表定义主码管理员id；
订单表定义主码记录号；
房间信息表中，房间号和酒店id构成一个主码。

参照完整性

订单表中定义外码客户id、酒店id、房间号，分别参照客户表中的主码和酒店信息表中的主码还有房间信息表中的主码；订单表中插入一条记录其客户id酒店id和房间号必须在其对应的表中存在，否则拒绝执行。
酒店信息表中定义外码管理员id，参照管理员表中的主码；酒店信息表的酒店id修改或者删除时会影响订单表和房间信息表，应设置级联删除或修改。
房间信息表中定义外码酒店id，参照酒店信息表中的主码；

用户自定义完整性

表级定义实体完整性隐含了所有的主码和外码都非空，所以列级不允许取空值的语句可不写。
客户信息表：客户的电子邮件和手机号可为空值，但取值唯一；salt不能为空值，取值唯一；密码不能为空且取值唯一；其余字段都可为空。
性别只允许取值“男”或“女”，电话号码只允许十一位。
酒店信息表：除了邮箱都不能为空。
房间信息表：房间号、房间类型和价格取值唯一且不能为空。房间状态为已预定则不能够再次预订。在预定时间内，房间状态为已预定，该时间段以外的房间状态为空房。房间状态大约有20种，这里只用其中三种（‘VC’ vacant clean 空干净房，‘C/I’ Check In 入住，‘OK’ 已检查房）
订单表：为使同一订单可预订多种房型，订单表中同一订单应有多条记录。所有属性都不能为空。
管理员表：所有属性取值都唯一且不能为空。
房间类型表：所有属性取值都唯一且不能为空。

数据字典

客户信息表：客户id、姓名、性别、电子邮箱、手机号、密码（MD5加密后的内容）、salt、身份证号
酒店信息表：酒店id、管理员id、酒店名称、邮箱、电话号码、详细地址、酒店级别
房间信息表：酒店id、房间号、类型号、房间状态
订单表：记录号、订单号、客户id、酒店id、房间号、起始日期、结束日期、数量、总价格
管理员表：管理员id、管理员密码、salt
房间类型表：类型号、类型价格、类型描述

概念结构设计

房间-房间类型ER模型

房间和房间类型为Part-of联系，一个房间对应一个房间类型，一个房间类型对应多个房间，为一对多的联系。将房间类型单独放在一个表中，可以减少冗余。通过房间号将两表建立连接，可以查询对应的价格。
同时可以消去订单表中的传递依赖，使该模式达到第三范式：
原订单表：记录号、订单号、客户id、酒店id、房间号、起始日期、结束日期、数量、总价格、房间类型、房间价格
原订单表中房间类型和房间价格依赖于房间号，通过记录号可以确定房间号，通过房间号可以确定房间类型和房间价格。可见记录号与房间类型和房间价格之间是传递依赖。所以删除这两个字段，剩下所有属性都唯一依赖于主码。

酒店-管理员ER模型

最高层的管理员可以管理所有酒店及其管理员，而每个酒店只设置一个管理员。最高层的管理员有创建角色和用户的权限。通过酒店id可以唯一确定一个管理员。为了消除传递依赖，同样把管理员和酒店分成两个表。

总体ER模型

• 客户和订单之间是一对多的关系。一个客户可以下多个订单，而一个订单只属于一个客户。
• 订单和房间之间是一对多的关系。一个订单可以对应多个房间，但一个房间在同一时间段只对应一个订单。
• 房间和酒店是一对多的关系。一个房间只属于一个酒店。
• 对于每一个表，其非主键列都直接依赖于主键，可以达到第三范式。在最初的设计中，房间信息表中包含房间价格，该属性可以通过与房间类型表连接得到，所以在ER图中是冗余项。同理，酒店信息表中，可以根据管理员号与管理员表连接获取酒店管理员的信息，用以酒店管理员登录。

逻辑结构设计

关系模式

客户（用户名，姓名，性别，电子邮箱，手机号，密码，salt，身份证号）
订单（记录号，订单号，客户id，酒店id，房间号，起始日期，结束日期，数量，总价格）
酒店（酒店id，管理员id，酒店名字，邮箱，电话号码，详细地址，酒店级别）
管理员（管理员id，管理员密码，salt）
房间类型（类型号，类型价格，类型描述）
房间（酒店id，房间号，类型号，房间状态）

设计用户子模式

为方便客户理解，客户id字段名称改为用户名。

• 顾客信息表：
  为顾客建立视图顾客信息1{用户名，姓名，性别，电子邮箱，手机号，身份证号}
  为酒店管理员建立视图顾客信息2{用户名，姓名，性别，电子邮箱，手机号，}
• 酒店信息表：
  为顾客建立视图酒店信息1{酒店名称，邮箱，电话号码，详细地址，酒店级别}
  为酒店管理员建立视图酒店信息2{酒店id，管理员id，酒店名字，邮箱，电话号码，详细地址，酒店级别}
• 订单表：
  为顾客建立视图订单1{订单号，客户id，酒店id，房间号，起始日期，结束日期，数量，总价格}
  为管理员建立视图订单2{记录号，订单号，客户id，酒店id，房间号，起始日期，结束日期，数量，总价格}

数据库实现

注意：如果是用nevicat，在创建表时所有表的引擎要改为InnoDB，否则无法添加外键。或者是设置默认引擎为InnoDBSET default_storage_engine=InnoDB;

创建表

• 客户信息表

CREATE TABLE clientinfo(cid VARCHAR(20) PRIMARY KEY,/*客户id（用户名） 设置为主码*/cname VARCHAR(10) NOT NULL, /*客户姓名 */ csex CHAR(2) NOT NULL CHECK(csex IN('男','女')),/*性别 设置约束只能在‘男’和‘女’中取值*/cemail VARCHAR(30), /*邮箱*/cphone VARCHAR(11) NOT NULL CHECK(LENGTH(cphone)=11) ,/*电话号码 约束长度为11*/cpassword VARCHAR(50) NOT NULL, /*密码（md5）加密后的内容*/salt varchar(8) NOT NULL CHECK(LENGTH(salt)=7), /*加盐算法salt值*/ identitynum varchar(18) CHECK(LENGTH(identitynum)=18) /*身份证 约束长度为18*/);

• 管理员表

CREATE TABLE managerinfo(mid VARCHAR(20) PRIMARY KEY,/*管理员id*/mpassword VARCHAR(50) NOT NULL, /*密码*/salt VARCHAR(20) NOT NULL CHECK(LENGTH(salt)=7) /*salt值*/);

• 房间类型表

CREATE TABLE roomtype(typeid VARCHAR(20) PRIMARY KEY,/*类型号*/price DOUBLE NOT NULL, /*价格*/description VARCHAR(100) /*类型描述*/);

• 酒店信息表

CREATE TABLE hotelinfo(hid VARCHAR(20) PRIMARY KEY, /*酒店号 设置主键约束*/mid VARCHAR(20) NOT NULL,/*管理员id 非空*/hname VARCHAR(20) NOT NULL,/*酒店名称 非空*/email varchar(30), /*电子邮箱*/phone VARCHAR(15), /*联系方式*/address VARCHAR(30) NOT NULL,/*地址*/level INTEGER NOT NULL,/*酒店级别*/FOREIGN KEY(mid) REFERENCES managerinfo(mid) ON DELETE CASCADE ON UPDATE CASCADE/*管理员id 设置外键约束*/);

• 房间信息表

CREATE TABLE roominfo(hid VARCHAR(20) NOT NULL,/*酒店id*/rid VARCHAR(20) NOT NULL, /*房间号*/typeid VARCHAR(20) NOT NULL,/*类型号*/roomstatus VARCHAR(2) NOT NULL CHECK(roomstatusIN('VC','OK','C')), /*房间状态 设置自定义约束 */PRIMARY KEY(hid,rid), /*主键约束*/FOREIGN KEY(typeid) REFERENCES roomtype(typeid) ON DELETE CASCADE ON UPDATE CASCADE, /*外键约束*/FOREIGN KEY(hid) REFERENCES hotelinfo(hid)ON DELETE CASCADE ON UPDATE CASCADE);

• 订单表

CREATE TABLE orderinfo(id int NOT NULL PRIMARY KEY,/*记录号 主键约束*/oid VARCHAR(20) NOT NULL,/*订单号*/cid VARCHAR(20) NOT NULL,/*客户id*/hid VARCHAR(20) NOT NULL,/*酒店id*/rid VARCHAR(20) NOT NULL,/*房间号*/startdate DATE NOT NULL, /*起始日期*/enddate DATE NOT NULL, /*结束日期*/num INTEGER NOT NULL,/*数量*/totalprice DOUBLE NOT NULL,/*总价*/orderdate DATETIME NOT NULL,/*订单日期*/FOREIGN KEY(cid) REFERENCES clientinfo(cid) ON DELETE CASCADE ON UPDATE CASCADE, /*外键约束*/FOREIGN KEY(hid,rid) REFERENCES roominfo(hid,rid) ON DELETE CASCADE ON UPDATE CASCADE);

创建视图

• 顾客订单视图

CREATE VIEW client_order_info AS SELECT oid,hid,rid,startdate,enddate,num,totalprice FROM orderinfo WITH CHECK OPTION;

• 管理员订单视图

CREATE VIEW manager_order_info AS SELECT id,oid,hid,rid,startdate,enddate,num,totalprice FROM orderinfo WITH CHECK OPTION;

• 管理员顾客信息视图

CREATE VIEW manager_clientinfoAS SELECT cid,cname,csex,cemail,cphone FROM clientinfo WITH CHECK OPTION;

• 酒店房间视图
  对每个酒店的房间建立一个视图，每个酒店对应的管理员只能看到自己酒店房间的信息。

CREATE VIEW hotel_001_roominfo AS SELECT rid,typeid,roomstatus FROM roominfo WHERE hid='001';CREATE VIEW hotel_002_roominfo AS SELECT rid,typeid,roomstatus FROM roominfo WHERE hid='002';CREATE VIEW hotel_003_roominfo AS SELECT rid,typeid,roomstatus FROM roominfo WHERE hid='003';CREATE VIEW hotel_004_roominfo AS SELECT rid,typeid,roomstatus FROM roominfo WHERE hid='004';CREATE VIEW hotel_005_roominfo AS SELECT rid,typeid,roomstatus FROM roominfo WHERE hid='005';

创建索引

客户信息表

如果用户信息表中手机号字段参与身份认证，则必须创建UNIQUE索引。
密码和salt值肯定唯一，不作说明了。

CREATE UNIQUE INDEX idx_clientinfo_cphone ON clientinfo(cphone);CREATE UNIQUE INDEX idx_clientinfo_identitynum ON clientinfo(identitynum);CREATE UNIQUE INDEX idx_clientinfo_cpassword ON clientinfo(cpassword);

管理员表

CREATE UNIQUE INDEX idx_managerinfo_mpassword ON managerinfo(mpassword);CREATE UNIQUE INDEX idx_managerinfo_salt ON managerinfo(salt);

创建角色

为每个酒店创建一个管理员角色，以及分配相应的权限。

CREATE ROLE manager1,manager2,manager3,manager4,manager5;GRANT SELECT,DELETE,INSERT ON hotel_001_roominfo to manager1; GRANT SELECT,DELETE,INSERT ON hotel_002_roominfo to manager2; GRANT SELECT,DELETE,INSERT ON hotel_003_roominfo to manager3; GRANT SELECT,DELETE,INSERT ON hotel_004_roominfo to manager4; GRANT SELECT,DELETE,INSERT ON hotel_005_roominfo to manager5; GRANT SELECT ON manager_clientinfo TO manager1; 

数据库运行和维护

生成数据

• 管理员表

INSERT INTO managerinfo(mid,mpassword,salt) VALUES('002',MD5('wtQY7t6'),'wtQY7t6');

实际上mpassword存储的值应该是用户输入的密码经过加盐后再用md5加密。这里为了方便直接对salt加密。

• 房间类型表

INSERT INTO roomtype VALUES('001',100,'单人间');INSERT INTO roomtype VALUES('002',150,'标准间');INSERT INTO roomtype VALUES('003',200,'套间');INSERT INTO roomtype VALUES('004',250,'商务间');

• 酒店信息表

INSERT INTO hotelinfo VALUES('001','001','如家1',NULL,'1234567','太原',1);INSERT INTO hotelinfo VALUES('002','002','如家2',NULL,'1234566','青岛',1);

• 房间信息表

一共五个酒店，每个酒店五种房间类型，每种房间类型各一间，房间状态为VC

INSERT INTO roominfo VALUES('001','001','001','VC');INSERT INTO roominfo VALUES('001','002','002','VC');INSERT INTO roominfo VALUES('001','003','003','VC');INSERT INTO roominfo VALUES('001','004','004','VC');INSERT INTO roominfo VALUES('001','005','005','VC');INSERT INTO roominfo VALUES('002','001','001','VC');INSERT INTO roominfo VALUES('002','002','002','VC');INSERT INTO roominfo VALUES('002','003','003','VC');INSERT INTO roominfo VALUES('002','004','004','VC');INSERT INTO roominfo VALUES('002','005','005','VC');INSERT INTO roominfo VALUES('003','001','001','VC');INSERT INTO roominfo VALUES('003','002','002','VC');INSERT INTO roominfo VALUES('003','003','003','VC');INSERT INTO roominfo VALUES('003','004','004','VC');INSERT INTO roominfo VALUES('003','005','005','VC');INSERT INTO roominfo VALUES('004','001','001','VC');INSERT INTO roominfo VALUES('004','002','002','VC');INSERT INTO roominfo VALUES('004','003','003','VC');INSERT INTO roominfo VALUES('004','004','004','VC');INSERT INTO roominfo VALUES('004','005','005','VC');INSERT INTO roominfo VALUES('005','001','001','VC');INSERT INTO roominfo VALUES('005','002','002','VC');INSERT INTO roominfo VALUES('005','003','003','VC');INSERT INTO roominfo VALUES('005','004','004','VC');INSERT INTO roominfo VALUES('005','005','005','VC');

• 顾客信息表
  插入五名顾客信息

INSERT INTO clientinfo VALUES('001','小明','男','1245@163.com','1111111111',MD5('45g6aag'),'45g6aag','140108200205190025');INSERT INTO clientinfo VALUES('002','小刚','男','12345@163.com','2222222222',MD5('4f8sga3'),'4f8sga3','37010520020511005X');INSERT INTO clientinfo VALUES('003','小红','男','12495@qq.com','3333333333',MD5('g8sfhs2'),'g8sfhs2','140103200205110081');INSERT INTO clientinfo VALUES('004','大雄','男','17245@163.com','4444444444',MD5('abf6a51'),'abf6a51','140108200205090521');INSERT INTO clientinfo VALUES('005','胖虎','男','18245@163.com','5555555555',MD5('4ts6b1t'),'4ts6b1t','140107200206190368');

• 订单表
  订单表在生成数据的时候需要注意订单号的格式。
  这里规定订单号前三位是酒店号，加当前年份的后两位，后边随意，固定10位。
  注意这里在插入数据时要检查房间数量和总价是否一致。
  判断订单表中的数量和总价是否一致，一致返回1，不一致返回0

SELECT price*num=totalprice from roomtype,orderinfo,roominfo where roominfo.typeid=roomtype.typeid and orderinfo.hid=roominfo.hid and orderinfo.rid=roominfo.rid;

INSERT INTO orderinfo VALUES(1,'0012200001','001','001','001','2022-02-12','2022-02-13',1,100,'2022-02-12');

操作语言

查询语句

• 确定集合查询
  查询性别为‘女’的顾客姓名

SELECT cname FROM clientinfo where csex='女';

• 字符匹配查询
  查询邮箱为qq邮箱的顾客

SELECT cname,cemail FROM clientinfo where cemail REGEXP 'qq.com$';

• 聚集函数查询
  查询小红的订单个数

SELECT COUNT(*) FROM orderinfo,clientinfo WHERE clientinfo.cid=orderinfo.cid and cname='小红'; 

• 分组查询
  查询每个顾客所有订单的总价和订单个数

SELECT cid,SUM(totalprice),COUNT(oid) FROM orderinfo GROUP BY cid;

• 连接查询
  查询所有预订过‘001’号酒店‘002’号房间的顾客姓名

SELECT cname FROM clientinfo,orderinfo where clientinfo.cid=orderinfo.cid AND orderinfo.hid='001' and orderinfo.rid='002';

• 带有any或all谓词查询
  查询预订过大雄预订过的房间的顾客姓名

SELECT cname FROM clientinfo,orderinfo where (hid,rid)=ANY(SELECT hid,rid FROM orderinfo,clientinfo WHERE clientinfo.cname='大雄' AND clientinfo.cid=orderinfo.cid) AND clientinfo.cid=orderinfo.cid AND cname!='大雄';

• 带有exists或not exists查询
  查询没有预订过‘001’号酒店‘002’号房间的顾客

SELECT cname FROM clientinfo WHERE NOT EXISTS (SELECT * FROM orderinfo WHERE hid='001' AND rid='002' AND clientinfo.cid=orderinfo.cid);

更新语句

修改‘001’房型价格为50

UPDATE roomtype SET price=50 WHERE typeid='001';

删除‘005’号房型

DELETE FROM roomtype WHERE typeid='005';

因为房间类型号是订单表和房间表的外键，并设置了级联删除，删除该房型后，对应的房间和相关订单都被删除。

审计

show global variables like '%general%';

临时开启审计功能

set global general_log = on;

执行几条sql语句进行测试

触发器

当修改房间类型价格时，自动修改订单表中的总价

DELIMITER $CREATE TRIGGER update_totalprice AFTER UPDATE ON roomtype FOR EACH ROW BEGINUPDATE orderinfo,roominfo SET orderinfo.totalprice=orderinfo.num*NEW.price WHERE orderinfo.hid=roominfo.hid AND orderinfo.rid=roominfo.rid AND roominfo.typeid=NEW.typeid;END $DELIMITER ;UPDATE roomtype SET price=75 WHERE typeid='001';

可以看到订单表中房型为‘001’ 的房间订单总价都已修改。

存储过程

建立一张消费记录表，记录每个顾客在不同酒店消费的订单数和订单总价。

SET default_storage_engine=InnoDB;CREATE TABLE records(cid char(20) NOT NULL,hid char(20) NOT NULL,num INT NOT NULL,totalpriceofOrder DOUBLE NOT NULL,FOREIGN KEY(cid) REFERENCES clientinfo(cid) ON DELETE CASCADE,FOREIGN KEY(hid) REFERENCES hotelinfo(hid) ON DELETE CASCADE,PRIMARY key(cid,hid) );

创建表后，添加两条记录，数量和总价设为0.
创建存储过程，更新消费记录表中的订单数和订单总价。

CREATE PROCEDURE Proc_UpdateRecords()BEGINUPDATE records SET num=(SELECT COUNT(*) FROM orderinfo WHERE orderinfo.cid=records.cid AND orderinfo.hid=records.hid),totalpriceofOrder=(SELECT SUM(totalprice) FROM orderinfo WHERE orderinfo.cid=records.cid AND orderinfo.hid=records.hid);ENDCALL Proc_UpdateRecords();

执行完存储过程后的结果如图所示