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

1、數(shù)據(jù)庫系統(tǒng)課程設(shè)計題目超市銷售管理數(shù)據(jù)庫設(shè)計院系信息技術(shù)與工程學(xué)院專業(yè)計算機科學(xué)與技術(shù)姓 名學(xué)號班級名稱指導(dǎo)教師成績2016年12月15日刖言一、系統(tǒng)需求分析1、處理對象2、系統(tǒng)功能及信息處理 3、安全性和完整性要求 4、系統(tǒng)結(jié)構(gòu)圖 二、概念模型 1、分E-R圖建立2、全局/整體E-R圖三、關(guān)系數(shù)據(jù)模型 1、關(guān)系模式建立2、用戶子模式建立 四、數(shù)據(jù)庫物理設(shè)計 五、數(shù)據(jù)庫實施與測試 1、數(shù)據(jù)庫實施2、數(shù)據(jù)庫測試六、總結(jié)附錄.、八、一前言超市銷售管理系統(tǒng)是為了解決超市銷售管理業(yè)務(wù)方面面臨的復(fù)雜業(yè)務(wù)流程 和繁瑣數(shù)據(jù)處理等問題，提高超市運營效率，提高經(jīng)濟效益，提高市場競爭力 而研究幵發(fā)的一款數(shù)據(jù)庫軟件

2、。在幵發(fā)平臺windows上，采用了 SQL數(shù)據(jù)庫程序設(shè)計語言予以實現(xiàn)；可訪問 SQLSEREVER2005 數(shù)據(jù)庫，具有商品錄入、商品 信息查詢、交易額計算、會員打折、打印銷售發(fā)票、退貨、打印退貨發(fā)票、記 錄交易細節(jié)、貨架管理、商品過期警告、缺貨警告等方面的功能，為超市管理 提供了有效的技術(shù)保障，并且可直接作為開發(fā)整個超市管理系統(tǒng)時已完成的一 部分模塊。一、系統(tǒng)需求分析1、處理對象系統(tǒng)要處理的基本對象包括營業(yè)員基本信息、會員基本信息、管理員基本 信息、商品基本信息、貨架基本信息、銷售單基本信息、退貨單基本信息等多 個對象。各個基本對象包含信息如下所示：營業(yè)員 （營業(yè)員號，姓名，收銀臺位置，上

3、班時間，下班時間，月薪，聯(lián)系 電話 ）;會員 （會員卡號，會員姓名，卡密碼，注冊時間，累計消費 ）;商品（商品編號，貨架編號，條形碼，商品名稱，商品價格，現(xiàn)有存量， 存量底線，生產(chǎn)日期，保質(zhì)期，供貨商） ；銷售單（銷售單編號，營業(yè)員編號，會員卡號，打印時間，有無折扣） ； 銷售商品（銷售單號，商品編號，銷售數(shù)量） ； 貨架（貨架編號，管理員號，貨架名稱） ； 管理員（管理員號，姓名，聯(lián)系電話，上班時間，下班時間，月薪） ； 退貨單（退貨單號，商品編號，退貨數(shù)量） ； 打?。ㄍ素泦翁枺N售單號，營業(yè)員號，打印時間） 。2、系統(tǒng)功能及信息處理本中小型超市銷售管理系統(tǒng)大體上包含三個模塊，為前臺收銀業(yè)

4、務(wù)處理， 前臺退貨業(yè)務(wù)處理以及后臺銷售處理功能模塊，具體如下所述。收銀業(yè)務(wù)通過掃描條形碼或者直接輸入商品名稱（對于同類多件商品采用一次錄入 加數(shù)量的方式），計算本次交易的總金額，同時打印銷售發(fā)票給顧客（包括交易 的流水賬號、每類商品的商品名、數(shù)量、該類商品的總金額、交易的時間、負(fù) 責(zé)本次收銀的員工號等信息） 。如果顧客是本店會員并持有本人會員卡， 則在交 易前先掃描會員卡，對此會員一定優(yōu)惠，并將所購物品的總金額累計到該會員 的總消費金額中，記錄好會員信息。退貨處理顧客持有銷售發(fā)票到收銀臺找收銀員退貨，若沒有相應(yīng)銷售發(fā)票不予以退貨。通過掃描銷售發(fā)票計算本次退貨處理的退貨額，并打印出退貨發(fā)票給顧客

5、,系統(tǒng)記錄好退貨信息。銷售處理作為超市后臺管理部分，此系統(tǒng)應(yīng)該具有功能包括當(dāng)打印銷售發(fā)票或退貨 發(fā)票時自動增減貨架上商品數(shù)量，當(dāng)貨架商品不足時自動生成缺貨警告信息給 管理員，系統(tǒng)能夠生成銷售排行榜供管理員查看。3、安全性和完整性要求安全性先通過視圖機制，不同的用戶只能訪問系統(tǒng)授權(quán)的視圖；再通過用 戶授權(quán)機制，通過用戶登陸來識別用戶級別，根據(jù)這個級別來分配用戶權(quán)限， 達到數(shù)據(jù)更高層次的安全保密功能。完整性要求用于確保各處理對象的主屬性取值唯一并且一般不能為空；各 處理對象的參照屬性取值必須是來自于被參照屬性?？梢酝ㄟ^用戶自定義完整 性（符合實際要求）來確保數(shù)據(jù)符合更高規(guī)范要求。詳細完整性要求見于

6、系統(tǒng) 的邏輯設(shè)計階段。4、系統(tǒng)結(jié)構(gòu)圖通過對中小型超市銷售管理業(yè)務(wù)及其功能方面的分析，構(gòu)造出超市管理系 統(tǒng)的總體結(jié)構(gòu)圖如下：由于本系統(tǒng)為銷售管理系統(tǒng)，只是超市管理系統(tǒng)的一部分，因此只實現(xiàn)了 收營業(yè)務(wù)、退貨處理和銷售處理部分的管理總體對這三個處理模塊進一步細化得 到如下分結(jié)構(gòu)圖：圖1-1收銀業(yè)務(wù)結(jié)構(gòu)圖1-2退貨處理結(jié)構(gòu)圖圖圖1-3銷售處理結(jié)構(gòu)圖二、概念模型1、分E-R圖建立根據(jù)分解第二層數(shù)據(jù)流程圖可分別得到三個分E-R圖。圖4-1收銀業(yè)務(wù)模塊E-R圖圖4-2退貨處理模塊E-R圖圖4-3銷售處理模塊E-R圖2、全局/整體E-R圖由于在做局部E R圖時，只考慮了局部功能模塊，這樣會導(dǎo)致各個ER圖之間有

7、很多不一致的地方，造成在合并各分ER圖生成全局ER圖時有很多沖突。通過仔細分析各個分E-R圖之間的聯(lián)系，消除冗余，消除沖突，最終成功生成全局E-R圖，如下圖所示。圖5系統(tǒng)總E-R圖三、關(guān)系數(shù)據(jù)模型1、關(guān)系模式建立關(guān)系模型由ER圖轉(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)系 電話）；會員（會員卡號，會員姓

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

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

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

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

12、數(shù)據(jù)庫及數(shù)據(jù)庫對象進行測試，對測 試結(jié)果進行分析，是否滿足設(shè)計要求。六、總結(jié)通過數(shù)據(jù)庫課程設(shè)計實習(xí)，對數(shù)據(jù)庫知識有了更廣泛的了解，在數(shù)據(jù)庫的 應(yīng)用方面有了很大的收獲。（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）進一步掌握相關(guān)的 SQL語句。開發(fā)設(shè)計超市銷售管理系統(tǒng)的過程中牽涉到相當(dāng)多基本表的建立，視圖、索引以及存儲過程的設(shè)計，這讓我對這些SQL語句操作更熟練了。（4）熟悉了對項目開發(fā)的大致過程。這次系統(tǒng)開發(fā)，我更加明

13、白體驗到了數(shù)據(jù)庫系統(tǒng)開發(fā)的過程，包括系統(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)，毫無疑問會大大提高超市的運作效 率，輔助提高超市的決策水平，

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

15、 primary key,Sname char(10) not null,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

16、) primary key,Mname char(15),Mpassword 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 refere

17、nces GoodsShelf(GSnum), Gbarcode char(20),Gname char(20),Gprice numeric(5,2),GSstock 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 M

18、ember(Mnum),Bdate smalldatetime,Bdiscount 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)

19、foreign key references Goods(Gnum),Rquantity 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 ShopAssis

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

21、unn on Menber（Mnun）;（3）對于Adminastrator （管理員），可在其屬性列 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

22、）;4、創(chuàng)建視圖 相應(yīng)SQL語句為：create view V_Salesmanasselect Snum,Swtime,Sctime,Ssalaary,Sphonefrom ShopAssistant create view V_member asselect Mnum,Mname,Mexpense from Membercreate view V_GoodsPrice asselect Gname,Gprice from Goods create view V_GoodsStore asselect Gname,GSname from Goods,GoodsShelf where = cr

23、eate view V_GoodsLack asselect Gname,GSstock,GSlimit from Goodswhere GSstock<=GSlimitcreate view V_GoodsShelf asselect GSname,Gnamefrom GoodsShelf,Goodswhere =create view V_Work as select ,from ShopAssistant,Administratorwhere =create view V_Phone(name,num,phone) as(select Sname,Snum,Sphonefrom S

24、hopAssistant)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 V_GoodsEnd as selectGname,Gproducetime,Gtime,DateAdd(DAY,Gtime,Gproducetime)EndTime from G

25、oodscreate view V_GoodsUnload asselect Gname,DateAdd(DAY,Gtime,Gproducetime)EndTimefrom Goods,GoodsShelfwhere DateAdd(DAY,Gtime,Gproducetime)<=GETDATE()and =create view v_BillExpense asselect Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpensefrom SellGoods,Goodswhere =5、創(chuàng)建觸發(fā)器(1) 當(dāng)打印一張銷售發(fā)票

26、時，即刪除數(shù)據(jù)庫中剛建立的銷售發(fā)票時，相應(yīng) 商品在貨架上的現(xiàn)有存量減少，相應(yīng)會員總消費額增加。其相應(yīng)SQL語句為：create trigger t1 on v_BillExpenseINSTEAD OF delete asdeclare Quantity int,Gname char(10),TotalExpense float-Bnum char(10)select Quantity=Bquantity from deletedselect Gname=Gname from deleted-select TotalExpense=TotalExpense from deleted-selec

27、t Bnum=Bnum from deletedupdate Goodsset GSstock=GSstock-Quantitywhere =Gname-update Member-set Mexpense=Mexpense+TotalExpense-where Bnum=-and =Mnum(2) 當(dāng)從過期商品名單中刪除某記錄時，相應(yīng)商品現(xiàn)有存量減少.其相應(yīng)SQL語句為：create trigger t2 on V_GoodsUnload INSTEAD OF deleteasdeclare Gname char(10)select Gname=Gname from deleteddelet

28、e from Goodswhere Gname=Gname(3) 打印一張退貨發(fā)票，即刪除退貨單中的記錄，表示R 商品重新返回原貨架存儲(假設(shè)脫貨商品不影響第二次銷售) ，貨架商品現(xiàn)有存量增加 .其相應(yīng)SQL語句為：create trigger t3 on ReturnGoodsafter deleteasdeclare Gnum char(10),Rquantity intselect Gnum=Gnum from deletedselect Rquantity from deletedupdate Goodsset GSstock=GSstock+Rquantitywhere Gnum=G

29、num6、建立儲存過程(1) 計算某個銷售單上，每種商品的累計銷售額(即輸出每種商品名稱， 件數(shù)，單價，費用小計) 。其相應(yīng)SQL語句為：create procedure p_TotalExpense(BS_Bnum varchar,Bnum varchar output,Gname varchar output,Bquantity int output,Gprice numeric output,TotalExpense numeric output)asselectBnum=Bnum,Gname=Gname,Bquantity=Bquantity,Gprice=Gprice,TotalEx

30、 pense=(Bquantity*Gprice)from v_BillExpensewhere Bnum=BS_Bnum其驗證語句為：DeclareT_Bnum varchar,T_Gname varchar,T_Bquantity 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 outputPrint T_Bnum+' '+T_Gname+' '+T_Bquantity+' '+T_Gprice+' '+T_TotalExpense(2) 計算某個銷售單上，顧客消費商品總數(shù)量和總消費額。其相應(yīng)SQL語句為：create procedure p_BillExpenseBS_Bnum varcharasselectBnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpensefrom v_BillExpensegroup by Bnu