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

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

2、第三个实验课题.,有兴趣,可考虑另外两用等全过程,进一步了解总之,因时间关系, 只要求每个读者任选一个课题.假设有时间 个课题,也会有所收 益.A. 1. 1实验一零件交易中央治理系统实验目的通过完成从用户需求 分析、数据库设计到上机编程、调试和应 和掌握本书中所讲解的内容.实验简述零件交易中央治理系 供给商信息、顾客信 供给商信息包括供给 顾客信息包括顾客号 零件信息包括零件号 此系统可以让供给商 还可以让顾客增加、 交易员可以利用顾客统主要提供顾客和供给商之间完成零件交 息以及零件信息.易的功能,其中包括商号、供给商名、,顾客名、地址、零件名、重量、地址、 、简介; ；颜色、简介等.增加、

3、删除和修改所提供的零件产品, 删除和修改所需求的零件.提出的需求信息和供给商提出的供给信息来提出交易的建议,由供应商和顾客进行确认后即完成这笔交易.实验要求完成该系统的数据库设计：用SQl实现数据库的 设计,并在SQLServer上调试通过.A. 1.2实验三民航售票系统实验目的通过完成从用户需求 分析、数据库设计到上机编程、调试和应 和掌握本书中所讲解的内容.实验简述民航订票系统主要分 为机场、航空公司和客户三方的效劳.航用等全过程,进一步了解空公司提供航线和飞机的料,机场那么对在本 机场起飞和降落的航班和机票进行治理, 路和剩余票数.的查询,以及网上订票等功能.客户又可 普通客户只有普 通

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

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

6、供的零件产品, 删除和修改所需求的零件.提出的需求信息和供给商提出的供给信息易的功能,来提出交易的建议,由供给商和顾客进行确认后即完成这笔交易.课程设计全过程1需求分析：实际详 细调查2数据库设计：SQL Server 2000设计概念模型设计实际到概念逻辑设计逻辑推导物理设计理论到实现SQL编程、调试测试验证实践反复 检验3应用程序编程、调试、测试用人机交互前台开发工具 VB.NET开发windows和Web应用程序需求分析详细地调查分析系统对象、功能、性能等需求l供给商供给商的操作流程图 如图A1所示.图A1供给商操作分类表2 .顾客顾客的地位和供给商 几乎是对称的,所以功能分类上也很相似

7、.顾客的操作流程图如图A2所示.顾客提出交易员提出交易申请交易建议3 .交易员交易员的工作就是提 出交易和完成交易.这里需要仔细考虑的问题是：一个交易如何产生,并如何达成,可 以用图A3来说明这个问题.我们在处理交易的时 候可能面临如下问题：1 一个交易只能在交易 双方都同意的情况下才可以进行,所以数据库中的供求信息只能作为达成某个交易的基 础；2交易的双方可能不同 时使用这个系统,因此需要系统提供一个双方交换信息的方式；3系统需要提供一种方便系统交易员向用户提出建议来促成交易的途径,并在保证数据 库供给商 提出交易交 易 员数据完整性的情况图A. 3交易员操作图概念模型设计数据库需要表述的信

8、(1)零件信息(2)供给商信息(3)顾客信息(从实践概括抽象出理论模型日R) 息有以下几种：(4)供给商集和零件集之间的联系(供给)图A. 4供给商和零件之间的联系(供给)E/R模型图A. 5顾客和零件之间的联系(求购)E/R模型6交易三元联系可以用E/R模型表述 该模型的设计,E/R图如图A7所示.零件号简介颜色重量供给价格价格供给商顾客 简介顾客名地址供给商名价格 地址供给商号图A. 7全局E/R模型逻辑设计从理论'E/R模型'到理论关系模型的整理转换通过E/R模型到关系模型的转化,可以得到如下关系模式：(1)零件实体集转换为关系：Part( ID, Color , Nam

9、e Weight, Intro)(2)供给商实体集转换为关系Provider(叵,Name Addtess , Tel , Intro)(3)顾客实体集转换为关系 Customer(JD, Name Addtess , Tel)(4)供给联系转换为关系Supply( PartlD , ProviderlD , Price , Quantity)(5)求购联系转换为关系OfferToBuy( CustomerlD , PartID , Price , Quantity)(6)交易联系转换为关系Business( CustomerlD , ProviderlD , PartID , Price ,

10、 Quantity)每个关系模式的主键码都用下划线标出.同时,对于从联系导出的关系 Supply供给,OfferToBuy求购和Business交易, 使用与之相联系的实体集的主健码 作为自己的键码,必须符合外键码约束.对于Customer顾客,Provider供给商和Part零件之间,不存在直接的约束,所 以可以存在没有供给商供给同时也没有顾客求购的零件.物理设计(从理论关系模型到实现实施数据库建立)(物理文件的安排和建立索引)1为了提升在表中搜 索元组的速度,在实际实现的时候应该基于键码 建立索引是各表中 建立索引的表项：part(ID) (2)Provider(ID) (3)Custom

11、er(ID) (4)Supply(PartID , ProviderID> (5) OfferTOBuy(CustomerID , PartID) (6) Business(CustomerlD , ProviderID , PartID)2用SQL实现设计 实现该设计的环境为 Windows 2000 Perfessinal+MSSQLServer 2000.01 .建立Part表CREATE TABLEPart ( ID smallint IDENTITY(1,1) PRIMARYKEY CLUSTERED Color varchar(20), Name varchar(20) NO

12、T NULL, Weight int DEFAULT 0, Intro text) 2.建立 Provider 表CREATE TABLE Provider( ID smallint IDENTITY(1,1) PRIMARYKEY 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)

13、PRIMARYKEY CLUSTERED, Name varchar(20)NOT NULL,Address varchar(30), TeL Varchar(20)4 .建立Supply表CREATE TABLESupply( PartID Smallint,ProviderlD smallint, Price int, Quantity int, CONSTRAINPK_SUPPLY PRIMARYKEYCLUSTERED(PartID,ProviderID), CONSTRAINT FK_SUPPLY_PARTID FOREIGN KEY(PartID) REFERENCES Part(

14、ID), CONSTRAINT FK_SUPPLY_PROVIDERID FOREIGN KEY(ProviderID)REFERENCES-ovider(ID)5 .建立 OfferToBuy 表CREATE TABLEferToBuy( CustomerID smallint, PartIDSmallint,Price int, Quantity int, CONSTRAINTPK_OFFERTOBUY PRIMARYKEYCLUSTERED(CustomerID,PartID), CONSTRAINTK_OFFERTOBUY_CUSTOMEROREIGNKEY(CustomerID)RE

15、FERENCESustomer(ID), CONSTRAINTK_OFFERTOBUFOREIGNKEY(PartID) REFERENCESart(ID)6 .建立 Business 表CREATE TABLEBusiness( CustomerID smallint, ProviderID smallint, PartIDSmallint,Price int, Quantity int, CONSTRAINT PK_BUSINEssPRIMARY KEYClUSTERED(CuscomerID,ProviderID,PartID), CONSTRAINFK_BUSINESS_CUSTOME

16、RID FOREIGN KEY(CustomerID)REFERENCESustomer(ID), CONSTRAINFK_BUSINESS_PROVIDERlD FOREIGN KEY(ProviderID)REFERENCES-ovider(ID), CONSTRAINFK_BUSINESS_PARTID FOREIGN KEY(PartID)REFERENCESPart(ID)7 .供给商操作(1)注册(register)INSERT INTO Provider(Name , password , Address , TeI , Intro) VALUES(#Name #password

17、 , #Address , #Tel , #Intro)在登记操作后,供给商得到一个唯一的ID,可以根据这个ID采查询和修改供给商的数 据.(2)注销(unregister)DELETE Provider WHERE(ID=#ID);(3)修改个人馆息(update)UPdate Provider Set(Name=#Name,Address=#Address , Tel=#Tel , Intro=#Intro) WHERE(ID= #ID);(4)增加供给项(add_supply_item)INSERT INTO Supply(PartID , Providerid , Price , Qu

18、antity)VALUES(#PartID , #ProvderlD , #Price ; #Quantily);(5)删除供给项(delete_supply_item)DELETE SupPlyWHERE(PartlD=#PartID AND ProvideID=#ProviderlD) ;(6)修改供给项(update_supply_item)UPDATESupplySET(Price=#Price , Quantity=#Quantity)WHERE(PartlD=#PartID AND ProviderID=#ProviderID) '很明显,系统并没有 提供面向供给商修改零件

19、信息的接口,所以供给商提供的零件必须已经在零件表中存 在；可以这祥假设,交易所的治理员负责更新零件信息,而供给商可以向交易所申请增加某 种零件的信息.事实上顾客也可以提出这 样的要求.8 .顾客操作 (1)注册(register)INSERT INTO Customer(Name , Address , Tel)VALUES(#Name #Address , #Tel);在登记操作后,顾客得到一个唯一的ID,可以根据这个ID来查询和修改顾客的数据.(2)注销(unregister)DELETE CustomerWHERE<ID=#ID)修改个人信息(update)UPDATE Custo

20、merSet(Name=#Name, Address=#Address , Tel=#Tel)WHERE(1D=#ID)(4)增加需求项(add_OfferToBuy_item)INSERT INTO OfferToBuy(PartID,CustomeriD , Price , Quantity)VALUES(#PartID , #CustomerID , #Price , #Quantity)'(5)删除需求项(delete_OfferToBuy_iterm)DELETE OfferToBuyWHERE(PartlD=#PartlD ANDCustomerlD=#CustomerID

21、);(6)修改需求项(叩 date_OfferToBuy_item)UPDATE OfferToBuy SET(Price=#Price , Quantity=#QuantityWHERE(PartlD=#PartID ANDCustomeriD=#CustomerID)9 .交易员针对需求分析中提出的问题,我们提出了 “协议书的解决方案,方案的说明如下：(1)每个交易在达成以前都作为协议书保存在数据库中,协议书具有和交易一样的完备信息,可以在条件成熟的情况下转为一个达成的交易；(2)协议书只有在供给商 和顾客都签字的情况下才有效；有效的协议书由交易员签发,协议书一经签发,就 生效,说明一个交

22、易的达成,数据库中的数据将同时予以修改；(3)协议书可以由供给商、顾客或者交易员中的任意一个人提出申请.当协议书在双方没有都签字前,协议 的双方或者交易员都可以删除这个协议书；但是,当协议书签字完毕后,协议书就不得删除(修改),只能由交易员进行处理；(4)协议书有可能在转成交易的过程中失败,由于在交易达成以前,数据库中的数据有可能由于其他交易而 变化,一个协议书可能失效,这是允许的.根据以上分析,对数 据库的模型作一些修改,增加协议书表,其关系模式如下：Agreement(CustomerlD , ProviderlD , PartID , Price , Quantity , Custome

23、rSign , Provide rSign) 对应的SQL描述为：CREATE TABLAgreement( Customerm smallint , ProviderlD smallint , PartlDsmallint ,Price int, Quantity int , CustomerSign int , ProviderSign int , CONSTRAINT PK_AGREEMENTPRIMARY KEYCLUSTERED(CustomerID ProviderID , PartID), CONSTRAINT FK_AGREEMENT_CUSTOMERID FOREIGN KE

24、Y(CustomerID) REFERENCES Customer(ID), CONSTRAINT FK_ AGREEMENT_PROVlDERID FOREIGN KEY(ProviderID) REFERENCES Provider(ID),CONSTRAIN1FK_AGREEMENT_PARTIFOREIGN KEY(PartID) REFERENCESart(ID)与上述其他操作相比,对交易的操作对数据完整性要求比拟高,其中需要注意的地方是;要预防同一用户(供给商,顾客)的数据因两个交易而同时修改；需要同时对供给数据库(Supply)、需求数据库(OfferToBuy)、交易数据库(B

25、usiness) 和协议数据库(Agreement)作出修改,而且需要保持这些修改的原子性；很显然,这些要求正 是对于一个事务(transaction) 的要求,所以可以用一个事务来完 成签发一个协议的操 作.事务的描述如下：CREATE PROPASS_AGREEMENT providerID int , customerid int , partlD int ASDECLARE TransNameVARCHAR(20)SELECT TransName='Pass_Agreement'BEGIN TRANSACTIOTransName DECIARE price INT, q

26、UANTITYntSELECT price=price , quantity=quantity FROM Agreement WHERE prIVIderID=providerID ANDcustomerID=customerIDAND PanID=partIDinsertINTOBusiness(ProviderID , CustomerID , PartID , Price , Quantity)VALues(providerid , customerID, PartID, price,quanti ty)UPDATE Supply SET quantity=quantity-quanti

27、ty WHERHoviderID=prividerID ANDpartID=partID IF (SELECT quantity FROM SupplyWHERE3roiderid=provider ANDpartID=PartID)<0ROLLBACK TRANSACTlON TranSNameDELETE FROMSupply WHERE quantity=0UPDATE OfferToBuy SET quantity=quanttity-quantityWHERE CustomerID=customerid AND partlD=partIDIF(SELECT quandtity

28、FROM OfferToBuyWHERE CustomerID=CustomerID AND partID=partlD)<0 ROLLBACKTRANSACTIOTransNameDELETE FROMOfferToBuy WHEREjuantity=0 COMMIT TRANSACTIOTransName为了使用方便,这里定义了一个存贮过程；功能是完成从Agreementt的一个元组到Business的一个元组的转化工作.这里考虑到了删除空的Suppiy和OfferTOBUY项,更加重要的是,这里考虑 到了非法的Agreement的情况,在一段时间后,由于供给商或者顾客修改数据,Ag

29、reement可能就非法,这时就需 要把这个事务废除,所以,这 里检查了 Supply表 和OfferToBuy表中的数据,保证数据仍 然正确.另外交易员,或者说 交易所必须承当的一项任务是更新零件列 表.这里在考虑顾客和供 应商的时候+并没有 给予他们修改零件列表的权利,所以他们 必须根据数据库中已有的项更 新自己的供求信息.由于这个数据库实际 上更加偏重于模型化,而不是一个实际环境中的数据库,所以在实现应用模型的时候我们还需要对这个数据库的模型作一些修改.由于本实验在模型设 计上使用了 Microsoft Transact-SQL的语法,因此以上的数 据库 操作都是在 SQLSERVER2

30、000测试通过的.实验数据例如：测试阶段(1.实验方案设计2.测试,查找错误校正错误,检查是否符合用户的功能性能 要求)1.实验方案设计(1)输入数据设计：1)插入零件信息；insert into Part(Color , Name Weight, Intro) values('black' , 'stick' , '30' , 'of steel') ; 显示刚插人的零件id ：select id from Part where name='stick' ； id 1(1 row(s) affected)(不同的

31、实验,id值可能不同.以后相应操 作要保持前后一致就可以丁.)2)插入供给商信息：insert into Provider(Name , password , Address , Tel , Intro) values('coml' , '1234','北京','nothing');显示刚插入的供给商id :select id from Provider where name='coml' id1(1 row(s) affected)3)插入顾客信息：insert into Customer(Name , Addr

32、ess , Tel)values('cusl' ,'北京,")显示刚插入的顾客id ：select id from Customer where name id1(1 row(S)affected)4)插入供给商供给信息：insert into Supply(PartID , ProviderlD , Price , Quantity) values(1 , 1, 20, 100);5)插入顾客需求信息：insert into OfferToBuy(PartlD , CustomerID , Priee , Quantity) values(1 , 1, 20

33、, 50);6)插入协议信息：intoProviderinsertAgreement(CustomerID,ProviderID , PartlD , Price , Quantity , CustomerSign , Sign)values(1 , 1, 1, 20, 30, 1, 1);(2)执行交易操作设计：1)执行交易存储过程PASSAGREEMENT为：1, 1, 1 :PASS_AGREEMENT 1,1；(后面的三个参数分别对应前面选择出的供应商ID、顾客ID和零件ID.)2)结果：显示交易后供给信息和需求信息：select Quantity from Supply where

34、PartlD = 1 and ProviderlD=1; Quantity 70 (1 row(s) affected)select Quantity from OfferToBuy where PartlD=l and CustomerID Quantity201 rows affected3分析结果：首先,保存在 Supply表中1D为1的零件供给量 为100参见Supply表白Insert语句, 保存在 OfferToBuy 表中ID为1的零件需求量为 50参见OFFERToBu索的Insert 语句.在 Agreement表中指出ID为1的供给商和ID为1的顾客要交易30个ID为1的零

35、件.当执 行存 储过程 PASS_AGREEMEN, Supply和OfferToBuy表中相应的数量都减少 了 30,交易成功.4再次执行交易操作：delete from Business ;附录：交易系统的另一种SQM言数据表建立：/* Microsoft SQL Server - Scripting*/* Server: 7523TEACHER*/* Database:商品交易系统*/* Creation Date 02-10-18 9:46:43*/CREATE TABLEdbo.customer( id smallint IDENTITY (1, 1) NOT NULL, name

36、varchar (20) NOTNULL , address varchar (30) NULL , Intro text NULL ) GOALTER TABLEdbo.customerWITH NOCHECK ADDCONSTRAINPK_customerPRIMARYKEY CLUSTERED(id)ON PRIMARYGOid smallint color varchar name varchar weight int Intro textGOCREATE TABLEdbo.partIDENTITY (1, 1) NOT NULL,(20) NULL,(20) NOTNULL ,NUL

37、L ,NULL )ALTER TABLEdbo.part WITH NOCHECK ADD CONSTRAINPK_partPRIMARYKEY CLUSTERED(id)ON PRIMARYGOCREATE TABLEvider( id smallint IDENTITY (1, 1) NOT NULL, name varchar (20) NOTNULL , password varchar (8) NOT NULL, address varchar (30) NULL , Tel varchar(20) NULL ,Intro textNULL )GOALTER TABLE

38、viderWITH NOCHECK ADDCONSTRAINPK_providerPRIMARYKEY CLUSTERED(id)ON PRIMARY GOCREATE TABLEdbo.supply(partid smallint NOTNULL, providerid smallint NOT NULL, price smallint NULL , quantity int NULL ) GOCREATE TABLEdbo.bussiness(customerID smallint NOT NULL, partid smallint NOT NULL, providerid

39、smallint NOT NULL, price smallintNULL ,quantity intNULL )GOCREATE TABLEdbo.offertobuy(customerID smallint NOT NULL,partid smallint NOT NULL, price smallint NULL , quantity int NULL )ALTER TABLEdbo.bussiness CONSTRAINTPK_bussiness (customerID, partid, providerid )ON PRIMARYGOALTER TABLEdbo.offertobuyCONSTRAINPK_offertobuy(customerID, partid)ON PRIMARYGOALTER TABLEdbo.supply CONSTRAINPK_supply(partid,WITH NOCHECK ADD PRIMARYKEY CLUSTEREDWITH NOCHECK ADDPRIMARYKEY CLUSTEREDWITH NOCHECK ADD PRIMARYKEY CLUSTEREDproviderid)ON P