超市管理系统+论文(一)毕业论文(5)

/*----------创建表间约束----------*/
/*商品信息表中厂商编号、供应商编号分别与厂商表、供应商表之间的外键约束*/
ALTER TABLE MerchInfo ADD
 CONSTRAINT [FK_MerchInfo_Factory] FOREIGN KEY
 (
  [FactoryID]
 ) REFERENCES Factory (
  [FactoryID]
 ),
 CONSTRAINT [FK_MerchInfo_Provide] FOREIGN KEY
 (
  [ProvideID]
 ) REFERENCES Provide (
  [ProvideID]
 )
GO
/*销售表中商品编号与商品信息表之间的外键约束*/
ALTER TABLE Sale ADD
 CONSTRAINT [FK_Sale_MerchInfo] FOREIGN KEY
 (
  [MerChID]
 ) 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
/*在入库表上建立一个以入库编号、入库日期、商品编号为索引项的非聚集索引*/ （转载自http://zw.ＮＳＥaＣ.com科教作文网）
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 （科教范文网 lw.nseaC.Com编辑发布）
GO
/*创建用于查询入库情况的视图*/
CREATE VIEW v_Stock
AS
SELECT MerchInfo.MerchName as 商品名称,
       MerchInfo.BarCode as 条形码,
       Facto