数据库应用技术大作业——旅馆管理系统数据库设计.doc

SQL实践作业：旅馆管理系统数据库设计与实现自动化学院自动化专业小组成员及所完成的工作：所完成的工作：数据库整体结构的设计，ER图的绘制和其他工作的审核系统名称：旅馆管理系统一、系统需求1.背景：随着科技和经济的发展，旅游业已经成为一个热门的产业，并且传统的手工已不适应现代酒店管理的需要，及时、准确、全方位的网络化信息管理成为必需。在酒店的管理及业务日益复杂、要求在不断提高的现状下，利用高科技、现代化的电脑自动化管理系统来处理日益繁重的酒店业务，对于大型的酒店是必须具备的管理方式。 酒店客房管理系统是根据酒店对客房管理的实际情况进行编写的，主要目的是为了方便酒店对客房的实际情况进行集中的查询与管理工作，以提高整个酒店的工作。酒店客房管理的科学化、系统化、信息化成为各个酒店追求的目标。因此，而要实现这些功能，就要求各个酒店配备一套客房管理系统，以便在酒店内实施良好的完善的管理且以最快地速度响应客户的需求，及时为他们提供服务，为他们提供一个高效、便捷的居住环境。2.旅店需求特点分析： 通过对旅店的调研，对于旅店的需求特点有了基本了解(1)基本要求：系统能够进行客户的信息的存储，客户信息的删除，客户信息的 更新，客户账单的结算因此要求系统一定要严密准确，不能 出现插入和删除异常，因此要求数据库系统的设计符合第四范式(2)高级要求：旅店面对的经常是一些突发的情况，比如客户的提前退房，客户 要求换房，客户要求其他的附加服务这些突发事件是我们在我们的系统中需要着重考虑的3.旅客需求特点分析： 通过对用户的调研，我们发现用户希望住旅馆的手续能够尽量简单，但是要求旅馆的各种信息准确，出现错误的可能性尽量低4.旅店管理系统需求分析：l 旅店的客房特点分析：(1) 通过每个房间唯一的房间号来区别(也就是我们数据库系统中的room_num),房间分成不同的类型(room_type)有A、B、C三种类型，它们对应的价格也会不同，这个由顾客自己选择，体现了我们设计系统的人性化；(2) 房间的状态(room_state)会根据是否有用户入住而不同，如果该房间有人入住的话，那么就将房间的状态标记为busy；(3) 房间同时拥有IsBooked状态，如果房间被预定的话，那么就将这个房间的状态标记为Booked，如果没有预定的话，那么房间的状态为nobook，这样的话方便旅客入住、换房、续住种种操作，避免住进了已经有人预订的房间,同时有些房间即使已经有人预定了，但是在预定旅客之前其他旅客还是可以住进来的，只要他在预定旅客入住之前退房就可以了，样大大提高了客房的利用率。(4) 考虑房间和顾客的关系，房间和旅客关系是一对多的，因为从现实出发，房间分为单人房、双人房等，旅客的收费是按照床位来收费的，但是不同房间的床位的价格是不同的，但是在我们的数据库中默认所有的房间都是单人房，这是我们设计的不足，更理想的是在每种类型中分为单人房、双人房，然后为每个床位设立状态，每位顾客都是按照床位来收房，而不是简单的按照房间来收费，但是在现实生活中旅馆的类型又有很多种，我们所设计的数据库可能只是适合其中的部分类型的旅馆。l 旅客特点分析：从旅馆的角度来说，最重要的就是准确地记录旅客在住宿期间一切信息，并且要求准确(1) lodger表用来记录旅客的信息，有lodger_name,id_num,room_num,echeckin_date,exp_checkout_date,pre_payment(2) lodger表用来记录当前正在发生入住的旅客的信息，这个表是动态的，l 如果旅客退房，与之相关的表示checkout实体集，将用户的信息载入这个表，因为退房的信息是非常关键的，因为旅店的账目的结算是要依靠这个表的，然后他的信息就会从lodger这个表中删除；l 旅客可能要求换房，实现这个功能的是change_room联系集这个联系集联系了lodger和Room两个表，将原来的房间的状态修改为Free,将新入住的房间修改为busy；l 旅客可能要求续住，我们当然会满足这个要求，实现这个要求的要求是联系集con_room，如果旅客续住那么在情况允许的情况下，可以继续在原来的房间住宿，并修改exp_checkout_date 的信息，如果原来的房间已经有人预定的话，那么建议旅客更改房间，重新登记lodger信息；l 旅店的账目管理特点分析l旅店的账目管理系统是一个比较复杂的系统，要求每天都要进行更新，同时还须具备日结、月结、季度结、年结的功能，与此相关的是checkout,DayAccount,MonthAccount,YearAccount他们最终依赖的表都是checkout表，在我们的系统中checkout表用于记录退房用户的信息，我们并没有设定主键只是设立了外依赖，因为在这个表中不可能有两个完全相同的元组(lodger_name,room_num,cur_date,(cur_year,cur_month,cur_day),room_account,back_change),属性(lodger_name,room_num,cur_date,)使任意两个元组都不可能相同，因为cur_date是精确到秒的，在实际情况中具有相同lodger_name,在同一时间内从同一个房间退房时不会发生地，之所以不删除这个表的元组，是因为我们想保留这些信息以便日后的查询，比如核对账单，比如发生刑事案件需要旅店配合时，可能需要查询相关的信息l日结DayAccount的实现也比较复杂，首先我们定制一个作业Inseting DayAccount 每天0:00:00向DayAccount插入一个用于初始化的元组，然后建立一个checkout与DayAccount之间的触发器，如果向checkout中插入信息，那么就将 room_account累加到DayAccount中的day_account中去，这样就能自动统计了每天的盈利状况lMonthAccount和YearAccount的算法和DayAccount类似，同样通过定制作业和建立触发器，实现自动运算，在这就不多介绍了二、系统概念模型（E-R图）三、关系模式（逻辑模型）四、物理设计（表结构）Table1:Room(Entity Set)Attribute: room_num (房间号),room_type (房间类型),room_price(房间价格),room_state(房间状态),IsBooked (预定状态)PrimaryKey: room_numTable2：Lodger (Entity Set)Attribute:lodger_name (客户姓名),id_num (身份证号),room_num (房间号),checkin_date (入住时间),exp_checkout_date (预期退房时间), pe_payment (客户预付款)PrimaryKey：lodger_num,id_numForeignKey：room_num references RoomTable 3：Room_Prebook (Entity Set)Attribute:reserve_name (预定客户姓名),room_num (预定房间号), exp_checkin_date(预期入住时间),pre_payment (预付款)PrimaryKey:reserve_nameForeignKey：room_num references RoomTable4：DayAccount (Entity Set)Attribute：cur_year (年),cur_month (月),cur_day (日),day_account(月结)PrimaryKey:cur_year,cur_month,cur_dayTable5：checkout(Relation Set)Attribute: lodger_name (客户姓名),room_num (房间号),cur_date (日期),(cur_year,cur_month,cur_day)(年/月/日),live_days (住宿天数),room_account (账单),back_change (找零)ForeignKey:lodger_name references Lodger room_num references Room Table6: MonthAccount(Entity Set)Attribute: cur_year (年),cur_month (月),month_account (日结)PrimaryKey:cur_year,cur_monthTable7:YearAccount (Entity Set) Attribute: cur_year (年),year_account (年结) PrimaryKey: cur_year Table8:Remind(EntitySet) Attribute: lodger_name (客户姓名),room_num (房间号),cur_date (日期) PrimaryKey: lodger_name,cur_date ForeignKey: lodger_name 五、系统实现1.功能模块设计：(1) 客房信息管理集合a) 查询房间的空闲状态b) 查询房间的预定状态c) 查询某种类型房间的价格d) 更新某种类型房间的价格e) 更新房间的空闲状态f) 更新房间的预定状态(2) 旅客信息管理集合a) 查询入住旅客的详细信息b) 查询已预订旅客的信息c) 查询已退房旅客的信息d) 取消预定操作e) 换房操作f) 续住操作g) 退房操作(3) 旅店账户管理集合a) 退房客户结算b) 每日结算c) 每月结算d) 每年结算(4) 服务管理项目集合a) 每日提醒那些旅客已经到退房的时候b) 每日提醒那些预定的客户将在今天住进来2.创建数据库的SQL语句CREATE DATABASE HotelON(NAME = NHotel,FILENAME = NE:HotelHotel.mdf,SIZE = 3MB,MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)LOG ON( NAME = NHotel_log,FILENAME = NE:HotelHotel.ldf,SIZE = 1MB,MAXSIZE =2048GB, FILEGROWTH = 10%)GO说明:分别建立了mdf文件和ldf文件,规定mdf文件的初始大小为3MB,增长速率为1MB每次，日志文件初始大小为1MB增长速率为10%. 3创建表的SQL语句(1)创建room表，存储客房信息CREATE Table Room( room_num int not null, room_state char(10) not null, room_type char(10) not null, room_price char(10) not null, primary key(room_num) )（2）创建Lodger表，存储旅客信息CREATE Table Lodger( lodger_name char(20) not null, id_num bigint not null, room_num int not null, checkin_date datetime not null, exp_checkout_date datetime not null, pre_payment money, primary key(lodger_name), foreign key(room_num) references Room, unique(room_num)（3）创建Room_preBook表，用于存储预定客户信息CREATE TABLE Room_Prebook( reserve_name char(10) not null, room_num int not null, exp_checkin_date datetime not null, pre_payment money, primary key(reserve_name), foreign key(room_num)references Room)（4）创建日结表，存储日结信息CREATE Table DayAccount( cur_year int, cur_month int, cur_day int, day_account money,)（5）创建退房表，存储退房旅客信息CREATE Table Checkout( lodger_name char(20) not null, room_num int not null, cur_date datetime default GETDATE(), cur_year int , cur_month int , cur_day int , live_days int, room_account money, back_change money, foreign key (room_num) references Room)（6）创建MonthAccount表，存储月结信息create Table MonthAccount( cur_year int not null, cur_month int not null, Month_account money, foreign key (cur_year) references DayAccount)(7)创建YearAccount表，用于存储年结信息create Table YearAccount( cur_year int not null, year_account int not null, foreign key (cur_year) references DayAccount)(8)创建Remind表，用于每日提醒create Table Remind ( lodger_name char(20) not null, room_num char(20) not null, cur_date datetime default getdate() foreign key (lodger_name) references Lodger)4.存储过程展示：存储过程一：proc_query_freeroom代码：create proc proc_query_freeroomasselect room_num,room_type,room_statefrom roomwhere room_state = Free功能：查询当前空闲的房间存储过程二：proc_query_bookedroom代码：create proc proc_query_bookedroomasselect room_num,room_type,IsBookedfrom roomwhere IsBooked = booked功能：查询当前已经预定出去的房间存储过程三:proc_query_price代码：create proc proc_query_priceasselect distinct room_type,room_pricefrom room功能:查询不同类型房间的价格存储过程四：proc_inc_price代码：create proc proc_inc_priceroom_type char(10),new_price moneyasupdate room set room_price = new_pricewhere room_type = room_type功能：更改某种类型房间的价格存储过程五：proc_QueryLodger代码：create procedure proc_QueryLodgercheckin_date datetime asselect lodger_name,id_num,exp_checkout_date,pre_paymentfrom lodgerwhere datepart(day,checkin_date)=datepart(day,checkin_date)功能：查询指定日期入住的旅客存储过程六：proc_querybook代码：create proc proc_query_bookasselect room_num,reserve_name ,exp_checkin_date,pre_paymentfrom Room_prebook功能：查询预订的旅客的信息存储过程七：proc_bookCancel代码：create procedure proc_bookCancelreserve_name char(20)asupdate roomset IsBooked = nobookwhere room_num = (select room_num from Room_PreBook where reserve_name = reserve_name)delete from Room_PreBookwhere reserve_name = reserve_name执行：执行后的room_prebook表存储过程八：proc_changeroom代码：create proc proc_changeroomlodger_name char(10),source_room_num int,target_room_num intasif lodger_name in(select lodger_name from lodger) and target_room_num in (select room_num from room where room_state = Free)beginupdate roomset room_state = Freewhere room_num = source_room_numupdate roomset room_state = Busywhere room_num = target_room_numupdate lodger set room_num = target_room_numwhere lodger_name = lodger_nameend 存储过程九：proc_cont_lodge代码：create proc proc_cont_lodgelodger_name char(20),exp_checkout_date datetimeAsupdate Lodgerset exp_checkout_date = exp_checkout_datewhere lodger_name = lodger_nameupdate room set room_state = Busywhere room_num = (select room_num from lodger where lodger_name = lodger_name 存储过程十：proc_checkout_lodger代码：create procedure proc_checkout_lodger name char(20) as declare room_num intinsert checkout(lodger_name,room_num,cur_date,cur_year,cur_month,cur_day,live_days,room_account,back_change)select l.lodger_name, l.room_num, getdate(), year(getdate(), month(getdate(), day(getdate(), DATEDIFF(day, checkin_date, getdate() , DATEDIFF(day, checkin_date, getdate()* r.room_price , l.pre_payment - DATEDIFF(day, checkin_date, getdate()* r.room_price from lodger as l,room as rwhere l.lodger_name = name and l.room_num = r.room_num功能:退房并将旅客信息插进checkout表中存储过程十一：proc_dayaccount_checkout代码：create procedure proc_dayaccount_checkoutcur_year int,cur_month int ,cur_day int asinsert DayAccount(cur_year,cur_month,cur_day,day_account)select cur_year,cur_month,cur_day,sum(room_account)from checkoutwhere cur_year = cur_year and cur_month = cur_month and cur_day = cur_day group by cur_year,cur_month,cur_day功能：对checkout表中某一天的所有盈利进行累加并将结果存入dayaccount中存储过程十二：proc_dayaccount代码：create procedure proc_QueryDayAccountyear int,month int ,day intasselect cur_year,cur_month,cur_day,day_accountfrom DayAccountwhere cur_year = year and cur_month =month and cur_day = day 存储过程十三：proc_query_monthaccount代码：create proc proc_query_monthaccountyear int ,month intasselect cur_year,cur_month,month_accountfrom MonthAccountwhere cur_year = year and cur_month = month功能：查询月账单存储过程十四：proc_queryYearAccount代码：create proc proc_queryYearAccountyear intasselect cur_year,year_accountfrom YearAccountwhere cur_year = year功能：查询年盈利情况存储过程十五：proc_remind代码：create PROC proc_remindasINSERT INTO Remind(lodger_name,room_num) SELECT lodger_name,room_num FROM lodger WHERE datepart(day,exp_checkout_date) = datepart(day,getdate()功能：将住宿在当天要到期的旅客的姓名和其他相关信息放入每日提醒中以便对旅客进行提醒存储过程十六：proc_room_remind代码：create proc proc17_room_remindasupdate roomset room_state = Freewhere room_num in (select room_num from remind where datepart(day,cur_date)= datepart(day,getdate() )功能：将到期旅客所住房间的状态标记为free，那么这间房在当天的下午就可以被租出去了，这样的功能提高了房间的利用率执行前：存储过程十七：proc_discount代码：create proc proc_discountlodger_name char(20),discount numericasupdate checkoutset room_account = room_account * discountwhere lodger_name = lodger_name 功能：为某些符合条件的旅客进行打折存储过程十八： InitRoom代码：create proc InitRoomasupdate roomset room_state = freewhere room_num not in (select room_num from lodger) update roomset room_state = busywhere room_num in(select room_num from lodger)update roomset room_type = Cwhere room_type = 1update roomset room_type = Bwhere room_type = 2update roomset room_type = Awhere room_type = 3功能：规范化room存储过程十九：proc_count_freeroom代码：create proc proc_count_freeroomas select room_type,count(room_num)from room where room_state = Freegroup by room_type功能：统计目前空房数，按照房间类型来区分存储过程二十：proc_checkout_month代码：create proc proc_checkout_monthyear int ,month intas select lodger_name ,cur_year,cur_monthfrom checkoutwhere cur_year = year and cur_month =month功能：统计某月客户的退房情况触发器实践1.Tigger1: trigger1_room_lodger代码：create trigger trigger1_room_lodger on lodger for insertasbegindeclare room_num bigintselect room_num = inserted.room_num from insertedupdate roomset room_state = Busywhere room_num = room_numend功能：当向lodger中插入新的数据时(即有新旅客住进来)，那么将相应的房间的状态修改为busy。说明在向lodger中插入后，在触发器的作用下，room的状态发生了改变。2.Trigger2: trigger2_room_room_prebook代码：create trigger trigger2_room_room_prebook on room_prebook for insertas declare room_num bigintselect room_num = inserted.room_num from insertedbeginupdate roomset IsBooked = Bookedwhere room_num = room_num end功能说明:如果向room_prebook中插入订房旅客的信息，那么相关的房间的IsBooked状态将会被标记为booked3.Trigger3: trigger3_checkout on checkoutcreate trigger trigger3_checkout on checkout for insert as begin declare room_number intdeclare room_num intdeclare lodger_name char(20)-修改room状态select room_number = i.room_num from checkout as c ,inserted as iwhere i.lodger_name = c.lodger_nameupdate room set room_state = Freewhere room_num = room_number-修改lodger状态select room_num = c.room_num ,lodger_name = c.lodger_namefrom checkout as c,inserted as iwhere c.lodger_name = i.lodger_name and c.room_num = i.room_num delete from lodgerwhere lodger_name = lodger_name and room_num = room_num end功能：如果checkout表有数据插入，触发下列两项动作：(1) 将旅客所住房间在room中的room_state标记为Free(2) 将该旅客信息从lodger中删除4.Trigger4：tigger4_dayaccount_checkout代码：Create trigger tigger4_dayaccount_checkout on checkout for insertas begin declare room_account money select room_account = room_accountfrom insertedupdate DayAccount set day_account = day_account + room_account where cur_year = year(getdate() and cur_month = month(getdate() and cur_day = day(getdate()end功能:这是旅店的日结系统，首先通过定制作业每个一天向dayac