1、精選優(yōu)質(zhì)文檔-傾情為你奉上學(xué)號某送水公司的送水系統(tǒng)課程設(shè)計(jì)報(bào)告題目：某送水公司的送水系統(tǒng)專業(yè)：網(wǎng)絡(luò)工程班級：姓名：指導(dǎo)教師：成績：計(jì)算機(jī)學(xué)院2017年12月8日1 設(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)建存儲過程統(tǒng)計(jì)每個(gè)送水員工指定月份送水的數(shù)量； 創(chuàng)建存儲過程查詢指定月份用水量最大的前10個(gè)用戶，并按用水量遞減排列；    建立數(shù)據(jù)庫相關(guān)表之間的參照完整性約束。2

2、 概要設(shè)計(jì) 2.1 需求分析隨著經(jīng)濟(jì)不斷發(fā)展，城市的不斷擴(kuò)張，城市居民的用水需求量也在不斷的攀高。為了適應(yīng)人們的消費(fèi)需求，近年來我國的飲用水市場發(fā)展極快。各地類似“送水公司”這樣的服務(wù)業(yè)也隨之不斷發(fā)展。各企業(yè)競爭激烈，提高公司員工工作效率和服務(wù)質(zhì)量是制勝的重要因素，另一方面在于與客戶的溝通和公司自我形象的不斷完善。在用水旺季電話較多時(shí)由于接話員效率低，不可避免的會發(fā)生漏接電話等情況，失去一些定水客戶。在這種情況下，擁有一個(gè)專業(yè)的針對送水行業(yè)上述問題的軟件系統(tǒng)就成為同行業(yè)中競爭發(fā)展的有利優(yōu)勢。另外，目前市場上這方面的軟件系統(tǒng)使用范圍比較有限，就算有使用的也很少涉及送水配送業(yè)務(wù)。目前大多數(shù)公司現(xiàn)行

3、的管理系統(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)員工管理：新建、查詢、編輯、刪除員工信息、獎懲、權(quán)限管理客戶管理：新建、查詢、編輯、刪除客戶信息礦泉水管理：新建、查詢、編輯、刪除礦泉水信息，實(shí)現(xiàn)時(shí)時(shí)更新供應(yīng)商管理：新建、查詢、編輯、刪除供應(yīng)商信息，實(shí)現(xiàn)時(shí)時(shí)更新以保證公司利益?zhèn)}庫管理：進(jìn)貨、出庫登記、商品庫存統(tǒng)計(jì)管理，生成商品積壓或

4、缺貨報(bào)告單訂單管理：新建、查詢、訂單編輯、未發(fā)貨訂單顯示、發(fā)貨中訂單顯示、已完成訂單顯示、已取消訂單顯示財(cái)務(wù)管理：新建、查詢、編輯、刪除財(cái)務(wù)報(bào)表，統(tǒng)計(jì)、比較各級階段的財(cái)務(wù)情況進(jìn)貨管理：新建、查詢、編輯、刪除進(jìn)貨訂單信息，與供應(yīng)商的聯(lián)系，交易，員工人員分配銷售管理：新建、查詢、編輯、刪除銷售信息，與客戶的聯(lián)系，交易，員工人員分配，做好售后服務(wù)等2.3 E-R圖礦泉水與倉庫的E-R圖進(jìn)貨E-R圖銷售E-R圖訂單處理E-R圖 全局E-R圖 將E-R圖轉(zhuǎn)換成關(guān)系模式，并注明主碼和外碼員工：員工編號，姓名，職稱，電話，工資，業(yè)績，出勤客戶：客戶編號，姓名，地址，電話公司：公司編號，公司名稱，地址，聯(lián)系電

5、話 礦泉水：產(chǎn)品編號，產(chǎn)品名稱，生產(chǎn)商，生產(chǎn)日期，產(chǎn)品種類，進(jìn)貨價(jià)格，銷售價(jià)格訂單：訂單編號，客戶編號，商品編號，訂單數(shù)量，訂單日期訂單處理情況，總金額供應(yīng)商：供應(yīng)商編號，單位名稱，地址，電話費(fèi)用：費(fèi)用編號，付費(fèi)方式，已付，金額倉庫：倉庫編號，倉庫名稱，倉庫容量進(jìn)貨：公司編號，員工號，訂單號，進(jìn)貨日期，進(jìn)貨數(shù)量，訂單處理狀態(tài)存取：商品編號，倉庫編號，存取數(shù)量，存取日期銷售：公司編號，客戶編號，數(shù)量，產(chǎn)品編號，金額2.4數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)：員工含義說明：是送水系統(tǒng)進(jìn)行完成事務(wù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)員工的有關(guān)信息組成：員工編號、姓名、職稱、電話、工資、業(yè)績、出勤數(shù)據(jù)結(jié)構(gòu)：客戶含義說明：是送

6、水系統(tǒng)產(chǎn)生事務(wù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)客戶的有關(guān)信息組成：客戶編號、姓名、地址、聯(lián)系電話數(shù)據(jù)結(jié)構(gòu)：公司含義說明：是送水系統(tǒng)產(chǎn)生事務(wù)的另一方的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)公司的有關(guān)信息組成：公司編號、公司名稱、地址、聯(lián)系電話數(shù)據(jù)結(jié)構(gòu)：礦泉水含義說明：是送水系統(tǒng)主體數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)礦泉水的有關(guān)信息組成：產(chǎn)品編號、產(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)信息組成：訂單編號、客戶編號、商品編號、訂單數(shù)量、訂單日期、訂單處理情況、總金額 數(shù)據(jù)結(jié)構(gòu)：供應(yīng)商含義說明：是送水系統(tǒng)提供貨源的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)供應(yīng)

7、商的有關(guān)信息組成：供應(yīng)商編號、單位名稱、地址、電話數(shù)據(jù)結(jié)構(gòu)：費(fèi)用含義說明：是送水系統(tǒng)訂單里包含的一部分?jǐn)?shù)據(jù)結(jié)構(gòu)，定義了一個(gè)費(fèi)用的有關(guān)信息組成：費(fèi)用編號、付費(fèi)方式、已付、金額 數(shù)據(jù)結(jié)構(gòu)：倉庫含義說明：是送水系統(tǒng)礦泉水存儲地的數(shù)據(jù)結(jié)構(gòu)，定義了一個(gè)倉庫的有關(guān)信息組成：倉庫編號、倉庫名稱、倉庫容量3 設(shè)計(jì)過程或程序代碼create table buy( w_id char(8) not null, c_id char(8) not null, order_id char(8) not null, order_num numeric(36) null, oder_date date null, orde

8、r_state varchar(100) null, constraint PK_BUY primary key (w_id, c_id); create index beingbuy_FK on buy (w_id ASC); create index buy_FK on buy (c_id ASC);create table buyin( com_id char(8) not null, o_id char(8) not null, in_date date null, in_money numeric(8,2) null, in_num real null, in_goods varch

9、ar(100) null, in_price numeric(36) null, constraint PK_BUYIN primary key (com_id, o_id); create index buyin_FK on buyin (com_id ASC);create index sale_FK on buyin (o_id ASC);create table company( com_id char(8) not null, c_id char(8) not null, com_name varchar(50) null, com_tel char(11) not null, co

10、m_address varchar(50) not null, out_date date null, out_money numeric(30) null, out_num real null, out_goods varchar(100) null, constraint PK_COMPANY primary key (com_id), constraint AK_COM_ADDRESS_COMPANY unique (com_address); create index out_store_FK on company (c_id ASC);create unique index comp

11、any_AK on company (com_address ASC); create table custerm( c_id char(8) not null, c_name varchar(50) null, c_address varchar(100) null, c_tel numeric(11) not null, constraint PK_CUSTERM primary key (c_id); create table deal( w_id char(8) not null, c_id char(8) not null, e_id char(8) not null, deal_d

12、ate date null, deal_num real null, constraint PK_DEAL primary key (w_id, c_id, e_id); create index deal_FK on deal (e_id ASC);create index deal2_FK on deal (w_id ASC,c_id ASC); create table employee( e_id char(8) not null, com_id char(8) null, e_name varchar(50) null, e_tel char(11) not null, e_leve

13、al varchar(50) null, e_wage numeric(11) null, e_present varchar(100) null, constraint PK_EMPLOYEE primary key (e_id); create index belong_to_FK on employee (com_id ASC); create table money( w_id char(8) null, c_id char(8) null, money_id char(8) not null, money_way varchar(100) null, money_insum nume

14、ric(36) null, money_sum numeric(36) null, order_id char(8) null); create index belongto_FK on money (w_id ASC,c_id ASC); create table offer( o_id char(8) not null, o_name varchar(50) null, o_address varchar(50) null, o_tel varchar(11) null, constraint PK_OFFER primary key (o_id), constraint AK_O_TAD

15、DRESS_OFFER unique (o_address); create table "save"( s_id char(8) not null, s_name varchar(50) null, s_num real not null, constraint PK_SAVE primary key (s_id); create table save_produce( w_id char(8) not null, s_id char(8) not null, save_water_date date null, save_water_num real null, con

16、straint PK_SAVE_PRODUCE primary key (w_id, s_id); create index save_produce_FK on save_produce (w_id ASC); create index save_produce2_FK on save_produce (s_id ASC); create table water( w_id char(8) not null, w_name varchar(50) null, w_kind varchar(50) null, w_produce varchar(50) null, w_time date no

17、t null, in_price numeric(36) null, out_price numeric(36) null, constraint PK_WATER primary key (w_id);alter table buyin add constraint FK_BUYIN_SALE_OFFER foreign key (o_id) references offer (o_id) alter table company add constraint FK_COMPANY_OUT_STORE_CUSTERM foreign key (c_id) references custerm

18、(c_id) alter table deal add constraint FK_DEAL_DEAL_EMPLOYEE foreign key (e_id) references employee (e_id) alter table deal add constraint FK_DEAL_DEAL2_BUY foreign key (w_id, c_id) references buy (w_id, c_id) alter table employee add constraint FK_EMPLOYEE_BELONG_T_COMPANY foreign key (com_id) refe

19、rences company (com_id) alter table money add constraint FK_MONEY_BELONGTO_BUY foreign key (w_id, c_id) references buy (w_id, c_id) alter table save_produce add constraint FK_SAVE_PRO_SAVE_PROD_WATER foreign key (w_id) references water (w_id) alter table save_produce add constraint FK_SAVE_PRO_SAVE_

20、PROD_SAVE foreign key (s_id) references "save" (s_id)4設(shè)計(jì)結(jié)果與分析 4.1測試數(shù)據(jù)insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '龍井水', '茶水&#

21、39;, '長春', '17-6月-2011', 4, 10);insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '農(nóng)夫山泉', '山泉', '哈爾濱', '05-9月

22、-2014', 2, 14);insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '純凈水', '山泉', '沈陽', '13-5月-2011', 12, 3);insert into

23、"water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '娃哈哈', '牛奶', '長春', '26-4月-2012', 14, 9);insert into "water" ("w_id&q

24、uot;, "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '冰紅茶', '飲料', '北京', '06-10月-2013', 6, 7);insert into "water" ("w_id", "w_name", "

25、;w_kind", "w_produce", "w_time", "in_price", "out_price") values ('', '冰糖雪梨', '飲料', '秦皇島', '20-4月-2014', 8, 16);insert into "water" ("w_id", "w_name", "w_kind", "w_produc

26、e", "w_time", "in_price", "out_price") values ('', '白水', '山泉', '哈爾濱', '16-10月-2012', 5, 17);insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", &

27、quot;in_price", "out_price") values ('', '可樂', '飲料', '長春', '06-11月-2012', 16, 1);insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_

28、price") values ('', '美年達(dá)', '飲料', '佳木斯', '24-10月-2014', 3, 2);insert into "water" ("w_id", "w_name", "w_kind", "w_produce", "w_time", "in_price", "out_price") values (''

29、;, '咖啡', '飲料', '牡丹江', '01-11月-2013', 10, 18);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小王', '上海', 16);insert into "custerm" ("c_id", "c

30、_name", "c_address", "c_tel") values ('', '小李', '北京', 12);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小趙', '佳木斯', 1);insert into "custerm&qu

31、ot; ("c_id", "c_name", "c_address", "c_tel") values ('', '小錢', '大慶', 17);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小范', '哈爾濱', 1

32、1);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小鄧', '沈陽', 4);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('',

33、'小劉', '牡丹江', 2);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小張', '大連', 15);insert into "custerm" ("c_id", "c_name", "c_address", "c_t

34、el") values ('', '小吳', '哈爾濱', 0);insert into "custerm" ("c_id", "c_name", "c_address", "c_tel") values ('', '小馬', '沈陽', 14);insert into "buy" ("w_id", "c_id", "or

35、der_id", "order_num", "oder_date", "order_state") values ('', '', '', 19, '19-10月-2013', '簽約');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date"

36、;, "order_state") values ('', '', '', 3, '13-8月-2013', '簽約');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values ('', '',

37、 '', 11, '25-2月-2012', '處理中');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values ('', '', '', 5, '28-3月-2014', '已完成');inse

38、rt into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values ('', '', '', 17, '23-9月-2012', '已完成');insert into "buy" ("w_id", "c_id&quo

39、t;, "order_id", "order_num", "oder_date", "order_state") values ('', '', '', 15, '18-3月-2013', '欠款中');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "od

40、er_date", "order_state") values ('', '', '', 7, '15-11月-2014', '處理中');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values (''

41、;, '', '', 18, '20-9月-2013', '處理中');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values ('', '', '', 13, '14-9月-2012', '

42、;已完成');insert into "buy" ("w_id", "c_id", "order_id", "order_num", "oder_date", "order_state") values ('', '', '', 8, '15-3月-2013', '欠款中');insert into "money" ("money_id&q

43、uot;, "w_id", "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '現(xiàn)金', 14, 18, ''); insert into "money" ("money_id", "w_id", "c_id"

44、;, "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '現(xiàn)金', 9, 11, '');insert into "money" ("money_id", "w_id", "c_id", "money_way", "money_ins

45、um", "money_sum", "order_id") values ('', '', '', '信用卡', 5, 18, '');insert into "money" ("money_id", "w_id", "c_id", "money_way", "money_insum", "money_sum", "o

46、rder_id") values ('', '', '', '信用卡', 6, 23, '');insert into "money" ("money_id", "w_id", "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '

47、', '', '信用卡', 11, 29, '');insert into "money" ("money_id", "w_id", "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '支票', 15, 30

48、, '');insert into "money" ("money_id", "w_id", "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '支票', 7, 19, '');insert into "money&quo

49、t; ("money_id", "w_id", "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '支票', 16, 32, '');insert into "money" ("money_id", "w_id&quo

50、t;, "c_id", "money_way", "money_insum", "money_sum", "order_id") values ('', '', '', '現(xiàn)金', 19, 25, '');insert into "money" ("money_id", "w_id", "c_id", "money_way&qu

51、ot;, "money_insum", "money_sum", "order_id") values ('', '', '', '現(xiàn)金', 8, 22, '');insert into "deal" ("w_id", "c_id", "e_id", "deal_date", "deal_num") values ('',

52、 '', '', '20-10月-2013', 12);insert into "deal" ("w_id", "c_id", "e_id", "deal_date", "deal_num") values ('', '', '', '20-10月-2013', 3);insert into "deal" ("w_id", &

53、quot;c_id", "e_id", "deal_date", "deal_num") values ('', '', '', '16-1月-2012', 10);insert into "deal" ("w_id", "c_id", "e_id", "deal_date", "deal_num") values ('', &

54、#39;', '', '17-1月-2014', 1);insert into "deal" ("w_id", "c_id", "e_id", "deal_date", "deal_num") values ('', '', '', '06-3月-2012', 15);insert into "deal" ("w_id", "c_id", "e_id", "deal_date", "deal_num") values ('', '', '', '15-9月-2012', 18);insert into "deal" (&