某送水公司的送水系統(tǒng)-課程設(shè)計(jì)報(bào)告

數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)報(bào)告1學(xué)號(hào)《某送水公司的送水系統(tǒng)》課程設(shè)計(jì)報(bào)告題目：某送水公司的送水系統(tǒng)專業(yè)：網(wǎng)絡(luò)工程班級(jí)：姓名：指導(dǎo)教師：成績(jī)：計(jì)算機(jī)學(xué)院2017年12月8日設(shè)計(jì)內(nèi)容及要求1.1實(shí)現(xiàn)工作人員、客戶信息的管理；實(shí)現(xiàn)礦泉水類別和供應(yīng)商的管理；

實(shí)現(xiàn)礦泉水入庫管理和出庫管理；1.2創(chuàng)建觸發(fā)器，實(shí)現(xiàn)入庫、出庫時(shí)相應(yīng)類型礦泉水的數(shù)量的增加或減少;

創(chuàng)建存儲(chǔ)過程統(tǒng)計(jì)每個(gè)送水員工指定月份送水的數(shù)量；

創(chuàng)建存儲(chǔ)過程查詢指定月份用水量最大的前10個(gè)用戶，并按用水量遞減排列；

建立數(shù)據(jù)庫相關(guān)表之間的參照完整性約束。概要設(shè)計(jì)2.1需求分析隨著經(jīng)濟(jì)不斷發(fā)展，城市的不斷擴(kuò)張，城市居民的用水需求量也在不斷的攀高。為了適應(yīng)人們的消費(fèi)需求，近年來我國的飲用水市場(chǎng)發(fā)展極快。各地類似“送水公司”這樣的服務(wù)業(yè)也隨之不斷發(fā)展。各企業(yè)競(jìng)爭(zhēng)激烈，提高公司員工工作效率和服務(wù)質(zhì)量是制勝的重要因素，另一方面在于與客戶的溝通和公司自我形象的不斷完善。在用水旺季電話較多時(shí)由于接話員效率低，不可避免的會(huì)發(fā)生漏接電話等情況，失去一些定水客戶。在這種情況下，擁有一個(gè)專業(yè)的針對(duì)送水行業(yè)上述問題的軟件系統(tǒng)就成為同行業(yè)中競(jìng)爭(zhēng)發(fā)展的有利優(yōu)勢(shì)。另外，目前市場(chǎng)上這方面的軟件系統(tǒng)使用范圍比較有限，就算有使用的也很少涉及送水配送業(yè)務(wù)。目前大多數(shù)公司現(xiàn)行的管理系統(tǒng)雖然解決了公司業(yè)務(wù)上的處理問題，但在員工工作效率、客戶信息管理、信息查詢分析等方面不完善，需要進(jìn)一步的優(yōu)化系統(tǒng)處理業(yè)務(wù)流程和解決公司以往單人操作系統(tǒng)處理煩瑣事務(wù)等問題。本系統(tǒng)需完成的功能：工作人員、客戶的管理、礦泉水的類別（信息）、供應(yīng)商的信息、礦泉水入庫、出庫、收費(fèi)、進(jìn)貨、銷售管理。2.2系統(tǒng)功能結(jié)構(gòu)員工管理：新建、查詢、編輯、刪除員工信息、獎(jiǎng)懲、權(quán)限管理客戶管理：新建、查詢、編輯、刪除客戶信息礦泉水管理：新建、查詢、編輯、刪除礦泉水信息，實(shí)現(xiàn)時(shí)時(shí)更新供應(yīng)商管理：新建、查詢、編輯、刪除供應(yīng)商信息，實(shí)現(xiàn)時(shí)時(shí)更新以保證公司利益?zhèn)}庫管理：進(jìn)貨、出庫登記、商品庫存統(tǒng)計(jì)管理，生成商品積壓或缺貨報(bào)告單訂單管理：新建、查詢、訂單編輯、未發(fā)貨訂單顯示、發(fā)貨中訂單顯示、已完成訂單顯示、已取消訂單顯示財(cái)務(wù)管理：新建、查詢、編輯、刪除財(cái)務(wù)報(bào)表，統(tǒng)計(jì)、比較各級(jí)階段的財(cái)務(wù)情況進(jìn)貨管理：新建、查詢、編輯、刪除進(jìn)貨訂單信息，與供應(yīng)商的聯(lián)系，交易，員工人員分配銷售管理：新建、查詢、編輯、刪除銷售信息，與客戶的聯(lián)系，交易，員工人員分配，做好售后服務(wù)等2.3E-R圖礦泉水與倉庫的E-R圖進(jìn)貨E-R圖銷售E-R圖訂單處理E-R圖全局E-R圖將E-R圖轉(zhuǎn)換成關(guān)系模式，并注明主碼和外碼員工：{員工編號(hào)，姓名，職稱，電話，工資，業(yè)績(jī)，出勤}客戶：{客戶編號(hào)，姓名，地址，電話}公司：{公司編號(hào)，公司名稱，地址，聯(lián)系電話

}礦泉水：{產(chǎn)品編號(hào)，產(chǎn)品名稱，生產(chǎn)商，生產(chǎn)日期，產(chǎn)品種類，進(jìn)貨價(jià)格，銷售價(jià)格}訂單：{訂單編號(hào)，客戶編號(hào)，商品編號(hào)，訂單數(shù)量，訂單日期訂單處理情況，總金額}供應(yīng)商：{供應(yīng)商編號(hào)，單位名稱，地址，電話}費(fèi)用：{費(fèi)用編號(hào)，付費(fèi)方式，已付，金額}倉庫：{倉庫編號(hào)，倉庫名稱，倉庫容量}進(jìn)貨：{公司編號(hào)，員工號(hào)，訂單號(hào)，進(jìn)貨日期，進(jìn)貨數(shù)量，訂單處理狀態(tài)}存?。簕商品編號(hào)，倉庫編號(hào)，存取數(shù)量，存取日期}銷售：{公司編號(hào)，客戶編號(hào)，數(shù)量，產(chǎn)品編號(hào)，金額}2.4數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)：?jiǎn)T工含義說明：是送水系統(tǒng)進(jìn)行完成事務(wù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)員工的有關(guān)信息組成：?jiǎn)T工編號(hào)、姓名、職稱、電話、工資、業(yè)績(jī)、出勤數(shù)據(jù)結(jié)構(gòu)：客戶含義說明：是送水系統(tǒng)產(chǎn)生事務(wù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)客戶的有關(guān)信息組成：客戶編號(hào)、姓名、地址、聯(lián)系電話數(shù)據(jù)結(jié)構(gòu)：公司含義說明：是送水系統(tǒng)產(chǎn)生事務(wù)的另一方的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)公司的有關(guān)信息組成：公司編號(hào)、公司名稱、地址、聯(lián)系電話數(shù)據(jù)結(jié)構(gòu)：礦泉水含義說明：是送水系統(tǒng)主體數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)礦泉水的有關(guān)信息組成：產(chǎn)品編號(hào)、產(chǎn)品名稱、生產(chǎn)商、生產(chǎn)日期、產(chǎn)品種類、進(jìn)貨價(jià)格、銷售價(jià)格數(shù)據(jù)結(jié)構(gòu)：訂單含義說明：是送水系統(tǒng)礦泉水交易依據(jù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)訂單的有關(guān)信息組成：訂單編號(hào)、客戶編號(hào)、商品編號(hào)、訂單數(shù)量、訂單日期、訂單處理情況、總金額數(shù)據(jù)結(jié)構(gòu)：供應(yīng)商含義說明：是送水系統(tǒng)提供貨源的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)供應(yīng)商的有關(guān)信息組成：供應(yīng)商編號(hào)、單位名稱、地址、電話數(shù)據(jù)結(jié)構(gòu)：費(fèi)用含義說明：是送水系統(tǒng)訂單里包含的一部分?jǐn)?shù)據(jù)結(jié)構(gòu)，定義了一個(gè)費(fèi)用的有關(guān)信息組成：費(fèi)用編號(hào)、付費(fèi)方式、已付、金額數(shù)據(jù)結(jié)構(gòu)：倉庫含義說明：是送水系統(tǒng)礦泉水存儲(chǔ)地的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)倉庫的有關(guān)信息組成：倉庫編號(hào)、倉庫名稱、倉庫容量設(shè)計(jì)過程或程序代碼createtablebuy(w_idchar(8)notnull,c_idchar(8)notnull,order_idchar(8)notnull,order_numnumeric(36)null,oder_datedatenull,order_statevarchar(100)null,constraintPK_BUYprimarykey(w_id,c_id));createindexbeingbuy_FKonbuy(w_idASC);createindexbuy_FKonbuy(c_idASC);createtablebuyin(com_idchar(8)notnull,o_idchar(8)notnull,in_datedatenull,in_moneynumeric(8,2)null,in_numrealnull,in_goodsvarchar(100)null,in_pricenumeric(36)null,constraintPK_BUYINprimarykey(com_id,o_id));createindexbuyin_FKonbuyin(com_idASC);createindexsale_FKonbuyin(o_idASC);createtablecompany(com_idchar(8)notnull,c_idchar(8)notnull,com_namevarchar(50)null,com_telchar(11)notnull,com_addressvarchar(50)notnull,out_datedatenull,out_moneynumeric(30)null,out_numrealnull,out_goodsvarchar(100)null,constraintPK_COMPANYprimarykey(com_id),constraintAK_COM_ADDRESS_COMPANYunique(com_address));createindexout_store_FKoncompany(c_idASC);createuniqueindexcompany_AKoncompany(com_addressASC);createtablecusterm(c_idchar(8)notnull,c_namevarchar(50)null,c_addressvarchar(100)null,c_telnumeric(11)notnull,constraintPK_CUSTERMprimarykey(c_id));createtabledeal(w_idchar(8)notnull,c_idchar(8)notnull,e_idchar(8)notnull,deal_datedatenull,deal_numrealnull,constraintPK_DEALprimarykey(w_id,c_id,e_id));createindexdeal_FKondeal(e_idASC);createindexdeal2_FKondeal(w_idASC,c_idASC);createtableemployee(e_idchar(8)notnull,com_idchar(8)null,e_namevarchar(50)null,e_telchar(11)notnull,e_levealvarchar(50)null,e_wagenumeric(11)null,e_presentvarchar(100)null,constraintPK_EMPLOYEEprimarykey(e_id));createindexbelong__to_FKonemployee(com_idASC);createtablemoney(w_idchar(8)null,c_idchar(8)null,money_idchar(8)notnull,money_wayvarchar(100)null,money_insumnumeric(36)null,money_sumnumeric(36)null,order_idchar(8)null);createindexbelongto_FKonmoney(w_idASC,c_idASC);createtableoffer(o_idchar(8)notnull,o_namevarchar(50)null,o_addressvarchar(50)null,o_telvarchar(11)null,constraintPK_OFFERprimarykey(o_id),constraintAK_O_TADDRESS_OFFERunique(o_address));createtable"save"(s_idchar(8)notnull,s_namevarchar(50)null,s_numrealnotnull,constraintPK_SAVEprimarykey(s_id));createtablesave_produce(w_idchar(8)notnull,s_idchar(8)notnull,save_water_datedatenull,save_water_numrealnull,constraintPK_SAVE_PRODUCEprimarykey(w_id,s_id));createindexsave_produce_FKonsave_produce(w_idASC);createindexsave_produce2_FKonsave_produce(s_idASC);createtablewater(w_idchar(8)notnull,w_namevarchar(50)null,w_kindvarchar(50)null,w_producevarchar(50)null,w_timedatenotnull,in_pricenumeric(36)null,out_pricenumeric(36)null,constraintPK_WATERprimarykey(w_id));altertablebuyinaddconstraintFK_BUYIN_SALE_OFFERforeignkey(o_id)referencesoffer(o_id)altertablecompanyaddconstraintFK_COMPANY_OUT_STORE_CUSTERMforeignkey(c_id)referencescusterm(c_id)altertabledealaddconstraintFK_DEAL_DEAL_EMPLOYEEforeignkey(e_id)referencesemployee(e_id)altertabledealaddconstraintFK_DEAL_DEAL2_BUYforeignkey(w_id,c_id)referencesbuy(w_id,c_id)altertableemployeeaddconstraintFK_EMPLOYEE_BELONG__T_COMPANYforeignkey(com_id)referencescompany(com_id)altertablemoneyaddconstraintFK_MONEY_BELONGTO_BUYforeignkey(w_id,c_id)referencesbuy(w_id,c_id)altertablesave_produceaddconstraintFK_SAVE_PRO_SAVE_PROD_WATERforeignkey(w_id)referenceswater(w_id)altertablesave_produceaddconstraintFK_SAVE_PRO_SAVE_PROD_SAVEforeignkey(s_id)references"save"(s_id)4設(shè)計(jì)結(jié)果與分析4.1測(cè)試數(shù)據(jù)insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111110','龍井水','茶水','長(zhǎng)春','17-6月-2011',4,10);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111111','農(nóng)夫山泉','山泉','哈爾濱','05-9月-2014',2,14);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111112','純凈水','山泉','沈陽','13-5月-2011',12,3);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111113','娃哈哈','牛奶','長(zhǎng)春','26-4月-2012',14,9);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111114','冰紅茶','飲料','北京','06-10月-2013',6,7);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111115','冰糖雪梨','飲料','秦皇島','20-4月-2014',8,16);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111116','白水','山泉','哈爾濱','16-10月-2012',5,17);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111117','可樂','飲料','長(zhǎng)春','06-11月-2012',16,1);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111118','美年達(dá)','飲料','佳木斯','24-10月-2014',3,2);insertinto"water"("w_id","w_name","w_kind","w_produce","w_time","in_price","out_price")values('11111119','咖啡','飲料','牡丹江','01-11月-2013',10,18);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111110','小王','上海',16);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111111','小李','北京',12);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111112','小趙','佳木斯',1);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111113','小錢','大慶',17);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111114','小范','哈爾濱',11);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111115','小鄧','沈陽',4);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111116','小劉','牡丹江',2);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111117','小張','大連',15);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111118','小吳','哈爾濱',0);insertinto"custerm"("c_id","c_name","c_address","c_tel")values('21111119','小馬','沈陽',14);insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111110','21111110','51111110',19,'19-10月-2013','簽約');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111110','21111111','51111111',3,'13-8月-2013','簽約');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111112','21111111','51111112',11,'25-2月-2012','處理中');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111113','21111112','51111113',5,'28-3月-2014','已完成');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111114','21111113','51111114',17,'23-9月-2012','已完成');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111114','21111115','51111115',15,'18-3月-2013','欠款中');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111116','21111116','51111116',7,'15-11月-2014','處理中');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111117','21111117','51111117',18,'20-9月-2013','處理中');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111114','21111116','51111118',13,'14-9月-2012','已完成');insertinto"buy"("w_id","c_id","order_id","order_num","oder_date","order_state")values('11111119','21111118','51111119',8,'15-3月-2013','欠款中');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111110','11111110','21111110','現(xiàn)金',14,18,'51111110');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111111','11111110','21111111','現(xiàn)金',9,11,'51111111');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111112','11111112','21111111','信用卡',5,18,'51111112');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111113','11111113','21111112','信用卡',6,23,'51111113');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111114','11111114','21111113','信用卡',11,29,'51111114');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111115','11111114','21111115','支票',15,30,'51111115');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111116','11111116','21111116','支票',7,19,'51111116');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111117','11111117','21111117','支票',16,32,'51111117');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111118','11111114','21111116','現(xiàn)金',19,25,'51111118');insertinto"money"("money_id","w_id","c_id","money_way","money_insum","money_sum","order_id")values('61111119','11111119','21111118','現(xiàn)金',8,22,'51111119');insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111110','21111110','71111110','20-10月-2013',12);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111110','21111111','71111110','20-10月-2013',3);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111112','21111111','71111111','16-1月-2012',10);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111113','21111112','71111112','17-1月-2014',1);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111114','21111113','71111113','06-3月-2012',15);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111114','21111115','71111114','15-9月-2012',18);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111116','21111116','71111115','05-3月-2013',9);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111117','21111117','71111116','25-2月-2014',0);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111114','21111116','71111116','02-12月-2012',17);insertinto"deal"("w_id","c_id","e_id","deal_date","deal_num")values('11111119','21111118','71111117','29-4月-2012',4)4.2結(jié)論

該數(shù)據(jù)庫具有員工管理、客戶信息管理、礦泉水管理、倉庫管理、訂單管理、財(cái)務(wù)管理、進(jìn)貨管理、銷售管理功能，由最初的問題描述，需求分析到后面的概念模型設(shè)計(jì)、邏輯模型設(shè)計(jì)、物理設(shè)計(jì)實(shí)現(xiàn)具體的數(shù)據(jù)庫表的創(chuàng)建、數(shù)據(jù)插入、查詢、修改、刪除功能，觸發(fā)器和存儲(chǔ)過程的創(chuàng)建，數(shù)據(jù)的測(cè)試，數(shù)據(jù)庫的備份與恢復(fù)，由抽象空間到物理空間一步一步地實(shí)現(xiàn)，可以說是還是比較艱難的。1.

功能的描述和需求分不開，感覺兩者沒什么區(qū)別，導(dǎo)致開始進(jìn)入困惑中，后經(jīng)查相關(guān)資料后仍然沒弄清楚，干脆直接做需求分析，略過功能描述。2.

powerdesinger

里E-R沒認(rèn)識(shí)清楚，不知道概念模型設(shè)計(jì)里畫的就是E-R圖，還有就是外鍵的處理，自己在實(shí)體的聯(lián)系里添加了外鍵，導(dǎo)致模型檢測(cè)出現(xiàn)錯(cuò)誤，外鍵是由聯(lián)系自動(dòng)創(chuàng)建添加，不需要自己添加。在設(shè)計(jì)中遇到的最大的問題就是檢測(cè)模型沒有錯(cuò)，在轉(zhuǎn)換的時(shí)候出現(xiàn)錯(cuò)，整了一下午都沒頭緒，心里煩躁了。果斷決定將所有實(shí)體重寫，比對(duì)，最后發(fā)現(xiàn)offer（供應(yīng)商）實(shí)體沒設(shè)置主鍵，在與送水公司的關(guān)聯(lián)（進(jìn)貨）中無法實(shí)現(xiàn)。在進(jìn)行模型設(shè)計(jì)中學(xué)會(huì)了變通，不一定按照開始想好的思路，出現(xiàn)錯(cuò)誤后分析錯(cuò)誤并改正，利用好網(wǎng)絡(luò)平臺(tái)，隨時(shí)查找所需信息。3.

觸發(fā)器的創(chuàng)建和過程存儲(chǔ)的實(shí)現(xiàn)，在學(xué)習(xí)PL/SQL時(shí)就稀里糊涂的，剛接觸時(shí)想通過copy

網(wǎng)上的，后來發(fā)現(xiàn)網(wǎng)上的寫的亂糟糟，也不符合要求，最后還是自己重新把PL/SQL相關(guān)內(nèi)容拿出來邊學(xué)邊用，經(jīng)過不懈努力，終于搞定了。4.

數(shù)據(jù)字典的編寫，以前為了考試基本就知道數(shù)據(jù)字典包含哪些內(nèi)容，不知道具體怎樣編寫，而且有的還不知道有些到底說的什么意思，在這次的練習(xí)中，我