hotel.sql

酒店管理信息系统是一个由人、计算机和数据库组成的进行酒店经营管理的系统

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_work_dep]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Work_jiangcheng] DROP CONSTRAINT fk_work_dep
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_work_jiangcheng]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Work_jiangcheng] DROP CONSTRAINT fk_work_jiangcheng
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_worker_move]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Work_move] DROP CONSTRAINT fk_worker_move
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bar_income_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Bar_income_item]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Card]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Card]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Card_consume]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Card_consume]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Card_liver_paper]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Card_liver_paper]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Consumed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Consumed]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Dep_infor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Dep_infor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fun_client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Fun_client]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fun_engage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Fun_engage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fun_income_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Fun_income_item]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fun_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Fun_item]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Grade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Grade]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Grade_installation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Grade_installation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Guest_information]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Guest_information]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Installation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Installation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Live_paper]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Live_paper]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reserved]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reserved]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Room]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Room]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Room_grade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Room_grade]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Seat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Seat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Seat_engage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Seat_engage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supply_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Supply_item]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VIP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VIP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Work_jiangcheng]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work_jiangcheng]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Work_move]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work_move]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Worker_infor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Worker_infor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[engage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[engage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[engage_infor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[engage_infor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pbcatcol]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pbcatcol]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pbcatedt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pbcatedt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pbcatfmt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pbcatfmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pbcattbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pbcattbl]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pbcatvld]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pbcatvld]
GO

CREATE TABLE [dbo].[Bar_income_item] (
	[bar_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
         constraint PK_bar_item_id primary key ,
	[bar_item_number] [int] NOT NULL ,
	[bar_item_consumeincush_number] [int] NULL ,
	[bar_item_consumeincush_when] [datetime] NULL ,
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[bar_seat_id] [char] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Card] (
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
        constraint PK_card_id primary key ,
	[card_summoney] [money] NULL ,
	[card_leftmoney] [money] NULL ,
	[card_use] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[card_exist] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[card_number] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Card_consume] (
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
        constraint FK_card_id  foreign key(card_id) references card(card_id),
	[consume_item_name] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[consume_item_num] [int] NULL ,
	[consume_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[consume_item_price] [money] NULL ,
	[consume_item_time] [datetime] NULL ,

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Card_liver_paper] (
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
        constraint FK_card  foreign key(card_id) references card(card_id),
	[guest_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 


) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Consumed] (
	[consumed_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL primary key,
	[fun_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[fun_item_number] [int] NOT NULL ,
	[fun_client] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dep_infor] (
	[dep_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL primary key,
	[dep_name] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[dep_master_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[dep_master_name] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[dep_zhiwu] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ren_time] [datetime] NULL ,
	[jianli_time] [datetime] NULL ,
	[caizheng] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[shuoming] [char] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fun_client] (
	[client_name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL primary key,
	[fun_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
        foreign key(fun_item_id) references Fun_item(fun_item_id),
	[fun_item_number] [int] NOT NULL ,
	[consume_start] [datetime] NOT NULL ,
	[consume_end] [datetime] NULL ,
	[card_or_cash] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fun_engage] (
	[client_name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL primary key ,
	[fun_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[engage_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pre_money] [money] NOT NULL ,
	[fun_item_start] [datetime] NOT NULL ,
	[fun_item_end] [datetime] NOT NULL ,
	[card_or_cash] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[card_id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fun_income_item] (
	[fun_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
        constraint FK_fun_item_id foreign key(fun_item_id) references Fun_item(fun_item_id) ,
	[fun_item_consume_num] [int] NOT NULL ,
	[fun_item_consumeincush_num] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fun_item] (
	[fun_item_name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[fun_item_id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL primary key ,
	[fun_item_num] [int] NOT NULL ,
	[fun_item_price] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Grade] (
	[grade_id] [int] NOT NULL primary key ,
	[grade_name] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[grade_price] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Grade_installation] (
	[grade_id] [int] NOT NULL 
        constraint FK_grade_id foreign key(grade_id) references Grade(grade_id),
	[installation_id] [int] NOT NULL 
        constraint FK_installation_id foreign key(installation_id) references Installation(installation_id)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Guest_information] (
	[guest_id] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL primary key ,
	[vip_type] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[sum_money] [money] NULL ,
	[others] [char] (80) COLLATE Chinese_PRC_CI_AS NULL ,
	[guest_type] [char] (20) COLLATE Chinese_PRC_CI_AS NULL