1、數(shù)據(jù)庫系統(tǒng)課程設(shè)計(jì)題目超市銷售管理數(shù)據(jù)庫設(shè)計(jì)院系信息技術(shù)與工程學(xué)院專業(yè) 計(jì)算機(jī)科學(xué)與技術(shù)姓名學(xué)號班級名稱指導(dǎo)教師成績2016年12月15日 TOC o 1-5 h z HYPERLINK l bookmark6 o Current Document 前言1 HYPERLINK l bookmark8 o Current Document 一、系統(tǒng)需求分析1 HYPERLINK l bookmark10 o Current Document 1、處理對象1 HYPERLINK l bookmark12 o Current Document 2、系統(tǒng)功能及信息處理1 HYPERLINK l boo

2、kmark14 o Current Document 3、安全性和完整性要求 2 HYPERLINK l bookmark16 o Current Document 4、系統(tǒng)結(jié)構(gòu)圖2 HYPERLINK l bookmark18 o Current Document 二、概念模型3 HYPERLINK l bookmark20 o Current Document 1、分E-R圖建立3 HYPERLINK l bookmark22 o Current Document 2、全局/整體E-R圖4 HYPERLINK l bookmark24 o Current Document 三、關(guān)系數(shù)據(jù)模型

3、5 HYPERLINK l bookmark26 o Current Document 1、關(guān)系模式建立5 HYPERLINK l bookmark28 o Current Document 2、用戶子模式建立5 HYPERLINK l bookmark30 o Current Document 四、數(shù)據(jù)庫物理設(shè)計(jì)6 HYPERLINK l bookmark32 o Current Document 五、數(shù)據(jù)庫實(shí)施與測試 6 HYPERLINK l bookmark34 o Current Document 1、數(shù)據(jù)庫實(shí)施6 HYPERLINK l bookmark36 o Current D

4、ocument 2、數(shù)據(jù)庫測試6 HYPERLINK l bookmark38 o Current Document 六、總結(jié)6 HYPERLINK l bookmark40 o Current Document 附錄8前言超市銷售管理系統(tǒng)是為了解決超市銷售管理業(yè)務(wù)方面面臨的復(fù)雜業(yè)務(wù)流程和繁瑣 數(shù)據(jù)處理等問題，提高超市運(yùn)營效率，提高經(jīng)濟(jì)效益，提高市場競爭力而研究開發(fā)的 一款數(shù)據(jù)庫軟件。在開發(fā)平臺windows上，采用了 SQL數(shù)據(jù)庫程序設(shè)計(jì)語言予以實(shí)現(xiàn); 可訪問SQL SEREVER 2005數(shù)據(jù)庫，具有商品錄入、商品信息查詢、交易額計(jì)算、會 員打折、打印銷售發(fā)票、退貨、打印退貨發(fā)票、記錄交易

5、細(xì)節(jié)、貨架管理、商品過期 警告、缺貨警告等方面的功能，為超市管理提供了有效的技術(shù)保障，并且可直接作為 開發(fā)整個超市管理系統(tǒng)時已完成的一部分模塊。一、系統(tǒng)需求分析1、處理對象系統(tǒng)要處理的基本對象包括營業(yè)員基本信息、會員基本信息、管理員基本信息、 商品基本信息、貨架基本信息、銷售單基本信息、退貨單基本信息等多個對象。各個 基本對象包含信息如下所示：營業(yè)員（營業(yè)員號，姓名，收銀臺位置，上班時間，下班時間，月薪，聯(lián)系電話）;會員（會員卡號，會員姓名，卡密碼，注冊時間，累計(jì)消費(fèi) ）；商品（商品編號，貨架編號，條形碼，商品名稱，商品價格，現(xiàn)有存量，存量底線，生產(chǎn)日期，保質(zhì)期，供貨商）；銷售單（銷售單編號，

6、營業(yè)員編號，會員卡號，打印時間，有無折扣）；銷售商品（銷售單號，商品編號，銷售數(shù)量）；貨架（貨架編號，管理員號，貨架名稱）；管理員（管理員號，姓名，聯(lián)系電話，上班時間，下班時間，月薪） ；退貨單（退貨單號，商品編號，退貨數(shù)量）；打?。ㄍ素泦翁?，銷售單號，營業(yè)員號，打印時間）。2、系統(tǒng)功能及信息處理本中小型超市銷售管理系統(tǒng)大體上包含三個模塊，為前臺收銀業(yè)務(wù)處理，前臺退 貨業(yè)務(wù)處理以及后臺銷售處理功能模塊，具體如下所述。收銀業(yè)務(wù)通過掃描條形碼或者直接輸入商品名稱（對于同類多件商品采用一次錄入加數(shù)量 的方式），計(jì)算本次交易的總金額，同時打印銷售發(fā)票給顧客（包括交易的流水賬號、 每類商品的商品名、數(shù)量

7、、該類商品的總金額、交易的時間、負(fù)責(zé)本次收銀的員工號 等信息）。如果顧客是本店會員并持有本人會員卡， 則在交易前先掃描會員卡，對此會 員一定優(yōu)惠，并將所購物品的總金額累計(jì)到該會員的總消費(fèi)金額中，記錄好會員信息。退貨處理顧客持有銷售發(fā)票到收銀臺找收銀員退貨，若沒有相應(yīng)銷售發(fā)票不予以退貨。通 過掃描銷售發(fā)票計(jì)算本次退貨處理的退貨額，并打印出退貨發(fā)票給顧客，系統(tǒng)記錄好 退貨信息。銷售處理作為超市后臺管理部分，此系統(tǒng)應(yīng)該具有功能包括當(dāng)打印銷售發(fā)票或退貨發(fā)票時 自動增減貨架上商品數(shù)量，當(dāng)貨架商品不足時自動生成缺貨警告信息給管理員，系統(tǒng) 能夠生成銷售排行榜供管理員查看。3、安全性和完整性要求安全性先通過視

8、圖機(jī)制，不同的用戶只能訪問系統(tǒng)授權(quán)的視圖；再通過用戶授權(quán) 機(jī)制，通過用戶登陸來識別用戶級別，根據(jù)這個級別來分配用戶權(quán)限，達(dá)到數(shù)據(jù)更高 層次的安全保密功能。完整性要求用于確保各處理對象的主屬性取值唯一并且一般不能為空；各處理對 象的參照屬性取值必須是來自于被參照屬性?？梢酝ㄟ^用戶自定義完整性（符合實(shí)際 要求）來確保數(shù)據(jù)符合更高規(guī)范要求。詳細(xì)完整性要求見于系統(tǒng)的邏輯設(shè)計(jì)階段。4、系統(tǒng)結(jié)構(gòu)圖通過對中小型超市銷售管理業(yè)務(wù)及其功能方面的分析，構(gòu)造出超市管理系統(tǒng)的總圖1超市管理總體結(jié)構(gòu)圖由于本系統(tǒng)為銷售管理系統(tǒng)，只是超市管理系統(tǒng)的一部分，因此只實(shí)現(xiàn)了收營業(yè) 務(wù)、退貨處理和銷售處理部分的功能。對這三個處理模

9、塊進(jìn)一步細(xì)化得到如下分結(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)致各個 R圖之間 有很多不一致的地方，造成在合并各分 E-R圖生成全局E-R圖時有很多沖突。通過 仔細(xì)分析各個分E-R圖之間的聯(lián)系，消除冗余，消除沖突，最終成功生成全局E-R圖, 如下圖所示。三、關(guān)系數(shù)據(jù)模型1、關(guān)系模式建立關(guān)系模型由 R圖轉(zhuǎn)換而來，實(shí)際上就是要將

10、實(shí)體、實(shí)體的屬性和實(shí)體之間的聯(lián)系明確表示出來，這種轉(zhuǎn)換一般遵循如下規(guī)則：一個實(shí)體型轉(zhuǎn)換為一個關(guān)系模式。實(shí)體的屬性就是關(guān)系的屬性，實(shí)體的碼就是關(guān) 系的碼。此數(shù)據(jù)庫系統(tǒng)包括營業(yè)員、會員、管理員、商品、貨架、銷售單、銷售商品、退 貨單、打印多個關(guān)系模式：營業(yè)員（營業(yè)員號,姓名，收銀臺位置，上班時間，下班時間，月薪，聯(lián)系電話）;會員（會員卡號、會員姓名，卡密碼，注冊時間，累計(jì)消費(fèi) ）；商品（商品編號,貨架編號，條形碼，商品名稱，商品價格，現(xiàn)有存量，存量底線，生產(chǎn)日期，保質(zhì)期，供貨商）；銷售單（銷售單編號，營業(yè)員編號，會員卡號、打印時間,有無折扣）；銷售商品（銷售單號，商品編號，銷售數(shù)量）;貨架（貨架編

11、號,管理員號，貨架名稱）；管理員（管理員號、還j,旗系電話，上班時間，下班時間，月薪） ；退貨單（退貨單號,商品編號，退貨數(shù)量）；打?。ㄍ素泦翁?，銷售單號，營業(yè)員號，打印時間）;2、用戶子模式建立用戶子模式可通過建立視圖來表示。視圖是虛表，是從一個或幾個基本表（或視圖）中導(dǎo)出的表，在系統(tǒng)的數(shù)據(jù)字典中僅存放了視圖的定義， 不存放視圖對應(yīng)的數(shù)據(jù) 定義視圖可以簡化應(yīng)用程序、可以實(shí)現(xiàn)一定的權(quán)限控制。為了滿足用戶需求及方便后 期數(shù)據(jù)庫實(shí)施階段的設(shè)計(jì)，此系統(tǒng)設(shè)計(jì)了如下視圖。各視圖定義：商品保質(zhì)期（商品名稱，生產(chǎn)日期，保質(zhì)期，過期日期）下架商品（商品名稱，貨架名稱，過期日期）營業(yè)員基本信息（營業(yè)員號，上班時

12、間，下班時間，月薪，聯(lián)系電話）會員總消費(fèi)情況（卡號，姓名，累計(jì)消費(fèi)）商品價格（商品名稱，價格）商品存放（商品名稱，貨架名稱）缺貨冏品（冏品名稱，現(xiàn)有存量，底線存量）貨架信息（貨架名稱，存放商品名）上班情況（上班時間，營業(yè)員姓名，管理員姓名）工作人員聯(lián)系方式（姓名，編號，聯(lián)系電話）工作人員工資信息（姓名，編號，工資）四、數(shù)據(jù)庫物理設(shè)計(jì)此數(shù)據(jù)庫系統(tǒng)建立的索引如下所述。（1）對于基本表ShopAssistant （營業(yè)員），由于要經(jīng)常對屬性列SnumS詢從而 獲得某個營業(yè)員的完整信息，并且很少對其更新，因此可以給屬性列Snumt一個聚簇 索引。（2）同理對于基本表Member （會員），由于要經(jīng)常對

13、屬性列 Mnum查詢從而獲得 某個會員的完整信息，并且很少對其更新，因此可以給屬性列Mnum建一個聚簇索引。（3）對于Adminastrator （管理員），可在其屬性列 Anum上建立唯一性索引，索 引值按降序排列。（4）同樣對于基本表 GoodsShelf （貨架）、Goods （商品），可在其主屬性上建立 唯一性索引。（5）對于基本表BillSell （銷售單）、SellGoods （銷售商品）、ReturnGoods （退貨 單）、PrintBill （打?。?，由于其屬性值經(jīng)常發(fā)生變化，權(quán)衡系統(tǒng)為維護(hù)索引付出的代價， 可考慮不建立索引。五、數(shù)據(jù)庫實(shí)施與測試數(shù)據(jù)庫實(shí)施與測試階段主要內(nèi)容包

14、括數(shù)據(jù)庫實(shí)施和測試兩個部分。1、數(shù)據(jù)庫實(shí)施數(shù)據(jù)庫及數(shù)據(jù)庫對象建立主要包括：數(shù)據(jù)庫、基本表、視圖、索引、觸發(fā)器以及存儲過程。相應(yīng) T-SQL和 PL/SQL語句詳見附錄.數(shù)據(jù)入庫對各個數(shù)據(jù)表成功要錄入了 50條左右的測試記錄，測試結(jié)果滿足設(shè)計(jì)要求。2、數(shù)據(jù)庫測試數(shù)據(jù)庫測試階段主要內(nèi)容是對建立的數(shù)據(jù)庫及數(shù)據(jù)庫對象進(jìn)行測試，對測試結(jié)果 進(jìn)行分析，是否滿足設(shè)計(jì)要求。六、總結(jié)通過數(shù)據(jù)庫課程設(shè)計(jì)實(shí)習(xí)，對數(shù)據(jù)庫知識有了更廣泛的了解，在數(shù)據(jù)庫的應(yīng)用方 面有了很大的收獲。（2）加深了對數(shù)據(jù)庫系統(tǒng)相關(guān)知識和 SQL SERVER 200數(shù)據(jù)庫相關(guān)功能的理解。以前只停留于記憶書本上關(guān)于數(shù)據(jù)庫系統(tǒng)的理論知識，沒有切身

15、實(shí)地的實(shí)踐過， 而通過這次實(shí)習(xí)我再次加深了對數(shù)據(jù)庫相關(guān)功能的理解與應(yīng)用。(3)進(jìn)一步掌握相關(guān)的SQL語句。開發(fā)設(shè)計(jì)超市銷售管理系統(tǒng)的過程中牽涉到相當(dāng)多基本表的建立，視圖、索引以 及存儲過程的設(shè)計(jì)，這讓我對這些 SQL語句操作更熟練了。(4)熟悉了對項(xiàng)目開發(fā)的大致過程。這次系統(tǒng)開發(fā)，我更加明白體驗(yàn)到了數(shù)據(jù)庫系統(tǒng)開發(fā)的過程， 包括系統(tǒng)需求分析、 概念設(shè)計(jì)、邏輯設(shè)計(jì)、物理設(shè)計(jì)，再到數(shù)據(jù)庫實(shí)施、系統(tǒng)的測試和調(diào)試，對項(xiàng)目(系 統(tǒng))開發(fā)的大致流程有了一定的了解，為以后的系統(tǒng)的開發(fā)打下了良好的基礎(chǔ)。同樣在在這次實(shí)習(xí)中，我看到了自己的基礎(chǔ)知識的薄弱性，更體驗(yàn)到了基礎(chǔ)知識 的重要性。比如說對具體的 SQL語句還

16、不是很熟悉，在畫 E-R圖、設(shè)計(jì)帶輸出變量的 存儲過程以及建立相關(guān)索引(聚簇索引)時感到有些棘手。遇到問題不可怕，其實(shí)能 遇到問題是好事，它能讓我在解決問題時學(xué)到更多的新知識，更能增加我的自信。和傳統(tǒng)管理模式相比較，使用本系統(tǒng)，毫無疑問會大大提高超市的運(yùn)作效率，輔 助提高超市的決策水平，管理水平，為降低經(jīng)營成本，提高效益，減少差錯，節(jié)省人力，減少顧客購物時間，增加客流量，提高顧客滿意度，增強(qiáng)超市擴(kuò)張能力，都能提供有效的技術(shù)保障。但超市管理系統(tǒng)涉及范圍寬，要解決的問題多，功能復(fù)雜，實(shí)現(xiàn) 困難，本系統(tǒng)只能做出其中的銷售管理部分功能，只適合小型超市使用。由于自己初次獨(dú)立設(shè)計(jì)開發(fā)數(shù)據(jù)庫系統(tǒng)，能力非常有

17、限，加上時間倉促，本系統(tǒng) 毫無疑問有許多的不足之處。對于出現(xiàn)的以上問題，我們深表歉意，懇請老師批評指 正。附錄1、創(chuàng)建數(shù)據(jù)庫create database Supermarket_SM;2、創(chuàng)建基本表create table ShopAssistant(Snum char(10) 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 cha

18、r(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),Mpassword char(6) not null,MregisterTime date, Mexpense numeric(10,2) )create table GoodsShelf(GSnum char(10) primary key,Anum

19、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 char(20),Gname char(20),Gprice numeric(5,2),GSstock smallint,GSlimit smallint,Gproducetime date,Gtime int,Gsupplier char

20、(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,Bdiscount char(2) )create table SellGoods(Bnum char(15),Gnum char(10),Bquantity smallint,primary key(Gnum,Bnum),foreign

21、 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),Rquantity smallint )create table PrintBill(Bnum char(15),Rnum char(10),Snum char(10),Rdata smalldatetime,primary key (B

22、num,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 (營業(yè)員)，由于要經(jīng)常對屬性列SnumS詢從而 獲得某個營業(yè)員的完整信息，并且很少對其更新，因此可以給屬性列Snumt一個聚簇 索引。其相應(yīng)SQL語句為：create clustered index dex_Snum on S

23、hopAssistant(Snum);(2)同理對于基本表Member(會員)，由于要經(jīng)常對屬性列MnumS詢從而獲得 某個會員的完整信息，并且很少對其更新，因此可以給屬性列Mnunft一個聚簇索引。其相應(yīng)SQL語句為：create clustered index dex_Munm on Member(Mnum);(3)對于Adminastrator (管理員)，可在其屬性列 Anum上建立唯一性索引，索 引值按降序排列。其相應(yīng)SQL語句為：create unique index dex_Anum on Administrator(Anum desc);(4)同樣對于基本表 GoodsShel

24、f (貨架)、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_Salesman asselect Snum,Swtime,Sctime,Ssalaary,Sphonefrom ShopAssistantcreate view V_member asselect Mnum,Mname,Mexpensefrom Membercr

25、eate view V_GoodsPriceasselect Gname,Gpricefrom Goodscreate view V_GoodsStore asselect Gname,GSnamefrom Goods,GoodsShelfwhere Goods.GSnum=GoodsShelf.GSnumcreate view V_GoodsLack asselect Gname,GSstock,GSlimitfrom Goodswhere GSstock=GSlimit create view VGoodsShelf10asselect GSname,Gname from GoodsShe

26、lf,Goods where GoodsShelf.GSnum=Goods.GSnumcreate view V_Work asselect ShopAssistant.Swtime,ShopAssistant.Sname,Administrator.Anamefrom ShopAssistant,Administratorwhere ShopAssistant.Swtime=Administrator.Awtimecreate view V_Phone(name,num,phone) as(select Sname,Snum,Sphonefrom ShopAssistant)union(se

27、lect Aname,Anum,Aphone from Administrator)create view V_Salary(name,num,salary) as(select Sname,Snum,Ssalaaryfrom ShopAssistant)union(select Aname,Anum,Asalary from Administrator)create view V_GoodsEnd asselect Gname,Gproducetime,Gtime,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goodscreate view V_G

28、oodsUnload asselect Gname,GoodsShelf.GSname,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goods,GoodsShelfwhere DateAdd(DAY,Gtime,Gproducetime)=GETDATE()and Goods.GSnum=GoodsShelf.GSnumcreate view v_BillExpense asselect Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpensefrom SellGoods,Goodswher

29、e SellGoods.Gnum=Goods.Gnum5、創(chuàng)建觸發(fā)器11(1)當(dāng)打印一張銷售發(fā)票時，即刪除數(shù)據(jù)庫中剛建立的銷售發(fā)票時，相應(yīng)商品在貨架上的現(xiàn)有存量減少，相應(yīng)會員總消費(fèi)額增加。其相應(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-sel

30、ect TotalExpense=TotalExpense from deleted-select Bnum=Bnum from deletedupdate Goodsset GSstock=GSstock-Quantity where Goods.Gname=Gname -update Member-set Mexpense=Mexpense+TotalExpense-where Bnum=SellBill.Bnum-and SellBill.Mnum=Mnum(2)當(dāng)從過期商品名單中刪除某記錄時，相應(yīng)商品現(xiàn)有存量減少 .其相應(yīng)SQL語句為：create trigger t2 on V_Go

31、odsUnloadINSTEAD OF delete asdeclare Gname char(10)select Gname=Gname from deleteddelete from Goodswhere Gname=Gname(3)打印一張退貨發(fā)票，即刪除退貨單中的記錄，表示R商品重新返回原貨架存 儲(假設(shè)脫貨商品不影響第二次銷售)，貨架商品現(xiàn)有存量增加.其相應(yīng)SQL語句為：create trigger t3 on ReturnGoods after delete asdeclare Gnum char(10), Rquantity intselect Gnum=Gnum from de

32、letedselect Rquantity from deletedupdate Goodsset GSstock=GSstock+Rquantity where Gnum=Gnum6、建立儲存過程(1)計(jì)算某個銷售單上，每種商品的累計(jì)銷售額(即輸出每種商品名稱，件數(shù)，12 單價，費(fèi)用小計(jì))。其相應(yīng)SQL語句為：create procedure p_TotalExpense(BS_Bnum varchar,Bnum varchar output,Gname varchar output,Bquantity int output,Gprice numeric output, TotalExpen

33、se numeric output) as selectBnum=Bnum,Gname=Gname,Bquantity=Bquantity,Gprice=Gprice,TotalExpense= (Bquantity*Gprice)from v_BillExpensewhere Bnum=BS_Bnum 其驗(yàn)證語句為： DeclareT_Bnum varchar,T_Gname varchar,T_Bquantity int,T_Gprice varchar,T_TotalExpense varcharExecute p_TotalExpense GS-0000004,T_Bnum outpu

34、t,T_Gname output,T_Bquantity output,T_Gprice output,T_TotalExpense output Print T_Bnum+ +T_Gname+ +T_Bquantity+ +T_Gprice+ +T_TotalExpense彳2)計(jì)算某個銷售單上，顧客消費(fèi)商品總數(shù)量和總消費(fèi)額。其相應(yīng)SQL語句為：create procedure p_BillExpenseBS_Bnum varchar asselect Bnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpense from v_BillExpense group by Bnum having Bnum=BS_B