数据库课程设计—零件管理系统

1、数据库原理课程设计题目 零件交易中心管理系统学院 信息项目学院专业计算机科学与技术班级计科072学号学生姓名指导教师 编写日期2018-03-021. 需求分析32. 概念模型设计43. 逻辑设计4. 物理设计105. 测试阶段6. 总结131.需求分析i. 供应商供应商的操作流程图如图2-1所示。2.顾客注册图2-1F供应商操作分类表.增加供应项删除供应项应顾客的地位和，供应流程图如图2-2所示。乎是对称曝改所以功注册顾客图2-2顾客操作分类表增加需求项IIII注销能分类上也很相似.顾客的操作二UUU二=壬=2三J删除需求项注销IIIIIII一1IIII修改需求项修改个人信息3.交易员交易员

2、的工作就是提出交易和完成交易。这里需要仔细考虑的问题是：一个 交易如何产生，并如何达成，可以用图 2-3来说明这个问题.我们在处理交易的时候可能面临如下问题:(1 一个交易只能在交易双方都同意的情况下才可以进行，所以数据库中的 供求信息只能作为达成某个交易的基础;(2交易的双方可能不同时使用这个系统，因此需要系统提供一个双方交换 信息的方式;(3系统需要提供一种方便系统(交易员 向用户提出建议来促成交易的途径，并在保证数据库数据完整性的情况下达成交易。2.概念模型设计供应商交易员操客提出提出交易交易申请交易员提出 交易建议商信息、顾客信息及供应商集和零件集1.正式签字零件名价格简介2.重量电话

3、数量价颜色顾客名重量地址零件号颜色数量重量价格顾客名3.逻数量地址Part(D，Color， Name Weight，Intro>供应商以及顾客签字£应 丄图供应I商供应商和零 件集之间的联系电话数量顾客号号零件名简介零件号供应商3零件顾客商号y系件总成交易零件可以用EzR模型表述亥模型的设计;M>供应商供应零件(1> 零3-3 所顾客。N顾客以得到如下关系模式:(2>供应商实体集转换为关系 ProviderQL, Name Addtess , Tel , lntro>(3>顾客实体集转换为关系 CustomerQD, Name Addtess,

4、Tel>(4>供应联系转换为关系 Supply(PartlD , ProviderlD , Price , Quantity>(5> 求购联系转换为关系 OfferToBuy(CustomerlD , PartID , Price , Quan tity>(6> 交易联系转换为关系Business(CustomerlD , ProviderlD , PartID ,Price , Quantity>每个关系模式的主键码都用下划线标出。同时，对于从联系导出的关系Supply(供应> ,OfferToBuy(求购 > 和 Business(交易

5、 > ,使用与之相联系的实体集的主健码作为自己的键码，必须符合 外键码约束。对于Customer(顾客> ,Provider(供应商 >和Part(零件之间，不存在直接 的约束，所以可以存在没有供应商供应同时也没有顾客求购的零件。4. 物理设计1为了提高在表中搜索元组的速度，在实际实现的时候应该基于键码建立 索引是各表中建立索引的表项：(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实现设计实现该设计的环境为 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)供应商操作 注册 (r

12、egister>INSERT INTO Provider(Name ， password ，Address ，TeI ，Intro> VALUES(#Nam，e #password ， #Address ，#Tel ， #Intro> 在登记操作后，供应商得到一个唯一的ID ，可以根据这个 ID 采查询和修改供应商的数据。 注销 (unregister>DELETE Provider WHERE(ID=#ID> ； 修改个人馆息 (update>UPdate Provider Set(Name=#Name,Address=#Address ， Tel=#Te

13、l ， Intro=#Intro>WHERE(ID= #ID> ; 增加供应项 (add_supply_item>INSERT INTO Supply(PartID， Providerid ，Price ， Quantity>VALUES(#PartID，#ProvderlD ， #Price ； #Quantily> ； 删除供应项 (delete_supply_item>DELETE SupPlyWHERE(PartlD=#PartID AND ProvideID=#ProviderlD>； 修改供应项 (update_supply_item>

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

15、e #Address , #Tel> ;在登记操作后，顾客得到一个唯一的ID，可以根据这个ID来查询和修改顾客的数据。 注销(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、针对需求分析中提出的问题，我们提出了“协议书”的解决方案，方案的说 明如下： 每个交易在达成以前都作为协议书保存在数据库中，协议书具有和交易一 样的完备信息，可以在条件成熟的情况下转为一个达成的交易； 协议书只有在供应商和顾客都签字的情况下才有效；有效的协议书由交易 员签发，协议书一经签发，就生效，表明一个交易的达成，数据库中的数据将同 时予以修改； 协议书可以由供应商、顾客或者交易员中的任意一个人提出申请。当协议 书在双方没有都签字前，协议的双方或者交易员都可以删除这个协议书；但是， 当协议书签字完毕后，协议书就不得删除(修改，只能由交易员进行处理； 协议书有可能在转成交易的过程中失败，因为

18、在交易达成以前，数据库中 的数据有可能因为其他交易而变化，一个协议书可能失效，这是允许的。根据以上分析，对数据库的模型作一些修改，增加协议书表，其关系模式如 下：PartID ， Price ， Qua ntity， CustomerSig n ，Agreeme nt(CustomerlD ， ProviderlDProviderSig n>对应的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>> 与上述其他操作相比，对交易的操作对数据完整性要求比较高，其中需要注 意的地方是；要防止同一用户 ( 供应商，顾客 >的数据因两个交易而同时修改；需要同时对供应数据库(Supply、需求数据库(OfferToBuy>、交易数据库(Business>和协议数据库(Agreement>作出修改，而且需要保持这些修改的原子 性；很显然，这些要求正是对于一个事务 (transaction> 的要求，所以可以用一 个事务来完成签发一个协议的操作

21、。事务的描述如下：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 的一个元组的转化工作。这里考虑到了删除空的 Suppiy 和 OfferTOBUY 项，更加重要的是，这里考虑到了非法的 Agreement 的情况，在一 段时间后，因为供应商或者顾客修改数据， Agreement 可能就非法，这时就需要 把这个事务废除，所以，这里检查了 Supply 表和 OfferToBuy 表中的数

25、据，确保 数据仍然正确。另外交易员，或者说交易所必须承担的一项任务是更新零件列表。这里在考 虑顾客和供应商的时候十并没有给予他们修改零件列表的权利，所以他们必须根 据数据库中已有的项更新自己的供求信息。因为这个数据库实际上更加偏重于模型化，而不是一个实际环境中的数据 库，所以在实现应用模型的时候我们还需要对这个数据库的模型作一些修改。因为本实验在模型设计上使用了 Microsoft Transact-SQL 的语法，因此以 上的数据库操作都是在 SQLSERVER20上）测试通过的。5. 测试阶段1输入数据设计<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 值可能不同。以后相应操作要保持前后一致就可以丁。 ><2）插入供应商信息：create procedure insert_gysasinsert into Provider(Name，password，Ad

27、dress，Tel ， Intro>values('coml' ， '1234' ，' 北京 ' ， 6543210， 'nothing'> 显示刚插入的供应商 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)插入供应商供应信息：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)插入协议信息：create procedure insert_xyxxQuantity ，asinsert into Agreement(CustomerID,ProviderID ， PartlD ， Price ， CustomerSign ， ProviderSign>values(1，1，1，20，30， 1，1>；2. 执行交易操作设计<1)执行交易存储过程 PASS_AGREEME参数为：1, 1, 1:PASS_AGREEMENT，11，1。(后面的三个参数分别对应前面选择出的供应商ID、顾客ID和

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