一個簡單的酒店系統的資料庫設計

    以前學習SQL SERVER時的課程設計，下午整理資料時無意中居然找到，雖然都好幾年了，但發現用來學習SQL SERVER還是不錯的。
    現在都用PowerDesigner了，以前這些代碼可都是一行一行敲出來的，只是沒有加欄位說明，不記得當初有沒有資料庫設計說明文檔。

----------------------------開始-----------------------------

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

--建立資料庫
create database myhotel--Keen畢業設計
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 'Keen','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
----------------------------Keen畢業設計-------------------------------------------------

--觸發器  
--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
------------------------------------------------------------------
----------------------------調試結束-------------------------------
------------------------------------------------------------------
 (本文為本人畢業設計，如有轉載或者引用，請表明出處。)