sql实例零件销售中心管理完整整理.docx

sql实例零件销售中心管理完整整理.docx

《sql实例零件销售中心管理完整整理.docx》由会员分享，可在线阅读，更多相关《sql实例零件销售中心管理完整整理.docx（24页珍藏版）》请在冰豆网上搜索。

sql实例零件销售中心管理完整整理

重庆工商大学计算机科学与技术专业

数据库原理

课

程

设

计

教案实验指导书

重点课程数据库原理教研组

2004.1

数据库原理课程设计教案实验指导

SQLServer2000课程设计教案实验指导

A．1综合实验

本课程的教案实验分为两部分：

第一部分是按照实验指导书所要求的实验在计算机上完成；

第二部分是作完上述实验后按照本课程设计教案实验指导书做的一个综合性实验。

通过教案实验可使读者较系统、全面地掌握相关的教案内容和必要的上机操作。

下面给出三个实验课题，其中第一个课题还附有参考答案。

希望读者在理解题意的基础上发挥自己的创新精神，有创意地完成教案实验。

如果觉得有参考答案可能会束缚自己的思维，也可选作第二或第三个实验课题．。

总之，因时间关系，只要求每个读者任选一个课题。

若有时间，有兴趣，可考虑另外两个课题，也会有所收益．

A．1．1实验一零件交易中心管理系统

（实验目的]

通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容。

（实验简述）

零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能，其中包括

供应商信息、顾客信息以及零件信息。

供应商信息包括供应商号、供应商名、地址、电话、简介；

顾客信息包括顾客号，顾客名、地址、电话；

零件信息包括零件号、零件名、重量、颜色、简介等。

此系统可以让供应商增加、删除和修改所提供的零件产品，

还可以让顾客增加、删除和修改所需求的零件。

交易员可以利用顾客提出的需求信息和供应商提出的供应信息来提出交易的建议，由供应商和顾客进行确认后即完成这笔交易。

（实验要求）

完成该系统的数据库设计：

用SQl实现数据库的设计，并在SQLServer上调试通过．

A．1．2实验三民航售票系统

（实验目的）

通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容。

··

（实验简述]

民航订票系统主要分为机场、航空公司和客户三方的服务。

航空公司提供航线和飞机的资料，机场则对在本机场起飞和降落的航班和机票进行管理，而客户能得到的服务应该有航班线路和剩余票数．的查询，以及网上订票等功能。

客户又可以分为两类，一类是普通客户，对于普通客户只有普通的查询功能和订票功能，没有相应的机票优惠，另一种是经常旅客，需要办理注册手续，但增加了里程积分功能和积分优惠政策。

机场还要有紧急应对措施，在航班出现延误时，要发送相应的信息。

（实验要求）

完成该系统的数据库设计；

用SQL、实现数据库的设计，并在SQlServcr上调试通过。

A．1．3实验二图书管理系统

（实验目的）

通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容．

[实验简述]

一个简单的图书管理系统包括图书馆内书籍的信息、学校在校学生的信息以及学生的借阅信息。

此系统功能分为面向学生和面向管理员两部分，其中学生可以进行借阅、续借、归还和查询书籍等操作，管理员可以完成书籍和学生的增加，删除和修改以及对学生，借阅、续借、归还的确认．

[实验要求]

完成该系统的数据库设计；

用SQL实现数据库的设计，并在SQSQLServer上调试通过．

A．2零件交易中心管理系统实验报告

（参考答案）

（实验目的）

通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容。

（实验简述）

零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能，

其中包括供应商信息、顾客信息以及零件信息。

此系统可以让供应商增加、删除和修改所提供的零件产品，

还可以让顾客增加、删除和修改所需求的零件。

交易员可以利用顾客提出的需求信息和供应商提出的供应信息来提出交易的建议，由供应商和顾客进行确认后即完成这笔交易。

[课程设计全过程]

1需求分析：

（实际详细调查）

2数据库设计：

（SQLServer2000设计）

概念（模型）设计（实际到概念）

逻辑设计（逻辑推导）

物理设计（理论到实现）

SQL编程、调试（测实验证）（实践反复检验）

3应用程序编程、调试、测试（用人机交互前台开发工具VB.NET开发windows和Web应用程序）

[需求分析]

（详细地调查分析系统对象、功能、性能等需求）

l供应商

供应商的操作流程图如图A1所示。

图A1供应商操作分类表

2．顾客

顾客的地位和供应商几乎是对称的，所以功能分类上也很相似．顾客的操作流程图如图A2所示。

图A2顾客操作分类表

3．交易员

交易员的工作就是提出交易和完成交易。

这里需要仔细考虑的问题是：

一个交易如何产生，并如何达成，可以用图A3来说明这个问题．

我们在处理交易的时候可能面临如下问题：

（1）一个交易只能在交易双方都同意的情况下才可以进行，所以数据库中的供求信息只能作为达成某个交易的基础；

（2）交易的双方可能不同时使用这个系统，因此需要系统提供一个双方交换信息的方式；

（3）系统需要提供一种方便系统（交易员）向用户提出建议来促成交易的途径，并在保证数据库数据完整性的情况下达成交易。

图A．3交易员操作图

[概念模型设计]

（从实践概括抽象出理论模型E/R）

数据库需要表述的信息有以下几种：

（1）零件信息

（2）供应商信息

（3）顾客信息

（4）供应商集和零件集之间的联系（供应）

图A．4供应商和零件之间的联系（供应）E/R模型

（5）顾客集和零件集之间的联系（求购）

图A．5顾客和零件之间的联系（求购）E/R模型

（6）交易（三元联系）

可以用E/R模型表述该模型的设计，E/R图如图A7所示。

图A．7全局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）供应联系转换为关系Supply（PartlD，ProviderlD，Price，Quantity）

（5）求购联系转换为关系OfferToBuy（CustomerlD，PartID，Price，Quantity）

（6）交易联系转换为关系Business（CustomerlD，ProviderlD，PartID，Price，Quantity）

每个关系模式的主键码都用下划线标出。

同时，对于从联系导出的关系Supply（供应），OfferToBuy（求购）和Business（交易），使用与之相联系的实体集的主健码作为自己的键码，必须符合外键码约束。

对于Customer（顾客），Provider（供应商）和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实现设计]

实现该设计的环境为Windows2000Perfessinal+MSSQLServer2000.0

1．建立Part表

CREATETABLEPart（

IDsmallintIDENTITY（1,1）PRIMARYKEYCLUSTERED,

Colorvarchar（20）,

Namevarchar（20）NOTNULL,

WeightintDEFAULT0,

Introtext）

2．建立Provider表

CREATETABLEProvider（

IDsmallintIDENTITY（1,1）PRIMARYKEYCLUSTERED,

Namevarchar（20）NOTNULL,

passwordvarchar（8）NOTNULL,

Addressvarchar（30）,

Telvarchar（20）,

Introtext）

3．建立Customer表

CREATETABLECustomer（

IDSmallintIDENTITY（1,1）PRIMARYKEYCLUSTERED,

Namevarchar（20）NOTNULL,

Addressvarchar（30）,

TeLVarchar（20））

4．建立Supply表

CREATETABLESupply（

PartIDSmallint,

ProviderIDsmallint,

Priceint,

QUantityint,

CONSTRAINTPK_SUPPLYPRIMARYKEYCLUSTERED（PartID,ProviderID）,

CONSTRAINTFK_SUPPLY_PARTIDFOREIGNKEY（PartID）REFERENCESPart（ID）,

CONSTRAINTFK_SUPPLY_PROVIDERIDFOREIGNKEY（ProviderID）REFERENCESProvider（ID））

5．建立OfferToBuy表

CREATETABLEOfferToBuy（

CustomerIDsmallint,

PartIDSmallint,

Priceint,

Quantityint,

CONSTRAINTPK_OFFERTOBUYPRIMARYKEYCLUSTERED（CustomerID,PartID）,

CONSTRAINTFK_OFFERTOBUY_CUSTOMERIDFOREIGNKEY（CustomerID）

REFERENCESCustomer（ID）,

CONSTRAINTFK_OFFERTOBUYFOREIGNKEY（PartID）

REFERENCESPart（ID））

6．建立Business表

CREATETABLEBusiness（

CustomerIDsmallint,

ProviderIDsmallint,

PartIDSmallint,

Priceint,

Quantityint,

CONSTRAINTPK_BUSINEssPRIMARYKEYClUSTERED（CuscomerID,ProviderID,PartID）,

CONSTRAINTFK_BUSINESS_CUSTOMERIDFOREIGNKEY（CustomerID）

REFERENCESCustomer（ID）,

CONSTRAINTFK_BUSINESS_PROVIDERlDFOREIGNKEY（ProviderID）

REFERENCESProvider（ID）,

CONSTRAINTFK_BUSINESS_PARTIDFOREIGNKEY（PartID）

REFERENCESPart（ID））

7．供应商操作

（1）注册（register）

INSERTINTOProvider（Name，password，Address，TeI，Intro）

VALUES（#Name，#password，#Address，#Tel，#Intro）

在登记操作后，供应商得到一个唯一的ID，可以根据这个ID采查询和修改供应商的数据。

（2）注销（unregister）

DELETEProviderWHERE（ID=#ID）；

（3）修改个人馆息（update）

UPdateProviderSet（Name=#Name,Address=#Address，Tel=#Tel，Intro=#Intro）

WHERE（ID＝#ID）；

（4）增加供应项（add_supply_item）

INSERTINTOSupply（PartID，Providerid，Price，Quantity）

VALUES（#PartID，#ProvderlD，#Price；#Quantily）；

（5）删除供应项（delete_supply_item）

DELETESupPly

WHERE（PartlD=#PartIDANDProvideID=#ProviderlD）；

（6）修改供应项（update_supply_item）

UPDATESupplySET（Price=#Price，Quantity=#Quantity）

WHERE（PartlD=#PartIDANDProviderID=#ProviderID）‘

很明显，系统并没有提供面向供应商修改零件信息的接口，所以供应商提供的零件必须已经在零件表中存在；可以这祥假设，交易所的管理员负责更新零件信息，而供应商可以向交易所申请增加某种零件的信息．事实上顾客也可以提出这样的要求。

8．顾客操作‘

（1）注册（register）

INSERTINTOCustomer（Name，Address，Tel）

VALUES（#Name，#Address，#Tel）；

在登记操作后，顾客得到一个唯一的ID，可以根据这个ID来查询和修改顾客的数据．

（2）注销（unregister）

DELETECustomer

WHERE

（3）修改个人信息（update）

UPDATECustomerSet（Name=#Name，Address=#Address，Tel=#Tel）

WHERE（1D=#ID）；

（4）增加需求项（add_OfferToBuy_item）

INSERTINTOOfferToBuy（PartID,CustomeriD，Price，Quantity）

VALUES（#PartID，#CustomerID，#Price，#Quantity）'

（5）删除需求项（delete_OfferToBuy_iterm）

DELETEOfferToBuy

WHERE（PartlD=#PartlDANDCustomerlD=#CustomerID）；

（6）修改需求项（叩date_OfferToBuy_item）

UPDATEOfferToBuySET（Price=#Price，Quantity=#Quantity

WHERE（PartlD=#PartIDANDCustomeriD=#CustomerID）

9．交易员

针对需求分析中提出的问题，我们提出了“协议书”的解决方案，方案的说明如下：

（1）每个交易在达成以前都作为协议书保存在数据库中，协议书具有和交易一样的完备信息，可以在条件成熟的情况下转为一个达成的交易；

（2）协议书只有在供应商和顾客都签字的情况下才有效；有效的协议书由交易员签发，协议书一经签发，就生效，表明一个交易的达成，数据库中的数据将同时予以修改；

（3）协议书可以由供应商、顾客或者交易员中的任意一个人提出申请。

当协议书在双方没有都签字前，协议的双方或者交易员都可以删除这个协议书；但是，当协议书签字完毕后，协议书就不得删除（修改），只能由交易员进行处理；

（4）协议书有可能在转成交易的过程中失败，因为在交易达成以前，数据库中的数据有可能因为其他交易而变化，一个协议书可能失效，这是允许的。

根据以上分析，对数据库的模型作一些修改，增加协议书表，其关系模式如下：

Agreement（CustomerlD，ProviderID，PartID，Price，Quantity，CustomerSign，ProviderSign）

对应的SQL描述为：

CREATETABLEAgreement（

Customermsmallint，

ProviderlDsmallint，

PartlDsmallint，

Priceint,

Quantityint，

CustomerSignint，

ProviderSignint，·

CONSTRAINTPK_AGREEMENTPRIMARYKEYCLUSTERED（CustomerID，ProviderID，PartID），

CONSTRAINTFK_AGREEMENT_CUSTOMERIDFOREIGNKEY（CustomerID）

REFERENCESCustomer（ID），

CONSTRAINTFK_AGREEMENT_PROVlDERIDFOREIGNKEY（ProviderID）

REFERENCESProvider（ID），

CONSTRAINTFK_AGREEMENT_PARTIDFOREIGNKEY（PartID）

REFERENCESPart（ID））

与上述其他操作相比，对交易的操作对数据完整性要求比较高，其中需要注意的地方是；

要防止同一用户（供应商，顾客）的数据因两个交易而同时修改；

需要同时对供应数据库（Supply）、需求数据库（OfferToBuy）、交易数据库（Business）

和协议数据库（Agreement）作出修改，而且需要保持这些修改的原子性；

很显然，这些要求正是对于一个事务（transaction）的要求，所以可以用一个事务来完成签发一个协议的操作。

事务的描述如下：

CREATEPROCPASS_AGREEMENT

@providerIDint，

@customeridint，

@partlDint

AS

DECLARE@TransNameVARCHAR（20）

SELECT@TransName='Pass_Agreement'

BEGINTRANSACTION@TransName

DEClARE@priceINT，@qUANTITYint

SELECT@price=price，@quantity=quantityFROMAgreement

WHEREprIVIderID=@providerIDANDcustomerID=@customerIDANDPanID=@partID

1NSERTINTOBusiness（ProviderID，CustomerID，PartID，Price，Quantity）

VALues（@providerid，@customerID，@PartID，@price,@quantity）

UPDATESupplySETquantity=quantity-@quantity

WHEREProviderID=@prividerIDANDpartID=@partID

IF（SELECTquantityFROMSupply

WHEREProiderid=@providerANDpartID=@PartID）<0

ROLLBACKTRANSACTlON@TranSName

DELETEFROMSupplyWHEREquantity=0

UPDATEOfferToBuySETquantity=quanttity-@quantity

WHERECustomerID=@customeridANDpartlD=@partID

IF（SELECTquandtityFROMOfferToBuy

WHERECustomerID=@CustomerIDANDpartID=@partlD）<0

ROLLBACKTRANSACTION@TransName

DELETEFROMOfferToBuyWHEREquantity=0

COMMITTRANSACTION@TransName

为了使用方便,这里定义了一个存贮过程；功能是完成从Agreementt的一个元组到Business的一个元组的转化工作。

这里考虑到了删除空的Suppiy和OfferTOBUY项，更加重要的是，这里考虑到了非法的Agreement的情况，在一段时间后，由于供应商或者顾客修改数据，Agreement可能就非法，这时就需要把这个事务废除，所以，这里检查了Supply表和OfferToBuy表中的数据，确保数据仍然正确。

另外交易员，或者说交易所必须承担的一项任务是更新零件列表。

这里在考虑顾客和供应商的时候÷并没有给予他们修改零件列表的权利，所以他们必须根据数据库中已有的项更新自己的供求信息。

由于这个数据库实际上更加偏重于模型化，而不是一个实际环境中的数据库，所以在实现应用模型的时候我们还需要对这个数据库的模型作一些修改。

由于本实验在模型设计上使用了MicrosoftTransact-SQL的语法，因此以上的数据库操作都是在SQLSERVER2000上测试通过的。

[实验数据示例：

测试阶段]

（1.实验方案设计2.测试，查找错误校正错误，检查是否符合用户的功能性能要求）

1．实验方案设计

（1）输入数据设计：

1）插入零件信息；

insertintoPart（Color，Name，Weight，Intro）

values（'black'，'stick'，'30'，'ofsteel'）；

显示刚插人的零件id：

selectidfromPartwherename='stick'；

id

----

1

（1row（s）affected）

（不同的实验，id值可能不同。

以后相应操作要保持前后一致就可以丁。

）

2）插入供应商信息：

insertintoProvider（Name，password，Address，Tel，Intro）

values（'coml'，'1234'，'北京'，6543210，'nothing'）；

显示刚插入的供应商id：

selectidfromProviderwherename='coml'；

id

---

1

（1row（s）affected）

3）插入顾客信息：

insertintoCustomer（Name，Address，Tel）

values（'cusl'，'北京'，'6666666'）'

显示刚插入的顾客id：

selectidfromCustomerwherename

id

---

1

（1row（S）affected）

4）插入供应商供应信息：

insertintoSupply（PartID，ProviderlD，Price，Quantity）

values（1，1，20