hoteldb.sql

酒店管理系统,您酒店物业管理的好帮手

------检查表是否存在-------
if exists (select * from sysobjects where name = 'ItemsType')

drop table ItemsType

go

use hotelDB

go

create table ItemsType
( 
ItemsTypeID int identity(1,1),	   
TypeName Varchar(20)			 
)

go


---14.建立商品项目价格表 ItemsPrice

------检查表是否存在-------
if exists (select * from sysobjects where name = 'ItemsPrice')

drop table ItemsPrice

go

use hotelDB

go

create table ItemsPrice
( 
ItemID int	identity(100,1)	Not null,   
ItemName Varchar(20)	Not null,	   
ItemsTypeID	int	Not null, 
Price	Numeric(20,2)	Not null,	   
Remark	Varchar(200) 
)

go


---15.建立消费清单表(帐目)	Account表

------检查表是否存在-------
if exists (select * from sysobjects where name = 'Account')

drop table Account

go

use hotelDB

go

create table Account
( 
AccountID int idetity(1,1)	Not null,	   
RoomUseID	int	Not null,   
ItemID	int	Not null,
ANumber	int	Not null,   
Consume	Numeric(20,2)	Not null,	   
Rebate	int,
EndPrice Numeric(20,2),	   
Remark	Varchar(200)	 
)

go








---17.建立会员卡associator表

------检查表是否存在-------
if exists (select * from sysobjects where name = 'associator')

drop table associator

go

use hotelDB

go

create table associator
( 
CarID	int	identity(1,1),	   
CarType	Varchar(8)	Not null,   
Rebate	float	Not null,
amass	int,
Remark	Varchar(200)			 
)

go


---18.建立会员卡号   associatorInfo表

------检查表是否存在-------
if exists (select * from sysobjects where name = 'associatorInfo')

drop table associatorInfo

go

use hotelDB

go

create table associatorInfo
( 
CarNum	int	Identity(900088001,1),		   
CarID	int	Not null,		   
BuyerID	int	Not null,   
balance	Numeric(20,2),   
cumulate	Numeric(20,2),   
UseCount	int,   
Amass	int,   
BeginTime	datetime	Not null,	   
UserID	Char(6)	Not null,
State	Varchar(8) 
)

go

---19.建立操作日志表   JobInfo

------检查表是否存在-------
if exists (select * from sysobjects where name = 'JobInfo')

drop table JobInfo

go

use hotelDB

go

create table JobInfo
( 
JobID   int	identity(1,1),		   
UserID	Char(6)	Not null,	   
UseTime	datetime	Not null,	
Note varchar(30) not null, 	   
NoteMoney	Numeric(20,2)	Not null,	   			 
)

go


---20.建立交易信息表	businessInfo

------检查表是否存在-------
if exists (select * from sysobjects where name = 'businessInfo')

drop table businessInfo

go

use hotelDB
go
create table businessInfo
( 
businessID int identity(1,1),
Item varchar(20) not null, 	   
State   Varchar(4) Not null,	
businessMoney	Numeric(20,2) not null,   
UserID	Char(6)	Not null,		   
businessTime dateTime Not null		 
)

go
------21----------------房态表---------------------
if exists (select * from sysobjects where name = 'RoomState')

drop table RoomState
GO
create table RoomState
(
RoomID varchar(10) not null,
dt10 bit,
dt11 bit,
dt13 bit,
dt14 bit,
dt15 bit,
dt16 bit,
dt17 bit,
dt18 bit,
dt19 bit,
dt20 bit,
dt21 bit,
dt22 bit,
dt23 bit,
dt24 bit,
dt25 bit,
dt26 bit,
dt27 bit,
dt28 bit,
dt29 bit,
dt30 bit,
dt31 bit,
dt1 bit,
dt2 bit,
dt3 bit,
dt4 bit,
dt5 bit,
dt6 bit,
dt7 bit,
dt8 bit,
dt9 bit,
constraint PK_RoomSate_RoomID primary key(RoomID)
)

-----------------证件类型--------------------

create table certificate
(
certificateID int identity(1,1),
TypeName varchar(20) not null,
constraint PK_certificateID primary key(certificateID)
)


------------------消费方式----------------
create table Consume
(
ConsumeID int identity(1,1),
ConsumeType varchar(10) not null,
constraint PK_Consume_ConsumeID primary key(ConsumeID)
)


Create table remind 
(
RemindID int identity(1,1),
RemindTime DateTime ,
State char(4) ,
Remark varchar(200)
)
/*---------各个表的约束关系----------*/

---1.顾客表	Buyer--

alter table Buyer
      
      add constraint df_Blacklist default(0) for Blacklist

go

---5.建立客房表	GuestRoom

alter table GuestRoom

      add constraint fk_TypeNum foreign key(TypeNum) references RoomType(TypeNum)

alter table GuestRoom

      add constraint ck_RoomPhone check(RoomPhone like '[0-9][0-9][0-9][0-9][0-9]')

alter table GuestRoom

      add constraint df_MorningCall default(0) for MorningCall

alter table GuestRoom

      add constraint df_Secrecy default(0) for Secrecy

go

-------7---------预定有客房表---DestineRoomList---------

alter table DestineRoomList
add constraint FK_DestineRoomList_ListID foreign key(ListID) references Destine(DestineID)

alter table DestineRoomList
add constraint FK_DestineRoomList_GuestRoomID foreign key(GuestRoomID) references GuestRoom(GuestRoomID)


-------8-----------换房记录表-----------------------------------

alter table ExchangeNode
add constraint FK_ExchangeNode_BuyerID foreign key(BuyerID) references Buyer(BuyerID)

alter table ExchangeNode
add constraint FK_ExchangeNode_RoomID foreign key(RoomID) references GuestRoom(GuestRoomID)

alter table ExchangeNode
add constraint FK_ExchangeNode_NewRoomID foreign key(NewRoomID) references GuestRoom(GuestRoomID)

alter table ExchangeNode
add constraint FK_ExchangeNode_UserID foreign key(UserID) references UserInfo(UserID)








---9.客户订房表    RoomUse

alter table RoomUse