超市管理信息系统
[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
/*在入库表上建立一个以入库编号、入库日期、商品编号为索引项的非聚集索引*/
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 商品名称,
       MerchInfo.BarCode as 条形码,
       Factory.FactoryName as 厂商,
       Provide.ProvideName as 供货商,
       Stock.MerchPrice as 入库价格,
       Stock.MerchNum as 入库数量,
       Stock.TotalPrice as 入库总额,
       Stock.StockDate as 入库日期
FROM Stock,MerchInfo,Provide,Factory
Where Stock.MerchID = MerchInfo.MerchID
      and Provide.ProvideID=MerchInfo.ProvideID
      and Factory.FactoryID=MerchInfo.FactoryID
      and Stock.StockState=1
GO

上一页  [1] [2] [3] [4] [5] [6] [7] 下一页

上一篇文章： java中国象棋源代码

下一篇文章： 单片机温度控制系统