数据库酒店管理系统课程设计

实验题目题目8：酒店管理系统数据库的设计与实现描述：设计一个酒店管理系统，实现下列功能：1酒店客房信息维护（包括录入，修改和删除）；2客人信息维护（包括录入，修改和删除；） 3入住信息登记； 酒店管理系统关系模式:房号(房间号，类型，状态，价格，位置，床位数)入住信息(房间号，证件号，姓名，性别，入住日期)退房信息(房间号，证件号，结算日期，应付金额)旅客(证件类型，证件号，姓名，性别，年龄，籍贯，现住址，职业)使用(物品号，证件号，姓名，使用日期，押金)物品(物品号，数量，价格，用途)位 置旅 客客 房姓名性别证件号证件类型现住址房间号状态价 格床位数物 品使 用数 量物品号用 途证件号物品号押 金使用日期姓 名价 格类型应付金额入住籍贯职业退 房 E-R 图一：创建酒店管理系统数据库create database 酒店管理系统 /* 创建酒店管理系统数据库 */ 二：创建客房信息表并添加数据create table 客房 /* 创建客房信息表 */( 房间号 char(8)primary key, 类型 varchar(16)not null, 状态 char(8)not null, 价格 smallmoney not null, 位置 varchar(14)not null, 床位数 int check(床位数0)not null)insert into 客房 values(101,普通间,在使用,50,三楼,2)insert into 客房 values(102,普通间,在使用,40,三楼,1)insert into 客房 values(103,空调间,未使用,80,三楼,2)insert into 客房 values(104,空调间,在使用,60,二楼,1)insert into 客房 values(105,豪华间,在使用,200,二楼,2)insert into 客房 values(106,豪华间,在使用,180,一楼,1)insert into 客房 values(107,贵宾间,未使用,300,一楼,1)insert into 客房 values(108,贵宾间,在使用,400,一楼,2)所建的表为：101 普通间在使用 50.0000 三楼2102 普通间在使用 40.0000 三楼1103 空调间未使用 80.0000 三楼2104 空调间在使用 60.0000 二楼1105 豪华间在使用 200.0000二楼2106 豪华间在使用 180.0000一楼1107 贵宾间未使用 300.0000一楼1108 贵宾间在使用 400.0000一楼2 三：创建旅客信息表并添加数据create table 旅客 /* 创建旅客信息表 */( 证件类型 varchar(16), 证件号 varchar(18)not null, 姓名 varchar(10) not null, 性别 char(4)not null, 年龄 int check(年龄0)not null, 籍贯 text not null, 现住址 text not null, 职业 varchar(20)not null)insert into 旅客 values(学生证,赵亦,男,20,河北唐山,江西南昌,学生)insert into 旅客 values(身份证,钱尔,男,24,北京大兴区,上海浦东,工程师)insert into 旅客 values(军官证,李思,男,30,江西抚州,广州,团长)insert into 旅客 values(身份证,孙珊,女,32,湖北武汉,湖南长沙,教师)insert into 旅客 values(身份证,周武,男,35,南昌,香港,公务员)insert into 旅客 values(身份证,吴丽,女,40,南京,合肥,经理)所建的表为：学生证赵亦男 20河北唐山江西南昌学生身份证钱尔男 24北京大兴区上海浦东工程师军官证李思男 30江西抚州广州团长身份证孙珊女 32湖北武汉湖南长沙教师身份证周武男 35南昌香港公务员身份证吴丽女 40南京合肥经理 四：创建入住信息表并添加数据create table 入住信息 /* 创建入住信息表 */( 房间号 char(8)not null, 证件号 varchar(18)not null, 姓名 varchar(10) not null, 性别 char(4)not null, 入住日期 datetime not null)insert into 入住信息 values(101,赵亦,男,2011-5-3)insert into 入住信息 values(102,钱尔,男,2011-2-6)insert into 入住信息 values(104,李思,男,2011-8-7)insert into 入住信息 values(105,孙珊,女,2011-7-4)insert into 入住信息 values(106,周武,男,2011-3-6)insert into 入住信息 values(108,吴丽,女,2011-9-2)所建的表为：101 赵亦男 2011-05-03 00:00:00.000102 钱尔男 2011-02-06 00:00:00.000104 李思男 2011-08-07 00:00:00.000105 孙珊女 2011-07-04 00:00:00.000106 周武男 2011-03-06 00:00:00.000108 吴丽女 2011-09-02 00:00:00.000 五：创建退房信息表并添加数据create table 退房信息 /* 创建退房信息表 */( 房间号 char(8)not null, 证件号 varchar(18) not null, 结算日期 datetime, 应付金额 smallmoney)insert into 退房信息(房间号,证件号)values(101,)insert into 退房信息(房间号,证件号)values(102,)所建的表为：101 NULLNULL102 NULLNULL 六：创建使用表并添加数据create table 使用 /* 创建使用表 */( 物品号 char(6)not null, 证件号 varchar(18), 使用日期 datetime, 押金 smallmoney)insert into 使用 values(001,2011-10-2,50)所建的表为：001 2011-10-02 00:00:00.00050.0000 七：创建物品表并添加数据create table 物品 /* 创建物品表 */( 物品号 char(6)not null, 物品名 varchar(16)not null, 数量 int not null, 价格 money not null, 用途 text)insert into 物品 values(001,毛巾,100,20,洗浴)insert into 物品 values(002,牙膏,50,10,洗漱)insert into 物品 values(003,拖鞋,80,30,穿着)insert into 物品 values(004,香皂,100,8,洗浴)所建的表为：001 毛巾10020.0000 洗浴002 牙膏5010.0000 洗漱003 拖鞋8030.0000 穿着004 香皂1008.0000 洗浴 八：创建旅客录入的存储过程create procedure 旅客录入 /* 录入旅客信息的存储过程 */证件类型 varchar(16), -输入参数，对应表各字段证件号 varchar(18),姓名 varchar(10),性别 char(4),年龄 int,籍贯 text,现住址 text,职业 varchar(20),返回信息 varchar(50)=null output -输出参数,执行结果返回信息asbegin tran -启用事务-检查是否有同一客人记录if exists(select 证件号,姓名 from 旅客 where 证件号=证件号 and 姓名=姓名)begin select 返回信息=已存在该客人信息 goto onerror end-新增客人信息insert into 旅客(证件类型,证件号,姓名,性别,年龄,籍贯,现住址,职业)values(证件类型,证件号,姓名,性别,年龄,籍贯,现住址,职业)if error0begin select 返回信息=新增失败 goto onerrorendselect 返回信息=新增成功commit tranreturn-出错处理onerror: rollback tran returndeclare 姓名 varchar(10)declare 返回信息 varchar(50)exec 旅客录入 证件类型=学生证,证件号=,姓名=张锦杰,性别=男,年龄=19,籍贯=广东汕尾,现住址=江西南昌,职业=学生print 姓名+姓名select * from 旅客drop procedure 旅客录入drop table 旅客 九：创建旅客修改的存储过程create procedure 旅客修改 /* 修改旅客信息的存储过程 */证件类型 varchar(16), -输入参数,对应表各字段证件号 varchar(18),姓名 varchar(10),性别 char(4),年龄 int,籍贯 text,现住址 text,职业 varchar(20),返回信息 varchar(50)=null output -输出参数，执行结果返回信息asif not exists(select 证件号 from 旅客 where 证件号=证件号) select 返回信息=不存在此旅客的信息update 旅客 set 证件类型=证件类型,姓名=姓名,性别=性别,年龄=年龄,籍贯=籍贯,现住址=现住址,职业=职业where 证件号=证件号exec 旅客修改 证件号=,证件类型=身份证,姓名=吴丽,性别=女,年龄=48,籍贯=南京,现住址=合肥,职业=经理declare 返回信息 varchar(50)print 返回信息+返回信息select 返回信息=返回信息select * from 旅客drop procedure 旅客修改 十：创建旅客删除的存储过程create procedure 旅客删除 /* 删除旅客信息的存储过程 */证件号 varchar(18),返回信息 varchar(50)=null outputas begin tranif not exists(select 证件号 from 旅客 where 证件号=证件号)begin select 返回信息=不存在此旅客的信息 goto onerror enddelete from 旅客 where 证件号=证件号if error0begin select 返回信息=删除失败 goto onerror endselect 返回信息=成功删除 commit tran -提交事务 returnonerror: rollback tran returndeclare 证件号 varchar(18)declare 返回信息 varchar(50)exec 旅客删除 证件号=select * from 旅客drop procedure 旅客删除 十一：创建客房录入的存储过程create procedure 客房录入 /* 录入客房信息的存储过程 */房间号 char(8),类型 varchar(16),状态 char(8),价格 smallmoney ,位置 varchar(14),床位数 int,返回信息 varchar(50)=null outputasbegin tranif exists(select 房间号 from 客房 where 房间号=房间号)begin select 返回信息=已存在该客房信息 goto onerror endinsert into 客房(房间号,类型,状态,价格,位置,床位数)values(房间号,类型,状态,价格,位置,床位数)if error0begin select 返回信息=新增失败 goto onerror end select 返回信息=新增成功 commit tran return onerror: rollback tran returndeclare 返回信息 varchar(50)exec 客房录入 房间号=109,类型=标准间,状态=未使用,价格=50,位置=四楼,床位数=2drop procedure 客房录入delete from 客房 where 房间号=109select * from 客房十二：创建客房修改的存储过程create procedure 客房修改 /* 客房修改的存储过程 */房间号 char(8),类型 varchar(16),状态 char(8),价格 smallmoney,位置 varchar(14),床位数 int,返回信息 varchar(50)=null output -输出参数,执行结果返回信息asif not exists(select 房间号 from 客房 where 客房.房间号=房间号) select 返回信息=不存在该客房信息 print 返回信息+返回信息-修改客房信息update 客房 set 类型=类型,状态=状态,价格=价格,位置=位置,床位数=床位数where 客房.房间号=房间号 exec 客房修改 房间号=1018,类型=普通间,状态=在使用,价格=500,位置=三楼,床位数=2declare 返回信息 varchar(50)select 返回信息=返回信息print 返回信息+返回信息select * from 客房drop procedure 客房修改 十三：创建客房删除的存储过程create procedure 客房删除 /* 删除客房信息的存储过程 */房间号 varchar(18),返回信息 varchar(50)=null outputas begin tranif not exists(select 房间号 from 客房 where 房间号=房间号)begin select 返回信息=不存在此客房的信息 goto onerror enddelete from 客房 where 房间号=房间号if error0begin select 返回信息=删除失败 goto onerror endselect 返回信息=成功删除 commit tran -提交事务 returnonerror: rollback tran returndeclare 房间号 varchar(18)declare 返回信息 varchar(50)exec 客房删除 房间号=101select * from 客房drop procedure 客房删除 十四：创建客房查询的存储过程create procedure 客房查询 /* 查询客房信息的存储过程 */房间号 char(8),类型 varchar(16)output,状态 char(8)output,价格 smallmoney output,位置 varchar(14) output,床位数 int outputasselect 类型,状态,价格,位置,床位数from 客房where 房间号=房间号 declare 房间号 char(8)declare 类型 varchar(16)declare 状态 char(8)declare 价格 smallmoney declare 位置 varchar(14)declare 床位数 int exec 客房查询 101,类型 output,状态 output,价格 output,位置 output,床位数 outputprint 状态+状态drop procedure 客房查询十五：创建付款的存储过程 create procedure 付款(证件号 varchar(18) /* 获得应付金额的存储过程 */asupdate 退房信息set 结算日期=getdate()where 退房信息.证件号=证件号 and 结算日期 is nulldeclare 价格 smallmoneyselect 价格 from 客房 where 退房信息.证件号=证件号select 入住日期 from 入住信息 where 退房信息.证件号=证件号update 退房信息,入住信息set 应付金额=datediff(dd,入住日期,getdate()*价格where 证件号=证件号 and 应付金额 is null and 退房信息.证件号=入住信息.证件号update 客房 set 状态=未使用 where 退房信息.证件号=证件号exec 付款 证件号=drop procedure 付款select * from 客房select * from 旅客select * from 入住信息select * from 退房信息十六：创建入住登记触发器 create trigger 入住登记触发 /* 入住登记触发器 */on 入住信息for insertasbegin update 客房 set 状态=在使用 where 房间号 in (select 房间号 from inserted) enddrop trigger 入住登记触发select * from 入住信息select * from 客房insert into 入住信息 values(103,10204,张金杰,男,2011-8-8) 十七：创建退房触发器 create trigger 退房触发器 /* 退房触发 */on 退房信息for insert asbegin update 客房 set 状态=未使用 where 房间号 in (select 房间号 from inserted)delete from 旅客 wh