jiudian.sql

用vb编的一个酒店客房管理系统

use master
go

if exists (select name from dbo.sysdatabases where name='dbJiuDian' )
	drop database dbJiuDian
go

--创建数据库
create database   dbJiuDian
on  primary
	(	name=dbJiuDian,
		filename='f:\jiudian\dbJiuDian.mdf',
		size=100mb,
		filegrowth=20
	)
log on
	(	name=logJiuDian,
		filename='f:\jiudian\logJiuDian.ldf',
		size=100mb,
		filegrowth=20
	)
go

use dbJiuDian
go

--创建‘房间价格’表
create table FangJianJiaGe
	( leiBie varchar(20)  constraint pk_FJJG_leiBie  primary key,
	  jiaGe int not null
	)
go

--创建‘房间’表
create table FangJian
	(haoMa  varchar(8) 
		constraint pk_FJ_haoMa primary key   clustered,
	 leiBie  varchar(20)
		constraint fk_FJ_leiBie  foreign key  references  FangJianJiaGe(leiBie)
						on delete cascade     on update cascade
	)
go

--创建‘职位工资’表
create table ZhiWeiGongZi
	(  zhiWei  varchar(20) constraint pk_ZWGZ_zhiWei primary key clustered,
	   jiBenGongZi  int  constraint ck_ZWGZ_jiBenGongZi check(jiBenGongZi>=500) not null
	)
go


--创建‘员工信息’表
create table YuanGongXinXi
	(  bianHao  varchar(10) constraint pk_YGXX_bianHao  primary key clustered,
	   xingMing varchar(20) not null, 
	   xingBie  varchar(10)  null constraint  ck_YGXX_xingBie check( xingBie in ('男','女') ),
	   nianLing smallint  constraint ck_YGXX_nianLing check (nianLing>18 and nianLing<70),
  	   zhiWei  varchar(20)  constraint fk_YGXX_zhiWei foreign key references ZhiWeiGongZi(zhiWei) 
						on delete cascade    on update cascade,
	   lianXiDianHua varchar(15) null,
	   dianZiYouJian varchar(30) null,
	   miMa  varchar(30) default('123456') 
	)
go 



--创建‘顾客’表
create table GuKe
	(  bianHao  int identity(1,1) constraint pk_GK_bianHao primary key clustered,
	   zhengJIanLeiBie  varchar(30),
	   zhengJianHaoMa  varchar(30),
	   xingMing  varchar(20),
           xingBie  varchar(20)  constraint ck_GK_xingBie check(xingBie in('男','女'))
	)
go	 

--创建‘销售记录’表
create table XiaoShouJiLu
	(  bianHao int identity(1,1) constraint pk_XSJL_bianHao primary key clustered,
	   fangJianHao varchar(8) constraint fk_XSJL_fangJianHao not null foreign key references FangJian(haoMa)
								on delete cascade on update cascade,
	   guKeBianHao int  not null constraint fk_XSJL_guKeBianHao foreign key references GuKe(bianHao)
							on delete cascade on update cascade,
	   kaiShiShiJian  smalldatetime not null,
	   tuiFanfShiJian  smalldatetime default getdate(),
	   xiaoFeiJinE  money not null
	)
go


--创建‘开房预定房间’表
create table KaiFangYuDingFangJian 
	(  fangJianHao varchar(8) constraint fk_KFYDFJ_fangJianHao references FangJian(haoMa)
						on delete cascade on update cascade,
	   guKeBianHao int constraint fk_KFYDFJ_guKeBianHao references GuKe(bianHao)
						on delete cascade on update cascade,
	   --开房为0，预定房间为1
	   kaiFangYuDingFangJian  smallint  constraint ck_KFYDFJ_kaiFangYuDingFangJian check(kaiFangYuDingFangJian in(0,1)),
	   kaiShiShiJian smalldatetime default getdate(),
	   yaJin  money null,
	   constraint pk_KFYDFJ primary key(fangJianHao,guKeBianHao)
	)
go

--创建触发器，当退房向‘销售记录’表中插入记录后，删除‘开房预定房间’表中的相应记录

use dbJiuDian
go

create trigger tg_XSJL_insert	on XiaoShouJiLu  after insert
	as delete from KaiFangYuDingFangJian where fangJianHao in		
			(	select p1.fangJianHao from KaiFangYuDingFangJian as p1,XiaoShouJiLu as p2
				where p1.fangJianHao=p2.fangJianHao 
				and p1.guKeBianHao=p2.guKeBianHao
			)
go

use dbJiuDian
go

--向‘房间价格’表中插入值
insert into FangJianJiaGe  values('标准单人间',50)
insert into FangJianJiaGe  values('标准双人间',80)
insert into FangJianJiaGe  values('豪华单人间',150)
insert into FangJianJiaGe  values('豪华双人间',160)
go

--向‘房间’表中插入值
insert into FangJian  values('A101','标准单人间')
insert into FangJian  values('A103','标准单人间')
insert into FangJian  values('A102','标准双人间')
insert into FangJian  values('A104','标准双人间')
insert into FangJian  values('B101','豪华单人间')
insert into FangJian  values('B103','豪华单人间')
insert into FangJian  values('B102','豪华双人间')
go

--向‘职位工资’表中插入值
insert into ZhiWeiGongZi values('前台',800)
insert into ZhiWeiGongZi values('人事处',800)
insert into ZhiWeiGongZi values('物资处',800)
insert into ZhiWeiGongZi values('财务处',800)
insert into ZhiWeiGongZi values('经理',1500)
go


--向‘员工信息’表中插入值
insert into YuanGongXinXi values('001','张三','男',25,'前台','88161234','',default)
insert into YuanGongXinXi values('002','李四','女',35,'前台','88162345','',default)
insert into YuanGongXinXi values('003','王五','男',45,'人事处','88163456','',default)
insert into YuanGongXinXi values('004','赵六','男',27,'物资处','88164567','',default)
insert into YuanGongXinXi values('005','孙七','女',29,'财务处','88165678','','005')
insert into YuanGongXinXi values('006','乾八','男',48,'经理','13212345678','[e-mail]',default)
go

--向‘顾客’表中插入值
insert into GuKe values('身份证','61412345678901234','张三','男')
insert into GuKe values('学生证','04011234','李四','女')
go

--向‘开预定房间’表中插入值
insert into KaiFangYuDingFangJian  values('A101',1,1,default,cast('' as money))
insert into KaiFangYuDingFangJian  values('B101',2,0,default,cast('' as money))
go