sql数据库实例零件销售中心管理系统

1、重庆工商大学计算机科学与技术专业数据库原理教学实验指导书2004.1数据库原理课程设计教学实验指导SQL Server2000 课程设计教学实验指导A1 综合实验本课程的教学实验分为两部分：第一部分是按照实验指导书所要求的实验在计算机 上完成；第二部分是作完上述实验后按照本课程设计教学实 验指导书做的一个综合性实验。通过教学实验可使读 者较系统、全面地掌握相关的教学内容和必要的上机 操作。下面给出三个实验课题，其中第一个课题还附有 参考答案。希望读者在理解题意的基础上发挥自己的 创新精神，有创意地完成教学实验。如果觉得有参考 答案可能会束缚自己的思维，也可选作第二或第三个 实验课题总之，因时间

2、关系， 只要求每个读者任选一个课题。 若有时间，有兴趣，可考虑另外两个课题，也会有所 收益A11 实验一 零件交易中心管理系统（实验目的 通过完成从用户需求分析、数据库设计到上机编 程、调试和应用等全过程，进一步了解和掌握本书中 所讲解的内容。（实验简述） 零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能，其中包括供应商信息、顾客信息以及零件信息。供应商信息包括供应商号、供应商名、地址、电话、简介；顾客信息包括顾客号，顾客名、地址、电话；零件信息包括零件号、零件名、重量、颜色、简介 等。此系统可以让供应商增加、删除和修改所提供的零 件产品，还可以让顾客增加、删除和修改所需求的零件

3、。交易员可以利用顾客提出的需求信息和供应商提出 的供应信息来提出交易的建议，由供应商和顾客进行 确认后即完成这笔交易。（实验要求） 完成该系统的数据库设计： 用 SQl 实现数据库的设计，并在 SQLServer 上调试 通过A12 实验三 民航售票系统（实验目的） 通过完成从用户需求分析、数据库设计到上机编 程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容。-（实验简述 民航订票系统主要分为机场、航空公司和客户三 方的服务。航空公司提供航线和飞机的资料，机场则 对在本机场起飞和降落的航班和机票进行管理，而客 户能得到的服务应该有航班线路和剩余票数 的查询， 以及网上订票等功能。客户

4、又可以分为两类，一类是 普通客户，对于普通客户只有普通的查询功能和订票 功能，没有相应的机票优惠，另一种是经常旅客，需 要办理注册手续，但增加了里程积分功能和积分优惠 政策。机场还要有紧急应对措施，在航班出现延误时， 要发送相应的信息。（实验要求）完成该系统的数据库设计；用SQL实现数据库的设计，并在 SQIServcr上调 试通过。A13 实验二 图书管理系统（ 实验目的 ） 通过完成从用户需求分析、数据库设计到上机编 程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容 实验简述 一个简单的图书管理系统包括图书馆内书籍的信 息、学校在校学生的信息以及学生的借阅信息。此系 统功能分为面

5、向学生和面向管理员两部分，其中学生 可以进行借阅、续借、归还和查询书籍等操作，管理 员可以完成书籍和学生的增加，删除和修改以及对学 生，借阅、续借、归还的确认 实验要求 完成该系统的数据库设计；A2 零件交易中心管理系统实验报告（ 参考答案 ）的）用SQL实现数据库的设计，并在SQSQLServer上调 试通过（ 实验通过完成从用户需求分析、数据库设计到上机编 程、调试和应用等全过程，进一步了解和掌握本书中 所讲解的内容。（ 实验简述 ） 零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能，其中包括供应商信息、顾客信息以及零件信息。此系统可以让供应商增加、删除和修改所提 供的零件产

6、品，还可以让顾客增加、删除和修改所需求的零 件。交易员可以利用顾客提出的需求信息和供应 商提出的供应信息来提出交易的建议，由供应商和顾 客进行确认后即完成这笔交易。 课程设计全过程 1 需求分析：（实际详细调查）2 数据库设计： （SQL Server 2000 设计 ）概念（模型） 设计实际到概念）7逻辑设计物理设计逻辑推导） 理论到实现）SQL编程、调试（测试验证）（实践反复检验）3 应用程序编程、 调试、测试 （ 用人机交互前 台开发工具VB.NET开发windows和 Web应用程序）需求分析（详细地调查分析系统对象、功能、性能等需求）I供应商供应商的操作流程图如图 A1所示增加供应项

7、修改供应项删除供应项注销修改个人信息图A1供应商操作分类表2.顾客顾客的地位和供应商几乎是对称的，所以功能分类上也很相似.顾客的操作流程图如图A2示所。增加需求项删除需求项注销修改需求项修改个人信息图A2顾客操作分类表3 .交易员,并如何达交易员的工作就是提出交易和完成交易。这里需 要仔细考虑的问题是：一个交易如何产生 成，可以用图A3来说明这个问题.我们在处理交易的时候可能面临如下问题：(1) 一个交易只能在交易双方都同意的情况下才可以 进行，所以数据库中的供求信息只能作为达成某个交 易的基础；(2) 交易的双方可能不同时使用这个系统，因此需要系统提供一个双方交换信息的方式；供应商交易员提出

8、 系统需要提供一种方便系统:-交易员-)-向用户提出 建议来促成交易的途径，提并在保i证数据库数据完整性T协议书草案供应商以及顾客签字正式签字交易员签发8 J完成交易目 r J I.J I的情况下达成交交/员图 A 3 交易员操作图 概念模型设计 (从实践概括抽象出理论模型 E/R) 数据库需要表述的信息有以下几种：(1) 零件信息9(2) 供应商信息顾客信息(4)供应商集和零件集之间的联系(供应)图A4供应商和零件之间的联系(供应)E/R模型(5)顾客集和零件集之间的联系(求购)11图A5顾客和零件之间的联系（求购）E/R模型（6）交易（二兀联系）可以用E/R模型表述该模型的设计，E/R图如

9、 图A7所示。数量供应价格零件i 厂一供应商r：:客号零件名零件号简介颜色重量求购应商名一?丿 C供应商号J电话地址图A7全局E/R模型逻辑设计(从理论 E/R模型到理论关系模型的整理转换)通过E/R模型到关系模型的转化，可以得到如下 关系模式：关系:(1) 零件实体集转换为Part( ID，Color，Name Weight，Intro)关系(2) 供应商实体集转换为Provider( ID， Name Addtess，Tel，Intro)关系关系(3) 顾客实体集转换为Customer(ID, Name Addtess, Tel)(4) 供应联系转换为12Supply( PartID ,

10、ProviderlD , Price , Quantity)关系(5) 求购联系转换为OfferToBuy( CustomerlD，PartID，Price，Quantity)(6) 父易联系转换为关系Business( CustomerlD , ProviderlD , PartID , Price ,Qua ntity)每个关系模式的主键码都用下划线 标出。司时，对于从联系导出的关系Supply（供应）,OfferToBuy（求购）和Business（交易），使用与之相联 系的实体集的主健码作为自己的键码，必须符合外键 码约束。对于 Customer（顾客），Provider（ 供应商）和

11、Part（零件）之间，不存在直接的约束，所以可以存在 没有供应商供应同时也没有顾客求购的零件。物理设计从理论关系模型'到实现 实施数据库建立' )物理文件的安排和建立索引)1 为了提高在表中搜索元组的速度， 在实际实现的 时候应该基于键码建立索引是各表中建立索引的表 项：(1)part(ID)(2)Provider(ID)(3) Customer(ID)(4) Supply(PartID，ProviderID>(5)OfferTOBuy(CustomerID，PartID)(6)Business(CustomerlD ， ProviderID ，PartID)2用SQL实

12、现设计实 现 该 设 计 的 环 境 为 Windows 2000Perfessinal+MSSQLServer 2000.01建立 Part 表CREATE TABLEPart (ID smallint IDENTITY(1,1)PRIMARY KEY CLUSTER, EDColor varchar(20),Name varchar(20) NOT NULL,Weight int DEFAULT 0,Intro text)2建立 Provider 表CREATE TABLEProvider(31ID smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,N

13、ame varchar(20) NOT NULL,password varchar(8)NOTNULL,Address varchar(30),Tel varchar(20),Intro text)3建立 Customer 表CREATETABLE Customer(ID Smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,Namevarchar(20) NOT NULL,Address varchar(30),TeL Varchar(20) )4建立 Supply 表CREATE TABLE Supply(PartID Smallint,ProviderI

14、D smallint,Price int,QUantity int,CONSTRAINTPK_SUPPLY PRIMARYKEY CLUSTERED(PartID,ProviderID),CONSTRAINT FK_SUPPLY_PARTIDFOREIGN KEY(PartID) REFERENCES Part(ID),CONSTRAINFTK_SUPPLY_PROVIDERIDFOREIGNKEY(ProviderID) REFERENCEPSrovider(ID)5建立 OfferToBuy 表CREATE TABLE OfferToBuy(CustomerID smallint,Part

15、ID Smallint,Price int,Quantity int,CONSTRAINT PK_OFFERTOBUYPRIMARY KEY CLUSTERED(CustomerID,PartID),CONSTRAINTFK_OFFERTOBUY_CUSTOMERID FOREIGN KEY(CustomerID)REFERENCES Customer(ID),CONSTRAINT FK_OFFERTOBUYFOREIGN KEY(PartID)REFERENCES Part(ID)6建立 Business 表CREATE TABLE Business(CustomerID smallint,

16、ProviderID smallint,PartID Smallint,Price int,Quantity int,CONSTRAINT PK_BUSINEssKEYPRIMARYClUSTERED(CuscomerID,ProviderID,PartID),CONSTRAINTFK_BUSINESS_CUSTOMERIDFOREIGNKEY(CustomerID)REFERENCES Customer(ID),CONSTRAINTFK_BUSINESS_PROVIDERlD FOREIGKENY(ProviderID)REFERENCES Provider(ID),CONSTRAINTFK

17、_BUSINESS_PARTID FOREIGKNEY(PartID)EFERENCES Part(ID)7供应商操作(1) 注册 (register)INSERT INTO Provider(Name ， password ，Address，TeI ，Intro)VALUES(#Nam，e #password ， #Address ，#Tel ，#Intro)在登记操作后，供应商得到一个唯一的ID，可以根据这个 ID 采查询和修改供应商的数据。(2) 注销 (unregister)DELETE Provider WHERE(ID=#ID) ；(3) 修改个人馆息 (update)UPdate

18、Tel=#TelProviderSet(Name=#Name,Address=#Address ，Intro=#Intro)WHERE(&#ID);(4) 增加供应项 (add_supply_item)INSERT INTO Supply(PartID ， Price ， Quantity)VALUES(#PartID#ProvderlDProviderid#Price#Quantily) ；(5) 删除供应项 (delete_supply_item)DELETE SupPlyANDANDWHERE(PartlD=#PartIDProvideID=#ProviderlD) ；(6) 修

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

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

21、ID ， #PriceVALUES(#PartID#Quantity)'(5) 删除需求项 (delete_OfferToBuy_iterm)DELETE OfferToBuyANDANDWHERE(PartlD=#PartlDCustomerlD=#CustomerID) ；(6) 修改需求项 ( 叩 date_OfferToBuy_item)UPDATE OfferToBuy SET(Price=#PriceQuantity=#QuantityWHERE(PartlD=#PartIDCustomeriD=#CustomerID)9 交易员 针对需求分析中提出的问题，我们提出了“协议

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

23、因为 在交易达成以前，数据库中的数据有可能因为其他交 易而变化，一个协议书可能失效，这是允许的根据以上分析，对数据库的模型作一些修改，增加协议书表，其关系模式如下：Agreement(CustomerlD , ProviderlD , PartID ,Price , Quantity , CustomerSign , ProviderSign)对应的SQL苗述为:TABLECREATEAgreeme nt(Customerm smal lintP roviderlDsmalli ntPartlD smalli ntP riceint,Qua ntity intCustomerSig n int

24、 ,P roviderSig nint ,CONSTRAINTPK AGREEMENTP RIMARYKEYCLUSTERED(CustomeripProviderlD , PartID),CONSTRAINTFK_AGREEMENT_CUSTOMERID FOREIGN KEY(CustomerID)REFERENCES Customer(ID，)FKCONSTRAINTAGREEMENT_PROVlDERID FOREIGN KEY(ProviderID)REFERENCES Provider(ID)，CONSTRAINTFK_AGREEMENT_PARTID FOREKIGEYN(Par

25、tID)EFERENCES Part(ID)与上述其他操作相比，对交易的操作对数据完整性要求比较高，其中需要注意的地方是；要防止同一用户 ( 供应商，顾客 ) 的数据因两个交易而同时修改；需要同时对供应数据库 (Supply) 、需求数据库(OfferToBuy) 、交易数据库 (Business)和协议数据库 (Agreement) 作出修改，而且需要保持这些修改的原子性；很显然，这些要求正是对于一个事务(transaction) 的要求， 所以可以用一个事务来完成签 发一个协议的操作。事务的描述如下：CREATE PROC PASS_AGREEMENTproviderID int ，cus

26、tomerid int ，partlD intASDECLARETransNameVARCHAR(20)SELECTTransName='Pass_Agreement'TRANSACTIONBEGINTransNameDEClARE price INT，qUANTITYintSELECT price=pricequantity=quantity FROM AgreementWHEREprIVIderID=providerIDcustomerID=customerID AND PanID=partID1NSERTANDINTOBusiness(ProviderID ，Custom

27、erID，PartID ，Price ， Quantity)VALues(provideridUPDATE SupplySETcustomerID，PartID，price,quantity)quantity=quantity-quantityWHEREProviderID=prividerID AND partID=partIDIF (SELECT quantityFROMSupplyWHEREProiderid=provider AND partID=PartID)<0ROLLBACKTRANSACTlONTranSNameDELETEFROMSupply WHEREquantity

28、=0UPDATE OfferToBuy SETquantity=quanttity-quantityWHERECustomerID=customerid AND partlD=partIDIF(SELECT quandtityFROMOfferToBuyWHERECustomerID=CustomerID AND partID=partlD)<0ROLLBACKTRANSACTIONTransNameDELETE FROMOfferToBuyWHERE quantity=0COMMIT TRANSACTION TransName为了使用方便 , 这里定义了一个存贮过程； 功能是 完成从

29、Agreementt 的一个元组到 Business 的一个元 组的转化工作。这里考虑到了删除空的 Suppiy 和 OfferTOBU 丫项，更加重要的是，这里考虑到了非法的 Agreement的情况，在一段时间后，由于供应商或者顾 客修改数据， Agreement 可能就非法， 这时就需要把这 个事务废除， 所以， 这 里检查了 Supply 表和 OfferToBuy 表中的数据， 确保数据仍然正确。另外交易员，或者说交易所必须承担的一项任务 是更新零件列表。这里在考虑顾客和供应商的时候十 并没有给予他们修改零件列表的权利，所以他们必须 根据数据库中已有的项更新自己的供求信息。由于这个数

30、据库实际上更加偏重于模型化，而不 是一个实际环境中的数据库，所以在实现应用模型的 时候我们还需要对这个数据库的模型作一些修改。由于 本实验在 模型设计上使用了 Microsoft Transact-SQL 的语法，因此以上的数据库操作都是在SQLSERVER20Q上测试通过的。 实验数据示例：测试阶段 2. 测试，查找错误校正错误， 检查（1. 实验方案设计 是否符合用户的功能性能要求 ）1实验方案设计1）输入数据设计：1)插入零件信息；， Name， Weight ，insert into Part(ColorIntro)values('black'， 'stick&

31、#39; ， '30' ， 'ofsteel') ；显示刚插人的零件 id ：select id from Part where name='stick'id(1 row(s) affected)(不同的实验， id 值可能不同。以后相应操作 要保持前后一致就可以丁。 )insert2) 插入供应商信息：into Provider(Name ， password ，Address ， Tel ， Intro)values('coml'，'1234' ，' 北京 ' ，6543210，'not

32、hing')显示刚插入的供应商 id ：selectidfrom Provider wherename='coml' ；id1(1 row(s) affected)3) 插入顾客信息：，Address，Tel)insert into Customer(Namevalues('cusl'' 北京 ' ，'6666666')'32显示刚插入的顾客 id ：select id from Customer where name id(1 row(S)affected)， ProviderlD ，4) 插入供应商供应信息：

33、insert into Supply(PartIDPrice ， Quantity)values(1，1，20，100) ；5) 插入顾客需求信息：insert into OfferToBuy(PartlD ，CustomerID ， Priee ， Quantity)values(1，1，20，50) ；6) 插入协议信息：intoinsertAgreement(CustomerID,ProviderID ，PartlD ，Price ，Quantity ，CustomerSign ，ProviderSign)values(1， 1 ，1 ，20，30， 1 ， 1 ) ；（2）执行交易操作

34、设计 ：1）执行交易存储过程PASS_AGREEME参数为:1，1，1：PASS_AGREEMEN，T 1，1;（ 后面的三个参数分别对应前面选择出的供应商ID、顾客ID和零件ID。）2）结果 ：显示交易后供应信息和需求信息：select Quantity from Supply wherePartID = 1 and ProviderlD=1;Quantity 70(1 row(s) affected)select Quantity from OfferToBuy where PartlD=l and CustomerID ；Quantity 20 (1 row(s) affected)3

35、）分析结果：首先，保存在Supply表中1D为1的零件供应量 为 100（ 参见 Supply 表的 Insert 语句 ） ，保存在OfferToBuy 表中ID 为 1 的零件需求量为 50（参见OFFERToBu表的 Insert 语句）。在 Agreement 表中指 出 ID 为 1 的供应商和 ID 为 1 的顾客要交易 30个 ID 为1的零件。当执行存储过程 P ASS_AGREEMEN后， Supply 和 OfferToBuy 表中相应的数量都减少了 30， 交易成功。4）再次执行交易操作：delete from Business附录：交易系统的另一种 SQL语言（数据表建

36、立）:36*/* Microsoft SQL Server - Scripting/* Server: 7523TEACHER*/* Database: 商品交易系统*/*/* Creation Date 02-10-18 9:46:43CREATE TABLE dbo.customer (id smallint IDENTITY (1, 1) NOTNULL ,name varchar (20) NOT NULL , address varchar (30) NULL ,Intro text NULL ) GOALTER TABLE dbo.customer WITHNOCHECK ADDC

37、ONSTRAINT PK_customer PRIMARY KEYCLUSTEREDid) ON PRIMARYGOCREATE TABLE dbo.part (id smallint IDENTITY(1, 1) NOTNULL ,color varchar (20) NULL , name varchar (20) NOT NULL , weight int NULL , Intro text NULL )GOALTER TABLE dbo.part WITHNOCHECK ADDCONSTRAINT PK_part PRIMARYKEY CLUSTEREDid) ON PRIMARYGO

38、CREATE TABLE vider (provideridsmallintNOTNULL ,name varchar (20) NOT NULL , password varchar (8) NOTNULL, address varchar (30) NULL , Tel varchar (20) NULL , Intro text NULL )GOALTER TABLE viderWITH NOCHECK ADDCONSTRAINTPK_providerPRIMARY KEY CLUSTEREDid) ON PRIMARYGOCREATE TABLE dbo.s

39、upply (partid smallint NOT NULL ,NULL ,price smallint NULL , quantity int NULL )GOCREATETABLEdbo.bussiness (customerID smallint NOTNULL, partid smallint NOT NULL , providerid smallint NOTNULL, price smallint NULL , quantity int NULL )GOCREATE TABLE dbo.offertobuycustomerID smallint NOTNULL, partid s

40、mallint NOT NULL ,38GOprice smallint NULL ,quantity int NULL )ALTERTABLEdbo.bussiness WITHNOCHECK ADDCONSTRAINT PK_bussinessPRIMARY KEY CLUSTEREDcustomerID,partid, providerid) ON PRIMARYGOALTER TABLE dbo.offertobuy WITHNOCHECK ADDCONSTRAINTPK_offertobuyPRIMARY KEY CLUSTEREDcustomerID, partid) ON PRIMARYALTER TABLE dbo.supply WITHNOCHECK ADDCONSTRAINT PK_supplyPRIMARY KEY CLUSTEREDpart