GO
exec sp_dboption N'碟片出租系统', N'auto update statistics', N'true' GO
use [碟片出租系统] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dp_zj]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger [dbo].[dp_zj] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_资金记录表_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[insert_资金记录表_1] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[update_会员用户_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[update_会员用户_1] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[一般租借资金流量表]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[一般租借资金流量表] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[会员资金流量]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[会员资金流量] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[一般客户]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[一般客户] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[会员用户]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[会员用户] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[会员资金记录表]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[会员资金记录表] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[借还过程表]') OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[借还过程表] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[操作员管理表]') OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[操作员管理表] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[碟片管理表]') OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[碟片管理表] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[资金记录表]') OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[资金记录表] GO
CREATE TABLE [dbo].[一般客户] ( [编号] [int] IDENTITY (1, 1) NOT NULL , [办理时间] [datetime] NOT NULL , [经办人] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[会员用户] ( [编号] [int] NOT NULL , [名字] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [证件号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [密码] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [办理时间] [datetime] NOT NULL , [经办人] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [剩余碟数] [int] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[会员资金记录表] ( [编号] [int] IDENTITY (1, 1) NOT NULL ,
and
and
and
and
[收入] [int] NOT NULL , [办理时间] [datetime] NOT NULL , [经办人] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[借还过程表] ( [自动编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [碟片号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [客户号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [租借时间] [datetime] NOT NULL , [归还时间] [datetime] NOT NULL , [租碟操作员] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [还碟操作员] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [会员验证] [int] NOT NULL , [备注] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[操作员管理表] ( [编号] [int] NOT NULL , [姓名] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [性别] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL , [身份] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL , [密码] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [工作时间] [datetime] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[碟片管理表] ( [编号] [int] NOT NULL , [名称] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [类别] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [国家] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [主演] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [单价] [int] NOT NULL , [库存数量] [int] NOT NULL , [购买时间] [datetime] NOT NULL , [办理人] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[资金记录表] ( [编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[客户号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [碟片编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [压金] [money] NOT NULL , [收入] [money] NOT NULL , [支出] [money] NOT NULL , [余额] [money] NOT NULL , [操作] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL , [交易时间] [datetime] NOT NULL , [操作员号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [备注] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[一般客户] WITH NOCHECK ADD CONSTRAINT [PK_一般客户] PRIMARY KEY CLUSTERED ( [编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[会员用户] WITH NOCHECK ADD CONSTRAINT [PK_会员用户] PRIMARY KEY CLUSTERED ( [编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[会员资金记录表] WITH NOCHECK ADD CONSTRAINT [PK_会员资金记录表] PRIMARY KEY CLUSTERED ( [编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[借还过程表] WITH NOCHECK ADD CONSTRAINT [PK_借还过程表] PRIMARY KEY CLUSTERED ( [自动编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[碟片管理表] WITH NOCHECK ADD CONSTRAINT [PK_碟片] PRIMARY KEY CLUSTERED (
[编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[资金记录表] WITH NOCHECK ADD CONSTRAINT [PK_资金记录表] PRIMARY KEY CLUSTERED ( [编号] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[借还过程表] WITH NOCHECK ADD CONSTRAINT [CK_会员验证] CHECK ([会员验证] = '0' or [会员验证] = '1') GO
ALTER TABLE [dbo].[操作员管理表] WITH NOCHECK ADD CONSTRAINT [CK_操作员身份] CHECK ([身份] = '员工' or [身份] = '店长'), CONSTRAINT [CK_员工性别] CHECK ([性别] = '男' or [性别] = '女') GO
ALTER TABLE [dbo].[碟片管理表] WITH NOCHECK ADD CONSTRAINT [DF_碟片管理表_编号] DEFAULT (0) FOR [编号] GO
ALTER TABLE [dbo].[资金记录表] WITH NOCHECK ADD CONSTRAINT [CK_资金记录表] CHECK ([操作] = '借' or [操作] = '还') GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_NULLS ON GO
CREATE VIEW dbo.资金流量表 AS
SELECT 编号, 收入, 支出, 操作员号, 备注, 日期 FROM dbo.资金记录表 GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO