(完整word版)数据库课程设计—零件管理系统0001

1、数据库原理课程设计 题目零件交易中心管理系统 学院信息工程学院 专业计算机科学与技术 班级 计科072 学号 学生姓名 指导教师 编写日期 2010-03-02 1. 需求分析 2 2. 概念模型设计 3 2 3. 逻辑设计 4 4. 物理设计 5 5. 测试阶段 10 6. 总结 13 1.需求分析 1.供应商 供应商的操作流程图如图2-1所示 项厂、修改供应项修改个人信息 L: 图2-1 供应商操作分类表 2顾客 顾客的地位和供应商几乎是对称的，所以功能分类上也很相似顾客的操作 流程图如图2-2所示 修改个人信息 图2-2顾客操作分类表 删除需求项 1 I I I I I 3.交易员 交易

2、员的工作就是提出交易和完成交易。这里需要仔细考虑的问题是：一个 交易如何产生，并如何达成，可以用图 2-3来说明这个问题. 我们在处理交易的时候可能面临如下问题： (1) 一个交易只能在交易双方都同意的情况下才可以进行，所以数据库中的供 求信息只能作为达成某个交易的基础； (2) 交易的双方可能不同时使用这个系统，因此需要系统提供一个双方交换信 息的方式; (3) 系统需要提供一种方便系统(交易员)向用户提出建议来促成交易的途径, 并在保证数据库数据完整性的情况下达成交易。 I I I I I I 1 I i I I I 供应商 提出交易 图2-3 交易员操作图 交易员签发 完成父易 交易员提

3、出 交易建议 顾客提出 交易申请 协议书草案 L 概念很型设 数据库需要表： 员 应商集和零件集之间的联系(供应) 2. 茅息有以下几种：零件信息及顾 1.供应商集和零件集之间的联系 I I I I I I I I I i I I L亠 (供应)正式签字 2.顾客集和零件集之间的联系（求购） 图3-2顾客和零件之间的联系（求购）E-R模型 3.交易（三元联系） 可以用E-R模型表述该模型的设计，E-R图如图3-3所示 零件号 简介 颜色 数量 数量 重量 零件 供应 求购 价格 价格 顾客号 交易 电话 顾客名 地址 供应商名 价格 地址 供应商号 零件名 供应商 简介 数量 顾客 1 电话

4、图3-3全局E-R模型 3. 逻辑设计 通过E/R模型到关系模型的转化，可以得到如下关系模式: 零件实体集转换为关系：Part(l, Color , Name Weight, Intro) (2) 供应商实体集转换为关系 Provider(l, Name Addtess , Tel , Intro) (3) 顾客实体集转换为关系CustomerQD, Name Addtess, Tel) 供应联系转换为关系 Supply(PartlD , ProviderlD , Price , Quantity) (5) 求购联系转换为关系 OfferToBuy(CustomerlD , PartID ,

5、Price , Quantity) (6) 交易联系转换为关系 Business(CustomerlD , ProviderlD , PartID , Price , Qua ntity) 每个关系模式的主键码都用下划线标出。 同时，对于从联系导出的关系Supply(供应)，OfferToBuy(求购)和 Business(交易),使用与之相联系的实体集的主健码作为自己的键码,必须符合 外键码约束。 对于Customer(顾客)，Provider(供应商)和Part(零件)之间，不存在直接的 约束，所以可以存在没有供应商供应同时也没有顾客求购的零件。 4. 物理设计 1. 为了提高在表中搜索元

6、组的速度，在实际实现的时候应该基于键码建立索 引是各表中建立索引的表项： (1) part(ID) (2) Provider(ID) (3) Customer(ID) (4) Supply(PartID, ProviderID (5) OfferTOBuy(CustomerID , PartID) (6) Business(CustomerlD , ProviderID , PartID) 2用SQL实现设计 实现该设计的环境为 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) 供应商操作 注册(register) INSERT INTO Provider(Name , password , Address , TeI , Intro) VALUES(#Nam, #password , #Address , #Tel , #lntro) 在登记操作后，供应商得到一个唯一的ID，可以根据这个ID采查询和修改 供应商的数据。 注销(unregister) DELETE Provider WHERE(ID=#ID) ； 修改个人馆息(update) UPdate Provider S

13、et(Name=#Name,Address=#Address ， Tel=#Tel ， Intro=#Intro) WHERE(ID= #ID); 增加供应项(add_supply_item) INSERT INTO Supply(PartID ， Providerid ， Price ， Quantity) VALUES(#PartID ，#ProvderlD，#Price ; #Quantily); 删除供应项(delete_supply_item) DELETE SupPly WHERE(PartlD=#PartID AND ProvideID=#ProviderlD); 修改供应项(u

14、pdate_supply_item) UPDATESupplySET(Price=#Price ， Quantity=#Quantity) WHERE(PartlD=#PartID AND ProviderID=#ProviderID) 很明显，系统并没有提供面向供应商修改零件信息的接口，所以供应商提供 的零件必须已经在零件表中存在；可以这祥假设，交易所的管理员负责更新零件 信息，而供应商可以向交易所申请增加某种零件的信息事实上顾客也可以提出 这样的要求。 (8) 顾客操作 注册(register) INSERT INTO Customer(Name， Address， Tel) VALUES

15、(#Name #Address , #Tel); 在登记操作后，顾客得到一个唯一的ID，可以根据这个ID来查询和修改顾客 的数据。 注销(unregister) DELETE Customer WHEREID=#ID)； 修改个人信息(update) UPDATE Customer Set(Name=#Name ， Address=#Address ，Tel=#Tel) WHERE(1D=#ID)； 增加需求项(add_OfferToBuy_item) INSERT INTO OfferToBuy(PartlD,CustomeriD ， Price ，Quantity) VALUES(#Par

16、tID，#CustomerID，#Price，#Quantity) 删除需求项(delete_OfferToBuy_iterm) DELETE OfferToBuy WHERE(PartlD=#PartlD AND CustomerlD=#CustomerID) ； 修改需求项(date_OfferToBuy_item) UPDATE OfferToBuy SET(Price=#Price ， Quantity=#Quantity WHERE(PartlD=#PartID AND CustomeriD=#CustomerID) (9) 交易员 针对需求分析中提出的问题，我们提出了“协议书”的解

17、决方案，方案的说 明如下： 每个交易在达成以前都作为协议书保存在数据库中，协议书具有和交易一 样的完备信息，可以在条件成熟的情况下转为一个达成的交易； 协议书只有在供应商和顾客都签字的情况下才有效；有效的协议书由交易 员签发，协议书一经签发，就生效，表明一个交易的达成，数据库中的数据将同 时予以修改； 协议书可以由供应商、顾客或者交易员中的任意一个人提出申请。当协议 书在双方没有都签字前，协议的双方或者交易员都可以删除这个协议书；但是， 当协议书签字完毕后，协议书就不得删除(修改)，只能由交易员进行处理； 协议书有可能在转成交易的过程中失败，因为在交易达成以前，数据库中 的数据有可能因为其他交

18、易而变化，一个协议书可能失效，这是允许的。 根据以上分析，对数据库的模型作一些修改，增加协议书表，其关系模式如 下： Agreement(CustomerlD ， ProviderlD ， PartID ， Price ， Quantity ， CustomerSign ， ProviderSig n) 对应的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) 与上述其他操作相比，对交易的操作对数据完整性要求比较高，其中需要注 意的地方是；要防止同一用户(供应商，顾客)的数据因两个交易而同时修改； 需要同时对供应数据库(Supply)、需求数据库(OfferToBuy)、交易数据库 (Bus in ess)和协议数据库(Agreeme nt)作出修改，而且需要保持这些修改的原子 性；很显然，这些要求正是对于一个事务(transaction)的要求，所以可以用一个 事务来完成签发一个协议的操作。事务的描述如下： 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 为了使用方便,这里定义了一个存贮过程；功能是完成从 Agreementt的一个 元组到Bus in ess的一个元组的转化工作。这里考虑到了删除空的Suppiy和 OfferTOBUY项，更加重要的是，这里考虑到了非法的Agreement的情况，在一段 时间后，由于供应商或者顾客修改数据，Agreement可能就非法，这时就需要把 这个事务废除，所以，这里检查了Supply表和OfferToBuy表中的数据，确保数 据仍然正确。 另外交易员，或者说交易所必须承担的一项任务是更新零件列表。这里在考 虑顾客和供应商的时候十并没有给

25、予他们修改零件列表的权利，所以他们必须根 据数据库中已有的项更新自己的供求信息。 由于这个数据库实际上更加偏重于模型化，而不是一个实际环境中的数据库， 所以在实现应用模型的时候我们还需要对这个数据库的模型作一些修改。 由于本实验在模型设计上使用了 Microsoft Tran sact-SQL的语法，因此以 上的数据库操作都是在 SQLSERVER20上!测试通过的。 5. 测试阶段 1输入数据设计 (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) (不同的实验，id值可能不同。以后相应操作要保持前后一致就可以丁。) (2) 插入供应商信息: create procedure in sert_gys as insert into Provider(Name , password , Address , Tel , Intro) values(coml , 1234,北京，6543210, nothing) 显示刚插入的供应商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) 插入供应商供应信息： 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) 插入协议信息： 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. 执行交易操作设计 (1) 执行交易存储过程 PASS_AGREEME参数为：1，1，1： PASS_AGREEMENT 11 , 1； (后面的三个参数分别对应前面选择出的供应商ID、顾客ID和零件ID。 (2) 结果：显示交易后供应信息和需求信息： create procedure交易后供应信息 PartID in t(8) ProviderlD in t(8) as select Quan tity from SUpply where PartID=PartID and ProviderlD=ProviderlD exec交易后供应信息 Quan tity 70 (1 row(s) affected) create procedure交易后需求信息 PartID in t(8) CustomerlD int(8) as select Quan tity from OfferToBuy where P