一个简单的酒店系统的数据库设计

    以前学习SQL SERVER时的课程设计，下午整理资料时无意中居然找到，虽然都好几年了，但发现用来学习SQL SERVER还是不错的。
    现在都用PowerDesigner了，以前这些代码可都是一行一行敲出来的，只是没有加字段说明，不记得当初有没有数据库设计说明文档。

----------------------------开始-----------------------------

--drop database hotel
if exists (select * from master..sysdatabases
           where name ='myhotel')
   drop database myhotel
go

--创建数据库
create database myhotel
on
(
 name = 'myhotel_dat',
 filename = 'c:/myhotel.mdf',
 size = 5,
 maxsize = 20,
 filegrowth = 1
)
log on
(
 name = 'myhotel_log',
 filename = 'c:/myhotel.log',
 size = 5,
 maxsize = 20,
 filegrowth =1
)
------------------------------------------------------------------
go

use myhotel
go

--创建表
--create table 表名(字段名 类型 primary,key,字段名,int,
--字段名 类型 check(字段名 in(范围)))
--bit 整型数据 1、0 或 NULL.如果一个表中有不多于 8 个的 bit 列，这些列将作为一个字节存储

--1,房间表(rootype='1'表示豪华双人房,roomstuats='0'表示末入住)
create table roomlist
    (roomid char(6) primary key,
     roomtypeid int ,
     roomstatus bit check (roomstatus in (0,1)))
go

--2.服务列表(serverlist)
----服务列表采用树型结构存储服务id
create table serverlist
    (serverid char(6) primary key,
     servername char(20),
     servercost money)
go

--3,房间类别列表(roomtypelist,'1'为豪华房,'2'为单人高级房,'3'为双人高级房)
create table roomtypelist
    (roomtypeid int primary key,
     roomtype char(20) not null,
     roomcost money not null)
go

--4,顾客表(guest)
create table guest
    (guestid int primary key IDENTITY(10001,1),
     guestname char(10) not null,
     guestcardno char(20) not null,
     guestsex char(2),
     guestphone char(30))
go

--5,服务使用表(serveruse)
--create table 表名(字段名 类型 primary key,字段名 类型 foreign key(外键名)
-- references 表名,
--字段名 类型 foreign key(外键名) references 表名,.....)
create table serveruse
     (serveruseid int primary key IDENTITY(1000,1),
      guestid int  foreign key (guestid) references guest,
      serverid char(6) foreign key (serverid) references serverlist,
      servertime datetime)
go

--6,客房使用单(guestroom)
create table guestroom
     (guestroomid int primary key identity(100,1),--自动编号
      roomid char(6) foreign key(roomid) references roomlist,
      guestid int  foreign key(guestid) references guest,
      indate datetime,
      outdate datetime)
go

--7,服务撤消单,结构和服务使用表相同
create table serverabort
     (abortid int primary key IDENTITY(1000,1),
      guestid int  foreign key(guestid) references guest,
      serverid char(6) foreign key(serverid) references serverlist,
      aborttime datetime)
go

--8,结帐(checkmoney),用于存放顾客结帐总金额和结帐时间
create table checkmoney
     (checkid int primary key IDENTITY(1000,1),
      guestid int  foreign key(guestid) references guest,
      totalcost money,
      intime datetime)
go
------------------------------------------------------------------

--完整性问题

--确保所有主键,外键的关系准确性
alter table roomlist add foreign key(roomtypeid) references roomtypelist(roomtypeid)
go

--创建缺省约束 create default 约束名 as 常量

--每个房间的类型的缺省值是豪华房(roomtype为1表示豪华房)
create default defroomtype as 1
go
sp_bindefault defroomtype,'roomlist.roomtypeid' --注意'.'与','
go

--每个房的状态缺省值(roomstatus)是空的(用0表示)

create default defroomstatus as 0
go
sp_bindefault defroomstatus,'roomlist.roomstatus'
go

--创建检查约束 alter table 表名 add check (字段 范围)
--顾客性别必须是'男'或'女'

alter table guest add check (guestsex in('男','女'))
go

--顾客入住日期和享用服务日期的缺省值是当前日期
create default defdate as getdate()
go
sp_bindefault defdate,'guestroom.indate'
go
sp_bindefault defdate,'serveruse.servertime'
go

--服务撤消时间的缺省值为当前时间
create default deftime as getdate()
go
sp_bindefault deftime,'serverabort.aborttime'
go

--输入的服务费必须大于0
alter table serverlist add check(servercost>0)
go

--电话号码的存储格式是(999)999-99999999
--like 后面的字符串不能换行分开写,如何换行?
alter table guest
    add constraint chkphone
    check (guestphone is null or guestphone like
    '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
go     
------------------------------------------------------------------

--向基表插入记录(服务取消表暂不插入记录,在过程中可以测试‘取消服务’)

go
insert serverlist values('a01','洗车-小车',10)
insert serverlist values('a02','洗车-大车',20)
insert serverlist values('b01','洗衣-外衣',10)
go
select * from serverlist
go
insert roomtypelist values(1,'豪华房',688)
insert roomtypelist values(2,'单人高级房',188)
insert roomtypelist values(3,'双人高级房',288)
select * from roomtypelist
go
insert roomlist(roomid,roomtypeid) values('1001',1)
insert roomlist values('1002',1,0)
insert roomlist values('1003',1,0)
insert roomlist(roomid,roomtypeid) values('1004',3)
insert roomlist(roomid,roomtypeid) values('1005',2)
insert roomlist(roomid,roomtypeid) values('1006',2)
go
select * from roomlist
insert guest values('张生','1234567','男','(080)027-87654321')
insert guest values('崔莺','1234567','女','(080)027-87654321')
insert guest values('柳下惠','1234567','男','(080)027-87654321')
select * from guest
go
insert serveruse(guestid,serverid) values(10002,'a01')
insert serveruse(guestid,serverid) values(10002,'a02')
insert serveruse(guestid,serverid) values(10001,'b01')
insert serveruse(guestid,serverid) values(10001,'a01')
select * from serveruse
go
insert guestroom(roomid,guestid) values('1001',10001)
insert guestroom(roomid,guestid) values('1002',10002)
insert guestroom(roomid,guestid,indate) values('1003',10003,'2004-6-20')
go
update roomlist set roomstatus=1
where roomid in (select roomid from guestroom)
select * from guestroom
go
insert serverabort(guestid,serverid) values(10001,'a01')
select * from serverabort
go
--测试记录插入完毕
------------------------------------------------------------------

--设计相应的视图
--房间视图(包括个房间的号码、类型、价格及房间状态)
create view viewroom
as
select a.roomid,b.roomtype,b.roomcost,房间状态=
(case a.roomstatus when 1 then '已预定'
 else '空' end)
from roomlist a
join roomtypelist b on a.roomtypeid=b.roomtypeid
go
select * from viewroom
go
--顾客视图(包括顾客的房间号码、顾客编号、顾客姓名及基本资料)
create view viewguest
as
select a.roomid,b.guestid,b.guestname,b.guestsex,b.guestphone
from guestroom a
join guest b on a.guestid=b.guestid
go
select * from viewguest
select * from guest
go
------------------------------------------------------------------

--在相应的键上创建索引

--create nonclustered index 索引名 on 表名(字段名)  '非聚集索引'
--create unique index 索引名 on 表名(字段名)    '唯一索引'
--create clustered index 索引名 on 表名 (字段名)    '聚集索引'
--查看索引 sp_helpindex 表名
--删除索引 dorp index 表名.索引名 

--在顾客表中对顾客姓名创建非聚集索引
create nonclustered index indguestname on guest(guestname)
go
------------------------------------------------------------------

--创建存储过程

--1,占用房间数,roomstatus=1的房间表示被占用.
create proc roomuse @temp int output
as
select @temp=count(*) from roomlist where roomstatus=1
go
declare @count int
execute roomuse @count output
select @count as '当前占用房间数'
go

--2,空余房间数,roomstatus=0的房间表示空余.
create proc roomisvoid @roomcount int output
as
select @roomcount=count(*) from roomlist where roomstatus=0
go
declare @count int
execute roomisvoid @count output
select @count as '当前空余房间数'
go

--3,空房间列表,roomstatus=0的房间表示空余.
create proc roomvoidlist
as
select roomid as '空房间号码'  from roomlist where roomstatus=0 
order by roomid
go
exec roomvoidlist
go

--4,空双人房列表,这里规定roomstatus=0时为空缺状态. 
create proc roomdoublevoid
as
select roomid,'空双人房' from roomlist where roomtypeid in(
select roomtypeid from roomtypelist where roomtype='双人高级房' )
and roomstatus=0
       order by roomid
go
exec roomdoublevoid
go

--5,空单人房列表
create proc roomonevoid
as
select roomid,'空单人房' from roomlist
where roomtypeid in(
select roomtypeid from roomtypelist where roomtype='单人高级房'
 and roomstatus=0) order by roomid
go
exec roomonevoid
go

--6,服务列表（procserverlist）
--表名与 过程名不能相同
create proc procserverlist
as
select '服务名称'=servername,'服务价格'=servercost
from serverlist order by serverid
go
exec procserverlist
go
--7,根据顾客名字查看他的详细信息(包括房间号)
/*alter proc guestinfobyname @guestname char(10)
as
select * from guest where guestname=@guestname
union
select * from guestroom where guestid in
(select guestid from guest where guest=@guestname)
*/
create proc guestinfobyname @guestname char(10)
as
select b.roomid ,a.* from guest a join guestroom b
on a.guestid=b.guestid
where a.guestname=@guestname
go
exec guestinfobyname '张生'
go

--8,根据房间号可查看相应顾客的信息
create proc guestinfobyroom @roomid char(6)
as
select * from guest where guestid in
(select guestid from guestroom where roomid=@roomid)
go
exec guestinfobyroom '1001'
go

--9,房间的价格(根据输入的房间号或房间类型)
create proc roomcost @roomid char(6)='%',@roomtype char(20)='%'
as
select a.roomid,房间状态=
(case a.roomstatus when 1 then '已预定'
 else '空' end),b.roomtype,b.roomcost
from roomlist a
join roomtypelist b on a.roomtypeid=b.roomtypeid
where a.roomid=@roomid or b.roomtype=@roomtype
go
exec roomcost '1001',''
exec roomcost '','豪华房'
go

--10,能计算顾客的结帐费用 
create proc totalcost @guestid int ,@total money output
as
  declare @servercost money,@roomcost money
  select @servercost=sum(servercost)
  from serverlist
  where serverid in
    (select serverid
     from serveruse
     where guestid=@guestid)
  select @roomcost=roomtypelist.roomcost
  from roomtypelist
  where roomtypeid in
     (select roomtypeid
      from roomlist
      where roomlist.roomid in    --还需一个子查询
     (select roomid
      from guestroom
      where guestid=@guestid))
  select @roomcost=@roomcost*(select datediff(day,indate,outdate)
  from guestroom
  where guestid=@guestid)
  select @total=@servercost+@roomcost
go
  declare @servercost money,@roomcost money
  exec totalcost 10001,@servercost output
  select '结帐金额'=(case @servercost when null then '0' end)
go

--11,基于顾客号能显示特定日期的顾客消费的服务列表及应付金额
create proc serverlistbyguestid @guestid int ,@time datetime      --统计金额
as
  select serverid,'应付金额'=sum(servercost)
  from serverlist
  where serverid in
     (select serverid
      from serveruse
      where guestid=@guestid and servertime=@time)
  group by serverid
go
  exec serverlistbyguestid 10003,'2004-6-24'

go

--12,统计当前的在住顾客数(利用客人入住时,outdate为空表示客人在住)
create proc guestnumber
as
   select count(*)as guestinroom from guestroom where outdate is null
go
   exec guestnumber
go

--13,基于房号显示客人享用的服务列表
create proc serverbyroom @roomid char(6)
as
begin
  if (select count(*) from guestroom where roomid=@roomid)<>0
     select a.serverid,a.servername,a.servercost from serverlist a
         where serverid in
              (select serverid from serveruse where guestid in
                 (select guestid from guestroom where roomid=@roomid ))
  else
     select  '无效房号或者没被占用的房号'
end 
go
  exec serverbyroom '1001'
  exec serverbyroom '1002'
--输入无效房号或者没被占用的房号
  exec serverbyroom '1003'
go

--14,删除顾客所享用的某项服务--是否应该带上消费日期-- 
create proc deleserveruse @guestname char(6),@servername char(20)
as
  delete serveruse
  where guestid =
         (select guestid from viewguest where guestname=@guestname)
  and serverid =
         (select serverid from serverlist where servername=@servername)
go
  exec deleserveruse '张生','洗车-小车'
  select * from serveruse
go

--15,随时查询顾客的应付金额
create proc guestcost @guestid int ,@total money output
as
  declare @servercost money,@roomcost money
  select @servercost=sum(servercost)
  from serverlist
  where serverid in
      (select serverid
       from serveruse
       where guestid=@guestid)
  select @roomcost=roomtypelist.roomcost
  from roomtypelist
  where roomtypeid in
      (select roomtypeid
       from roomlist
       where roomlist.roomid in    --用上面的结算
          (select roomid
           from guestroom
           where guestid=@guestid))
  select @roomcost=@roomcost*
    (select datediff(day,(select indate
                          from guestroom
                          where guestid=@guestid),getdate())
     from guestroom
     where guestid=@guestid)
  select @total=@servercost+@roomcost
go

--16,服务项目可以追加
create proc addserverlist @serverid char(6),@servername char(20),@servercost money
as
  insert into serverlist values(@serverid,@servername,@servercost)
go
  exec addserverlist 'c02','苹果汁',12
  select * from serverlist
go

--17,房屋类型可能增加
create proc addroomtype @roomtypeid int,@roomtype char(20),@roomcost money
as
  insert into roomtypelist values(@roomtypeid,@roomtype,@roomcost)
go
  exec addroomtype 4,'普通客房',108
  select * from roomtypelist
go

--18,登记客人资料    掉了'卡号'字段  卡号字段没缺省值
create proc addguest
@guestcardno char(20),
@guestname char(10),
@guestsex char(2),
@guestphone char(20)
as
  insert into guest values(@guestcardno,@guestname,@guestsex,@guestphone)
go
  exec addguest '我','210112197909094035','男','(080)027-12345678'
  select * from guest
go

--19,登记客房使用单 
create proc addroomuse @roomid char(6),@guestid int
as
  insert into guestroom(roomid,guestid) values(@roomid,@guestid)
go
  exec addroomuse '1004',10002
  select * from guestroom
go
---------------------------------------------------------------------------

--触发器  
--1,顾客登记就应该有触发器，向事务表中添加顾客记录(default)
create trigger trgaddguest on guest
for insert
as
   declare @guestid int
   select @guestid=guestid from inserted
   insert checkmoney(guestid) values(@guestid)
go
--2,一旦顾客享用任何服务该事务必须记入事务表并更新服务费
create trigger trgaddserveruse on serveruse
for insert
as
  declare @servercost money
  select servercost from serverlist where serverid =(select serverid from inserted)
--计算现在顾客的花费
  update checkmoney set totalcost= totalcost+ @servercost where guestid=(select guestid from inserted)
go
 select * from serverlist
go
--3,在结帐那一天,必须计算机顾客的房费,及总费用并将房间的出租状态更新为空
--客人退房时,客房使用单的要更新outtime
create trigger trgguestout on guestroom
for delete
as
  declare @servercost money,@roomcost money,@total money,@guestid int
--得到顾客号码
  select @guestid=guestid from deleted
--计算顾客的其他服务总共费用
  select @servercost=sum(servercost)
  from serverlist
  where serverid in
        (select serverid from serveruse where guestid=@guestid)
--计算顾客的房间费用
  select @roomcost=roomtypelist.roomcost
  from roomtypelist
  where roomtypeid in
      (select roomtypeid from roomlist
       where roomlist.roomid in   
           (select roomid from guestroom where guestid=@guestid))
  select @roomcost=@roomcost*(select datediff(day,indate,outdate)
  from guestroom where guestid=@guestid)
--更新顾客结帐的总共费用
  select @total=@servercost+@roomcost
  select @total
--更新房间的状态为空（0）
  update roomlist set roomstatus=0
  where roomid in(select roomid from deleted)
go

--4,如果消费被取消,那消费金额应自动减少
create trigger trgserverabort on serverabort
for insert
as
  declare @servercost money
  select servercost from serverlist where serverid =(select serverid from inserted)
--更新现在顾客的花费
  update checkmoney set totalcost= totalcost- @servercost
  where guestid=(select guestid from deleted)
go