2024数据库课设-酒店住宿管理系统

乔哥nb

已于 2024-06-14 00:34:06 修改

阅读量706

点赞数 16

文章标签：数据库

于 2024-05-21 22:12:17 首次发布

本文链接：https://blog.csdn.net/m0_74373652/article/details/139103660

版权

课设名称：酒店住宿管理系统

成员：222241816327qxc，222241816520hqq

【背景分析】

1.行业需求

酒店行业在全球范围内日益增长，对高效、自动化的管理系统的需求也随之增加。随着旅游业的复苏和全球商务活动的增加，酒店业务处理了大量的数据，包括客户信息、房间状态、财务交易和服务管理。一个有效的酒店管理系统可以提升客户服务质量，优化资源分配，并提高运营效率。

2.项目目的

本项目旨在设计和实现一个酒店管理系统的数据库，用于支持日常运营的各种活动，如客户预订、房间管理、账单处理和客户服务。此系统将使酒店管理者能够更高效地管理客户信息和房间分配，同时提供准确的财务报告和消费记录。

【需求分析】

客户登记入住:
- 客户到达酒店前台，提供必要的个人信息，如姓名、身份证号码和联系方式。
- 管理员根据客户的需求和房态信息，为客户分配房间，并在系统中记录入住信息，包括房间号、入住时间和预计退房时间。
- 此过程需确保客户信息的准确性和房间分配的合理性。
入住期间换房信息申请:
- 如果客户在入住期间需要更换房间，可向管理员提出申请。
- 管理员根据当前房态和客户需求，查找可用房间，并在系统中更新换房信息。
- 更新内容包括客户的新房间号和换房时间，同时确保原房间的信息得到适当处理。
入住期间消费和房费结算:
- 客户在入住期间的所有消费，如餐饮、洗衣等，都需记录在消费单中。
- 消费单需详细记录消费项目、数量和消费时间。
- 在客户退房时，管理员需调用系统，汇总房费和其他消费（包括罚款等），生成总费用，并向客户展示详细账单。
客户退房判断是否罚款:
- 客户退房时，系统需检查是否有迟退情况。
- 如果客户超过预定退房时间，系统根据超时时长自动计算罚款。
- 管理员需在系统中更新实际退房时间，并根据系统的罚款计算结果通知客户。

【数据流】

顶层：

零层：

【系统功能】

客户管理：包括客户信息登记和历史数据查询。
房间管理：实时更新房间状态，支持房间预订、入住和退房操作。
财务管理：自动化处理账单和发票生成，包括消费记录以及罚款等。
服务管理：管理酒店提供的附加服务，如餐饮服务、洗衣服务和房间更换等。

【数据库设计】暂时拟定

【实体分析】

【表设计】

Customer表

字段名	数据类型	长度	允许空	主键	说明
Cust#	varchar	5	no	yes	客户编号
Id	varchar	18	no	no	身份证号
Custname	varchar	10	no	no	客户名称
Custphone_num	varchar	11	no	no	手机号
Cust_type	varchar	10	no	no	客户类型

Room表

字段名	数据类型	长度	允许空	主键	说明
R#	varchar	5	no	yes	房间号
Rvolume	int		no	no	容纳人数
Rprice	decimal	5,1	no	no	房间价格
Rtype	varchar	10	no	no	房间类型
RjiIw	varchar	10	no	no	计量单位
Rstate	char	4	no	no	房间状态

Registration表

字段名	数据类型	长度	允许空	主键	说明
R#	varchar	5	no	yes	房间号
Cust#	varchar	5	no	yes	客户编号
rztime	datetime		no	yes	实际入住时间
Pre_tftime	datetime		no	no	预定退房时间
tftime	datetime		yes	no	实际退房时间

Employee表

字段名	数据类型	长度	允许空	主键	说明
E#	varchar	5	no	yes	职工编号
Ename	varchar	5	no	no	职工姓名
Esex	bit	1	yes	no	性别，0代表女生，1代表男生
Eage	int		no	no	职工年龄
D#	varchar	5	no	no	部门编号
Esalary	decimal	5,1	no	no	薪资
Ephone	char	11	no	no	手机号

Dept表

字段名	数据类型	长度	允许空	主键	说明
D#	varchar	5	no	yes	部门编号
Dname	varchar	5	no	no	部门名称

（6）Commodity表

字段名	数据类型	长度	允许空	主键	说明
C#	varchar	10	no	yes	服务编号
Cname	varchar	10	no	no	服务名称
Cprice	decimal	8,1	no	no	服务价格
Ctype	varchar	10	no	no	服务类型
jidw	varchar	10	yes	no	计量单位

Consumption_List表

字段名	数据类型	长度	允许空	主键	说明
CL#	varchar	10	no	yes	消费单号
C#	varchar	10	no	no	服务编号
Cust#	varchar	5	no	no	客户编号
Cquantity	int		yes	no	消费数量
GCprice	decimal	8,1	yes	no	消费总价
Ctime	date		no	no	消费时间
M#	varchar	10	no	no	管理员编号

Manager表

字段名	数据类型	长度	允许空	主键	说明
M#	varchar	10	no	yes	管理员编号
Mname	varchar	10	no	no	管理员名称

Roomfee表

字段名	数据类型	长度	允许空	主键	说明
Cust#	varchar	5	no	yes	客户编号
R#	varchar	5	no	yes	房间编号
Trzprice	decimal	10,1	yes	no	总房费
Tprice	decimal	10,1	yes	no	房间总费用
Tcprice	decimal	10,1	yes	no	服务总费用
Fine	decimal	10,1	yes	no	罚款

【系统功能设计】

功能名称	模块名称	说明
1.客户入住申请	1.1登记客户信息	客户入住，前台登记客户信息
	1.2查询需求房间信息	根据客户需求，查找所需要的房型
	1.3添加房间登记表	将客户入住信息以及房型时间添加到表中
2.更换房间申请	2.1查询更换房间信息	根据客户提出的换房申请查找相对应的房间
2.更换房间申请	2.2更新换房后登记表	将客户换房间后的信息进行登记表更新
3.客户消费	3.1添加消费单	将客户的消费单整合
3.客户消费	3.2查询消费单	查询客户在入住期间的消费
4.客户退房申请	4.1更新房间登记表	客户退房后将房间状态和登记表更新
4.客户退房申请	4.2查询总消费	查询客户的消费和房费以及相应的罚款

【表的创建】-（代码和表的截图）

后续进行。

【索引设计】

索引名	代码
index1	CREATE INDEX index1 ON Customer (Cust#)
index2	CREATE INDEX index2 ON Commodity (C#)
index3	CREATE INDEX index3ON Employee (E#)
index4	CREATE INDEX index4ON Dept (D#)
index5	CREATE INDEX index5 ON Manager (M#)
index6	CREATE INDEX index6 ON Room (R#)

【视图的创建】

视图名（作用）	代码
创建登记表视图包含（房间号，客户编号，实际入住时间，实际退房时间）便于查询客户入住情况	create view Registration_view as select `R#`,`Cust#`,rztime,tftime from Registration;
创建消费表视图包含（消费单号，客户编号，服务名称，服务数量，消费总价,管理员编号，管理员名字）便于客户查看消费的服务有哪些以及消费总价和是那个管理员负责的	create view Consumption_List_view as select `CL#`,`Cust#`,Cname,Cquantity,Gcprice, Manager.`M#`,Mname from Consumption_List,Commodity,Manager where Consumption_List.`C#`=Commodity.`C#` and Consumption_List.`M#`=Manager.`M#`;
创建职工视图包含(职工编号，职工名称，部门名称，职工手机号) 便于查询哪个职工属于哪个部门以及职工的联系电话	create view Employee_view as select `E#`,Ename,Dname,Ephone from Employee,Dept where Employee.`D#`=Dept.`D#`;
创建服务视图包含（服务编号，服务名称，服务价格，服务类型）便于客户查看服务有哪些以及价格和类型是什么样的	create view Commodity_view as select C#,Cname,Cprice,Ctype from commodity
创建房间表视图包含(房间号，容纳人数，房间价格，房间类型，房间状态，该房间实际入住时间，该房间实际退房实际) 便于顾客方便选择房间，如果那个房间有人已经入住，可以根据大概时间范围去入住	create view Room_view as select R#,Rvolume,Rprice,Ctype,Cstate,rztime,tftime from Room,Registration where Room.R#=Registration1.R#

视图名（作用）

代码

创建登记表视图包含（房间号，客户编号，实际入住时间，实际退房时间）

便于查询客户入住情况

create view Registration_view as

select `R#`,`Cust#`,rztime,tftime

from Registration;

创建消费表视图包含（消费单号，客户编号，服务名称，服务数量，消费总价,管理员编号，管理员名字）

便于客户查看消费的服务有哪些以及消费总价和是那个管理员负责的

create view Consumption_List_view as

select `CL#`,`Cust#`,Cname,Cquantity,Gcprice, Manager.`M#`,Mname

from Consumption_List,Commodity,Manager

where Consumption_List.`C#`=Commodity.`C#` and Consumption_List.`M#`=Manager.`M#`;

创建职工视图包含(职工编号，职工名称，部门名称，职工手机号)

便于查询哪个职工属于哪个部门以及职工的联系电话

create view Employee_view as

select `E#`,Ename,Dname,Ephone

from Employee,Dept

where Employee.`D#`=Dept.`D#`;

创建服务视图包含（服务编号，服务名称，服务价格，服务类型）

便于客户查看服务有哪些以及价格和类型是什么样的

create view Commodity_view as

select C#,Cname,Cprice,Ctype

from commodity

创建房间表视图包含(房间号，容纳人数，房间价格，房间类型，房间状态，该房间实际入住时间，该房间实际退房实际)

便于顾客方便选择房间，如果那个房间有人已经入住，可以根据大概时间范围去入住

create view Room_view as

select R#,Rvolume,Rprice,Ctype,Cstate,rztime,tftime

from Room,Registration

where Room.R#=Registration1.R#

【触发器的创建】

入住触发器，如果入住登记表中有新的数据添加，说明有客户入住，将客户入住房间号对应房间表里的状态改为busy	DELIMITER // CREATE TRIGGER tri_rz1 AFTER INSERT ON Registration FOR EACH ROW BEGIN UPDATE Room SET Rstate = 'busy' WHERE `R#` = NEW.`R#`; END; //
退房触发器，如果房间登记表中实际退房时间更新，（说明退房了）那么将房间表中该房间的状态由busy改为free	DELIMITER // CREATE TRIGGER tr_tf AFTER UPDATE ON Registration FOR EACH ROW BEGIN IF NEW.tftime <> OLD.tftime THEN UPDATE Room SET Rstate = 'free' WHERE `R#` = NEW.`R#`; END IF; END; //
Registration1表中有数据添加时，把Cust#和R#添加入Roomfee表中	DELIMITER // CREATE TRIGGER tr_RCust AFTER INSERT ON Registration FOR EACH ROW BEGIN INSERT INTO Roomfee (`Cust#`, `R#`) VALUES (NEW.`Cust#`, NEW.`R#`); END; //
当在消费单（Consumption_list）中输入消费商品的数量时，自动根据对应服务计价表里面的单价计算总价返回至消费单中的GCprice（总价）中	DELIMITER // CREATE TRIGGER tr_up_Gcprice1 AFTER INSERT ON Consumption_list FOR EACH ROW BEGIN DECLARE price1 DECIMAL(10, 2); DECLARE new_quantity1 INT; -- 获取商品价格 SELECT Cprice INTO price1 FROM Commodity WHERE `C#` = NEW.`C#`; -- 获取消费数量 SET new_quantity1 = NEW.Cquantity; -- 更新总价格 UPDATE Consumption_list SET GCprice = price1 * new_quantity1 WHERE `CL#` = NEW.`CL#` AND `C#` = NEW.`C#`; END; //
当添加Consumption_List中的内容时自动计算Roomfee该用户的总消费	DELIMITER // CREATE TRIGGER tr_tc AFTER UPDATE ON Consumption_List FOR EACH ROW BEGIN IF NEW.GCprice <> OLD.GCprice THEN UPDATE Roomfee SET Tcprice = ( SELECT SUM(GCprice) FROM Consumption_List WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#` ) WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; END IF; END; //
当实际退房时间更新时自动计算总房价触发器	DELIMITER // CREATE TRIGGER tr_trprice AFTER UPDATE ON Registration FOR EACH ROW BEGIN DECLARE tftime1 DATETIME; DECLARE rztime1 DATETIME; DECLARE Rprice1 DECIMAL(8,1); DECLARE Rnum DECIMAL(8,1); DECLARE CT VARCHAR(4); -- 获取客户类型 SELECT Custtype INTO CT FROM Customer WHERE `Cust#` = NEW.`Cust#`; -- 获取退房时间 SET tftime1 = NEW.tftime; -- 获取入住时间 SET rztime1 = NEW.rztime; -- 获取房间价格 SELECT Rprice INTO Rprice1 FROM Room WHERE `R#` = NEW.`R#`; -- 获取入住数量 SELECT rz_num INTO Rnum FROM Registration WHERE `R#` = NEW.`R#`; -- 检查退房时间是否更新 IF NEW.tftime <> OLD.tftime THEN IF CT = '普通' THEN UPDATE Roomfee SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum) WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`; ELSE UPDATE Roomfee SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum) * 0.8 WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`; END IF; END IF; END; //
当实际退房时间更新时根据实际退房时间自动计算罚款触发器	DELIMITER // CREATE TRIGGER tr_trfine AFTER UPDATE ON Registration FOR EACH ROW BEGIN DECLARE At DATETIME; DECLARE t DATETIME; DECLARE CT1 VARCHAR(4); DECLARE hour_diff INT; -- 获取客户类型 SELECT Custtype INTO CT1 FROM Customer WHERE `Cust#` = NEW.`Cust#`; -- 获取新的退房时间和预期退房时间 SET At = NEW.tftime; SET t = NEW.pre_tftime; -- 计算小时差 SET hour_diff = TIMESTAMPDIFF(HOUR, t, At); -- 计算罚款 IF hour_diff <= 0 THEN UPDATE Roomfee SET fine = 0 WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; ELSEIF hour_diff <= 6 AND hour_diff >= 1 THEN IF CT1 = '普通' THEN UPDATE Roomfee SET fine = 250 WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; ELSE UPDATE Roomfee SET fine = 0 WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; END IF; ELSEIF hour_diff > 6 THEN IF CT1 = '普通' THEN UPDATE Roomfee SET fine = (hour_diff / 24 * 500) WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; ELSE UPDATE Roomfee SET fine = (hour_diff / 24 * 200) WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`; END IF; END IF; END; //

入住触发器，如果入住登记表中有新的数据添加，说明有客户入住，将客户入住房间号对应房间表里的状态改为busy

DELIMITER //

CREATE TRIGGER tri_rz1

AFTER INSERT ON Registration

FOR EACH ROW

BEGIN

UPDATE Room

SET Rstate = 'busy'

WHERE `R#` = NEW.`R#`;

END;

退房触发器，如果房间登记表中实际退房时间更新，（说明退房了）那么将房间表中该房间的状态由busy改为free

DELIMITER //

CREATE TRIGGER tr_tf

AFTER UPDATE ON Registration

FOR EACH ROW

BEGIN

IF NEW.tftime <> OLD.tftime THEN

UPDATE Room

SET Rstate = 'free'

WHERE `R#` = NEW.`R#`;

END IF;

END;

Registration1表中有数据添加时，把Cust#和R#添加入Roomfee表中

DELIMITER //

CREATE TRIGGER tr_RCust

AFTER INSERT ON Registration

FOR EACH ROW

BEGIN

INSERT INTO Roomfee (`Cust#`, `R#`)

VALUES (NEW.`Cust#`, NEW.`R#`);

END;

当在消费单（Consumption_list）中输入消费商品的数量时，自动根据对应服务计价表里面的单价计算总价返回至消费单中的GCprice（总价）中

DELIMITER //

CREATE TRIGGER tr_up_Gcprice1

AFTER INSERT ON Consumption_list

FOR EACH ROW

BEGIN

DECLARE price1 DECIMAL(10, 2);

DECLARE new_quantity1 INT;

-- 获取商品价格

SELECT Cprice INTO price1

FROM Commodity

WHERE `C#` = NEW.`C#`;

-- 获取消费数量

SET new_quantity1 = NEW.Cquantity;

-- 更新总价格

UPDATE Consumption_list

SET GCprice = price1 * new_quantity1

WHERE `CL#` = NEW.`CL#` AND `C#` = NEW.`C#`;

END;

当添加Consumption_List中的内容时自动计算Roomfee该用户的总消费

DELIMITER //

CREATE TRIGGER tr_tc

AFTER UPDATE ON Consumption_List

FOR EACH ROW

BEGIN

IF NEW.GCprice <> OLD.GCprice THEN

UPDATE Roomfee

SET Tcprice = (

SELECT SUM(GCprice)

FROM Consumption_List

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`

)

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

END IF;

END;

当实际退房时间更新时自动计算总房价触发器

DELIMITER //

CREATE TRIGGER tr_trprice

AFTER UPDATE ON Registration

FOR EACH ROW

BEGIN

DECLARE tftime1 DATETIME;

DECLARE rztime1 DATETIME;

DECLARE Rprice1 DECIMAL(8,1);

DECLARE Rnum DECIMAL(8,1);

DECLARE CT VARCHAR(4);

-- 获取客户类型

SELECT Custtype INTO CT

FROM Customer

WHERE `Cust#` = NEW.`Cust#`;

-- 获取退房时间

SET tftime1 = NEW.tftime;

-- 获取入住时间

SET rztime1 = NEW.rztime;

-- 获取房间价格

SELECT Rprice INTO Rprice1

FROM Room

WHERE `R#` = NEW.`R#`;

-- 获取入住数量

SELECT rz_num INTO Rnum

FROM Registration

WHERE `R#` = NEW.`R#`;

-- 检查退房时间是否更新

IF NEW.tftime <> OLD.tftime THEN

IF CT = '普通' THEN

UPDATE Roomfee

SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum)

WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`;

ELSE

UPDATE Roomfee

SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum) * 0.8

WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`;

END IF;

END;

当实际退房时间更新时根据实际退房时间自动计算罚款触发器

DELIMITER //

CREATE TRIGGER tr_trfine

AFTER UPDATE ON Registration

FOR EACH ROW

BEGIN

DECLARE At DATETIME;

DECLARE t DATETIME;

DECLARE CT1 VARCHAR(4);

DECLARE hour_diff INT;

-- 获取客户类型

SELECT Custtype INTO CT1

FROM Customer

WHERE `Cust#` = NEW.`Cust#`;

-- 获取新的退房时间和预期退房时间

SET At = NEW.tftime;

SET t = NEW.pre_tftime;

-- 计算小时差

SET hour_diff = TIMESTAMPDIFF(HOUR, t, At);

-- 计算罚款

IF hour_diff <= 0 THEN

UPDATE Roomfee

SET fine = 0

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

ELSEIF hour_diff <= 6 AND hour_diff >= 1 THEN

IF CT1 = '普通' THEN

UPDATE Roomfee

SET fine = 250

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

ELSE

UPDATE Roomfee

SET fine = 0

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

END IF;

ELSEIF hour_diff > 6 THEN

IF CT1 = '普通' THEN

UPDATE Roomfee

SET fine = (hour_diff / 24 * 500)

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

ELSE

UPDATE Roomfee

SET fine = (hour_diff / 24 * 200)

WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;

END IF;

END;

【存储过程的创建】

存储过程名（作用）	代码
在入住和更换房间时查询空房间	DELIMITER // CREATE PROCEDURE up_joker1() BEGIN SELECT * FROM Room_view WHERE Rstate = 'free'; END; //
查询客户的消费记录	DELIMITER // CREATE PROCEDURE up_joker2() BEGIN SELECT * FROM Consumption_List_view; END; //
查询客户房间总消费（房费+服务消费+罚款）	DELIMITER // CREATE PROCEDURE up_joker3() BEGIN SELECT * FROM Roomfee; END; //

存储过程名（作用）

代码

在入住和更换房间时查询空房间

DELIMITER //

CREATE PROCEDURE up_joker1()

BEGIN

SELECT * FROM Room_view WHERE Rstate = 'free';

END;

查询客户的消费记录

DELIMITER //

CREATE PROCEDURE up_joker2()

BEGIN

SELECT * FROM Consumption_List_view;

END;

查询客户房间总消费（房费+服务消费+罚款）

DELIMITER //

CREATE PROCEDURE up_joker3()

BEGIN

SELECT * FROM Roomfee;

END;

【注：建立hotel数据库全部代码】

CREATE TABLE `hotel`.`customer` (
  `cust` VARCHAR(5) NOT NULL COMMENT '客户编号',
  `id` VARCHAR(20) NOT NULL COMMENT '身份证号',
  `custname` VARCHAR(10) NOT NULL COMMENT '客户名称',
  `Custphone_num` VARCHAR(11) NOT NULL COMMENT '手机号',
  `Cust_type` VARCHAR(10) NOT NULL COMMENT '客户类型',
  PRIMARY KEY (`cust`))
COMMENT = '客户';

INSERT INTO customer (cust, id, custname, custphone_num, cust_type) VALUES
('00001', '123456789012345678', '张三', '13800138000', '普通'),
('00002', '987654321098765432', '李四', '13800138001', '会员'),
('00003', '456789123456789123', '王五', '13800138002', '普通'),
('00004', '321098765432109876', '赵六', '13800138003', '会员'),
('00005', '654321098765432109', '孙七', '13800138004', '普通'),
('00006', '789123456789123456', '周八', '13800138005', '会员'),
('00007', '210987654321098765', '吴九', '13800138006', '普通'),
('00008', '543210987654321098', '郑十', '13800138007', '会员'),
('00009', '876543210987654321', '陈十一', '13800138008', '普通'),
('00010', '109876543210987654', '杨十二', '13800138009', '会员'),
('00011', '234567890123456789', '何十三', '13800138010', '普通'),
('00012', '345678901234567890', '吕十四', '13800138011', '会员'),
('00013', '567890123456789012', '史十五', '13800138012', '普通'),
('00014', '678901234567890123', '陶十六', '13800138013', '会员'),
('00015', '890123456789012345', '姜十七', '13800138014', '普通'),
('00016', '901234567890123456', '范十八', '13800138015', '会员'),
('00017', '012345678901234567', '冯十九', '13800138016', '普通'),
('00018', '123450987654321098', '余二十', '13800138017', '会员'),
('00019', '234561098765432109', '陶二十一', '13800138018', '普通'),
('00020', '345672109876543210', '严二十二', '13800138019', '会员');

CREATE TABLE `hotel`.`room` (
  `R#` VARCHAR(5) NOT NULL COMMENT '房间号',
  `Rvolume` INT NOT NULL COMMENT '容纳人数',
  `Rprice` DECIMAL(5,1) NOT NULL COMMENT '房间价格',
  `Rtype` VARCHAR(10) NOT NULL COMMENT '房间类型',
  `Rjldw` VARCHAR(10) NOT NULL COMMENT '计量单位',
  `Rstate` CHAR(4) NOT NULL COMMENT '房间状态',
  PRIMARY KEY (`R#`))
COMMENT = '房间';

INSERT INTO room (`R#`, Rvolume, Rprice, Rtype, Rjldw, Rstate) VALUES
('1001', 1, 120.0, '标间', '人/24小时', 'free'),
('1002', 2, 150.0, '大床房', '人/24小时', 'free'),
('1003', 1, 100.0, '单人间', '人/24小时', 'free'),
('1004', 3, 200.0, '套间', '人/24小时', 'free'),
('1005', 2, 130.0, '标间', '人/24小时', 'free'),
('1006', 1, 110.0, '单人间', '人/24小时', 'free'),
('1007', 2, 140.0, '大床房', '人/24小时', 'free'),
('1008', 1, 115.0, '单人间', '人/24小时', 'free'),
('1009', 3, 210.0, '套间', '人/24小时', 'free'),
('1010', 2, 125.0, '标间', '人/24小时', 'free'),
('2001', 1, 105.0, '单人间', '人/24小时', 'free'),
('2002', 2, 135.0, '大床房', '人/24小时', 'free'),
('2003', 1, 120.0, '单人间', '人/24小时', 'free'),
('2004', 3, 220.0, '套间', '人/24小时', 'free'),
('2005', 2, 140.0, '标间', '人/24小时', 'free'),
('2006', 1, 110.0, '单人间', '人/24小时', 'free'),
('2007', 2, 150.0, '大床房', '人/24小时', 'free'),
('2008', 1, 115.0, '单人间', '人/24小时', 'free'),
('2009', 3, 230.0, '套间', '人/24小时', 'free'),
('2012', 2, 130.0, '标间', '人/24小时', 'free');

CREATE TABLE Registration (
    `R#` VARCHAR(5) NOT NULL COMMENT '房间号',
    `Cust#` VARCHAR(5) NOT NULL COMMENT '客户编号',
    rztime DATETIME NOT NULL COMMENT '实际入住时间',
    Pre_tftime DATETIME NOT NULL COMMENT '预定退房时间',
    tftime DATETIME NOT NULL COMMENT '实际退房时间',
    PRIMARY KEY (`R#`, `Cust#`, rztime),
    FOREIGN KEY (`R#`) REFERENCES room (`R#`),
    FOREIGN KEY (`Cust#`) REFERENCES customer(`Cust#`)
) COMMENT '入住登记表';

INSERT INTO Registration (`R#`, `Cust#`, rztime, Pre_tftime, tftime) VALUES
('1001', '00001', '2024-01-03 12:20:00', '2024-01-04 12:10:00', '2024-01-04 12:00:00'),
('1002', '00002', '2024-01-04 8:20:00', '2024-01-05 8:10:00', '2024-01-06 9:00:00'),
('1003', '00003', '2024-01-04 10:10:00', '2024-01-06 10:10:00', '2024-01-06 10:02:00'),
('1004', '00004', '2024-01-06 13:20:00', '2024-01-07 13:20:00', '2024-01-07 13:01:00'),
('1005', '00005', '2024-01-07 12:20:00', '2024-01-08 12:10:00', '2024-01-08 12:00:00'),
('1006', '00006', '2024-01-08 12:20:00', '2024-01-09 12:10:00', '2024-01-09 12:00:00'),
('1007', '00007', '2024-01-09 12:20:00', '2024-01-10 12:10:00', '2024-01-10 12:00:00'),
('1008', '00008', '2024-01-10 12:20:00', '2024-01-11 12:10:00', '2024-01-11 12:00:00'),
('1009', '00009', '2024-01-11 12:20:00', '2024-01-12 12:10:00', '2024-01-12 12:00:00'),
('1010', '00010', '2024-01-12 12:20:00', '2024-01-13 12:10:00', '2024-01-13 12:00:00'),
('2001', '00011', '2024-01-13 12:20:00', '2024-01-14 12:10:00', '2024-01-14 12:00:00'),
('2002', '00012', '2024-01-14 12:20:00', '2024-01-15 12:10:00', '2024-01-15 12:00:00'),
('2003', '00013', '2024-01-15 12:20:00', '2024-01-16 12:10:00', '2024-01-16 12:00:00'),
('2004', '00014', '2024-01-16 12:20:00', '2024-01-17 12:10:00', '2024-01-17 12:00:00'),
('2005', '00015', '2024-01-17 12:20:00', '2024-01-18 12:10:00', '2024-01-18 12:00:00'),
('2006', '00016', '2024-01-18 12:20:00', '2024-01-19 12:10:00', '2024-01-19 12:00:00'),
('2007', '00017', '2024-01-19 12:20:00', '2024-01-20 12:10:00', '2024-01-20 12:00:00'),
('2008', '00018', '2024-01-20 12:20:00', '2024-01-21 12:10:00', '2024-01-21 12:00:00'),
('2009', '00019', '2024-01-21 12:20:00', '2024-01-22 12:10:00', '2024-01-22 12:00:00'),
('2012', '00020', '2024-01-22 12:20:00', '2024-01-23 12:10:00', '2024-01-23 12:00:00');

CREATE TABLE Employee (
    `E#` VARCHAR(5) NOT NULL PRIMARY KEY COMMENT '职工编号',
    Ename VARCHAR(5) NOT NULL COMMENT '职工姓名',
    Esex BIT(1) NULL COMMENT '性别，0代表女生，1代表男生',
    Eage INT NOT NULL COMMENT '职工年龄',
    `D#` VARCHAR(5) NOT NULL COMMENT '部门编号',
    Esalary DECIMAL(5,1) NOT NULL COMMENT '薪资',
    Ephone CHAR(11) NOT NULL COMMENT '手机号'
);

INSERT INTO Employee (`E#`, Ename, Esex, Eage, `D#`, Ephone, Esalary) VALUES
('16520', '胡强强', 1, 21, '001', '18321262958', 6000.0),
('16521', '张伟', 1, 30, '001', '13812345678', 7000.0),
('16522', '李娜', 0, 25, '002', '13912345679', 6500.0),
('16523', '王磊', 1, 28, '002', '13712345680', 7200.0),
('16524', '赵敏', 0, 27, '002', '13612345681', 6800.0),
('16525', '刘强', 1, 32, '003', '13512345682', 7500.0),
('16526', '孙梅', 0, 24, '004', '13412345683', 6300.0),
('16527', '周杰', 1, 29, '004', '13312345684', 7100.0),
('16528', '吴燕', 0, 26, '005', '13212345685', 6700.0),
('16529', '郑浩', 1, 31, '006', '13112345686', 7400.0),
('16530', '韩露', 0, 22, '007', '13012345687', 6200.0),
('16531', '陈刚', 1, 33, '007', '12912345688', 7600.0),
('16532', '林欣', 0, 23, '008', '12812345689', 6400.0),
('16533', '朱强', 1, 34, '009', '12712345690', 7800.0),
('16534', '钱丽', 0, 21, '009', '12612345691', 6100.0);

CREATE TABLE Dept (
    `D#` VARCHAR(5) NOT NULL PRIMARY KEY COMMENT '部门编号',
    Dname VARCHAR(5) NOT NULL COMMENT '部门名称'
);
INSERT INTO Dept (`D#`, Dname) VALUES
('001', '行政办'),
('002', '餐饮部门'),
('003', '财务部门'),
('004', '人力资源部'),
('005', '销售部'),
('006', '房东部'),
('007', '康乐部'),
('008', '工程部'),
('009', '安保部');

ALTER TABLE Employee
ADD CONSTRAINT FK_Dept
FOREIGN KEY (`D#`) REFERENCES Dept(`D#`);

CREATE TABLE Commodity (
    `C#` VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '服务编号',
    Cname VARCHAR(10) NOT NULL COMMENT '服务名称',
    Cprice DECIMAL(8,1) NOT NULL COMMENT '服务价格',
    Ctype VARCHAR(10) NOT NULL COMMENT '服务类型',
    jidw VARCHAR(10) NULL COMMENT '计量单位'
);

INSERT INTO Commodity (`C#`, Cname, Cprice, Ctype, jidw) VALUES
('c0001', '可乐', 5.0, '饮料酒水', '瓶'),
('c0002', '矿泉水', 3.0, '饮料酒水', '瓶'),
('c0003', '脉动', 7.0, '饮料酒水', '瓶'),
('c0004', '佳得乐', 8.0, '饮料酒水', '瓶'),
('c0005', '美年达', 4.0, '饮料酒水', '瓶'),
('c0006', '红牛', 10.0, '饮料酒水', '瓶'),
('c0007', '冰红茶', 6.0, '饮料酒水', '瓶'),
('c0008', '卡布奇诺', 35.0, '饮料酒水', '瓶'),
('c0009', '冰美式', 23.0, '饮料酒水', '瓶'),
('c0010', '外星人电解质', 10.0, '饮料酒水', '瓶'),
('c0011', '乐事薯片', 8.0, '食品', '件'),
('c0012', '奥利奥饼干', 9.0, '食品', '件'),
('c0013', '费列罗巧克力', 52.0, '食品', '件'),
('c0014', '山楂片', 5.0, '食品', '件'),
('c0015', '豆腐干', 14.0, '食品', '件'),
('c0016', '好丽友派', 16.0, '食品', '件'),
('c0017', '趣多多', 18.0, '食品', '件'),
('c0018', '呀土豆', 20.0, '食品', '件'),
('c0019', '卫龙辣条', 4.0, '食品', '件'),
('c0020', '沙琪玛', 10.0, '食品', '件'),
('c0021', '按摩', 80.0, '服务', '小时'),
('c0022', '健身房', 52.0, '服务', '次'),
('c0023', '自助餐厅', 90.0, '服务', '次'),
('c0024', '会议室租赁', 50.0, '服务', '次');
CREATE TABLE Consumption_List (
    `CL#` varchar(10) NOT NULL PRIMARY KEY,
    `Cust#` varchar(5) NOT NULL,
    `R#` varchar(10) NOT NULL,
    `C#` varchar(10) NOT NULL,
    Cquantity int,
    GCprice decimal(8,1),
    Ctime date NOT NULL,
    `M#` varchar(10) NOT NULL,
    FOREIGN KEY (`C#`) REFERENCES Commodity(`C#`),
    FOREIGN KEY (`Cust#`) REFERENCES Customer(`cust`)
);

INSERT INTO Consumption_List (`CL#`, `Cust#`, `R#`, `C#`, Cquantity, Ctime, `M#`, GCprice) VALUES
('cl001', '00001', '1001', 'c0013', 10, '2024-01-03 19:05:00', 'm001', 150.0),
('cl002', '00003', '2001', 'c0022', 3, '2024-01-04 18:15:00', 'm002', 156.0),
('cl003', '00003', '2001', 'c0008', 3, '2024-01-04 20:15:00', 'm002', 105.0),
('cl004', '00001', '1001', 'c0009', 3, '2024-01-03 19:25:00', 'm002', 69.0),
('cl005', '00002', '1002', 'c0008', 1, '2024-01-05 7:30:00', 'm003', 35.0),
('cl006', '00001', '1001', 'c0020', 1, '2024-01-04 11:25:00', 'm004', 35.0),
('cl007', '00004', '1005', 'c0021', 7, '2024-01-06 18:15:00', 'm005', 240.0),
('cl008', '00009', '1002', 'c0007', 4, '2024-01-11 19:35:00', 'm004', 24.0),
('cl009', '00007', '2003', 'c0008', 2, '2024-01-10 7:45:00', 'm001', 20.0),
('cl010', '00007', '2003', 'c0022', 5, '2024-01-10 8:45:00', 'm002', 34.0),
('cl011', '00011', '1008', 'c0016', 2, '2024-01-13 18:15:00', 'm005', 32.0),
('cl012', '00008', '1005', 'c0019', 2, '2024-01-10 18:15:00', 'm003', 8.0),
('cl013', '00011', '1008', 'c0019', 1, '2024-01-13 19:15:00', 'm003', 4.0),
('cl014', '00012', '2004', 'c0014', 4, '2024-01-14 19:25:00', 'm004', 32.0),
('cl015', '00013', '2001', 'c0008', 6, '2024-01-15 20:15:00', 'm004', 210.0),
('cl016', '00010', '2004', 'c0025', 1, '2024-01-12 21:15:00', 'm003', 150.0),
('cl017', '00013', '2001', 'c0008', 3, '2024-01-15 20:35:00', 'm002', 45.0),
('cl018', '00005', '2007', 'c0015', 2, '2024-01-07 22:15:00', 'm002', 42.0),
('cl019', '00008', '2009', 'c0004', 5, '2024-01-10 23:15:00', 'm003', 40.0),
('cl020', '00008', '2009', 'c0005', 4, '2024-01-10 23:25:00', 'm002', 16.0);

CREATE TABLE Manager (
    `M#` varchar(10) NOT NULL PRIMARY KEY,
    `Mname` varchar(10) NOT NULL
);

INSERT INTO Manager (`M#`, `Mname`) VALUES
('m001', '金一'),
('m002', '阿福'),
('m003', '小明'),
('m004', '老陈'),
('m005', '乔心成');

ALTER TABLE Consumption_List
ADD CONSTRAINT fk_manager
FOREIGN KEY (`M#`) REFERENCES Manager(`M#`);


CREATE TABLE Roomfee (
    `Cust#` varchar(5) NOT NULL,
    `R#` varchar(5) NOT NULL,
    Trzprice decimal(10,1) NOT NULL,
    Tprice decimal(10,1) NOT NULL,
    Tcprice decimal(10,1),
    Fine decimal(10,1),
    PRIMARY KEY (`Cust#`, `R#`),
    FOREIGN KEY (`Cust#`) REFERENCES customer(`cust`),
    FOREIGN KEY (`R#`) REFERENCES room(`R#`)
);

INSERT INTO Roomfee (`Cust#`, `R#`, Trzprice, Tprice, Tcprice, Fine) VALUES
('00001', '1001', 288.0, 259.0, 0.0, 547.0),
('00002', '1002', 1200.0, 35.0, 0.0, 1235.0),
('00003', '2001', 1080.0, 501.0, 0.0, 1581.0),
('00004', '1009', 9600.0, 0.0, 0.0, 9600.0),
('00005', '2003', 1920.0, 16.0, 0.0, 1936.0),
('00005', '1010', 2400.0, 0.0, 0.0, 2400.0),
('00006', '1005', 6400.0, 148.0, 0.0, 6548.0),
('00007', '2003', 2900.0, 500.0, 0.0, 2900.0),
('00008', '2002', 480.0, 0.0, 0.0, 480.0),
('00009', '1007', 1080.0, 0.0, 0.0, 1080.0),
('00010', '1008', 240.0, 32.0, 0.0, 272.0),
('00011', '1003', 1920.0, 8.0, 0.0, 1928.0),
('00012', '1004', 7200.0, 0.0, 1000.0, 8200.0),
('00013', '2008', 11200.0, 32.0, 0.0, 11332.0),
('00014', '1006', 4800.0, 0.0, 0.0, 4800.0),
('00014', '2004', 3200.0, 210.0, 0.0, 3410.0),
('00015', '2005', 1600.0, 0.0, 0.0, 1600.0),
('00015', '2009', 1920.0, 150.0, 0.0, 2070.0),
('00016', '2006', 12000.0, 25.0, 0.0, 12025.0),
('00017', '2007', 5600.0, 42.0, 0.0, 5642.0),
('00018', '2009', 1600.0, 61.0, 0.0, 1661.0),
('00019', '2008', 10000.0, 0.0, 0.0, 10000.0),
('00020', '2012', 12000.0, 0.0, 0.0, 12000.0);

create view Consumption_List_view as
select `CL#`,`Cust#`,Cname,Cquantity,Gcprice, Manager.`M#`,Mname
from Consumption_List,Commodity,Manager
where Consumption_List.`C#`=Commodity.`C#` and Consumption_List.`M#`=Manager.`M#`;

create view Employee_view as
select `E#`,Ename,Dname,Ephone
from Employee,Dept
where Employee.`D#`=Dept.`D#`;

create view Commodity_view as
select `C#`,Cname,Cprice,Ctype
from commodity;

create view Room_view as
select Room.`R#`,Rvolume,Rprice, Rtype,Rstate,rztime,tftime
from Room,Registration
where Room.`R#`=Registration.`R#`;

create view Room_view as
select Room.`R#`,Rvolume,Rprice, Rtype,Rstate,rztime,tftime
from Room,Registration
where Room.`R#`=Registration.`R#`;

DELIMITER //

CREATE TRIGGER tri_rz1
AFTER INSERT ON Registration
FOR EACH ROW
BEGIN
    UPDATE Room
    SET Rstate = 'busy'
    WHERE `R#` = NEW.`R#`;
END;
//

DELIMITER //

CREATE TRIGGER tr_RCust
AFTER INSERT ON Registration
FOR EACH ROW
BEGIN
    INSERT INTO Roomfee (`Cust#`, `R#`)
    VALUES (NEW.`Cust#`, NEW.`R#`);
END;
//

DELIMITER //
CREATE TRIGGER tr_up_Gcprice1
AFTER INSERT ON Consumption_list
FOR EACH ROW
BEGIN 
    DECLARE price1 DECIMAL(10, 2);
    DECLARE new_quantity1 INT;
    
    -- 获取商品价格
    SELECT Cprice INTO price1 
    FROM Commodity
    WHERE `C#` = NEW.`C#`;
    
    -- 获取消费数量
    SET new_quantity1 = NEW.Cquantity;
    
    -- 更新总价格
    UPDATE Consumption_list 
    SET GCprice = price1 * new_quantity1
    WHERE `CL#` = NEW.`CL#` AND `C#` = NEW.`C#`;
END;
//

DELIMITER //
CREATE TRIGGER tr_tc
AFTER UPDATE ON Consumption_List
FOR EACH ROW
BEGIN
    IF NEW.GCprice <> OLD.GCprice THEN
        UPDATE Roomfee
        SET Tcprice = (
            SELECT SUM(GCprice)
            FROM Consumption_List
            WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`
        )
        WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
    END IF;
END;
//


DELIMITER //
CREATE TRIGGER tr_trprice
AFTER UPDATE ON Registration
FOR EACH ROW
BEGIN
    DECLARE tftime1 DATETIME;
    DECLARE rztime1 DATETIME;
    DECLARE Rprice1 DECIMAL(8,1);
    DECLARE Rnum DECIMAL(8,1);
    DECLARE CT VARCHAR(4);

    -- 获取客户类型
    SELECT Custtype INTO CT
    FROM Customer
    WHERE `Cust#` = NEW.`Cust#`;

    -- 获取退房时间
    SET tftime1 = NEW.tftime;

    -- 获取入住时间
    SET rztime1 = NEW.rztime;

    -- 获取房间价格
    SELECT Rprice INTO Rprice1
    FROM Room
    WHERE `R#` = NEW.`R#`;

    -- 获取入住数量
    SELECT rz_num INTO Rnum
    FROM Registration
    WHERE `R#` = NEW.`R#`;

    -- 检查退房时间是否更新
    IF NEW.tftime <> OLD.tftime THEN
        IF CT = '普通' THEN
            UPDATE Roomfee
            SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum)
            WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`;
        ELSE
            UPDATE Roomfee
            SET Trzprice = (TIMESTAMPDIFF(HOUR, rztime1, tftime1) / 24 * Rprice1 * Rnum) * 0.8
            WHERE `R#` = NEW.`R#` AND `Cust#` = NEW.`Cust#`;
        END IF;
    END IF;
END;
//

DELIMITER //
CREATE TRIGGER tr_trfine
AFTER UPDATE ON Registration
FOR EACH ROW
BEGIN
    DECLARE At DATETIME;
    DECLARE t DATETIME;
    DECLARE CT1 VARCHAR(4);
    DECLARE hour_diff INT;
    
    -- 获取客户类型
    SELECT Custtype INTO CT1
    FROM Customer
    WHERE `Cust#` = NEW.`Cust#`;

    -- 获取新的退房时间和预期退房时间
    SET At = NEW.tftime;
    SET t = NEW.pre_tftime;
    
    -- 计算小时差
    SET hour_diff = TIMESTAMPDIFF(HOUR, t, At);
    
    -- 计算罚款
    IF hour_diff <= 0 THEN
        UPDATE Roomfee
        SET fine = 0
        WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
    ELSEIF hour_diff <= 6 AND hour_diff >= 1 THEN
        IF CT1 = '普通' THEN
            UPDATE Roomfee
            SET fine = 250
            WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
        ELSE
            UPDATE Roomfee
            SET fine = 0
            WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
        END IF;
    ELSEIF hour_diff > 6 THEN
        IF CT1 = '普通' THEN
            UPDATE Roomfee
            SET fine = (hour_diff / 24 * 500)
            WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
        ELSE
            UPDATE Roomfee
            SET fine = (hour_diff / 24 * 200)
            WHERE `Cust#` = NEW.`Cust#` AND `R#` = NEW.`R#`;
        END IF;
    END IF;
END;
//

DELIMITER //

CREATE PROCEDURE up_joker1()
BEGIN
    SELECT * FROM Room_view WHERE Rstate = 'free';
END;
//

DELIMITER //

CREATE PROCEDURE up_joker2()
BEGIN
    SELECT * FROM Consumption_List_view;
END;
//

DELIMITER //

CREATE PROCEDURE up_joker3()
BEGIN
    SELECT * FROM Roomfee;
END;
//

【具体建立的表格】

1.商品