(完整word版)數(shù)據(jù)庫(kù)課程設(shè)計(jì)—零件管理系統(tǒng)0001

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

2、員的工作就是提出交易和完成交易。這里需要仔細(xì)考慮的問(wèn)題是：一個(gè) 交易如何產(chǎn)生，并如何達(dá)成，可以用圖 2-3來(lái)說(shuō)明這個(gè)問(wèn)題. 我們?cè)谔幚斫灰椎臅r(shí)候可能面臨如下問(wèn)題： (1) 一個(gè)交易只能在交易雙方都同意的情況下才可以進(jìn)行，所以數(shù)據(jù)庫(kù)中的供 求信息只能作為達(dá)成某個(gè)交易的基礎(chǔ)； (2) 交易的雙方可能不同時(shí)使用這個(gè)系統(tǒng)，因此需要系統(tǒng)提供一個(gè)雙方交換信 息的方式; (3) 系統(tǒng)需要提供一種方便系統(tǒng)(交易員)向用戶提出建議來(lái)促成交易的途徑, 并在保證數(shù)據(jù)庫(kù)數(shù)據(jù)完整性的情況下達(dá)成交易。 I I I I I I 1 I i I I I 供應(yīng)商 提出交易 圖2-3 交易員操作圖 交易員簽發(fā) 完成父易 交易員提

3、出 交易建議 顧客提出 交易申請(qǐng) 協(xié)議書(shū)草案 L 概念很型設(shè) 數(shù)據(jù)庫(kù)需要表： 員 應(yīng)商集和零件集之間的聯(lián)系(供應(yīng)) 2. 茅息有以下幾種：零件信息及顧 1.供應(yīng)商集和零件集之間的聯(lián)系 I I I I I I I I I i I I L亠 (供應(yīng))正式簽字 2.顧客集和零件集之間的聯(lián)系（求購(gòu)） 圖3-2顧客和零件之間的聯(lián)系（求購(gòu)）E-R模型 3.交易（三元聯(lián)系） 可以用E-R模型表述該模型的設(shè)計(jì)，E-R圖如圖3-3所示 零件號(hào) 簡(jiǎn)介 顏色 數(shù)量 數(shù)量 重量 零件 供應(yīng) 求購(gòu) 價(jià)格 價(jià)格 顧客號(hào) 交易 電話 顧客名 地址 供應(yīng)商名 價(jià)格 地址 供應(yīng)商號(hào) 零件名 供應(yīng)商 簡(jiǎn)介 數(shù)量 顧客 1 電話

4、圖3-3全局E-R模型 3. 邏輯設(shè)計(jì) 通過(guò)E/R模型到關(guān)系模型的轉(zhuǎn)化，可以得到如下關(guān)系模式: 零件實(shí)體集轉(zhuǎn)換為關(guān)系：Part(l, Color , Name Weight, Intro) (2) 供應(yīng)商實(shí)體集轉(zhuǎn)換為關(guān)系 Provider(l, Name Addtess , Tel , Intro) (3) 顧客實(shí)體集轉(zhuǎn)換為關(guān)系CustomerQD, Name Addtess, Tel) 供應(yīng)聯(lián)系轉(zhuǎn)換為關(guān)系 Supply(PartlD , ProviderlD , Price , Quantity) (5) 求購(gòu)聯(lián)系轉(zhuǎn)換為關(guān)系 OfferToBuy(CustomerlD , PartID ,

5、Price , Quantity) (6) 交易聯(lián)系轉(zhuǎn)換為關(guān)系 Business(CustomerlD , ProviderlD , PartID , Price , Qua ntity) 每個(gè)關(guān)系模式的主鍵碼都用下劃線標(biāo)出。 同時(shí)，對(duì)于從聯(lián)系導(dǎo)出的關(guān)系Supply(供應(yīng))，OfferToBuy(求購(gòu))和 Business(交易),使用與之相聯(lián)系的實(shí)體集的主健碼作為自己的鍵碼,必須符合 外鍵碼約束。 對(duì)于Customer(顧客)，Provider(供應(yīng)商)和Part(零件)之間，不存在直接的 約束，所以可以存在沒(méi)有供應(yīng)商供應(yīng)同時(shí)也沒(méi)有顧客求購(gòu)的零件。 4. 物理設(shè)計(jì) 1. 為了提高在表中搜索元

6、組的速度，在實(shí)際實(shí)現(xiàn)的時(shí)候應(yīng)該基于鍵碼建立索 引是各表中建立索引的表項(xiàng)： (1) part(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 TABLEPart ( ID smalli

7、 nt IDENTITY(1,1)PRIMARY KEY CLUSTERED Color varchar(20), Name varchar(20) NOT NULL, Weight int DEFAULT 0, In tro text) (2) 建立 Provider 表 CREATE TABLE Provide ID smalli nt IDENTITY(1,1)PRIMARY KEY CLUSTERED, Name varchar(20) NOT NULL, password varchar(8) NOT NULL, Address varchar(30), Tel varchar(20

8、), In trotext) (3) 建立 Customer 表 CREATE TABLE Customer ( ID Smalli nt IDENTITY(1,1)PRIMARY KEY CLUSTERED, Namevarchar(20) NOT NULL, Address varchar(30), TeL Varchar(20) (4) 建立Supply表 CREATE TABLE Supply( PartID Smalli nt, ProviderID smalli nt, Price int, QUa ntity int, CONSTRAINT PK_SUPPLY PRIMARY K

9、EY CLUSTERED(PartID,ProviderID), CONSTRAINT FK_SUPPLY_PARTID FOREIGN KEY(PartID) REFERENCES Part(ID), CONSTRAINTK_SUPPLY_PROVIDERFOREIGNKEY(ProviderlD) REFERENCES Provider(ID) (5) 建立 OfferToBuy 表 CREATE TABLE OfferToBuy( CustomerID smalli nt, PartID Smalli nt, Price int, Quan tity int, CONSTRAINTPK_

10、OFFERTOBUYPRIMARYKEY CLUSTERED(CustomerID,PartID), CONSTRAINT FK_OFFERTOBUY_CUSTOMERID FOREIGN KEY(CustomerID) REFERENCES Customer(ID), CONSTRAINT FK_OFFERTOBUY FOREIGN KEY(PartID) REFERENCES Part(ID) (6) 建立 Business 表 CREATE TABLE Bus in ess( CustomerID smalli nt, ProviderID smalli nt, PartID Small

11、i nt, Price int. Qua ntity int, CONSTRAINT PK_BUSINEssPRIMARYKEY CIUSTERED(CuscomerlD,ProviderlD,PartlD), CONSTRAINT FK_BUSINESS_CUSTOMERID FOREIGN KEY(CustomerID) REFERENCES Customer(ID), CONSTRAINT FK_BUSINESS_PROVIDERID FOREIGN KEY(ProviderID) REFERENCES Provider(ID), CONSTRAINT FK_BUSINESS_PARTI

12、D FOREIGN KEY(PartID) REFERENCES Part(ID) (7) 供應(yīng)商操作 注冊(cè)(register) INSERT INTO Provider(Name , password , Address , TeI , Intro) VALUES(#Nam, #password , #Address , #Tel , #lntro) 在登記操作后，供應(yīng)商得到一個(gè)唯一的ID，可以根據(jù)這個(gè)ID采查詢和修改 供應(yīng)商的數(shù)據(jù)。 注銷(unregister) DELETE Provider WHERE(ID=#ID) ； 修改個(gè)人館息(update) UPdate Provider S

13、et(Name=#Name,Address=#Address ， Tel=#Tel ， Intro=#Intro) WHERE(ID= #ID); 增加供應(yīng)項(xiàng)(add_supply_item) INSERT INTO Supply(PartID ， Providerid ， Price ， Quantity) VALUES(#PartID ，#ProvderlD，#Price ; #Quantily); 刪除供應(yīng)項(xiàng)(delete_supply_item) DELETE SupPly WHERE(PartlD=#PartID AND ProvideID=#ProviderlD); 修改供應(yīng)項(xiàng)(u

14、pdate_supply_item) UPDATESupplySET(Price=#Price ， Quantity=#Quantity) WHERE(PartlD=#PartID AND ProviderID=#ProviderID) 很明顯，系統(tǒng)并沒(méi)有提供面向供應(yīng)商修改零件信息的接口，所以供應(yīng)商提供 的零件必須已經(jīng)在零件表中存在；可以這祥假設(shè)，交易所的管理員負(fù)責(zé)更新零件 信息，而供應(yīng)商可以向交易所申請(qǐng)?jiān)黾幽撤N零件的信息事實(shí)上顧客也可以提出 這樣的要求。 (8) 顧客操作 注冊(cè)(register) INSERT INTO Customer(Name， Address， Tel) VALUES

15、(#Name #Address , #Tel); 在登記操作后，顧客得到一個(gè)唯一的ID，可以根據(jù)這個(gè)ID來(lái)查詢和修改顧客 的數(shù)據(jù)。 注銷(unregister) DELETE Customer WHEREID=#ID)； 修改個(gè)人信息(update) UPDATE Customer Set(Name=#Name ， Address=#Address ，Tel=#Tel) WHERE(1D=#ID)； 增加需求項(xiàng)(add_OfferToBuy_item) INSERT INTO OfferToBuy(PartlD,CustomeriD ， Price ，Quantity) VALUES(#Par

16、tID，#CustomerID，#Price，#Quantity) 刪除需求項(xiàng)(delete_OfferToBuy_iterm) DELETE OfferToBuy WHERE(PartlD=#PartlD AND CustomerlD=#CustomerID) ； 修改需求項(xiàng)(date_OfferToBuy_item) UPDATE OfferToBuy SET(Price=#Price ， Quantity=#Quantity WHERE(PartlD=#PartID AND CustomeriD=#CustomerID) (9) 交易員 針對(duì)需求分析中提出的問(wèn)題，我們提出了“協(xié)議書(shū)”的解

17、決方案，方案的說(shuō) 明如下： 每個(gè)交易在達(dá)成以前都作為協(xié)議書(shū)保存在數(shù)據(jù)庫(kù)中，協(xié)議書(shū)具有和交易一 樣的完備信息，可以在條件成熟的情況下轉(zhuǎn)為一個(gè)達(dá)成的交易； 協(xié)議書(shū)只有在供應(yīng)商和顧客都簽字的情況下才有效；有效的協(xié)議書(shū)由交易 員簽發(fā)，協(xié)議書(shū)一經(jīng)簽發(fā)，就生效，表明一個(gè)交易的達(dá)成，數(shù)據(jù)庫(kù)中的數(shù)據(jù)將同 時(shí)予以修改； 協(xié)議書(shū)可以由供應(yīng)商、顧客或者交易員中的任意一個(gè)人提出申請(qǐng)。當(dāng)協(xié)議 書(shū)在雙方?jīng)]有都簽字前，協(xié)議的雙方或者交易員都可以刪除這個(gè)協(xié)議書(shū)；但是， 當(dāng)協(xié)議書(shū)簽字完畢后，協(xié)議書(shū)就不得刪除(修改)，只能由交易員進(jìn)行處理； 協(xié)議書(shū)有可能在轉(zhuǎn)成交易的過(guò)程中失敗，因?yàn)樵诮灰走_(dá)成以前，數(shù)據(jù)庫(kù)中 的數(shù)據(jù)有可能因?yàn)槠渌?/p>

18、易而變化，一個(gè)協(xié)議書(shū)可能失效，這是允許的。 根據(jù)以上分析，對(duì)數(shù)據(jù)庫(kù)的模型作一些修改，增加協(xié)議書(shū)表，其關(guān)系模式如 下： Agreement(CustomerlD ， ProviderlD ， PartID ， Price ， Quantity ， CustomerSign ， ProviderSig n) 對(duì)應(yīng)的SQL苗述為： CREATE TABLE Agreeme nt( Customerm smalli nt, ProviderlD smalli nt , PartlD smalli nt, Price int, Quan tity int , CustomerSig n int , Pr

19、oviderSig n int , CONSTRAINT PK_AGREEMENT PRIMARY KEY CLUSTERED(CustomerlPToviderlD , PartID) , CONSTRAINT FK_AGREEMENT_CUSTOMERFOREIGN KEY(CustomerID) REFERENCESCustomer(ID) , CONSTRAINT FK_ AGREEMENT_PROVlDERIDOREIGN KEY(ProviderID) REFERENCES Provider(ID) , CONSTRAINTK_AGREEMENT_PARTID FOREIGN KE

20、Y(PartID)REFERENCES Part(ID) 與上述其他操作相比，對(duì)交易的操作對(duì)數(shù)據(jù)完整性要求比較高，其中需要注 意的地方是；要防止同一用戶(供應(yīng)商，顧客)的數(shù)據(jù)因兩個(gè)交易而同時(shí)修改； 需要同時(shí)對(duì)供應(yīng)數(shù)據(jù)庫(kù)(Supply)、需求數(shù)據(jù)庫(kù)(OfferToBuy)、交易數(shù)據(jù)庫(kù) (Bus in ess)和協(xié)議數(shù)據(jù)庫(kù)(Agreeme nt)作出修改，而且需要保持這些修改的原子 性；很顯然，這些要求正是對(duì)于一個(gè)事務(wù)(transaction)的要求，所以可以用一個(gè) 事務(wù)來(lái)完成簽發(fā)一個(gè)協(xié)議的操作。事務(wù)的描述如下： CREATE PROC PASS_AGREEMENT providerlD int

21、， customerid int ， partlD int AS DECLARE Tra nsName VARCHAR(20) SELECT Tra nsName=Pass_Agreeme nt BEGIN TRANSACTION Tra nsName DEClARE price INT，qUANTITY int SELECT price=price ， quantity=quantity FROM Agreement WHERE prlVlderlD=providerlDAND customerID=customerID AND Pan ID=partlD 1NSERTNTOBusiness

22、(ProviderlD， CustomerlD， PartID ， Price ， Quantity) VALues(providerid ， customerID，PartID，price,quantity) UPDATE Supply SET qua ntity=qua ntity-qua ntity WHERE ProviderID=prividerID AND partID=partID IF (SELECT qua ntity FROM Supply WHERE Proiderid=provider AND partID=PartID)0 ROLLBACK TRANSACTlON T

23、ra nSName DELETE FROM Supply WHERE qua ntity=O UPDATE OfferToBuy SET qua ntity=qua nttity-qua ntity WHERE CustomerlD=customerid AND partlD=partlD IF(SELECT qua ndtity FROM OfferToBuy WHERE CustomerID=CustomerID AND partID=partlD)0 ROLLBACK TRANSACTION Tra nsName DELETE FROM OfferToBuy WHERE qua ntit

24、y=O COMMIT TRANSACTION Tra nsName 為了使用方便,這里定義了一個(gè)存貯過(guò)程；功能是完成從 Agreementt的一個(gè) 元組到Bus in ess的一個(gè)元組的轉(zhuǎn)化工作。這里考慮到了刪除空的Suppiy和 OfferTOBUY項(xiàng)，更加重要的是，這里考慮到了非法的Agreement的情況，在一段 時(shí)間后，由于供應(yīng)商或者顧客修改數(shù)據(jù)，Agreement可能就非法，這時(shí)就需要把 這個(gè)事務(wù)廢除，所以，這里檢查了Supply表和OfferToBuy表中的數(shù)據(jù)，確保數(shù) 據(jù)仍然正確。 另外交易員，或者說(shuō)交易所必須承擔(dān)的一項(xiàng)任務(wù)是更新零件列表。這里在考 慮顧客和供應(yīng)商的時(shí)候十并沒(méi)有給

25、予他們修改零件列表的權(quán)利，所以他們必須根 據(jù)數(shù)據(jù)庫(kù)中已有的項(xiàng)更新自己的供求信息。 由于這個(gè)數(shù)據(jù)庫(kù)實(shí)際上更加偏重于模型化，而不是一個(gè)實(shí)際環(huán)境中的數(shù)據(jù)庫(kù)， 所以在實(shí)現(xiàn)應(yīng)用模型的時(shí)候我們還需要對(duì)這個(gè)數(shù)據(jù)庫(kù)的模型作一些修改。 由于本實(shí)驗(yàn)在模型設(shè)計(jì)上使用了 Microsoft Tran sact-SQL的語(yǔ)法，因此以 上的數(shù)據(jù)庫(kù)操作都是在 SQLSERVER20上!測(cè)試通過(guò)的。 5. 測(cè)試階段 1輸入數(shù)據(jù)設(shè)計(jì) (1) 插入零件信息； create procedure in sert_lj as insert into Part(Color ， Name Weight， Intro) values(bla

26、ck ，stick ， 30 ， of steel) 顯示剛插人的零件id : exec in sert_lj id 1 (1 row(s) affected) (不同的實(shí)驗(yàn)，id值可能不同。以后相應(yīng)操作要保持前后一致就可以丁。) (2) 插入供應(yīng)商信息: create procedure in sert_gys as insert into Provider(Name , password , Address , Tel , Intro) values(coml , 1234,北京，6543210, nothing) 顯示剛插入的供應(yīng)商id : exec in sert_gys id 1 (

27、1 row(s) affected) (3) 插入顧客信息： create procedure in sert_gk as insert into Customer(Name , Address , Tel) values(cusl ,北京,6666666) 顯示剛插入的顧客id : exec in sert_gk id 1 (1 row(S)affected) (4) 插入供應(yīng)商供應(yīng)信息： create procedure in sert_gysgy as insert into Supply(PartlD, ProviderlD , Price , Quantity) values(1 ,

28、 1, 20, 100); (5) 插入顧客需求信息： create procedure in sert_gkxq as insert into OfferToBuy(PartlD，CustomerID， Priee ， Quantity) values(1 ， 1， 20， 50)； (6) 插入?yún)f(xié)議信息： create procedure in sert_xyxx as Qua ntity ， insertintoAgreement(CustomerlD,ProviderlD， PartlD ， Price ， CustomerSign，ProviderSign) values(1 ， 1

29、， 1，20， 30， 1， 1)； 2. 執(zhí)行交易操作設(shè)計(jì) (1) 執(zhí)行交易存儲(chǔ)過(guò)程 PASS_AGREEME參數(shù)為：1，1，1： PASS_AGREEMENT 11 , 1； (后面的三個(gè)參數(shù)分別對(duì)應(yīng)前面選擇出的供應(yīng)商ID、顧客ID和零件ID。 (2) 結(jié)果：顯示交易后供應(yīng)信息和需求信息： create procedure交易后供應(yīng)信息 PartID in t(8) ProviderlD in t(8) as select Quan tity from SUpply where PartID=PartID and ProviderlD=ProviderlD exec交易后供應(yīng)信息 Quan tity 70 (1 row(s) affected) create procedure交易后需求信息 PartID in t(8) CustomerlD int(8) as select Quan tity from OfferToBuy where P