MemberCard varchar(20) NOT NULL , TotalCost money NOT NULL , RegDate datetime NOT NULL ) GO
/*创建商品信息表*/ CREATE TABLE MerchInfo (
MerchID int identity(1,1) Primary key , MerchName varchar(50) Unique NOT NULL , MerchPrice money NOT NULL , MerchNum int NOT NULL , CautionNum int NOT NULL , PlanNum int NOT NULL ,
BarCode varchar(20) Unique NOT NULL , SalesProPrice money NULL , SalesProDateS datetime NULL , SalesProDateE datetime NULL , AllowAbate int NOT NULL , AllowSale int NOT NULL , FactoryID int NOT NULL , ProvideID int NOT NULL ) GO
/*创建供应商表*/ CREATE TABLE Provide (
ProvideID varchar(10) Primary key , ProvideName varchar(50) NOT NULL , ProvideAddress varchar(250) NULL , ProvidePhone varchar(25) NULL ) GO
/*创建销售表*/ CREATE TABLE Sale (
SaleID int identity(1,1) Primary key , MerChID int NOT NULL , SaleDate datetime NOT NULL , SaleNum int NOT NULL, SalePrice money NOT NULL ) GO
/*创建入库表*/ CREATE TABLE Stock (
StockID int identity(1,1) Primary key , MerchID int NOT NULL , MerchNum int NOT NULL , MerchPrice money NULL , TotalPrice money NULL , PlanDate datetime NULL , StockDate datetime NULL, StockState int NOT NULL ) GO
/*创建用户表*/ CREATE TABLE User (
UserID varchar(10) Primary key , UserName varchar(25) NOT NULL , UserPW varchar(50) NOT NULL , UserStyle int NOT NULL , ) GO
/*----------创建表间约束----------*/
/*商品信息表中厂商编号、供应商编号分别与厂商表、供应商表之间的外键约束*/ ALTER TABLE MerchInfo ADD
CONSTRAINT [FK_MerchInfo_Factory] FOREIGN KEY (
[FactoryID]
) REFERENCES Factory ( ),
CONSTRAINT [FK_MerchInfo_Provide] FOREIGN KEY (
[ProvideID] [FactoryID]
) REFERENCES Provide ( ) GO
/*销售表中商品编号与商品信息表之间的外键约束*/ ALTER TABLE Sale ADD
CONSTRAINT [FK_Sale_MerchInfo] FOREIGN KEY (
[MerChID] [ProvideID]
) REFERENCES MerchInfo (
[MerchID]
) ON DELETE CASCADE GO
/*入库表中商品编号与商品信息表之间的外键约束*/ ALTER TABLE Stock ADD
CONSTRAINT [FK_Stock_MerchInfo] FOREIGN KEY (
[MerchID]
) REFERENCES MerchInfo (
[MerchID]
) ON DELETE CASCADE GO
/*----------创建索引----------*/
/*在交易表上建立一个以交易编号、交易日期为索引项的非聚集索引*/ CREATE nonclustered INDEX IX_Dealing ON Dealing(DealingID, DealingDate) GO
/*在商品信息表上建立一个以商品编号为索引项的非聚集索引*/ CREATE nonclustered INDEX IX_MerchInfo ON MerchInfo(MerchID) GO
/*在销售表上建立一个以销售编号、销售日期为索引项的非聚集索引*/ CREATE nonclustered INDEX IX_Sale ON Sale(SaleID, SaleDate) GO
/*在入库表上建立一个以入库编号、入库日期、商品编号为索引项的非聚集索引*/ CREATE nonclustered INDEX IX_Stock ON Stock(StockID, StockDate, MerchID) GO
/*----------创建视图----------*/ /*创建用于查询交易情况的视图*/ CREATE VIEW v_Dealing AS
SELECT DealingDate as 交易日期, UserName as 员工名称, MemberCard as 会员卡号, DealingPrice as 交易金额 FROM Dealing GO
/*创建用于查询进货计划的视图*/
CREATE VIEW v_PlanStock AS
SELECT Stock.StockID as SID,
MerchInfo.MerchName as 商品名称, MerchInfo.BarCode as 条形码, Factory.FactoryName as 厂商, Provide.ProvideName as 供货商, Stock.MerchNum as 计划进货数量, Stock.PlanDate as 计划进货日期 FROM Stock,MerchInfo,Provide,Factory Where Stock.MerchID = MerchInfo.MerchID and Provide.ProvideID=MerchInfo.ProvideID and Factory.FactoryID=MerchInfo.FactoryID and Stock.StockState=0 GO
/*创建用于查询销售明细记录的视图*/ CREATE VIEW v_Sale AS
SELECT MerchInfo.MerchName as 商品名称, MerchInfo.BarCode as 条形码, MerchInfo.MerchPrice as 商品价格, Sale.SalePrice as 销售价格, Sale.SaleNum as 销售数量, Sale.SaleDate as 销售日期 FROM Sale INNER JOIN
MerchInfo ON Sale.MerChID = MerchInfo.MerchID GO
/*创建用于查询入库情况的视图*/ CREATE VIEW v_Stock AS
SELECT MerchInfo.MerchName as 商品名称,
超市管理系统需求分析与详细设计
