數(shù)據(jù)庫課程設(shè)計(jì)—零件交易中心管理系統(tǒng)

1、數(shù)據(jù)庫原理課程設(shè)計(jì)題 目 零件交易中心管理系統(tǒng) 學(xué) 院 信息工程學(xué)院 專 業(yè) 計(jì)算機(jī)科學(xué)與技術(shù) 班 級(jí) 計(jì)科072 學(xué) 號(hào) 學(xué)生姓名 指導(dǎo)教師 編寫日期 2010-03-02 1.需求分析32.概念模型設(shè)計(jì)43.邏輯設(shè)計(jì)54.物理設(shè)計(jì)55.測試階段106.總結(jié)13 1.需求分析1供應(yīng)商供應(yīng)商的操作流程圖如圖2-1所示。增加供應(yīng)項(xiàng)修改供應(yīng)項(xiàng)刪除供應(yīng)項(xiàng)修改個(gè)人信息供應(yīng)項(xiàng)注冊(cè)注銷圖2-1 供應(yīng)商操作分類表2顧客顧客的地位和供應(yīng)商幾乎是對(duì)稱的，所以功能分類上也很相似顧客的操作流程圖如圖2-2所示。增加需求項(xiàng)修改需求項(xiàng)刪除需求項(xiàng)修改個(gè)人信息顧客注冊(cè)注銷圖2-2 顧客操作分類表3交易員交易員的工作就是提出

2、交易和完成交易。這里需要仔細(xì)考慮的問題是：一個(gè)交易如何產(chǎn)生，并如何達(dá)成，可以用圖2-3來說明這個(gè)問題我們?cè)谔幚斫灰椎臅r(shí)候可能面臨如下問題：(1)一個(gè)交易只能在交易雙方都同意的情況下才可以進(jìn)行，所以數(shù)據(jù)庫中的供求信息只能作為達(dá)成某個(gè)交易的基礎(chǔ)；(2)交易的雙方可能不同時(shí)使用這個(gè)系統(tǒng)，因此需要系統(tǒng)提供一個(gè)雙方交換信息的方式；(3)系統(tǒng)需要提供一種方便系統(tǒng)(交易員)向用戶提出建議來促成交易的途徑，并在保證數(shù)據(jù)庫數(shù)據(jù)完整性的情況下達(dá)成交易。交易員協(xié)議書草案供應(yīng)商以及顧客簽字正式簽字交易員簽發(fā)完成交易供應(yīng)商提出交易顧客提出交易申請(qǐng)交易員提出交易建議圖2-3 交易員操作圖2.概念模型設(shè)計(jì)數(shù)據(jù)庫需要表述的信

3、息有以下幾種：零件信息、供應(yīng)商信息、顧客信息及供應(yīng)商集和零件集之間的聯(lián)系(供應(yīng))。1. 供應(yīng)商集和零件集之間的聯(lián)系(供應(yīng))m 零件顏色零件號(hào)零件名重量簡介供應(yīng)商名供應(yīng)商供應(yīng)商號(hào)地址電話簡介供應(yīng)數(shù)量價(jià)格n 圖3-1供應(yīng)商和零件之間的聯(lián)系(供應(yīng)) e-r模型2.顧客集和零件集之間的聯(lián)系(求購)m 零件顏色零件號(hào)零件名重量簡介顧客電話顧客號(hào)顧客名地址求購數(shù)量價(jià)格n圖3-2 顧客和零件之間的聯(lián)系(求購) e-r模型3.交易(三元聯(lián)系)可以用e-r模型表述該模型的設(shè)計(jì)，e-r圖如圖3-3所示。零件顏色零件號(hào)零件名重量簡介求購數(shù)量價(jià)格供應(yīng)商號(hào)供應(yīng)商名地址電話簡介供應(yīng)數(shù)量價(jià)格顧客電話顧客號(hào)顧客名地址交易價(jià)格

4、數(shù)量供應(yīng)商圖3-3 全局e-r模型3.邏輯設(shè)計(jì)通過e/r模型到關(guān)系模型的轉(zhuǎn)化，可以得到如下關(guān)系模式：(1)零件實(shí)體集轉(zhuǎn)換為關(guān)系：part(id，color，name，weight，intro)(2)供應(yīng)商實(shí)體集轉(zhuǎn)換為關(guān)系provider(id，name，addtess，tel，intro)(3)顧客實(shí)體集轉(zhuǎn)換為關(guān)系customer(id，name，addtess，tel)(4)供應(yīng)聯(lián)系轉(zhuǎn)換為關(guān)系supply(partld，providerld，price，quantity)(5)求購聯(lián)系轉(zhuǎn)換為關(guān)系offertobuy(customerld，partid，price，quantity)(6)交易

5、聯(lián)系轉(zhuǎn)換為關(guān)系business(customerld，providerld，partid，price，quantity) 每個(gè)關(guān)系模式的主鍵碼都用下劃線標(biāo)出。同時(shí)，對(duì)于從聯(lián)系導(dǎo)出的關(guān)系supply(供應(yīng))，offertobuy(求購)和business(交易)，使用與之相聯(lián)系的實(shí)體集的主健碼作為自己的鍵碼，必須符合外鍵碼約束。對(duì)于customer(顧客)，provider(供應(yīng)商)和part(零件)之間，不存在直接的約束，所以可以存在沒有供應(yīng)商供應(yīng)同時(shí)也沒有顧客求購的零件。4.物理設(shè)計(jì)1為了提高在表中搜索元組的速度，在實(shí)際實(shí)現(xiàn)的時(shí)候應(yīng)該基于鍵碼建立索引是各表中建立索引的表項(xiàng)： (1)part(

6、id) (2)provider(id) (3)customer(id) (4)supply(partid，providerid (5)offertobuy(customerid，partid)(6)business(customerld，providerid，partid)2用sql實(shí)現(xiàn)設(shè)計(jì)實(shí)現(xiàn)該設(shè)計(jì)的環(huán)境為windows 2000 perfessinal+mssqlserver 2000.（1）建立part表 create table part( id smallint identity(1,1) primary key clustered, color varchar(20), name

7、varchar(20) not null, weight int default 0, intro text)（2）建立provider表 create table provider( id smallint identity(1,1) primary key clustered, name varchar(20) not null, password varchar(8) not null, address varchar(30), tel varchar(20), intro text)（3）建立customer表create table customer(id smallint iden

8、tity(1,1) primary key clustered,name varchar(20) not null, address varchar(30),tel varchar(20) )（4）建立supply表create table supply(partid smallint,providerid smallint,price int,quantity int,constraint pk_supply primary key clustered(partid,providerid),constraint fk_supply_partid foreign key(partid) ref

9、erences part(id),constraint fk_supply_providerid foreign key(providerid) references provider(id)（5）建立offertobuy表create table offertobuy(customerid smallint,partid smallint,price int,quantity int,constraint pk_offertobuy primary key clustered(customerid,partid),constraint fk_offertobuy_customerid for

10、eign key(customerid) references customer(id),constraint fk_offertobuy foreign key(partid) references part(id) （6）建立business表create table business(customerid smallint,providerid smallint,partid smallint,price int,quantity int,constraint pk_business primary key clustered(cuscomerid,providerid,partid),

11、constraint fk_business_customerid foreign key(customerid) references customer(id),constraint fk_business_providerld foreign key(providerid) references provider(id),constraint fk_business_partid foreign key(partid)references part(id)（7）供應(yīng)商操作注冊(cè)(register)insert into provider(name，password，address，tei，i

12、ntro)values(#name，#password，#address，#tel，#intro)在登記操作后，供應(yīng)商得到一個(gè)唯一的id，可以根據(jù)這個(gè)id采查詢和修改供應(yīng)商的數(shù)據(jù)。注銷(unregister) delete provider where(id=#id)；修改個(gè)人館息(update)update provider set(name=#name,address=#address，tel=#tel，intro=#intro)where(id#id)；增加供應(yīng)項(xiàng)(add_supply_item) insert into supply(partid，providerid，price，qu

13、antity) values(#partid，#provderld，#price；#quantily)；刪除供應(yīng)項(xiàng)(delete_supply_item) delete supply where(partld=#partid and provideid=#providerld)；修改供應(yīng)項(xiàng)(update_supply_item) updatesupplyset(price=#price，quantity=#quantity) where(partld=#partid and providerid=#providerid)很明顯，系統(tǒng)并沒有提供面向供應(yīng)商修改零件信息的接口，所以供應(yīng)商提供的零件必

14、須已經(jīng)在零件表中存在；可以這祥假設(shè)，交易所的管理員負(fù)責(zé)更新零件信息，而供應(yīng)商可以向交易所申請(qǐng)?jiān)黾幽撤N零件的信息事實(shí)上顧客也可以提出這樣的要求。（8）顧客操作注冊(cè)(register)insert into customer(name，address，tel)values(#name，#address，#tel)；在登記操作后，顧客得到一個(gè)唯一的id，可以根據(jù)這個(gè)id來查詢和修改顧客的數(shù)據(jù)。注銷(unregister)delete customer whereid=#id)；修改個(gè)人信息(update) update customer set(name=#name，address=#address

15、，tel=#tel) where(1d=#id)；增加需求項(xiàng)(add_offertobuy_item)insert into offertobuy(partid,customerid，price，quantity)values(#partid，#customerid，#price，#quantity)刪除需求項(xiàng)(delete_offertobuy_iterm)delete offertobuywhere(partld=#partld and customerld=#customerid)；修改需求項(xiàng)(date_offertobuy_item)update offertobuy set(pric

16、e=#price，quantity=#quantitywhere(partld=#partid and customerid=#customerid)（9）交易員針對(duì)需求分析中提出的問題，我們提出了“協(xié)議書”的解決方案，方案的說明如下：每個(gè)交易在達(dá)成以前都作為協(xié)議書保存在數(shù)據(jù)庫中，協(xié)議書具有和交易一樣的完備信息，可以在條件成熟的情況下轉(zhuǎn)為一個(gè)達(dá)成的交易；協(xié)議書只有在供應(yīng)商和顧客都簽字的情況下才有效；有效的協(xié)議書由交易員簽發(fā)，協(xié)議書一經(jīng)簽發(fā)，就生效，表明一個(gè)交易的達(dá)成，數(shù)據(jù)庫中的數(shù)據(jù)將同時(shí)予以修改；協(xié)議書可以由供應(yīng)商、顧客或者交易員中的任意一個(gè)人提出申請(qǐng)。當(dāng)協(xié)議書在雙方?jīng)]有都簽字前，協(xié)議的雙方或

17、者交易員都可以刪除這個(gè)協(xié)議書；但是，當(dāng)協(xié)議書簽字完畢后，協(xié)議書就不得刪除(修改)，只能由交易員進(jìn)行處理；協(xié)議書有可能在轉(zhuǎn)成交易的過程中失敗，因?yàn)樵诮灰走_(dá)成以前，數(shù)據(jù)庫中的數(shù)據(jù)有可能因?yàn)槠渌灰锥兓?，一個(gè)協(xié)議書可能失效，這是允許的。根據(jù)以上分析，對(duì)數(shù)據(jù)庫的模型作一些修改，增加協(xié)議書表，其關(guān)系模式如下： agreement(customerld，providerid，partid，price，quantity，customersign，providersign)對(duì)應(yīng)的sql描述為：create table agreement(customerm smallint，providerld small

18、int，partld smallint，price int,quantity int，customersign int，providersign int， constraint pk_agreement primary key clustered(customerid，providerid，partid)，constraint fk_agreement_customerid foreign key(customerid) references customer(id)，constraint fk_ agreement_provlderid foreign key(providerid) ref

19、erences provider(id)，constraint fk_agreement_partid foreign key(partid)references part(id)與上述其他操作相比，對(duì)交易的操作對(duì)數(shù)據(jù)完整性要求比較高，其中需要注意的地方是；要防止同一用戶(供應(yīng)商，顧客)的數(shù)據(jù)因兩個(gè)交易而同時(shí)修改； 需要同時(shí)對(duì)供應(yīng)數(shù)據(jù)庫(supply)、需求數(shù)據(jù)庫(offertobuy)、交易數(shù)據(jù)庫(business)和協(xié)議數(shù)據(jù)庫(agreement)作出修改，而且需要保持這些修改的原子性；很顯然，這些要求正是對(duì)于一個(gè)事務(wù)(transaction)的要求，所以可以用一個(gè)事務(wù)來完成簽發(fā)一個(gè)協(xié)議的

20、操作。事務(wù)的描述如下：create proc pass_agreementproviderid int，customerid int，partld intasdeclare transname varchar(20)select transname=pass_agreementbegin transaction transnamedeclare price int，quantity intselect price=price，quantity=quantity from agreementwhere prividerid=providerid and customerid=customerid

21、 and panid=partid1nsert into business(providerid，customerid，partid，price，quantity)values(providerid，customerid，partid，price,quantity)update supply set quantity=quantity-quantitywhere providerid=prividerid and partid=partidif (select quantity from supplywhere proiderid=provider and partid=partid)0 ro

22、llback transactlon transnamedelete from supply where quantity=0update offertobuy set quantity=quanttity-quantitywhere customerid=customerid and partld=partidif(select quandtity from offertobuywhere customerid=customerid and partid=partld)0rollback transaction transnamedelete from offertobuy where qu

23、antity=0commit transaction transname為了使用方便,這里定義了一個(gè)存貯過程；功能是完成從agreementt的一個(gè)元組到business的一個(gè)元組的轉(zhuǎn)化工作。這里考慮到了刪除空的suppiy和offertobuy項(xiàng)，更加重要的是，這里考慮到了非法的agreement的情況，在一段時(shí)間后，由于供應(yīng)商或者顧客修改數(shù)據(jù)，agreement可能就非法，這時(shí)就需要把這個(gè)事務(wù)廢除，所以，這里檢查了supply表和offertobuy表中的數(shù)據(jù)，確保數(shù)據(jù)仍然正確。另外交易員，或者說交易所必須承擔(dān)的一項(xiàng)任務(wù)是更新零件列表。這里在考慮顧客和供應(yīng)商的時(shí)候并沒有給予他們修改零件列表

24、的權(quán)利，所以他們必須根據(jù)數(shù)據(jù)庫中已有的項(xiàng)更新自己的供求信息。由于這個(gè)數(shù)據(jù)庫實(shí)際上更加偏重于模型化，而不是一個(gè)實(shí)際環(huán)境中的數(shù)據(jù)庫，所以在實(shí)現(xiàn)應(yīng)用模型的時(shí)候我們還需要對(duì)這個(gè)數(shù)據(jù)庫的模型作一些修改。由于本實(shí)驗(yàn)在模型設(shè)計(jì)上使用了microsoft transact-sql的語法，因此以上的數(shù)據(jù)庫操作都是在sqlserver2000上測試通過的。5.測試階段1輸入數(shù)據(jù)設(shè)計(jì)（1）插入零件信息； create procedure insert_ljasinsert into part(color，name，weight，intro)values(black，stick，30，of steel)顯示剛插人的零

25、件id： exec insert_lj id - 1 (1 row(s) affected)(不同的實(shí)驗(yàn)，id值可能不同。以后相應(yīng)操作要保持前后一致就可以丁。)（2）插入供應(yīng)商信息： create procedure insert_gysasinsert into provider(name，password，address，tel，intro)values(coml，1234，北京，6543210，nothing)顯示剛插入的供應(yīng)商id： exec insert_gys id - - - 1 (1 row(s) affected)（3）插入顧客信息： create procedure ins

26、ert_gkasinsert into customer(name，address，tel)values(cusl，北京，6666666)顯示剛插入的顧客id： exec insert_gk id - - - 1 (1 row(s)affected)（4）插入供應(yīng)商供應(yīng)信息： create procedure insert_gysgyas insert into supply(partid，providerld，price，quantity) values(1，1，20，100)；（5）插入顧客需求信息： create procedure insert_gkxqas insert into o

27、ffertobuy(partld，customerid，priee，quantity) values(1，1，20，50)；（6）插入?yún)f(xié)議信息：create procedure insert_xyxxas insert into agreement(customerid,providerid，partld，price，quantity，customersign，providersign) values(1，1，1，20，30，1，1)；2.執(zhí)行交易操作設(shè)計(jì)（1）執(zhí)行交易存儲(chǔ)過程pass_agreement,參數(shù)為：1，1，1：pass_agreement 1，1，1; (后面的三個(gè)參數(shù)分別對(duì)應(yīng)

28、前面選擇出的供應(yīng)商id、顧客id和零件id。)（2）結(jié)果：顯示交易后供應(yīng)信息和需求信息： create procedure 交易后供應(yīng)信息partid int(8)providerid int(8)asselect quantity from supply where partid=partid and providerid=providerid exec 交易后供應(yīng)信息quantity - - - - 70 (1 row(s) affected)create procedure 交易后需求信息partid int(8)customerid int(8)as select quantity from offertobuy where partld=par