數(shù)據(jù)庫課程設(shè)計指導(dǎo)書(精華)-資料下載頁
2025-08-04 00:23本頁面
【正文】 eate database SuperMarketdbon primary(name=SuperMarketdb,filename=39。C:\Program Files\Microsoft SQL Server\MSSQL\Data\39。,size=100MB,maxsize=200MB,filegrowth=20MB)log on(name=SuperMarketlog,filename=39。C:\Program Files\Microsoft SQL Server\MSSQL\Data\39。,size=60MB,maxsize=200MB,filegrowth=20MB)go創(chuàng)建基本表use [SuperMarketdb]go/*創(chuàng)建交易表*/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)GO/*創(chuàng)建廠商表*/CREATE TABLE Factory (FactoryID varchar(10) Primary key ,FactoryName varchar(50) NOT NULL ,FactoryAddress varchar(250) NULL ,FactoryPhone varchar(50) NULL)GO/*創(chuàng)建會員表*/CREATE TABLE Member (MemberID varchar(10) Primary key ,MemberCard varchar(20) NOT NULL ,TotalCost money NOT NULL ,RegDate datetime NOT NULL)GO/*創(chuàng)建商品信息表*/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/*創(chuàng)建供應(yīng)商表*/CREATE TABLE Provide (ProvideID varchar(10) Primary key ,ProvideName varchar(50) NOT NULL ,ProvideAddress varchar(250) NULL ,ProvidePhone varchar(25) NULL)GO/*創(chuàng)建銷售表*/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/*創(chuàng)建入庫表*/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/*創(chuàng)建用戶表*/CREATE TABLE User (UserID varchar(10) Primary key ,UserName varchar(25) NOT NULL ,UserPW varchar(50) NOT NULL ,UserStyle int NOT NULL ,)GO創(chuàng)建表間約束/*商品信息表中廠商編號,供應(yīng)商編號分別與廠商表,供應(yīng)商表之間的外鍵約束*/ALTER TABLE MerchInfo ADDCONSTRAINT [FK_MerchInfo_Factory] FOREIGN KEY([FactoryID]) REFERENCES Factory ([FactoryID]),CONSTRAINT [FK_MerchInfo_Provide] FOREIGN KEY([ProvideID]) REFERENCES Provide ([ProvideID])GO/*銷售表中商品編號與商品信息表之間的外鍵約束*/ALTER TABLE Sale ADDCONSTRAINT [FK_Sale_MerchInfo] FOREIGN KEY([MerChID]) REFERENCES MerchInfo ([MerchID]) ON DELETE CASCADEGO/*入庫表中商品編號與商品信息表之間的外鍵約束*/ALTER TABLE Stock ADDCONSTRAINT [FK_Stock_MerchInfo] FOREIGN KEY([MerchID]) REFERENCES MerchInfo ([MerchID]) ON DELETE CASCADEGO創(chuàng)建索引/*在交易表上建立一個以交易編號,交易日期為索引項的非聚集索引*/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創(chuàng)建視圖/*創(chuàng)建用于查詢交易情況的視圖*/CREATE VIEW v_DealingASSELECT DealingDate as 交易日期,UserName as 員工名稱,MemberCard as 會員卡號,DealingPrice as 交易金額FROM DealingGO/*創(chuàng)建用于查詢進貨計劃的視圖*/CREATE VIEW v_PlanStockASSELECT as SID, as 商品名稱, as 條形碼, as 廠商, as 供貨商, as 計劃進貨數(shù)量, as 計劃進貨日期FROM Stock,MerchInfo,Provide,FactoryWhere = and =and =and =0GO/*創(chuàng)建用于查詢銷售明細記錄的視圖*/CREATE VIEW v_SaleASSELECT as 商品名稱, as 條形碼, as 商品價格, as 銷售價格, as 銷售數(shù)量, as 銷售日期FROM Sale INNER JOINMerchInfo ON = GO/*創(chuàng)建用于查詢?nèi)霂烨闆r的視圖*/CREATE VIEW v_StockASSELECT as 商品名稱, as 條形碼, as 廠商, as 供貨商, as 入庫價格, as 入庫數(shù)量, as 入庫總額, as 入庫日期FROM Stock,MerchInfo,Provide,FactoryWhere = and =and =and =1GO以上創(chuàng)建界面略。 和傳統(tǒng)管理模式相比較,使用本系統(tǒng),毫無疑問會大大提高超市的運作效率,輔助提高超市的決策水平,管理水平,為降低經(jīng)營成本, 提高效益,減少差錯,節(jié)省人力,減少顧客購物時間,增加客流量,提高顧客滿意度,增強超市擴張能力, 提供有效的技術(shù)保障.由于開發(fā)者能力有限,加上時間倉促,本系統(tǒng)難免會出現(xiàn)一些不足之處,例如:本系統(tǒng)只適合小型超市使用,不能適合中大型超市使用。超市管理系統(tǒng)涉及范圍寬,要解決的問題多,功能復(fù)雜,實現(xiàn)困難,但由于限于時間,本系統(tǒng)只能做出其中的一部分功能
規(guī)章制度相關(guān)推薦
鄂ICP備17016276號-1