超市銷售數(shù)據(jù)庫

1、超市銷售數(shù)據(jù)庫數(shù)據(jù)庫系統(tǒng)課程設(shè)計題目超市銷售管理數(shù)據(jù)庫設(shè)計院系信息技術(shù)與工程學(xué)院專業(yè)計算機科學(xué)與技術(shù)姓名學(xué)號班級名稱指導(dǎo)教師成績2016年12月 15日1銷售處理貨架缺貨銷存貨架售量警補排管告貨行理圖 1-3銷售處理結(jié)構(gòu)圖二、概念模型1、分 E-R 圖建立根據(jù)分解第二層數(shù)據(jù)流程圖可分別得到三個分E-R 圖。姓名累計注冊時間消 費 額卡 號會 員密 碼編 號編號1條 形 碼姓名獲得時 間數(shù) 量名 稱收 銀 臺m位 置1nm銷售n價 格營業(yè)員銷售單商品上班時間打印商品生產(chǎn)日期下班時間保 質(zhì) 期聯(lián)系編號消費額月 薪發(fā) 貨 商電話商 品折扣編 號圖4-1收銀業(yè)務(wù)模塊 E-R圖1發(fā)票編號消費額折扣打印時

2、間退貨單號購物發(fā)票1編號商品編號m打印退貨單姓名1退貨數(shù)量營業(yè)員收 銀 臺位 置退費額上班時間下班時間月薪聯(lián)系電話圖 4-2 退貨處理模塊E-R圖管理員號條 形 碼現(xiàn)有存量貨架編號姓名商品編號聯(lián)系電話商品m1貨架m1存放管理管理員商品名稱上班時間價 格下班時間最低存量月薪圖 4-3 銷售處理模塊E-R圖2、全局 / 整體 E-R 圖由于在做局部 ER 圖時，只考慮了局部功能模塊，這樣會導(dǎo)致各個 ER圖之間有很多不一致的地方，造成在合并各分 E R 圖生成全局 E R圖時有很多沖突。通過仔細(xì)分析各個分 E-R 圖之間的聯(lián)系， 消除冗余，消除沖突，最終成功生成全局 E-R 圖，如下圖所示。2會 員

3、1打印時間銷售數(shù)量現(xiàn)有存量存量底線獲得m營業(yè)員1mmnm存放1打印銷售單銷售商品商品貨架11m打印管理m打印時間1退貨單管理員圖 5 系統(tǒng)總 E-R圖三、關(guān)系數(shù)據(jù)模型1、關(guān)系模式建立關(guān)系模型由 E R圖轉(zhuǎn)換而來，實際上就是要將實體、 實體的屬性和實體之間的聯(lián)系明確表示出來，這種轉(zhuǎn)換一般遵循如下規(guī)則：一個實體型轉(zhuǎn)換為一個關(guān)系模式。實體的屬性就是關(guān)系的屬性，實體的碼就是關(guān)系的碼。此數(shù)據(jù)庫系統(tǒng)包括營業(yè)員、會員、管理員、商品、貨架、銷售單、銷售商品、退貨單、打印多個關(guān)系模式：營業(yè)員 (營業(yè)員號，姓名，收銀臺位置，上班時間，下班時間，月薪，聯(lián)系電話 ); 會員 (會員卡號，會員姓名，卡密碼，注冊時間，累

4、計消費 );商品（商品編號，貨架編號，條形碼，商品名稱，商品價格，現(xiàn)有存量，存量底線，生產(chǎn)日期，保質(zhì)期，供貨商） ；銷售單（銷售單編號，營業(yè)員編號，會員卡號，打印時間，有無折扣） ；銷售商品（銷售單號，商品編號，銷售數(shù)量） ；貨架（貨架編號，管理員號，貨架名稱）；管理員（管理員號，姓名，聯(lián)系電話，上班時間，下班時間，月薪） ；退貨單（退貨單號，商品編號，退貨數(shù)量） ；打?。ㄍ素泦翁?，銷售單號，營業(yè)員號，打印時間）；2、用戶子模式建立用戶子模式可通過建立視圖來表示。視圖是虛表，是從一個或幾個基本表（或視圖）中導(dǎo)出的表， 在系統(tǒng)的數(shù)據(jù)字典中僅存放了視圖的定義， 不存放視圖對應(yīng)的數(shù)據(jù)。定義視圖可以簡

5、化應(yīng)用程序、可以實現(xiàn)一定的權(quán)限控制。為了滿足用戶需求及方便后期數(shù)據(jù)庫實施階段的設(shè)計，此系統(tǒng)設(shè)計了如下視圖。各視圖定義：商品保質(zhì)期（商品名稱，生產(chǎn)日期，保質(zhì)期，過期日期）下架商品（商品名稱，貨架名稱，過期日期）3營業(yè)員基本信息（營業(yè)員號，上班時間，下班時間，月薪，聯(lián)系電話）會員總消費情況（卡號，姓名，累計消費）商品價格（商品名稱，價格）商品存放（商品名稱，貨架名稱）缺貨商品（商品名稱，現(xiàn)有存量，底線存量）貨架信息（貨架名稱，存放商品名）上班情況（上班時間，營業(yè)員姓名，管理員姓名）工作人員聯(lián)系方式（姓名，編號，聯(lián)系電話）工作人員工資信息（姓名，編號，工資）四、數(shù)據(jù)庫物理設(shè)計此數(shù)據(jù)庫系統(tǒng)建立的索引如

6、下所述。（1）對于基本表 ShopAssistant （營業(yè)員） , 由于要經(jīng)常對屬性列Snum查詢從而獲得某個營業(yè)員的完整信息，并且很少對其更新，因此可以給屬性列 Snum建一個聚簇索引。（2）同理對于基本表 Member （會員） ,由于要經(jīng)常對屬性列 Mnum 查詢從而獲得某個會員的完整信息， 并且很少對其更新， 因此可以給屬性列 Mnum 建一個聚簇索引。（3）對于 Adminastrator （管理員），可在其屬性列 Anum 上建立唯一性索引，索引值按降序排列。（4）同樣對于基本表 GoodsShelf（貨架）、Goods（商品），可在其主屬性上建立唯一性索引。（5）對于基本表 B

7、illSell （銷售單）、SellGoods（銷售商品）、 ReturnGoods （退貨單）、 PrintBill （打?。?，由于其屬性值經(jīng)常發(fā)生變化，權(quán)衡系統(tǒng)為維護(hù)索引付出的代價，可考慮不建立索引。五、數(shù)據(jù)庫實施與測試數(shù)據(jù)庫實施與測試階段主要內(nèi)容包括數(shù)據(jù)庫實施和測試兩個部分。1、 數(shù)據(jù)庫實施1.1 數(shù)據(jù)庫及數(shù)據(jù)庫對象建立主要包括：數(shù)據(jù)庫、基本表、視圖、索引、觸發(fā)器以及存儲過程。相應(yīng)T-SQL 和PL/SQL 語句詳見附錄 .1.2 數(shù)據(jù)入庫對各個數(shù)據(jù)表成功要錄入了50 條左右的測試記錄，測試結(jié)果滿足設(shè)計要求。2、 數(shù)據(jù)庫測試數(shù)據(jù)庫測試階段主要內(nèi)容是對建立的數(shù)據(jù)庫及數(shù)據(jù)庫對象進(jìn)行測試，對測

8、試結(jié)果進(jìn)行分析，是否滿足設(shè)計要求。六、總結(jié)通過數(shù)據(jù)庫課程設(shè)計實習(xí)，對數(shù)據(jù)庫知識有了更廣泛的了解，在數(shù)據(jù)庫的應(yīng)用方面有了很大的收獲。4（2）加深了對數(shù)據(jù)庫系統(tǒng)相關(guān)知識和 SQL SERVER 2008數(shù)據(jù)庫相關(guān)功能的理解。以前只停留于記憶書本上關(guān)于數(shù)據(jù)庫系統(tǒng)的理論知識，沒有切身實地的實踐過，而通過這次實習(xí)我再次加深了對數(shù)據(jù)庫相關(guān)功能的理解與應(yīng)用。（3）進(jìn)一步掌握相關(guān)的SQL語句。開發(fā)設(shè)計超市銷售管理系統(tǒng)的過程中牽涉到相當(dāng)多基本表的建立，視圖、索引以及存儲過程的設(shè)計，這讓我對這些 SQL語句操作更熟練了。（4）熟悉了對項目開發(fā)的大致過程。這次系統(tǒng)開發(fā)， 我更加明白體驗到了數(shù)據(jù)庫系統(tǒng)開發(fā)的過程，包括

9、系統(tǒng)需求分析、概念設(shè)計、邏輯設(shè)計、物理設(shè)計，再到數(shù)據(jù)庫實施、系統(tǒng)的測試和調(diào)試，對項目（系統(tǒng)）開發(fā)的大致流程有了一定的了解，為以后的系統(tǒng)的開發(fā)打下了良好的基礎(chǔ)。同樣在在這次實習(xí)中，我看到了自己的基礎(chǔ)知識的薄弱性，更體驗到了基礎(chǔ)知識的重要性。比如說對具體的SQL語句還不是很熟悉，在畫E-R 圖、設(shè)計帶輸出變量的存儲過程以及建立相關(guān)索引（聚簇索引）時感到有些棘手。遇到問題不可怕，其實能遇到問題是好事，它能讓我在解決問題時學(xué)到更多的新知識，更能增加我的自信。和傳統(tǒng)管理模式相比較，使用本系統(tǒng)，毫無疑問會大大提高超市的運作效率，輔助提高超市的決策水平，管理水平，為降低經(jīng)營成本，提高效益，減少差錯，節(jié)省人力

10、，減少顧客購物時間，增加客流量，提高顧客滿意度，增強超市擴張能力，都能提供有效的技術(shù)保障。但超市管理系統(tǒng)涉及范圍寬，要解決的問題多，功能復(fù)雜，實現(xiàn)困難，本系統(tǒng)只能做出其中的銷售管理部分功能，只適合小型超市使用。由于自己初次獨立設(shè)計開發(fā)數(shù)據(jù)庫系統(tǒng)，能力非常有限，加上時間倉促，本系統(tǒng)毫無疑問有許多的不足之處。對于出現(xiàn)的以上問題，我們深表歉意，懇請老師批評指正。5附錄1、創(chuàng)建數(shù)據(jù)庫create database Supermarket_SM;2、創(chuàng)建基本表create table ShopAssistant(Snum char(10) primary key,Sname char(10) not n

11、ull,Splace char(10),Swtime char(10),Sctime char(10),Ssalaary numeric(10,1),Sphone char(20)create table Administrator(Anum char(5) primary key,Aname char(15) not null,Awtime char(10),Actime char(10),Asalary numeric(10,1),Aphone char(25)create table Member(Mnum char(15) primary key,Mname char(15),Mpas

12、sword char(6) not null,MregisterTime date,Mexpense numeric(10,2)create table GoodsShelf(GSnum char(10) primary key,Anum char(5) foreign key references Administrator(Anum), GSname char(15),)create table Goods(Gnum char(10) primary key,GSnum char(10) foreign key references GoodsShelf(GSnum),Gbarcode c

13、har(20),Gname char(20),Gprice numeric(5,2),6GSstock smallint,GSlimit smallint,Gproducetime date,Gtime int,Gsupplier char(50)create table BillSell(Bnum char(15) primary key,Snum char(10) foreign key references ShopAssistant(Snum),Mnum char(15) foreign key references Member(Mnum),Bdate smalldatetime,B

14、discount char(2)create table SellGoods(Bnum char(15),Gnum char(10),Bquantity smallint,primary key(Gnum,Bnum),foreign key (Gnum) references Goods(Gnum),foreign key (Bnum) references BillSell(Bnum)create table ReturnGoods(Rnum char(10) primary key,Gnum char(10) foreign key references Goods(Gnum),Rquan

15、tity smallint)create table PrintBill(Bnum char(15),Rnum char(10),Snum char(10),Rdata smalldatetime,primary key (Bnum,Rnum,Snum),foreign key (Bnum) references BillSell(Bnum), foreign key (Rnum) references ReturnGoods(Rnum), foreign key (Snum) references ShopAssistant(Snum)3、創(chuàng)建索引（1）對于基本表 ShopAssistant

16、 （營業(yè)員） , 由于要經(jīng)常對屬性列Snum查詢從而獲得某個營業(yè)員的完整信息，并且很少對其更新，因此可以給屬性列 Snum建一個聚簇索引。7其相應(yīng) SQL語句為：create clustered index dex_Snum on ShopAssistant(Snum);（2）同理對于基本表Member（會員） , 由于要經(jīng)常對屬性列Mnum查詢從而獲得某個會員的完整信息，并且很少對其更新，因此可以給屬性列Mnum建一個聚簇索引。其相應(yīng) SQL語句為：create clustered index dex_Munm on Member(Mnum);（3）對于 Adminastrator （管理員

17、），可在其屬性列 Anum上建立唯一性索引，索引值按降序排列。其相應(yīng) SQL語句為：create unique index dex_Anum on Administrator(Anum desc);（4）同樣對于基本表 GoodsShelf （貨架）、Goods（商品），可在其主屬性上建立唯一性索引。其相應(yīng) SQL語句為：create unique index dex_GSnum on GoodsShelf(GSnum); create unique index dex_Gnum on Goods(Gnum);4、創(chuàng)建視圖相應(yīng) SQL語句為：create view V_Salesmanasse

18、lect Snum,Swtime,Sctime,Ssalaary,Sphonefrom ShopAssistantcreate view V_memberasselect Mnum,Mname,Mexpensefrom Membercreate view V_GoodsPriceasselect Gname,Gpricefrom Goodscreate view V_GoodsStoreasselect Gname,GSnamefrom Goods,GoodsShelfwhere Goods.GSnum=GoodsShelf.GSnumcreate view V_GoodsLackassele

19、ct Gname,GSstock,GSlimitfrom Goodswhere GSstock=GSlimitcreate view V_GoodsShelf8asselect GSname,Gnamefrom GoodsShelf,Goodswhere GoodsShelf.GSnum=Goods.GSnumcreate view V_Workasselect ShopAssistant.Swtime,ShopAssistant.Sname,Administrator.Aname from ShopAssistant,Administratorwhere ShopAssistant.Swti

20、me=Administrator.Awtimecreate view V_Phone(name,num,phone)as(select Sname,Snum,Sphonefrom ShopAssistant)union(select Aname,Anum,Aphonefrom Administrator)create view V_Salary(name,num,salary)as(select Sname,Snum,Ssalaaryfrom ShopAssistant)union(select Aname,Anum,Asalaryfrom Administrator)create view

21、V_GoodsEndasselect Gname,Gproducetime,Gtime,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goodscreate view V_GoodsUnloadasselect Gname,GoodsShelf.GSname,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goods,GoodsShelfwhere DateAdd(DAY,Gtime,Gproducetime)=GETDATE()and Goods.GSnum=GoodsShelf.GSnumcreate vie

22、w v_BillExpenseasselect Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpense from SellGoods,Goodswhere SellGoods.Gnum=Goods.Gnum5、創(chuàng)建觸發(fā)器91）當(dāng)打印一張銷售發(fā)票時，即刪除數(shù)據(jù)庫中剛建立的銷售發(fā)票時，相應(yīng)商品在貨架上的現(xiàn)有存量減少，相應(yīng)會員總消費額增加。其相應(yīng) SQL語句為：create trigger t1 on v_BillExpenseINSTEAD OF deleteasdeclare Quantity int,Gname char(10),T

23、otalExpense float-Bnum char(10)select Quantity=Bquantity from deletedselect Gname=Gname from deleted-select TotalExpense=TotalExpense from deleted -select Bnum=Bnum from deletedupdate Goodsset GSstock=GSstock-Quantitywhere Goods.Gname=Gname-update Member-set Mexpense=Mexpense+TotalExpense-where Bnum

24、=SellBill.Bnum-and SellBill.Mnum=Mnum（2）當(dāng)從過期商品名單中刪除某記錄時，相應(yīng)商品現(xiàn)有存量減少.其相應(yīng) SQL語句為：create trigger t2 on V_GoodsUnloadINSTEAD OF deleteasdeclare Gname char(10)select Gname=Gname from deleteddelete from Goodswhere Gname=Gname（3）打印一張退貨發(fā)票，即刪除退貨單中的記錄，表示 R 商品重新返回原貨架存儲（假設(shè)脫貨商品不影響第二次銷售） ，貨架商品現(xiàn)有存量增加 .其相應(yīng) SQL語句為：cr

25、eate trigger t3 on ReturnGoodsafter deleteasdeclare Gnum char(10),Rquantity intselect Gnum=Gnum from deletedselect Rquantity from deletedupdate Goodsset GSstock=GSstock+Rquantitywhere Gnum=Gnum6、 建立儲存過程（1）計算某個銷售單上，每種商品的累計銷售額（即輸出每種商品名稱，件數(shù)，10單價，費用小計）。其相應(yīng) SQL語句為：create procedure p_TotalExpense(BS_Bnum

26、varchar,Bnum varchar output,Gname varchar output,Bquantity int output,Gprice numeric output,TotalExpense numeric output)asselectBnum=Bnum,Gname=Gname,Bquantity=Bquantity,Gprice=Gprice,TotalExpense=(Bquantity*Gprice)from v_BillExpensewhere Bnum=BS_Bnum其驗證語句為：DeclareT_Bnum varchar,T_Gname varchar,T_Bq

27、uantity int,T_Gprice varchar,T_TotalExpense varcharExecute p_TotalExpense GS-0000004,T_Bnum output,T_Gname output,T_Bquantity output,T_Gprice output,T_TotalExpense output Print T_Bnum+ +T_Gname+ +T_Bquantity+ +T_Gprice+ +T_TotalExpense（2）計算某個銷售單上，顧客消費商品總數(shù)量和總消費額。其相應(yīng) SQL語句為：create procedure p_BillExpe

28、nseBS_Bnum varcharasselect Bnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpense from v_BillExpensegroup by Bnumhaving Bnum=BS_Bnum（3）查詢某件商品價格。其相應(yīng) SQL語句為：create procedure p_priceGname char(10)asselect Gpricefrom V_GoodsPricewhere Gname=Gname（4）查詢某件商品現(xiàn)有數(shù)量。11其相應(yīng) SQL語句為：create procedure p_GquantityGname char(10)asselect GSstockfro