CREATE TABLE Dealing (
	DealingID int identity(1,1) Primary key ,
	DealingDate datetime NOT NULL ,
	DealingPrice money NOT NULL ,
	UserName varchar(25) NULL ,
	MemberCard varchar(20) NULL
)
<->
CREATE TABLE Factory (
	FactoryID int identity(1,1) Primary key ,
	FactoryName varchar(50) Unique NOT NULL ,
	FactoryAddress varchar(250) NULL ,
	FactoryPhone varchar(50) NULL
)
<->
CREATE TABLE Member (
	MemberID int identity(1,1) Primary key ,
	MemberCard varchar(20) Unique NOT NULL ,
	TotalCost money NOT NULL ,
	RegDate datetime NOT NULL
)
<->
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
)
<->
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
)
<->
CREATE TABLE StockTemp (
	ID int identity(1,1) Primary key ,
	MerchName varchar(50) NULL ,
	MerchNum int NULL ,
	PlanDate datetime NULL ,
	FactoryName varchar(50) NULL,
	ProvideName varchar(50) NULL
)
<->
CREATE TABLE Provide (
	ProvideID int identity(1,1) Primary key ,
	ProvideName varchar(50) Unique NOT NULL ,
	ProvideAddress varchar(250) NULL ,
	ProvidePhone varchar(25) NULL
)
<->
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
)
<->
CREATE TABLE SaleTemp (
	ID int identity(1,1) Primary key ,
	SaleDate int NULL ,
	SaleNumT int NULL,
	SaleMoneyT money NULL
)
<->
CREATE TABLE [User] (
	UserID int identity(1,1) Primary key ,
	UserName varchar(25) Unique NOT NULL ,
	UserPW varchar(50) NOT NULL ,
	UserStyle int NOT NULL
)
<->
ALTER TABLE MerchInfo ADD
	CONSTRAINT [FK_MerchInfo_Factory] FOREIGN KEY
	(
		[FactoryID]
	) REFERENCES Factory (
		[FactoryID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_MerchInfo_Provide] FOREIGN KEY
	(
		[ProvideID]
	) REFERENCES Provide (
		[ProvideID]
	) ON DELETE CASCADE 
<->
ALTER TABLE Sale ADD
	CONSTRAINT [FK_Sale_MerchInfo] FOREIGN KEY
	(
		[MerChID]
	) REFERENCES MerchInfo (
		[MerchID]
	) ON DELETE CASCADE 
<->
ALTER TABLE Stock ADD
	CONSTRAINT [FK_Stock_MerchInfo] FOREIGN KEY
	(
		[MerchID]
	) REFERENCES MerchInfo (
		[MerchID]
	) ON DELETE CASCADE 
<->
CREATE nonclustered INDEX IX_Dealing ON Dealing(DealingID, DealingDate)
<->
CREATE nonclustered INDEX IX_MerchInfo ON MerchInfo(MerchID)
<->
CREATE nonclustered INDEX IX_Sale ON Sale(SaleID, SaleDate)
<->
CREATE nonclustered INDEX IX_Stock ON Stock(StockID, StockDate, MerchID)
<->
CREATE VIEW v_Dealing
AS
SELECT DealingDate as 交易日期,
       UserName as 員工名稱,
       MemberCard as 會員卡號,
       DealingPrice as 交易金額
FROM Dealing
<->
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
<->
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
<->
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