dbhotel.sql

宾馆管理系统

use master
go

if exists (select * from sysdatabases where name = 'DBHotel') 
begin
drop database DBHotel
end
go

create database DBHotel
go

use DBHotel
go

-----创建登陆表

create table tbLogin
(
  cName	     Char(15)   not null,
  cPassword  Char(10)   not null,
  cPosition  Char(8)    not null
)

go
insert tbLogin values('1','1','管理用户')
go

---------创建客户管理表（预订与接待）
create table tbSK_Book
(
cAccountId	Char(6)     not null,
cRoomStory      Char(8)     not null,
cRoomNo	        Char(10)    not null,
cClientName	Char(15)    not null,
cSex 	        Char(2)     not null,
cZJ	        Char(10)    not null,
cZJ_No	        Char(20)    not null,
dRoom_In	datetime    not null,
dRoom_Out 	datetime    not null,
cRoomType       char(10)    not null,
vMessage        varchar(50)     
)
go

insert tbSk_Book values('00001','第一层','1','将接风','男','身份证','123456','2005-5-19','2005-5-30','占用','激发解放；咔飞机')
insert tbSk_Book values('00002','第一层','4','Mdeia','女','身份证','7894562','2005-5-19','2005-5-30','占用','激发解放；叽飞机豆腐')
insert tbSk_Book values('00003','第一层','7','法考福','男','身份证','123456','2005-5-29','2005-5-30','预定','激发解放；咔叽飞机反抗酱豆腐')
insert tbSk_Book values('00004','第二层','13','维多利亚','女','身份证','456870','2005-5-24','2005-5-30','占用','天天旧反对可是反抗')
insert tbSk_Book values('00002','第二层','15','贝克汉姆','男','身份证','7894562','2005-5-29','2005-5-30','预定','激发解放；叽飞机豆腐')
---------------------------------------
go

-------------创建房态设置表（只可停用房间）
create table tbRoomStatus
(
cRoomStory char(8) not null,
cRoomNo char(4) not null,
cRoomType char(15) not null
)
go
insert tbRoomStatus values('第一层','8','停用')
insert tbRoomStatus values('第一层','10','停用')
insert tbRoomStatus values('第一层','12','停用')

go
-------------创建房态表（可显示预订，接待，停用状态）
create table tbNewRoomStatus
(
 cRoomStory char(8) not null,
 cRoomNo char(4) not null,
 cRoomType char(15) not null
 )
go
-------------创建员工表

create table tbEmployee
(
cE_Id	Char(8)  not null,
cE_Name	Char(10) not null,
cE_Sex	Char(2)  not null,
vE_Address	Varchar(25) not null,
cE_Position	Char(8)  not null,
mE_Pay	 Money   not null
)
go

insert tbEmployee values('0001','私德','男','成都新都区','服务员',600)
insert tbEmployee values('0002','张凤','女','四川资阳安岳','收银员',800)
insert tbEmployee values('0003','和娲','女','四川乐只','经理',1500)

go
------------创建房价设置表
create table tbRoomPrice
(
cRoomStory char(15) not null,
mRoomPrice money 
)
go
insert tbRoomPrice values('第一层',100)
go
insert tbRoomPrice values('第二层',200)
go
insert tbRoomPrice values('第三层',300)
go
insert tbRoomPrice values('第四层',400)
go
-------------创建商品管理表
create table tbShopping
(
cShoppingId char(8) not null,
cFoodName  char(15) not null,
cFoodType  char(15) not null,
mFoodPrice money    not null,
iNumber    int   not null
)
go
insert tbShopping values('0001','可口可乐','饮料',2,100)
insert tbShopping values('0002','鸡腿','肉食',6,100)
insert tbShopping values('0003','茅台','酒类',98,100)
go
------------创建帐务表

create table tbAccount
(
cRoomNo	        Char(6)    not null,
cClientName	Char(15)   not null,
mRoomPrice      money      not null,
iDay            int     not null,
mEatCost	Money          null,
mtotalcost	Money      not null,
mCheckOut       money      not null
)

go
------------创建餐饮消费表
create table tbEat
(
cRoomNo     char(8)  not null,
cClientName Char(15) not null,
cFoodName   char(15) not null,
cFoodType   char(15) not null,
mFoodPrice  money    not null,
iNumber     int          null
)
go