数据库实例零件销售中心管理系统概述样本.doc

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

数据库原理
课
程
设
计

教学试验指导书

关键课程数据库原理教研组
.1

数据库原理课程设计教学试验指导
SQL Server课程设计教学试验指导
A．1 综合试验
本课程教学试验分为两部分：
第一部分是根据试验指导书所要求试验在计算机上完成；
第二部分是作完上述试验后根据本课程设计教学试验指导书做一个综合性试验。经过教学试验可使读者较系统、全方面地掌握相关教学内容和必需上机操作。
下面给出三个试验课题，其中第一个课题还附有参考答案。期望读者在了解题意基础上发挥自己创新精神，有创意地完成教学试验。假如认为有参考答案可能会束缚自己思维，也可选作第二或第三个试验课题． 。
总而言之，因时间关系，只要求每个读者任选一个课题。若有时间，有爱好，可考虑另外两个课题，也会有所收益．

A．1．1 试验一 零件交易中心管理系统
(试验目标]
经过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，深入了解和掌握本书中所讲解内容。
(试验简述)
零件交易中心管理系统关键提供用户和供给商之间完成零件交易功效，其中包含
供给商信息、用户信息和零件信息。
供给商信息包含供给商号、供给商名、地址、电话、介绍；
用户信息包含用户号，用户名、地址、电话；
零件信息包含零件号、零件名、重量、颜色、介绍等。
此系统能够让供给商增加、删除和修改所提供零件产品，
还能够让用户增加、删除和修改所需求零件。
交易员能够利用用户提出需求信息和供给商提出供给信息来提出交易提议，由供给商和用户进行确定后即完成这笔交易。
(试验要求)
完成该系统数据库设计：
用SQl实现数据库设计，并在SQLServer上调试经过．

A．1．2 试验三 民航售票系统
(试验目标)
经过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，深入了解和掌握本书中所讲解内容。 · ·
(试验简述]
民航订票系统关键分为机场、航空企业和用户三方服务。航空企业提供航线和飞机资料，机场则对在本机场起飞和降落航班和机票进行管理，而用户能得到服务应该有航班线路和剩下票数．查询，和网上订票等功效。用户又能够分为两类，一类是一般用户，对于一般用户只有一般查询功效和订票功效，没有对应机票优惠，另一个是常常旅客，需要办理注册手续，但增加了里程积分功效和积分优惠政策。机场还要有紧急应对方法，在航班出现延误时，要发送对应信息。
(试验要求)
完成该系统数据库设计；
用SQL、实现数据库设计，并在SQlServcr上调试经过。

A．1．3 试验二 图书管理系统
(试验目标)
经过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，深入了解和掌握本书中所讲解内容．
[试验简述]
一个简单图书管理系统包含图书馆内书籍信息、学校在校学生信息和学生借阅信息。此系统功效分为面向学生和面向管理员两部分，其中学生能够进行借阅、续借、归还和查询书籍等操作，管理员能够完成书籍和学生增加，删除和修改和对学生，借阅、续借、归还确实定．
[试验要求]
完成该系统数据库设计；
用SQL实现数据库设计，并在SQ SQLServer上调试经过．

A．2 零件交易中心管理系统试验汇报
(参考答案)
(试验目标)
经过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，深入了解和掌握本书中所讲解内容。
(试验简述)
零件交易中心管理系统关键提供用户和供给商之间完成零件交易功效，
其中包含供给商信息、用户信息和零件信息。
此系统能够让供给商增加、删除和修改所提供零件产品，
还能够让用户增加、删除和修改所需求零件。
交易员能够利用用户提出需求信息和供给商提出供给信息来提出交易提议，由供给商和用户进行确定后即完成这笔交易。
[课程设计全过程]
1需求分析：（实际具体调查）
2数据库设计：(SQL Server 设计)
概念(模型)设计 （实际到概念）
逻辑设计 （逻辑推导）
物理设计 （理论到实现）
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)供给商集和零件集之间联络(供给)
M : N
零件
颜色
零件号
零件名
重量
介绍
供给商名
供给商
供给商号
地址
电话
介绍
供给
数量
价格

图A．4供给商和零件之间联络(供给) E/R模型

(5)用户集和零件集之间联络(求购)
M : N
零件
颜色
零件号
零件名
重量
介绍
用户
电话
用户号
用户名
地址
求购
数量
价格

图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实现设计]
实现该设计环境为Windows Perfessinal+MSSQLServer .0
1．建立Part表
CREATE TABLE Part(
ID smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Color varchar(20),
Name varchar(20) NOT NULL,
Weight int DEFAULT 0,
Intro text)
2．建立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,
Name varchar(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),
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 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(CuscomerID,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．供给商操作
(1)注册(register)
INSERT INTO Provider(Name，password，Address，TeI，Intro)
VALUES(#Name，#password，#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，Quantity)
VALUES(#PartID，#ProvderlD，#Price；#Quantily)；
(5)删除供给项(delete_supply_item)
DELETE SupPly
WHERE(PartlD=#PartID AND ProvideID=#ProviderlD)；
(6)修改供给项(update_supply_item)
UPDATESupplySET(Price=#Price，Quantity=#Quantity)
WHERE(PartlD=#PartID AND ProviderID=#ProviderID)‘
很显著，系统并没有提供面向供给商修改零件信息接口，所以供给商提供零件必需已经在零件表中存在；能够这祥假设，交易所管理员负责更新零件信息，而供给商能够向交易所申请增加某种零件信息．实际上用户也能够提出这么要求。
8．用户操作 ‘
(1)注册(register)
INSERT INTO Customer(Name，Address，Tel)
VALUES(#Name，#Address，#Tel)；
在登记操作后，用户得到一个唯一ID，能够依据这个ID来查询和修改用户数据．
(2)注销(unregister)
DELETE Customer
WHERE<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)
VALUES(#PartID，#CustomerID，#Price，#Quantity)'
(5)删除需求项(delete_OfferToBuy_iterm)
DELETE OfferToBuy
WHERE(PartlD=#PartlD AND CustomerlD=#CustomerID)；
(6)修改需求项(叩date_OfferToBuy_item)
UPDATE OfferToBuy SET(Price=#Price，Quantity=#Quantity
WHERE(PartlD=#PartID AND CustomeriD=#CustomerID)
9．交易员
针对需求分析中提出问题，我们提出了“协议书”处理方案，方案说明以下：
(1)每个交易在达成以前全部作为协议书保留在数据库中，协议书含有和交易一样完备信息，能够在条件成熟情况下转为一个达成交易；
(2)协议书只有在供给商和用户全部签字情况下才有效；有效协议书由交易员签发，协议书一经签发，就生效，表明一个交易达成，数据库中数据将同时给予修改；
(3)协议书能够由供给商、用户或交易员中任意一个人提出申请。当协议书在双方没有全部签字前，协议双方或交易员全部能够删除这个协议书；不过，当协议书签字完成后，协议书就不得删除(修改)，只能由交易员进行处理；
(4)协议书有可能在转成交易过程中失败，因为在交易达成以前，数据库中数据有可能因为其它交易而改变，一个协议书可能失效，这是许可。
依据以上分析，对数据库模型作部分修改，增加协议书表，其关系模式以下：
Agreement(CustomerlD，ProviderID，PartID，Price，Quantity，CustomerSign，ProviderSign)
对应SQL描述为：
CREATE TABLE Agreement(
Customerm smallint，
ProviderlD smallint，
PartlD smallint，
Price int,
Quantity int，
CustomerSign int，
ProviderSign int， ·
CONSTRAINT PK_AGREEMENT PRIMARY KEY CLUSTERED(CustomerID，ProviderID，PartID)，
CONSTRAINT FK_AGREEMENT_CUSTOMERID FOREIGN KEY(CustomerID)
REFERENCES Customer(ID)，
CONSTRAINT FK_ AGREEMENT_PROVlDERID FOREIGN KEY(ProviderID)
REFERENCES Provider(ID)，
CONSTRAINT FK_AGREEMENT_PARTID FOREIGN KEY(PartID)
REFERENCES Part(ID))

和上述其它操作相比，对交易操作对数据完整性要求比较高，其中需要注意地方是；
要预防同一用户(供给商，用户)数据因两个交易而同时修改；
需要同时对供给数据库(Supply)、需求数据库(OfferToBuy)、交易数据库(Business)
和协议数据库(Agreement)作出修改，而且需要保持这些修改原子性；
很显然，这些要求正是对于一个事务(transaction)要求，所以能够用一个事务来完成签发一个协议操作。事务描述以下：
CREATE PROC PASS_AGREEMENT
@providerID int，
@customerid int，
@partlD int
AS
DECLARE @TransName VARCHAR(20)
SELECT @TransName='Pass_Agreement'
BEGIN TRANSACTION @TransName
DEClARE @price INT，@qUANTITY int
SELECT @price=price，@quantity=quantity FROM Agreement
WHERE prIVIderID=@providerID AND customerID=@customerID AND PanID=@partID
1NSERT INTO Business(ProviderID，CustomerID，PartID，Price，Quantity)
VALues(@providerid，@customerID，@PartID，@price,@quantity)
UPDATE Supply SET quantity=quantity-@quantity
WHERE ProviderID=@prividerID AND partID=@partID
IF (SELECT quantity FROM Supply
WHERE Proiderid=@provider AND partID=@PartID)<0
ROLLBACK TRANSACTlON @TranSName
DELETE FROM Supply WHERE quantity=0
UPDATE OfferToBuy SET quantity=quanttity-@quantity
WHERE CustomerID=@customerid AND partlD=@partID
IF(SELECT quandtity FROM OfferToBuy
WHERE CustomerID=@CustomerID AND partID=@partlD)<0
ROLLBACK TRANSACTION @TransName
DELETE FROM OfferToBuy WHERE quantity=0
COMMIT TRANSACTION @TransName
为了使用方便,这里定义了一个存贮过程；功效是完成从Agreementt一个元组到Business一个元组转化工作。这里考虑到了删除空Suppiy和OfferTOBUY项，愈加关键是，这里考虑到了非法Agreement情况，在一段时间后，因为供给商或用户修改数据，Agreement可能就非法，这时就需要把这个事务废除，所以，这里检验了Supply表和OfferToBuy表中数据，确保数据仍然正确。
另外交易员，或说交易所必需负担一项任务是更新零件列表。这里在考虑用户和供给商时候÷并没有给她们修改零件列表权利，所以她们必需依据数据库中已经有项更新自己供求信息。
因为这个数据库实际上愈加偏重于模型化，而不是一个实际环境中数据库，所以在实现应用模型时候我们还需要对这个数据库模型作部分修改。
因为本试验在模型设计上使用了Microsoft Transact-SQL语法，所以以上数据库操作全部是在SQLSERVER上测试经过。
[试验数据示例：测试阶段]
(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)
(不一样试验，id值可能不一样。以后对应操作要保持前后一致就能够丁。)
2）插入供给商信息：
insert into Provider(Name，password，Address，Tel，Intro)
values('coml'，'1234'，'北京'，6543210，'nothing')；

显示刚插入供给商id：
select id from Provider where name='coml'；
id
- - -
1
(1 row(s) affected)
3）插入用户信息：
insert into Customer(Name，Address，Tel)
values('cusl'，'北京'，'6666666')'
显示刚插入用户id：
select id from Customer where name
id
- - -
1
(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，50)；
6）插入协议信息：
insert into Agreement(CustomerID,ProviderID，PartlD，Price，Quantity，CustomerSign，ProviderSign)
values(1，1，1，20，30，1，1)；

（2）实施交易操作设计：
1）实施交易存放过程PASS_AGREEMENT,参数为：1，1，1：

PASS_AGREEMENT 1，1，1;
(后面三个参数分别对应前面选择出供给商ID、用户ID和零件ID。)
2）结果：显示交易后供给信息和需求信息：
select Quantity from Supply where PartlD＝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）分析结果：
首先，保留在Supply表中1D为1零件供给量为100(参见Supply表Insert语句)，保留在OfferToBuy表中ID为1零件需求量为50(参见OFFERToBuy表Insert语句)。在Agreement表中指出ID为1供给商和ID为1用户要交易30个ID为1零件。当实施存放过程PASS_AGREEMENT以后，Supply和OfferToBuy表中对应数量全部降低了30，交易成功。

4）再次实施交易操作：
delete from Business；

附录：交易系统另一个SQL语言(数据表建立)：
/* Microsoft SQL Server - Scripting */
/* Server: 7523TEACHER */
/* Database: 商品交易系统 */
/* Creation Date 02-10-18 9:46:43 */

CREATE TABLE [dbo].[customer] (
[id] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (20) NOT NULL ,
[address] [varchar] (30) NULL ,
[Intro] [text] NULL )
GO
ALTER TABLE [dbo].[customer] WITH NOCHECK ADD
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[part] (
[id] [smallint] IDENTITY (1, 1) NOT NULL ,
[color] [varchar] (20) NULL ,
[name] [varchar] (20) NOT NULL ,
[weight] [int] NULL ,
[Intro] [text] NULL )
GO
ALTER TABLE [dbo].[part] WITH NOCHECK ADD
CONSTRAINT [PK_part] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[provider] (
[id] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (20) NOT NULL ,
[password] [varchar] (8) NOT NULL ,
[address] [varchar] (30) NULL ,
[Tel] [varchar] (20) NULL ,
[Intro] [text] NULL )
GO
ALTER TABLE [dbo].[provider] WITH NOCHECK ADD
CONSTRAINT [PK_provider] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[supply] (
[partid] [smallint] NOT NULL ,
[providerid] [smallint] NOT NULL ,
[price] [smallint] NULL ,
[quantity] [int] NULL )
GO
CREATE TABLE [dbo].[bussiness] (
[customerID] [smallint] NOT NULL ,
[partid] [smallint] NOT NULL ,
[providerid] [smallint] NOT NULL ,
[price] [smallint] NULL ,
[quantity] [int] NULL )
GO

CREATE TABLE [dbo].[offertobuy] (
[customerID] [smallint] NOT NULL ,
[partid] [smallint] NOT NULL ,
[price] [smallint] NULL ,
[quantity] [int] NULL )

ALTER TABLE [dbo].[bussiness] WITH NOCHECK ADD
CONSTRAINT [PK_bussiness] PRIMARY KEY CLUSTERED
(
[customerID],
[partid],
[providerid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[offertobuy] WITH NOCHECK ADD
CONSTRAINT [PK_offertobuy] PRIMARY KEY CLUSTERED
(
[customerID],
[partid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[supply] WITH NOCHECK ADD
CONSTRAINT [PK_supply] PRIMARY KEY CLUSTERED
(
[partid],
[providerid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[bussiness] ADD
CONSTRAINT [FK_bussiness_customer] FOREIGN KEY
(
[customerID]
) REFERENCES [dbo].[customer] (
[id]
),
CONSTRAINT [FK_bussiness_part] FOREIGN KEY
(
[partid]
) REFERENCES [dbo].[part] (
[id]
),
CONSTRAINT [FK_bussiness_provider] FOREIGN KEY
(
[providerid]
) REFERENCES [dbo].[provider] (
[id]
)
GO

ALTER TABLE [dbo].[offertobuy] ADD
CONSTRAINT [FK_offertobuy_customer] FOREIGN KEY
(
[customerID]
) REFERENCES [dbo].[customer] (
[id]
),
CONSTRAINT [FK_offertobuy_part] FOREIGN KEY