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

1、數(shù)據(jù)庫原理課程設(shè)計題目 零件交易中心管理系統(tǒng)學(xué)院 信息項目學(xué)院專業(yè)計算機(jī)科學(xué)與技術(shù)班級計科072學(xué)號學(xué)生姓名指導(dǎo)教師 編寫日期2018-03-021. 需求分析32. 概念模型設(shè)計43. 邏輯設(shè)計4. 物理設(shè)計105. 測試階段6. 總結(jié)131.需求分析i. 供應(yīng)商供應(yīng)商的操作流程圖如圖2-1所示。2.顧客注冊圖2-1F供應(yīng)商操作分類表.增加供應(yīng)項刪除供應(yīng)項應(yīng)顧客的地位和，供應(yīng)流程圖如圖2-2所示。乎是對稱曝改所以功注冊顧客圖2-2顧客操作分類表增加需求項IIII注銷能分類上也很相似.顧客的操作二UUU二=壬=2三J刪除需求項注銷IIIIIII一1IIII修改需求項修改個人信息3.交易員交易員

2、的工作就是提出交易和完成交易。這里需要仔細(xì)考慮的問題是：一個 交易如何產(chǎn)生，并如何達(dá)成，可以用圖 2-3來說明這個問題.我們在處理交易的時候可能面臨如下問題:(1 一個交易只能在交易雙方都同意的情況下才可以進(jìn)行，所以數(shù)據(jù)庫中的 供求信息只能作為達(dá)成某個交易的基礎(chǔ);(2交易的雙方可能不同時使用這個系統(tǒng)，因此需要系統(tǒng)提供一個雙方交換 信息的方式;(3系統(tǒng)需要提供一種方便系統(tǒng)(交易員 向用戶提出建議來促成交易的途徑，并在保證數(shù)據(jù)庫數(shù)據(jù)完整性的情況下達(dá)成交易。2.概念模型設(shè)計供應(yīng)商交易員操客提出提出交易交易申請交易員提出 交易建議商信息、顧客信息及供應(yīng)商集和零件集1.正式簽字零件名價格簡介2.重量電話

3、數(shù)量價顏色顧客名重量地址零件號顏色數(shù)量重量價格顧客名3.邏數(shù)量地址Part(D，Color， Name Weight，Intro>供應(yīng)商以及顧客簽字£應(yīng) 丄圖供應(yīng)I商供應(yīng)商和零 件集之間的聯(lián)系電話數(shù)量顧客號號零件名簡介零件號供應(yīng)商3零件顧客商號y系件總成交易零件可以用EzR模型表述亥模型的設(shè)計;M>供應(yīng)商供應(yīng)零件(1> 零3-3 所顧客。N顧客以得到如下關(guān)系模式:(2>供應(yīng)商實體集轉(zhuǎn)換為關(guān)系 ProviderQL, Name Addtess , Tel , lntro>(3>顧客實體集轉(zhuǎn)換為關(guān)系 CustomerQD, Name Addtess,

4、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 , Quan tity>(6> 交易聯(lián)系轉(zhuǎn)換為關(guān)系Business(CustomerlD , ProviderlD , PartID ,Price , Quantity>每個關(guān)系模式的主鍵碼都用下劃線標(biāo)出。同時，對于從聯(lián)系導(dǎo)出的關(guān)系Supply(供應(yīng)> ,OfferToBuy(求購 > 和 Business(交易

5、 > ,使用與之相聯(lián)系的實體集的主健碼作為自己的鍵碼，必須符合 外鍵碼約束。對于Customer(顧客> ,Provider(供應(yīng)商 >和Part(零件之間，不存在直接 的約束，所以可以存在沒有供應(yīng)商供應(yīng)同時也沒有顧客求購的零件。4. 物理設(shè)計1為了提高在表中搜索元組的速度，在實際實現(xiàn)的時候應(yīng)該基于鍵碼建立 索引是各表中建立索引的表項：(1>part(ID>(2>Provider(ID>(3>Customer(ID>(4>Supply(PartID , ProviderID>(5>OfferTOBuy(CustomerID

6、 , PartID>(6>Business(CustomerlD , ProviderID , PartID>2用SQL實現(xiàn)設(shè)計實現(xiàn)該設(shè)計的環(huán)境為 Windows 2000 Perfessinal+MSSQLServer 2000.<1)建立Part表CREATE TABLEPart (ID smalli nt IDENTITY(1,1>PRIMARY KEY CLUSTEREDColor varchar(20>,Name varchar(20> NOT NULL,Weight int DEFAULT 0,Intro text><2)建立

7、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 IDENTITY(1,1> PRIMARY KEY CLUSTERED,Namevar

8、char(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> REFERENCES Part(ID&g

9、t;,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

10、 FOREIGN 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(Cuscom

11、erID,ProviderID,PartID>,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)商操作 注冊 (r

12、egister>INSERT INTO Provider(Name ， password ，Address ，TeI ，Intro> VALUES(#Nam，e #password ， #Address ，#Tel ， #Intro> 在登記操作后，供應(yīng)商得到一個唯一的ID ，可以根據(jù)這個 ID 采查詢和修改供應(yīng)商的數(shù)據(jù)。 注銷 (unregister>DELETE Provider WHERE(ID=#ID> ； 修改個人館息 (update>UPdate Provider Set(Name=#Name,Address=#Address ， Tel=#Te

13、l ， Intro=#Intro>WHERE(ID= #ID> ; 增加供應(yīng)項 (add_supply_item>INSERT INTO Supply(PartID， Providerid ，Price ， Quantity>VALUES(#PartID，#ProvderlD ， #Price ； #Quantily> ； 刪除供應(yīng)項 (delete_supply_item>DELETE SupPlyWHERE(PartlD=#PartID AND ProvideID=#ProviderlD>； 修改供應(yīng)項 (update_supply_item>

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

15、e #Address , #Tel> ;在登記操作后，顧客得到一個唯一的ID，可以根據(jù)這個ID來查詢和修改顧客的數(shù)據(jù)。 注銷(unregisterDELETE CustomerWHERE<ID=#ID> ； 修改個人信息(update>UPDATE Customer Set(Name=#Name Address=#Address ， Tel=#Tel> WHERE(1D=#ID； 增加需求項(add_OfferToBuy_item>INSERT INTO OfferToBuy(PartlD,CustomeriD ， Price ，Quantity>VA

16、LUES(#PartID，#CustomerID，#Price，#Quantity>' 刪除需求項(delete_OfferToBuy_iterm>DELETE OfferToBuyWHERE(PartlD=#PartlD AND CustomerlD=#CustomerID> ； 修改需求項(date_OfferToBuy_item>UPDATE OfferToBuy SET(Price=#Price ， Quantity=#QuantityWHERE(PartlD=#PartID AND CustomeriD=#CustomerID><9)交易員

17、針對需求分析中提出的問題，我們提出了“協(xié)議書”的解決方案，方案的說 明如下： 每個交易在達(dá)成以前都作為協(xié)議書保存在數(shù)據(jù)庫中，協(xié)議書具有和交易一 樣的完備信息，可以在條件成熟的情況下轉(zhuǎn)為一個達(dá)成的交易； 協(xié)議書只有在供應(yīng)商和顧客都簽字的情況下才有效；有效的協(xié)議書由交易 員簽發(fā)，協(xié)議書一經(jīng)簽發(fā)，就生效，表明一個交易的達(dá)成，數(shù)據(jù)庫中的數(shù)據(jù)將同 時予以修改； 協(xié)議書可以由供應(yīng)商、顧客或者交易員中的任意一個人提出申請。當(dāng)協(xié)議 書在雙方?jīng)]有都簽字前，協(xié)議的雙方或者交易員都可以刪除這個協(xié)議書；但是， 當(dāng)協(xié)議書簽字完畢后，協(xié)議書就不得刪除(修改，只能由交易員進(jìn)行處理； 協(xié)議書有可能在轉(zhuǎn)成交易的過程中失敗，因為

18、在交易達(dá)成以前，數(shù)據(jù)庫中 的數(shù)據(jù)有可能因為其他交易而變化，一個協(xié)議書可能失效，這是允許的。根據(jù)以上分析，對數(shù)據(jù)庫的模型作一些修改，增加協(xié)議書表，其關(guān)系模式如 下：PartID ， Price ， Qua ntity， CustomerSig n ，Agreeme nt(CustomerlD ， ProviderlDProviderSig n>對應(yīng)的SQL苗述為：CREATE TABLE Agreement(Customerm smallint ，ProviderlD smallint ，PartlD smallint ，Price int,Quantity int ，CustomerSi

19、gn int ，ProviderSign int ，CONSTRAINT PK_AGREEMENT PRIMARY KEY CLUSTERED(Custom，erIPDroviderID ， PartID> ， CONSTRAINT FK_AGREEMENT_CUSTOMERFIODREIGN KEY(CustomerID> REFERENCESCustomer(ID> ， CONSTRAINT FK_ AGREEMENT_PROVlDERIFDOREIGN KEY(ProviderID>REFERENCES Provider(ID> ， CONSTRAINTFK

20、_AGREEMENT_PARTID FOREIGN KEY(PartID>REFERENCES Part(ID>> 與上述其他操作相比，對交易的操作對數(shù)據(jù)完整性要求比較高，其中需要注 意的地方是；要防止同一用戶 ( 供應(yīng)商，顧客 >的數(shù)據(jù)因兩個交易而同時修改；需要同時對供應(yīng)數(shù)據(jù)庫(Supply、需求數(shù)據(jù)庫(OfferToBuy>、交易數(shù)據(jù)庫(Business>和協(xié)議數(shù)據(jù)庫(Agreement>作出修改，而且需要保持這些修改的原子 性；很顯然，這些要求正是對于一個事務(wù) (transaction> 的要求，所以可以用一 個事務(wù)來完成簽發(fā)一個協(xié)議的操作

21、。事務(wù)的描述如下：CREATE PROC PASS_AGREEMENTproviderID int ，customerid int ，partlD intASDECLARE TransName VARCHAR(20>SELECT TransName='Pass_Agreement'BEGIN TRANSACTION TransNameDEClARE price INT ，qUANTITY intSELECT price=price ， quantity=quantity FROM AgreementWHERE prIVIderID=providerID AND custo

22、merID=customerID 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 Proideri

23、d=provider AND partID=PartID><0ROLLBACK 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 TRANSA

24、CTION TransNameDELETE FROM OfferToBuy WHERE quantity=0COMMIT TRANSACTION TransName為了使用方便 , 這里定義了一個存貯過程；功能是完成從 Agreementt 的一個 元組到 Business 的一個元組的轉(zhuǎn)化工作。這里考慮到了刪除空的 Suppiy 和 OfferTOBUY 項，更加重要的是，這里考慮到了非法的 Agreement 的情況，在一 段時間后，因為供應(yīng)商或者顧客修改數(shù)據(jù)， Agreement 可能就非法，這時就需要 把這個事務(wù)廢除，所以，這里檢查了 Supply 表和 OfferToBuy 表中的數(shù)

25、據(jù)，確保 數(shù)據(jù)仍然正確。另外交易員，或者說交易所必須承擔(dān)的一項任務(wù)是更新零件列表。這里在考 慮顧客和供應(yīng)商的時候十并沒有給予他們修改零件列表的權(quán)利，所以他們必須根 據(jù)數(shù)據(jù)庫中已有的項更新自己的供求信息。因為這個數(shù)據(jù)庫實際上更加偏重于模型化，而不是一個實際環(huán)境中的數(shù)據(jù) 庫，所以在實現(xiàn)應(yīng)用模型的時候我們還需要對這個數(shù)據(jù)庫的模型作一些修改。因為本實驗在模型設(shè)計上使用了 Microsoft Transact-SQL 的語法，因此以 上的數(shù)據(jù)庫操作都是在 SQLSERVER20上）測試通過的。5. 測試階段1輸入數(shù)據(jù)設(shè)計<1）插入零件信息；create procedure insert_ljasi

26、nsert into Part（Color， Name， Weight ， Intro>values（'black' ，'stick' ，'30' ， 'of steel'>顯示剛插人的零件 id ：exec insert_ljid1（1 row（s> affected>（不同的實驗， id 值可能不同。以后相應(yīng)操作要保持前后一致就可以丁。 ><2）插入供應(yīng)商信息：create procedure insert_gysasinsert into Provider(Name，password，Ad

27、dress，Tel ， Intro>values('coml' ， '1234' ，' 北京 ' ， 6543210， 'nothing'> 顯示剛插入的供應(yīng)商 id ：exec insert_gysid1(1 row(s> affected><3)插入顧客信息：create procedure insert_gkasinsert into Customer(Name， Address ， Tel>values('cusl' ，'北京' ，'6666666

28、'>顯示剛插入的顧客 id ：exec insert_gkid1(1 row(S>affected><4)插入供應(yīng)商供應(yīng)信息：create procedure insert_gysgyasinsert into Supply(PartID， ProviderlD ， Price ， Quantity>values(1， 1， 20， 100> ；<5)插入顧客需求信息：create procedure insert_gkxqasinsert into OfferToBuy(PartlD，CustomerID ， Priee ， Quantity

29、>values(1，1，20， 50>；<6)插入?yún)f(xié)議信息：create procedure insert_xyxxQuantity ，asinsert into Agreement(CustomerID,ProviderID ， PartlD ， Price ， CustomerSign ， ProviderSign>values(1，1，1，20，30， 1，1>；2. 執(zhí)行交易操作設(shè)計<1)執(zhí)行交易存儲過程 PASS_AGREEME參數(shù)為：1, 1, 1:PASS_AGREEMENT，11，1。(后面的三個參數(shù)分別對應(yīng)前面選擇出的供應(yīng)商ID、顧客ID和

30、零件ID。><2)結(jié)果：顯示交易后供應(yīng)信息和需求信息： create procedure 交易后供應(yīng)信息 PartID int(8> ProviderID int(8> as select Quantity from SUpply where PartID=PartID and ProviderID=ProviderID exec 交易后供應(yīng)信息 Quantity70 (1 row(s> affected> create procedure 交易后需求信息 PartID int(8> CustomerID int(8> as select Quantity from OfferToBuy where PartlD=PartID and C