數(shù)據庫專題訓練(2012)

1、專題訓練 科 目： 數(shù)據庫專題訓練題 目： 專 業(yè)： 指導教師： 學生姓名： 班級學號： 年 月 日 1 需求分析1.1 項目概述隨著超市的出現(xiàn)，為實現(xiàn)超市管理的系統(tǒng)化、規(guī)范化和自動化，超市管理系統(tǒng)應運而生.它依靠現(xiàn)代化的計算機信息處理技術來管理超市，這能夠快速反映出商品的進銷存狀況和各種反饋信息分析，使管理人員快速對市場的變化做出相應決策，加快超市的系統(tǒng)建立的技術基礎;為超市提供方便，快捷的結賬體系，準確，高效的庫存和財務管理系統(tǒng)等;讓顧客在超市購物更加快捷方便，讓超市的管理和運行工作更加完善，方便。1.2 功能需求1.2.1 信息要求該系統(tǒng)由商品信息管理模塊，員工信息管理模塊，供應商信息管

2、理模塊，倉庫信息管理模塊等可以完成商品的進銷存和查詢，更改等的基本操作。并根據客戶的要求完善系統(tǒng)的功能需求。系統(tǒng)功能有：(1)商品信息的管理包括商品基本信息的新建、修改和刪除，商品的編號、商品名稱、商品規(guī)格等（2）供貨商信息管理：包括供貨商信息的新建、修改和刪除和供應商的名稱、供應商代號、供應商地址等。（3）員工信息管理：系統(tǒng)管理員的添加、刪除和修改密碼和員工姓名、性別、職稱、工號等。（4）倉庫信息管理：倉庫號、庫存商品號、商品數(shù)量等1.2.2 處理要求(1)針對超市進銷存管理系統(tǒng)，分別對采購部門、銷售部門和庫存保管部門進行詳細的調研和分析，總結出如下的需求信息：商品按類管理，所以需要有一商品

3、類型信息。如果一個商品類型存在商品，或存在下級商品類型，則該類型不可刪除。需要記錄供應商品信息。在涉及商品數(shù)量的地方，需要知道商品的庫存地方。商品銷售信息單中要包含登記商品銷售數(shù)量、單價等信息。在進貨信息中要包含商品供應商等信息。商品報損要有報損原因。進貨、銷售、報損操作要有相應信息管理員。只有管理員登錄之后才可以使用系統(tǒng)。默認的管理員不可以刪除。進貨、銷售、庫存、報損信息都要可以添加、修改、刪除、分類查找。當進行進貨、銷售和報損操作后，能相應更新庫存。(2)經上述系統(tǒng)功能分析和需求總結，考慮到將來功能的擴展，設計如下的數(shù)據項和數(shù)據結構：商品類型信息，包括數(shù)據項有：商品信息，包括的數(shù)據項有：商

4、品編號、商品名稱、商品的的生產日期、庫存量等。商供應商信息，包括供應商號、供應商名稱、聯(lián)系電話等。進貨信息，包括進貨商品號、數(shù)量、規(guī)格、單價等。銷售信息，包括銷售商品、數(shù)量、單價等。報損信息，包括報損商品、數(shù)量、原因、登記時間等。員工信息，包括員工號、姓名、職稱等1.2.3 系統(tǒng)要求（1） 精度 價錢精確到分,時間精確到秒，響應時間在0.5秒內。數(shù)據錄入精確，計算準確，同時與后臺庫存管理系統(tǒng)的數(shù)據保持一致性即可。（2） 時間特性要求 錄入并更新數(shù)據的時間正常情況下少于2秒,與庫存管理系統(tǒng)保持同步更新的時間越低越好，實現(xiàn)商品的銷售和記錄的高度統(tǒng)一 數(shù)據傳送時間盡量縮短. （3） 靈活性 對于該系

5、統(tǒng)，最大的問題在于當停電的時候如何解決銷售和庫存記錄的同步性。其一：在停電銷售未記錄入數(shù)據庫時，先由人工記錄，然后等電來的時候再人工手動錄入未存數(shù)據 其二：脫機處理，即當停電的時候，收銀臺能夠正常工作，但后臺庫存管理系統(tǒng)失效，則由前臺銷售系統(tǒng)暫時代行記錄任務，等庫存管理系統(tǒng)恢復工作后，再向庫存導入銷售數(shù)據，實現(xiàn)更新（針對大規(guī)模，多個消費服務點的超市平臺）可擴展性用JAVA進行實現(xiàn)，進而有良好的可擴展性，程序使用大量重載和多態(tài)，留有接口。對以后擴展留下豐富空間。安全性OS登陸、退出、換班與操作鎖定等權限驗證保護；斷電自動保護最大限度防止意外及惡意非法操作。易用性針對銷售員，執(zhí)行簡單，一切都是自動

6、，提示。針對普通查詢用戶使用智能、便捷的方法操作系統(tǒng)；針對系統(tǒng)管理員，只要對其進行簡單的培訓操作，就能解決在操作超市管理系統(tǒng)實際中所遇到的問題。對于技術人員來說，有良好的擴展性和維護性?？傮w來說本系統(tǒng)是一個面向各個領域用戶的簡單的軟件。輸入信息錯誤或不滿足條件時會給出錯誤提示信息，使各個管理員操作的十分簡單?？删S護性系統(tǒng)組成簡單，各個模塊間的獨立性很高，易于維護。1.3 數(shù)據流圖以下是超市管理系統(tǒng)的業(yè)務流程的分析（1） 超市管理系統(tǒng)業(yè)務流程圖的符號解釋 業(yè)務處理單位/個人 清單/報表 信息流程（2）超市管理系統(tǒng)進貨業(yè)務流程圖為圖11為某超市進銷存管理系統(tǒng)的進貨業(yè)務流程圖(3)超市管理系統(tǒng)銷售業(yè)

7、務流程圖圖12為某超市進銷存管理系統(tǒng)的銷售業(yè)務流程圖根據上面業(yè)務流程圖的描述，從系統(tǒng)的科學性、管理的合理性、實際運行的可行性角度出發(fā)，自頂向下對系統(tǒng)進行分解，導出了超市銷售管理系統(tǒng)的頂層數(shù)據流程圖、第二層數(shù)據流程圖和第三層數(shù)據流程圖。（1）某超市管理系統(tǒng)數(shù)據流程圖符號解釋 1、外部實體 2、處理 3、數(shù)據流 4、數(shù)據存儲（2）某超市管理系統(tǒng)頂層數(shù)據流程圖圖13為某超市管理系統(tǒng)頂層數(shù)據流程圖上圖是超市管理系統(tǒng)的頂層數(shù)據流程圖。由業(yè)務流程圖確定系統(tǒng)開發(fā)的外部實體即系統(tǒng)數(shù)據的來源與去處，從而確定了整個系統(tǒng)的外部實體和數(shù)據流，在頂層數(shù)據流程圖中，把超市管理系統(tǒng)作為一個處理環(huán)節(jié)，與實體間傳遞信息，簡單的

8、表達系統(tǒng)的各部分功能。（3）第一層數(shù)據流程圖圖為14為超市進銷存管理系統(tǒng)第一層數(shù)據流圖確定系統(tǒng)的主要處理功能，可以將系統(tǒng)分為進貨管理、庫存管理、銷售管理等幾部分。再確定各個輸入和輸出的數(shù)據流的以及與之相關的數(shù)據結構，為各個數(shù)據流、報表命名并編碼，從而得到超市銷售進銷存系統(tǒng)的第一層數(shù)據流程圖，如圖上圖所示 （4）第二層數(shù)據流程圖圖15為為再將第一層數(shù)據流程圖分解就可得到第二層流程圖。其中某些數(shù)據項處理可分解為多個處理過程。在本系統(tǒng)中有銷售模塊、進貨管理。每一部分都可以分解為第二層流程圖。具體的分解情1.4 數(shù)據字典數(shù)據項如表一數(shù)據項編號數(shù)據項名稱簡述類型及寬度取值范圍I1TID標識商品的代號Ch

9、ar（8）I2Tname某商品的名稱Char（14）I3TPrice某商品的價值IntI4Tproducedate某商品生產的時間Char(6)I5TKeepdate某商品的保存時間Char(2)I6TWeight某商品的重量Char(8)I7TProducename某商品的生產公司Char(14)I8TNorms某商品長寬Char(6)I9SName某商品的公司名稱Char(12)I10SAddress某商品生產的地方Char(16)I11SCodename某供應商的產品代號Char(8)I12SFax與供應商的傳真聯(lián)系方式Char(11)I13STele聯(lián)系供應商的電話方式Char(11)

10、I14SDate定貨單位要求的供貨日期Char(12)I15SOrder發(fā)出訂單的單據號Char(8)I16YID標識公司員工的代碼Char(6)I17Yname公司員工的姓名Char(6)I18YSex公司員工的性別Char(1)I19YAge公司員工的年齡Char(2)I20YZhichen公司的員工的職位名稱Char(6)I21KNO存儲商品的庫存號char（2）I22KNum某種商品當前的庫存量Char(4)I23KHnum商品的庫存上限CharI24KDnum商品的庫存下限CharI25KPnum盤盈盤虧數(shù)量IntI26KPerson與定貨發(fā)生聯(lián)系的供應商負債人Char(8)數(shù)據結構

11、如表二DS08-01：用戶訂單的數(shù)據結構DS08-02：訂貨單標識I1：定貨單編號DS08-03：用戶情況DS08-04：配件情況I2：日期I3：用戶代碼I10：配件代碼I4:用戶名稱I11：配件名稱I5：用戶地址I12：配件規(guī)格I6：用戶姓名I13：訂貨數(shù)量I7：電話I8：用戶銀行I9：帳號數(shù)據流編碼如表三數(shù)據流編號數(shù)據流名稱簡述數(shù)據流來源數(shù)據流去向數(shù)據流組成數(shù)據流量高峰流量F1訂貨單公司向供應商訂購商品的訂貨單驗收貨品模塊供應商商品編碼+商品名稱+單位+數(shù)量+單價+交貨日期+定單號550000份/月950000份/月F2發(fā)貨單供應商發(fā)出的收貨單供應商驗收貨品模塊商品編碼+商品名稱+單位+數(shù)

12、量+單價+訂單號+供應商550000份/月950000份/月F3不合格單采購部驗貨不合格單驗收貨品模塊退貨模塊商品編碼+商品名稱+單位+數(shù)量+單價+說明原因100份/月500份/月F4退貨單庫管員開出的退貨單退貨模塊供應商商品編碼+商品名稱+單位+數(shù)量+單價+退貨說明100份/月500份/月F5合格單采購部驗貨合格單驗收貨品模塊進庫模塊商品編碼+商品名稱+單位+數(shù)量+單價+合格說明550000份/月950000份/月F6入庫單庫管員開出的入庫單進庫模塊庫存臺帳商品編碼+商品名稱+單位+數(shù)量+單價+供應商550000份/月9500000份/月外部實體編碼如表四外部實體編號外部實體名稱簡述外部實體

13、組成輸入的數(shù)據流輸出的數(shù)據流E1供應商提供商品的直接廠家供應商編碼+單位全稱+簡稱+地址+聯(lián)系人+電話+帳號訂貨單發(fā)貨單E2顧客購買商品的顧客編碼+姓名+地址+電話+郵編銷售單顧客訂單E3倉庫存放倉庫的地方單位全稱+地址+電話實物清單報損單 處理邏輯如表五處理邏輯編號處理邏輯名稱簡述輸入的數(shù)據流處理輸出的數(shù)據流處理頻率P1.1驗收貨品采購部對來貨進行檢驗F2收貨單，來源于供應商發(fā)出的采購收貨單據根據收貨單驗貨，確定供應商提供貨品的數(shù)據、質量等是否合格F1訂貨單，向工業(yè)商店訂貨：F3不合格單，向供應商退貨：F5合格單，入庫1500次/月P1.2進貨采購的合格貨品入庫F5貨物合格單，來源于庫管員根

14、據入庫單槳采購貨品入庫，登記庫存和流水賬S2庫存臺帳、S1流水帳，每次入庫都要有詳細登記2000次/月P1.3退貨辦理退貨手續(xù)F3不合格單，來源于貨物驗收人員根據不合格單辦理退貨，并登記流水賬S1流水賬，F(xiàn)4退貨單將貨物退還給供應商100次/月P2.1銷售貨物隊顧客購物情況進行開單F7顧客訂單，來源于顧客的購物請求：庫存臺帳以供查詢根據數(shù)據流顧客訂單，導購員進行為顧客選貨物，消費記賬等F8銷售單，給顧客一份；F9出庫單；同時根據出庫單登記庫存臺帳及流水賬5000次/天P2.2確定退貨單對顧客請求的退貨單進行退貨確認F10顧客退單，來源于顧客的要求根據數(shù)據流顧客退單，主管就顧客提出原因進行退貨確

15、認F11確認后的顧客退貨單，經銷售主管批準后，為顧客辦理退貨100次/天P2.3退貨根據已確認的退單進行退貨F11已確認的顧客退單，來源于銷售主管已批準的退貨單根據批準退貨單，允許顧客退貨，并修改相關的庫存記錄F11庫存臺帳及流水賬，修改庫存臺帳及流水賬100次/天 數(shù)據存取如表六數(shù)據存取編號數(shù)據存儲名稱簡述數(shù)據存儲組成相關聯(lián)的處理S1庫存臺帳商品入庫、出庫及退貨后修改當前庫存量商品編碼+商品名稱+單位+當前庫存+商品下限+商品上限 P1.2、P1.3S2流水賬入庫、出庫及退貨的流水賬記錄類型+開單日期+單號+商品編號+商品名陳+單位+數(shù)量+單價P1.22 概念結構設計2.1 局部ER圖設計2

16、.1.1 分E-R圖建立根據第二層數(shù)據流程圖得到三個分E-R圖圖21為某超市進貨的ER圖圖22為超市銷售的ER圖圖23為超市存儲ER圖2.2 整體ER圖設計2.1.2 系統(tǒng)ER圖3 邏輯結構設計3.1 邏輯設計邏輯結構設計的任務就是把概念結構設計階段設計好的基本ER圖轉換為與選用的DBMS產品所支持的數(shù)據模型相符合的邏輯結構。從理論上講，設計邏輯結構應該選擇最適合于相應概念結構的數(shù)據模型，然后對支持這種數(shù)據模型的各種DBMS進行比較，從中選出最合適的DBMS。但實際情況往往是已給定了某種DBMS，設計人員沒有選擇的余地。設計邏輯結構時一般要分3步進行：（1）將概念結構轉換為一般的關系、網狀、層

17、次模型；（2）將轉換來的關系、網狀、層次模型向特定DBMS支持下的數(shù)據模型轉換；（3）對數(shù)據模型進行優(yōu)化。3.1.1 建立關系模式有一個超市管理系統(tǒng)數(shù)據庫，包括T、S、Y、K、TY、SK、TSYK個關系模式：T（TID，Tname，TPrice，Tproducedate，TKeepdate，TWeight，TNorms,TProducename）；S（SCodename ,SName，SAddress， SFax，Stele，SDate，SOrder）；Y(YID，YName，YSex，YAge，YZhichen)；K(KNo，KNum,KHnum,KDnum,KPnum,KPerson)；K

18、T(KNo，TID,QTY)TY（TID，YID，QTY）；SK（SNo，KNo,CQTY）；TSYK（TID，SName，YID，KNo，WQTY）1. 商品信息表T由商品編號（TID）、商品名稱（Tname）、商品單價（TPrice）、生產日期（Tproducedate）、保質期（TKeepdate）、商品重量（TWeight）、商品規(guī)格（TNorms）組成；2. 供應商信息表S由供應商名稱（SName）、供應商地址（SAddress）、供應商帳號（SCodename）、供應商傳真（SFax）、供應商電話（Stele）、交貨日期（SDate）、訂單號（SOrder）；3. 員工信息表Y由員

19、工編號（YID）、員工姓名（YName）、員工的性別（YSex）、員工的年齡（YAge）、員工的職稱（YZhichen）；4. 庫存信息表K由庫存號（KNo）現(xiàn)有庫存（KNum）、最高庫存（KHnum）、最低庫存（KDnum）、盈虧數(shù)量（KPnum）、聯(lián)系人（KPerson）組成；5. KT表是由存儲商品的庫存號（KNO），標識商品的代號（TID），某商品的數(shù)量（QTY）組成;6. YT由標識公司員工的代碼（YID），標識商品的代號(TID)，銷售的商品數(shù)量（CQTY）組成；7. ST供應商商品表由某供應商的產品代號（SCodename），標識商品的代號（TID），8. TSYK供應商商品員工

20、倉庫表由商品編號（TID）、供應商名稱（SName）、庫存號（KNo）、員工編號（YID）實際的商品數(shù)量(WQTY)組成；3.1.2 關系模式規(guī)范化處理根據F，分析每一個關系模式是否滿足3NF，對不滿足3NF的關系模式要進行規(guī)范化處理。3.1.3 用戶子模式建立對不同級別的用戶定義不同的view，如下圖用戶對象視圖描述作用管理員購買商品（商品編號，商品名稱，商品價格）用于顧客和管理者對購買商品情況的查詢管理員供應商（供應商編號，名稱，地址，電話，交貨日期）用于管理者對所有供應商信息的查詢管理員存放（倉庫號，商品現(xiàn)存量，商品最高數(shù)量，商品最低量）用于管理員查詢商品的簡要的信息管理員員工（員工姓名

21、，年齡，職稱）用于管理員查詢員工的基本信息表1為視圖表4 物理結構設計建立索引如下表：表2為索引表所在表名索引名稱索引類型所在表的屬性供應商SupInfo 唯一Pid商品信息表teleTwo聚簇Tprice倉庫信息Storetele聚簇InTime+OutTime員工信息ADBuyInfo聚簇BuyDate+BuyNum建立存儲過程如表：表4.2 為儲存過程表存儲過程名作用Telefind查詢商品基本信息Sum_money統(tǒng)計商品銷售情況CteleInfo顧客查詢商品信息Spp查詢供應商信息Up_TeleInfo更新商品信息表5 數(shù)據庫的實施5.1 創(chuàng)建數(shù)據庫商品信息表（Goods_Info）

22、用于存儲商品的信息。表中各字段功能定義如下表所示字段名字段類型長度主/外鍵字段值約束對應中文名GoodsIDint4PNot null商品編號GoodsNameVarchar50Not null商品名稱GoodsPriceMoney4Not null價格GoodsNumInt4Not null庫存數(shù)量CautionNumInt4Not null庫存報警數(shù)量PlanNumInt4null計劃進貨數(shù)BarCodeVarchar50Not null條形碼SalesProPriceMoney4Not null促銷價格SalesProDateSDatetime8Not null促銷起日期SalesPro

23、DateEDatetime8Not null促銷止日期AllowAbateInt4Not null允許打折AllowSaleInt4Not null允許銷售FactoryIDVarchar10FNot null廠商編號ProvideIDVarchar10FNot null供貨商編號用戶表(User)用于存儲用戶的基本登錄信息。表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名UserIDvarchar10PNot null用戶編號UserNameVarchar25Not null用戶名稱UserPWVarchar50Not null用戶密碼UserStyleInt4N

24、ot null用戶類型會員表(Menber)用于儲存會員的各種信息，表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名MemberIDVarchar10PNot null會員編號MemberCardVarchar20Not null會員卡號MemberCostMoney4Not null會員卡余額RegDateDatetime8Not null注冊日期銷售表(Sale) 用于存儲銷售信息。表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名SaleIDVarchar10PNot null銷售編號MerChIDVarchar10FNot null

25、商品編號SaleDateDatetime8Not null銷售日期SaleNumInt4Not null銷售數(shù)量SalePriceMoney4Not null銷售單額交易表(Dealing) 用于儲存交易信息。表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名DealingIDVarchar10PNot null交易編號DealingPriceMoney4Not null交易金額DealingDateMoney4Not null交易日期MemberIDVarchar10Not null會員卡號UserNameVarchar10FNot null用戶名稱字段名字段類型長

26、度主/外鍵字段值約束對應中文名ProvideIDvarchar10PNot null供貨商編號入庫紀錄表(Stock) 用于儲存商品入庫的詳細信息。表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名StockIDVarchar10PNot null入庫編號MerchIDVarchar10FNot null入庫商品編號MerchNumInt4Not null入庫數(shù)量MerchPriceMoney4Not null單額TotalPriceMoney4Not null總額StockDateDatetime8Datetime入庫日期PlanDateDatetime8Datet

27、ime計劃進貨日期StockStateInt4Not null入庫狀態(tài)供貨商表(Provide) 用于存儲供貨商的信息。表中各字段功能定義如下表所示。字段名字段類型長度主/外鍵字段值約束對應中文名ProvideIDvarchar10PNot null供貨商編號ProvideNameVarchar50Not null供貨商名稱ProvideAddressVarchar250Not null供貨商地址ProvidePhoneVarchar25Not null供貨商電話5.2 數(shù)據載入5.2.1基本表的查詢測試如下5.2.1視圖的測試如下5.2.3觸發(fā)器的測試如圖圖1.3為商品表上的觸發(fā)圖1.4為商

28、品表的觸發(fā)圖1.5為商品信息表的觸發(fā)5.2.4存儲過程的測試如下圖1.6為商品信息的存儲過程圖1.7為庫存信息的存儲過程圖1.8為供應商信息的存儲過程 圖1.9為員工信息的存儲過程5.3 主要SQL腳本（1）創(chuàng)建數(shù)據庫create database 超市管理系統(tǒng)數(shù)據庫（2）創(chuàng)建基本表create table T(TID Char(8) primary key,TProducename Char(14),Tname Char(14),TPrice Int,Tproducedate Char(6),TKeepdate Char(2),TWeight Char(8),TNorms Char(12),

29、)create table S（SCodename Char(8) primary key,SName Char(12),SAddress Char(16),SFax Char(11),STele Char(11),SDate Char(12),)create table Y（YID Char(6) primary key,Yname Char(6),YSex Char(1),YAge Char(2),YZhichen Char(6),)create table K（KNO char(2) primary key,KNum Char(4),KHnum Char(112),KDnum Char(

30、12),KPnum Int,KPerson Char(8),)create table TY（KNO char(8),TID Char(8),QTY char(6),)create table ST(SCodename Char(8),TID Char（8）,GQTY char（6）,)create table KT(KNO char（2）,TID Char（8）,QTY int,)create table TSYK(SCodename Char(8),YID Char(6),KNO char（2）,TID Char（8）,WQTY int,)（3）創(chuàng)建視圖購買商品信息視圖CREATE VIE

31、W VIEW_VTASSELECT TID,Tname,TPrice,FROM T供應商信息視圖CREATE VIEW VIEW _VSASSELECT SName,SAddress,SCodename,Stele,SDateFROM S倉庫信息視圖CREATE VIEW VIEW _VCASSELECT KNO,KNum,KHnum,KDnumFROM K員工信息視圖CREATE VIEW VIEW _DHASSELECT Yname,Yage，YzhichenFROM YWHERE YID=P006(4)創(chuàng)建觸發(fā)器在商品信息表上創(chuàng)建觸發(fā)器提醒信息管理員相應的操作create trigger

32、 tri_delete_teleon Tfor delete asprint 請在倉庫中刪除此商品在倉庫信息表上建立觸發(fā)器create trigger tri_S_updateon Kfor update asif (select TName from Twhere TID = F650) = 0print 此商品存放倉庫已滿在供應商信息表上建立觸發(fā)器create trigger tri_update_Steleon Sfor updateasif (select TIDfrom Twhere TID= I112)100print 存貨不足 請及時進貨(5) 建立儲存過程1、商品信息查詢的存儲過程：create