hoteldb.sql

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

--调用DOS命令创建文件夹
exec xp_cmdshell 'mkdir d:\hotel\database'

go

--建数据库hotelDB

------检查是否存在bbsDB数据库-----
if exists(select * from sysdatabases where name = 'hotelDB')
drop database hotelDB


create database hotelDB
on
(
name='hotelDB_data',
filename='D:\酒店管理系统V1.0方案\Hotel\Hotel\database\HotelDB_data.mdf'
)
log on
(
name='hotelDB_log',
filename='D:\酒店管理系统V1.0方案\Hotel\Hotel\database\HotelDB_log.ldf'
)

go


---1.建立顾客表	Buyer

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

drop table Buyer
go

------建表-------						-
use hotelDB

go
-------------------------顾客表----------------
create table Buyer
(
BuyerID	int identity(600880001,1),
BName	Varchar(8) Not null,	   
Birthday dateTime,	   
certificateID	int Not null,	   
BCarNum	Char(18) Not null,	   
BSex Char(2) not null,   
Phone	char(11) null,   
Company	Varchar(50),	
NationalityID int not null,
ProvinceID int not null,   
Address	Varchar(100) not null,   
Blacklist bit,
Remark	Varchar(200),
constraint pk_BuyerID primary key(BuyerID)
)
		 
go
--------2-------------------国籍表--------------------------

if exists (select * from sysobjects where name = 'Nationality')

drop table Nationality
GO
create table Nationality
(
NationalityID int not null,
NationalityName varchar(30) not null,
EnglishName varchar(30) not null,
DomainName char(2) null,
constraint PK_Nationality_NationalityID primary key(NationalityID)
)
GO

----------3------------------------省份表-----------------------

if exists (select * from sysobjects where name = 'province')

drop table province
GO
create table province
(
provinceID int identity(1,1),
provinceName varchar(30) not null,
constraint PK_province_ID primary key(provinceID)
)

GO

---4.建立客房类型表  RoomType

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

drop table RoomType

go

use hotelDB

go
create table RoomType
(
 
TypeNum int identity(1,1),	   
RoomType Varchar(10)	Not null,
PriceDay Numeric(20,2)	Not null,	   
halfDay	Numeric(20,2)	Not null,	   
PriceHour Numeric(20,2)	Not null,	   
Deposit	Numeric(20,2),   
BedConut int	Not null ,
constraint pk_TypeNum primary key(TypeNum)
)
		 
go


---5.建立客房表	GuestRoom

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

drop table GuestRoom

go

use hotelDB

go

create table GuestRoom
(
GuestRoomID Varchar(10) not null,   
TypeNum	int not null,   
Area Varchar(50),		   
RoomPhone int,		   
State Char(5) Not null,   
MorningCall dateTime,	   
Secrecy	bit,   
Remark	Varchar(200),
constraint pk_GuestRoomID primary key(GuestRoomID)		 
)
		 
go
--6 新增==预订房表 Destine
if exists (select * from sysobjects where name = 'Destine')

drop table Destine
GO
create table Destine
(
DestineID int identity(1000,1) not null,
--GuestRoomID Varchar(10) not null,
Dname Varchar(8) not null,
CarNum Char(18) not null,
Sex Char(2) not null,
phone Char(11) not null,
BeginTime dateTime not null,
SaveTime int null,
ToTime dateTime null,
Remark Varchar(200) null,
constraint PK_DestineID primary key(DestineID)
)
GO
----------7----------新增  主客订有客房表--------------------------
if exists (select * from sysobjects where name = 'DestineRoomList')

drop table DestineRoomList
GO
create table DestineRoomList
(
RoomListID int identity(1,1),
ListID int not null,
GuestRoomID varchar(10) not null
)

GO
-------8------------新增--------换房记录表---------------------------
if exists (select * from sysobjects where name = 'ExchangeNode')

drop table ExchangeNode
GO
create table ExchangeNode
(
ExchangeID int identity(1,1),
BuyerID int not null,
RoomID varchar(10) not null,
NewRoomID varchar(10) not null,
ChangeDate dateTime not null,
UserID char(6) not null,
whys varchar(50) null,
)


GO

GO
---9.建立客户开房表    RoomUse

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

drop table RoomUse

go

use hotelDB

go

create table RoomUse
(
RoomUseID int identity(880011001,1) Not null, 
BuyerID	int not null,	   
GuestRoomID Varchar(10) Not null,  
ArriveTime dateTime Not null,	
EndTime	dateTime,
CountMode Char(4),
charge	Numeric(20,2),	
OtherExpenses numeric(20,2),
InForegift numeric(20,2),
OutForegift numeric(20,2),
BNum int,	
ConsumeID int null,
Rebate float not null,
OpenUser char(6) not null,
FillTime dateTime not null,
Endcharge numeric(20,2) not null,
State varchar(8) not null,
UserID	Char(6)	Not null, 
ReckoningTime dateTime null,
Remark varchar(200) null
)
	 
go

--10新增 员工类型表EmployeeType

if exists (select * from sysobjects where name = 'EmployeeType')

drop table EmployeeType
GO
create table EmployeeType
(
TypeID int identity(1,1),
Type varchar(10) not null,
constraint PK_EmployeeType_TypeID primary key(TypeID)
)



---11.建立员工表Employee

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

drop table Employee

go

use hotelDB

go

create table Employee
(
 
EmployeeID Char(6) Not null,	 
EmployeeName Char(8) Not null,	   
Sex Char(2) Not null,   
Age int not null,   
schoolAge Varchar(10),   
EImage	image	null,
TypeID int not null,
State	Varchar(8) Not null,
Remark	Varchar(200)	 
)

go


---12.建立用户表  UserInfo 

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

drop table UserInfo

go

use hotelDB

go

create table UserInfo
( 
UserID	Char(6)	Not null,   
EmployeeID	Char(6)	Not null,	   
Password	Char(6)	Not null,	   
State	Char(4)	Not null,
frontManage	bit,  
financingManage	bit,  
SystemManage	bit,  
PriceManage	bit,  
InfoManage	bit,
constraint pk_UserID primary key(UserID)
)
     
go

---13.建立商品类型表 ItemsType