《數(shù)據(jù)庫(kù)課程設(shè)計(jì)》課程設(shè)計(jì)外貿(mào)公司的訂單管理系統(tǒng)

杭州電子科技大學(xué)數(shù)據(jù)庫(kù)課程設(shè)計(jì)課程設(shè)計(jì)手冊(cè) 外貿(mào)公司的訂單管理系統(tǒng)姓 名 學(xué) 號(hào) 07031760 設(shè)計(jì)小組長(zhǎng) 其 他 設(shè) 計(jì)小 組 成 員 指 導(dǎo) 教 師 設(shè) 計(jì) 時(shí) 間 2009.9.7 至 2009.9.18 杭州電子科技大學(xué)管理學(xué)院編制一、需求分析1.針對(duì)外貿(mào)企業(yè)的訂單管理系統(tǒng)，分別對(duì)商品、訂單、訂單明細(xì)、客戶(hù)、生產(chǎn)商和供應(yīng)商在整個(gè)訂單管理系統(tǒng)中的定位進(jìn)行詳細(xì)的分析，獲得其需求信息如下：訂單的接收是按照商品分類(lèi)進(jìn)行的，所以需要有每個(gè)商品的信息。登記于同一張訂單明細(xì)的必須是同一種商品。如果一個(gè)商品的訂單有一個(gè)或者多個(gè)勝生產(chǎn)商滿(mǎn)足，則該訂單不可被退回。需要完整地記錄商品、客戶(hù)、生產(chǎn)商、供應(yīng)商的信息。針對(duì)每一筆業(yè)務(wù)，在訂單中的商品信息欄中都要仔細(xì)的紀(jì)錄每一種商品的編號(hào)、日期、數(shù)量、單價(jià)和總價(jià)。在每一張訂單中都要詳盡的紀(jì)錄下訂單的單位名稱(chēng)、單位號(hào)、單位地址、交貨日期、貨品名稱(chēng)、貨品號(hào)、貨品單價(jià)和貨品總價(jià)。把所有相同的產(chǎn)品紀(jì)錄到同一張訂單明細(xì)表中，每一張訂單明細(xì)表中只能紀(jì)錄同一種商品。在每一張訂單明細(xì)表中需要紀(jì)錄訂單號(hào)、單位名稱(chēng)、商品號(hào)、商品名、規(guī)格、交貨日期、數(shù)量、單價(jià)和簽訂日期等。根據(jù)貨品名稱(chēng)、交貨日期、貨品數(shù)量、生產(chǎn)商的生產(chǎn)能力以及單位地址等實(shí)際因素，將訂單明細(xì)表進(jìn)行合理的合并和拆分，從而達(dá)到降低成本的目的。對(duì)接收訂單的貨品的生產(chǎn)商以及貨品的供應(yīng)商信息進(jìn)行詳盡的紀(jì)錄。訂單的接收、處理要有相應(yīng)的業(yè)務(wù)員信息。只有管理員登錄之后才可以使用系統(tǒng)。默認(rèn)的管理員不可以刪除?？梢詫?duì)作廢的、待生產(chǎn)的、正生產(chǎn)的、生產(chǎn)完成的訂單進(jìn)行分類(lèi)查找，但不能進(jìn)行修改、添加、刪除等操作。當(dāng)進(jìn)行訂單接收和處理操作后，能相應(yīng)更新數(shù)據(jù)庫(kù)更新。對(duì)所接收的訂單進(jìn)行數(shù)據(jù)分析，總結(jié)相應(yīng)商品的訂單服務(wù)。2. 經(jīng)上述系統(tǒng)功能分析和需求總結(jié)，考慮其業(yè)務(wù)和功能的不斷擴(kuò)展，設(shè)計(jì)如下的數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)：商品信息，包括的數(shù)據(jù)項(xiàng)有： 商品號(hào)，名稱(chēng)，規(guī)格，單價(jià)，描述。訂單明細(xì)信息，包括的數(shù)據(jù)項(xiàng)有： 訂單號(hào)，單位名稱(chēng)，商品號(hào)，商品名，規(guī)格，交貨日期，數(shù)量，單價(jià)，簽訂日期。訂單信息，包括的數(shù)據(jù)項(xiàng)有： 訂單號(hào)，單位，操作員號(hào)，地址，簽訂日期，單價(jià)，數(shù)量，商品號(hào)，交貨日期。業(yè)務(wù)員信息，包括的數(shù)據(jù)項(xiàng)有： 職工號(hào)，姓名，年齡，職稱(chēng)?？蛻?hù)信息，包括的數(shù)據(jù)項(xiàng)有：客戶(hù)姓名，賬號(hào)，地址，聯(lián)系方式。 生產(chǎn)訂單信息，包括的數(shù)據(jù)項(xiàng)有：生產(chǎn)商，商品名，數(shù)量，單價(jià)，賬號(hào)，聯(lián)系方式。供應(yīng)商信息，包括的數(shù)據(jù)項(xiàng)有： 姓名，地址，電話(huà)號(hào)碼，賬號(hào)。3.數(shù)據(jù)流圖訂單管理系統(tǒng)數(shù)據(jù)流圖如下： 在整個(gè)數(shù)據(jù)流圖中，P1，P2，P3，P4分別是客戶(hù)信息管理系統(tǒng)，產(chǎn)品信息管理系統(tǒng)，員工信息管理系統(tǒng)，供應(yīng)商管理系統(tǒng)，我們這里只作簡(jiǎn)單處理。P5.1，P5.2，P5.3是訂單管理系統(tǒng)的核心部分，P5.2審核系統(tǒng)是根據(jù)客戶(hù)的信用情況，剔除一部分不合格的訂單并觸發(fā)修改訂單明細(xì)，P5.3是根據(jù)供應(yīng)商的供應(yīng)能力等情況對(duì)訂單明細(xì)進(jìn)行拆分和合并處理，最終形成生產(chǎn)訂單。4.數(shù)據(jù)字典（1）、數(shù)據(jù)項(xiàng)數(shù)據(jù)項(xiàng)描述=職工號(hào)，char(6)，000000999999，前兩個(gè)數(shù)字表示所屬部門(mén)，中間兩個(gè)數(shù)字表示工作起始年份；姓名，varchar(8)；年齡，int(2)，1865，職工的年齡必須大于18周歲且小于65周歲；商品號(hào)，char(14) ，由阿拉伯?dāng)?shù)字和英文小寫(xiě)字母組成；單位名稱(chēng)，varchar(10)單位號(hào)，char(10)（2）、數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)描述=業(yè)務(wù)員信息，組成：職工號(hào)，姓名，年齡，職稱(chēng)；訂單信息，組成：訂單號(hào)，單位號(hào)，操作員號(hào)，地址，簽訂日期，單價(jià)，數(shù)量，交貨日期；客戶(hù)信息，組成：客戶(hù)號(hào)，客戶(hù)姓名，賬號(hào)，地址，聯(lián)系方式；（3）、數(shù)據(jù)流數(shù)據(jù)流描述=員工信息流，來(lái)源：人工錄入，去向：?jiǎn)T工信息表，組成：業(yè)務(wù)員信息；（4）、數(shù)據(jù)存儲(chǔ)數(shù)據(jù)存儲(chǔ)描述=訂單明細(xì)，客戶(hù)信息流，產(chǎn)品信息流，員工信息流，訂單明細(xì)信息流，組成：業(yè)務(wù)員信息，商品信息，客戶(hù)信息；（5）、處理過(guò)程處理過(guò)程描述=下單，輸入：客戶(hù)信息流，產(chǎn)品信息流，員工信息流，輸出：訂單明細(xì)信息流，處理：通過(guò)員工的操作，使顯示具體的訂單信息表和訂單明細(xì)表；2、 概念模型設(shè)計(jì)訂單管理系統(tǒng)E-R圖如下：在這里，我們?yōu)榱撕?jiǎn)化數(shù)據(jù)庫(kù)，將訂單管理設(shè)置為只處理一種商品，所以訂單的拆分和合并聯(lián)系分別是1：n 和n：1 。在訂單明細(xì)中可以反映商品的供應(yīng)者，在生產(chǎn)訂單中可以顯示每個(gè)供應(yīng)商的供應(yīng)量等信息。生產(chǎn)訂單屬性中的生產(chǎn)商號(hào)和供應(yīng)商供應(yīng)商號(hào)為異名同義，訂單明細(xì)屬性中的單位名稱(chēng)和客戶(hù)信息屬性的客戶(hù)姓名為異名同義。三、邏輯模型設(shè)計(jì)職工信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注職工號(hào)Char(6)否主鍵姓名Varchar(8)否年齡Int否職稱(chēng)Varchar否訂單信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注訂單號(hào)Char(6)否主鍵單位姓名 Varchar(20)否外鍵地址Varchar(100)否商品單價(jià)Money否數(shù)量Int否簽訂日期Datetime否交貨日期Datetime否操作員號(hào)Char(6)否外鍵商品號(hào)Varchar(10)否外鍵商品信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注商品號(hào)Varchar（10）否主鍵名稱(chēng)Varchar（20）否單價(jià)Money否規(guī)格Varchar（20）否描述Varchar（50）可訂單明細(xì)表屬性名數(shù)據(jù)類(lèi)型可否為空備注訂單號(hào)Char(6)否外鍵商品號(hào)Char(10)否外鍵商品名Varchar（20）否單價(jià)Money否數(shù)量Int否簽訂日期Datetime否交貨日期Datetime否單位名稱(chēng)Char（10）否外鍵規(guī)格Varchar（20）否生產(chǎn)商號(hào)Char（10）否外鍵生產(chǎn)訂單信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注生產(chǎn)商Varchar(20)否外鍵地址Varchar(100)否單價(jià)Money否賬號(hào)Varchar（20）否聯(lián)系方式Varchar（21）否供應(yīng)量Int否訂單號(hào)Char(6)否外鍵商品號(hào)Char(10)否外鍵供應(yīng)商信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注姓名Varchar(20)否主鍵地址Varchar(100)否電話(huà)號(hào)碼Varchar(20)可賬號(hào)Varchar(20)否客戶(hù)信息表屬性名數(shù)據(jù)類(lèi)型可否為空備注客戶(hù)姓名Varchar(20)否主鍵賬號(hào)Varchar(20)否地址Varchar(100)否聯(lián)系方式Varchar(20)否4、 物理模型設(shè)計(jì)（1） 、由于經(jīng)常在查詢(xún)條件中出現(xiàn)，所以決定在職工信息表中的職工號(hào)上建立唯一索引；（2） 、由于經(jīng)常在連接操作的鏈接條件中出現(xiàn)，所以決定在訂單信息表中的訂 單號(hào)上建立唯一索引；（3） 、由于經(jīng)常在查詢(xún)條件中出現(xiàn)，所以決定在客戶(hù)信息表中的客戶(hù)姓名上建立唯一索引；（4） 、由于經(jīng)常在查詢(xún)條件中出現(xiàn)，所以決定在供應(yīng)商信息表中的姓名上建立唯一索引；（5） 、由于經(jīng)常在連接操作的鏈接條件中出現(xiàn)，所以決定在商品信息表中的商品號(hào)上建立唯一索引；5、 數(shù)據(jù)庫(kù)實(shí)施和維護(hù)1、 創(chuàng)建數(shù)據(jù)庫(kù)create database 訂單管理系統(tǒng)2、 創(chuàng)建表創(chuàng)建職工信息表create table 職工信息表(職工號(hào) char(6) not null primary key,姓名 Varchar(8) not null,年齡 Int not null,職稱(chēng) Varchar(10) not null,)創(chuàng)建商品信息表create table 商品信息表(商品號(hào) Varchar(10) not null primary key,名稱(chēng) Varchar(20) not null,單價(jià) Money not null,規(guī)格 Varchar(20) not null,描述 Varchar(50)創(chuàng)建供應(yīng)商信息表create table 供應(yīng)商信息表(姓名 Varchar(20) not null primary key,地址 Varchar(100) not null,電話(huà)號(hào)碼 Varchar(20) not null,賬號(hào) Varchar(50) not null)創(chuàng)建客戶(hù)信息表create table 客戶(hù)信息表(客戶(hù)姓名 Varchar(20) not null primary key,賬號(hào) Varchar(20) not null,地址 Varchar (100)not null,聯(lián)系方式 Varchar(20) not null)創(chuàng)建訂單信息表create table 訂單信息表(訂單號(hào) char(6) not null primary key,地址 Varchar(100) not null,商品單價(jià) Money not null,數(shù)量 int not null,簽訂日期 datetime not null,交貨日期 datetime not null,單位 Varchar (20) foreign key references 客戶(hù)信息表(客戶(hù)姓名),操作員號(hào) char(6) foreign key references 職工信息表(職工號(hào)),商品號(hào) Varchar(10)foreign key references 商品信息表(商品號(hào))創(chuàng)建訂單明細(xì)表create table 訂單明細(xì)表(訂單號(hào) char(6) not null foreign key references 訂單信息表(訂單號(hào)),商品號(hào) Varchar(10) not null foreign key references 商品信息表(商品號(hào)),商品名 Varchar(20) not null,單價(jià) Money not null,數(shù)量 int not null,簽訂日期 datetime not null,交貨日期 datetime not null,單位 Varchar(20) foreign key references 客戶(hù)信息表(客戶(hù)姓名),規(guī)格 Varchar(20) not null,生產(chǎn)商 Varchar (20) foreign key references 供應(yīng)商信息表(姓名)創(chuàng)建生產(chǎn)訂單信息表create table 生產(chǎn)訂單信息表(地址 Varchar (100) not null,單價(jià) Money not null,賬號(hào) Varchar(20) not null,聯(lián)系方式 Varchar(21) not null,供應(yīng)量 int not null,生產(chǎn)商 Varchar(20) not null foreign key references 供應(yīng)商信息表(姓名),訂單號(hào) char(6) not null foreign key references 訂單信息表(訂單號(hào)),商品號(hào) Varchar(10) not null foreign key references 商品信息表(商品號(hào))3、創(chuàng)建視圖Create view 訂單明細(xì)視圖As select 訂單號(hào)，單位，商品號(hào)，生產(chǎn)商From 訂單明細(xì)表Create view 生產(chǎn)訂單視圖As select 生產(chǎn)商，訂單號(hào)，商品號(hào)，供應(yīng)量，聯(lián)系方式From 生產(chǎn)訂單表4、 創(chuàng)建索引在主鍵屬性和外鍵屬性列上都建立索引,檢查唯一性和完整性,加快連接查詢(xún)速度。Create unique index IX_職工號(hào) on 職工信息表(職工號(hào));Create unique index IX_訂單號(hào) on 訂單信息表(訂單號(hào));Create unique index IX_客戶(hù)姓名 on 客戶(hù)信息表(客戶(hù)姓名);Create unique index IX_姓名 on 供應(yīng)商信息表(姓名);Create unique index IX_商品號(hào) on 商品信息表(商品號(hào));5、 表中數(shù)據(jù)操作1、給數(shù)據(jù)庫(kù)賦值insert into 職工信息表values(070934,kimikong,24,操作員);insert into 職工信息表values(070317,massa,29,主管);insert into 職工信息表Values(040201,aloso,34,操作員)Insert Into 商品信息表Values (07031760,klmin,2000,長(zhǎng)度172cm，重量60KG,暢銷(xiāo)貨啊);Insert Into 商品信息表Values(07031763,lljing,1500,長(zhǎng)度165cm,體重70KG,有點(diǎn)胖胖);Insert Into 客戶(hù)信息表Values(雷敬文,07031761,16-624,15869027542);Insert Into 客戶(hù)信息表Values(李俊,07031764,16-610,1586902432);InsertInto 訂單信息表Values(001,杭電16#624,2000,10,2009.09.15,2009.09.18,雷經(jīng)文,070934,07031760)InsertInto 訂單信息表Values(002,杭電16#625,1500,20,2009.09.14,2009.09.17,李俊,040201,07031763)InsertInto 訂單明細(xì)表Values(001,07031760,klmin,2000,10,2009.09.15,2009.09.16,雷經(jīng)文,長(zhǎng)度172cm，重量60KG,老大)InsertInto 訂單明細(xì)表Values(002,07031763,lljing,1500,20,2009.09.14,2009.09.17,李俊,長(zhǎng)度165cm,重量70KG,老邱)InsertInto 生產(chǎn)訂單信息表Values(杭電2,2000,166261,110轉(zhuǎn)1,10,老大,001,07031760)InsertInto 生產(chǎn)訂單信息表Values(杭電3,1500,166262,110轉(zhuǎn)2,20,老邱,002,07031763)InsertIn耀 供應(yīng)商信息表Values(老大,杭電2,110轉(zhuǎn)1,166261)InsertInto 供應(yīng)商信息表Values(老邱,杭電3,110轉(zhuǎn)2,166262)2、其他操作把客戶(hù)信息表中李俊的聯(lián)系方式更正為15869023242update 客戶(hù)信息表set 聯(lián)系方式=15869023242where 客戶(hù)姓名=李俊;員工因故辭職Delete from 職工信息表where 職工號(hào)=0703176、 使用表格操作1、查找所有職工的年齡，并按年齡大小進(jìn)行排序select 職工號(hào),姓名,年齡from 職工信息表order by 年齡2、 查找訂單號(hào)為001的商品的描述select 訂單號(hào),a.商品號(hào),名稱(chēng),單價(jià),描述from 訂單信息表 as a,商品信息表 as bwhere 訂單號(hào)=001 and b.商品號(hào) = (select 商品號(hào)from 訂單明細(xì)表where 訂單號(hào)=001)3、 查找由供應(yīng)商老大供應(yīng)商的商品情況select 商品號(hào),商品名,單價(jià),規(guī)格,數(shù)量from 訂單明細(xì)表where 生產(chǎn)商=老大4、 查找指定客戶(hù)購(gòu)買(mǎi)的商品的具體信息select 商品號(hào),名稱(chēng),單價(jià),規(guī)格,描述from 商品信息表where 商品號(hào)=(select 商品號(hào)from 訂單明細(xì)表where 單位=雷經(jīng)文)7、 創(chuàng)建觸發(fā)器，游標(biāo)和存儲(chǔ)過(guò)程 在訂單明細(xì)表上建立觸發(fā)器，實(shí)現(xiàn)在商品規(guī)格作出改變時(shí)，相應(yīng)的訂單明細(xì)信息也作出更正。 Use 訂單管理系統(tǒng) Go Create trigger tri_商品信息更新 on 訂單明細(xì)表 For update As If rowcount=0 Return Update 商品信息表 Set 規(guī)格=(select 規(guī)格 from inserted) Where 規(guī)格=(select 規(guī)格 from deleted) If error!=0 Begin Rollback Return End Return Go在客戶(hù)信息表建立游標(biāo)，用游標(biāo)和變量逐行輸出客戶(hù)姓名和聯(lián)系方式declare name varchar(20),telephone varchar(20)declare cur_1 cursor for select 客戶(hù)姓名,聯(lián)系方式from 客戶(hù)信息表for read onlyopen cur_1fetch from cur_1 into name,telephoneprint 客戶(hù)姓名+name+聯(lián)系方式+telephonewhile fetch_status=0beginfetch from cur_1 into name,telephoneprint 客戶(hù)姓名+name+聯(lián)系方式+telephoneendclose cur_1deallocate cur_1在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,返回指定的生產(chǎn)訂單信息create proc pro_生產(chǎn)訂單信息生產(chǎn)商 Varchar(20),訂單號(hào) char(6),商品號(hào) Varchar(10),單價(jià) Money,供應(yīng)量 int outputAs select 訂單號(hào),商品號(hào),單價(jià),供應(yīng)量 from 生產(chǎn)訂單信息表 where 生產(chǎn)商=老邱Go6、 總結(jié)通過(guò)這次數(shù)據(jù)庫(kù)課程，我學(xué)到了不少。我發(fā)現(xiàn)考試題目會(huì)做根本不代表也會(huì)設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)，整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)最重要的環(huán)節(jié)是需求分析，只有把業(yè)務(wù)流程都理順了之后才能做下面的環(huán)節(jié)。我們的課題外貿(mào)公司的訂單管理，網(wǎng)上資料比較少，在老師的指導(dǎo)下，我們最終搞明白了訂單管理的具體流程和板塊。E-R圖不是那么好畫(huà)的，在設(shè)計(jì)的時(shí)候要考慮的因素，因?yàn)槲覀兪堑谝淮?，在轉(zhuǎn)換為邏輯模式和后面的數(shù)據(jù)庫(kù)實(shí)施時(shí)，我們對(duì)E-R圖作了一遍又一遍的修改，發(fā)現(xiàn)最后的圖跟剛開(kāi)始的圖已經(jīng)很不一樣了。在整個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程中，我們得到了陳老師細(xì)心的指導(dǎo)，尤其是前面需求分析和概念模型設(shè)計(jì)階段，這對(duì)我們的成果非常重要。