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

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

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

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

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

5、聯(lián)系轉換為關系business(customerld，providerld，partid，price，quantity) 每個關系模式的主鍵碼都用下劃線標出。同時，對于從聯(lián)系導出的關系supply(供應)，offertobuy(求購)和business(交易)，使用與之相聯(lián)系的實體集的主健碼作為自己的鍵碼，必須符合外鍵碼約束。對于customer(顧客)，provider(供應商)和part(零件)之間，不存在直接的約束，所以可以存在沒有供應商供應同時也沒有顧客求購的零件。4.物理設計1為了提高在表中搜索元組的速度，在實際實現(xiàn)的時候應該基于鍵碼建立索引是各表中建立索引的表項： (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實現(xiàn)設計實現(xiàn)該設計的環(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）供應商操作注冊(register)insert into provider(name，password，address，tei，i

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

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

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

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

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

17、者交易員都可以刪除這個協(xié)議書；但是，當協(xié)議書簽字完畢后，協(xié)議書就不得刪除(修改)，只能由交易員進行處理；協(xié)議書有可能在轉成交易的過程中失敗，因為在交易達成以前，數(shù)據(jù)庫中的數(shù)據(jù)有可能因為其他交易而變化，一個協(xié)議書可能失效，這是允許的。根據(jù)以上分析，對數(shù)據(jù)庫的模型作一些修改，增加協(xié)議書表，其關系模式如下： agreement(customerld，providerid，partid，price，quantity，customersign，providersign)對應的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)與上述其他操作相比，對交易的操作對數(shù)據(jù)完整性要求比較高，其中需要注意的地方是；要防止同一用戶(供應商，顧客)的數(shù)據(jù)因兩個交易而同時修改； 需要同時對供應數(shù)據(jù)庫(supply)、需求數(shù)據(jù)庫(offertobuy)、交易數(shù)據(jù)庫(business)和協(xié)議數(shù)據(jù)庫(agreement)作出修改，而且需要保持這些修改的原子性；很顯然，這些要求正是對于一個事務(transaction)的要求，所以可以用一個事務來完成簽發(fā)一個協(xié)議的

20、操作。事務的描述如下：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為了使用方便,這里定義了一個存貯過程；功能是完成從agreementt的一個元組到business的一個元組的轉化工作。這里考慮到了刪除空的suppiy和offertobuy項，更加重要的是，這里考慮到了非法的agreement的情況，在一段時間后，由于供應商或者顧客修改數(shù)據(jù)，agreement可能就非法，這時就需要把這個事務廢除，所以，這里檢查了supply表和offertobuy表中的數(shù)據(jù)，確保數(shù)據(jù)仍然正確。另外交易員，或者說交易所必須承擔的一項任務是更新零件列表。這里在考慮顧客和供應商的時候并沒有給予他們修改零件列表

24、的權利，所以他們必須根據(jù)數(shù)據(jù)庫中已有的項更新自己的供求信息。由于這個數(shù)據(jù)庫實際上更加偏重于模型化，而不是一個實際環(huán)境中的數(shù)據(jù)庫，所以在實現(xiàn)應用模型的時候我們還需要對這個數(shù)據(jù)庫的模型作一些修改。由于本實驗在模型設計上使用了microsoft transact-sql的語法，因此以上的數(shù)據(jù)庫操作都是在sqlserver2000上測試通過的。5.測試階段1輸入數(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)(不同的實驗，id值可能不同。以后相應操作要保持前后一致就可以丁。)（2）插入供應商信息： create procedure insert_gysasinsert into provider(name，password，address，tel，intro)values(coml，1234，北京，6543210，nothing)顯示剛插入的供應商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）插入供應商供應信息： 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í)行交易操作設計（1）執(zhí)行交易存儲過程pass_agreement,參數(shù)為：1，1，1：pass_agreement 1，1，1; (后面的三個參數(shù)分別對應

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