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

1 重庆工商大学计算机科学与技术专业重庆工商大学计算机科学与技术专业 数据数据库库原理原理 课课 程程 设设 计计 教学实验指导书教学实验指导书 重点重点课课程数据程数据库库原理教研原理教研组组 2004 1 2 数据库原理课程设计教学实验指导数据库原理课程设计教学实验指导 SQLSQL Server2000Server2000 课程设计教学实验指导课程设计教学实验指导 A 1 综合实验综合实验 本课程的教学实验分为两部分 第一部分是按照实验指导书所要求的实验在计算机上完成 第二部分是作完上述实验后按照本课程设计教学实验指导书做的一个综合性实验 通过 教学实验可使读者较系统 全面地掌握相关的教学内容和必要的上机操作 下面给出三个实验课题 其中第一个课题还附有参考答案 希望读者在理解题意的基础 上发挥自己的创新精神 有创意地完成教学实验 如果觉得有参考答案可能会束缚自己的思 维 也可选作第二或第三个实验课题 总之 因时间关系 只要求每个读者任选一个课题 若有时间 有兴趣 可考虑另外两 个课题 也会有所收益 A 1 1 实验一实验一 零件交易中心管理系统零件交易中心管理系统 实验目的 通过完成从用户需求分析 数据库设计到上机编程 调试和应用等全过程 进一步了解 和掌握本书中所讲解的内容 实验简述 零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能 其中包括 供应商信息 顾客信息以及零件信息 供应商信息包括供应商号 供应商名 地址 电话 简介 顾客信息包括顾客号 顾客名 地址 电话 零件信息包括零件号 零件名 重量 颜色 简介等 此系统可以让供应商增加 删除和修改所提供的零件产品 还可以让顾客增加 删除和修改所需求的零件 交易员可以利用顾客提出的需求信息和供应商提出的供应信息来提出交易的建议 由供 应商和顾客进行确认后即完成这笔交易 实验要求 完成该系统的数据库设计 用 SQl 实现数据库的设计 并在 SQLServer 上调试通过 A 1 2 实验三实验三 民航售票系统民航售票系统 实验目的 通过完成从用户需求分析 数据库设计到上机编程 调试和应用等全过程 进一步了解 和掌握本书中所讲解的内容 实验简述 民航订票系统主要分为机场 航空公司和客户三方的服务 航空公司提供航线和飞机的 资料 机场则对在本机场起飞和降落的航班和机票进行管理 而客户能得到的服务应该有航 班线路和剩余票数 的查询 以及网上订票等功能 客户又可以分为两类 一类是普通客户 对于普通客户只有普通的查询功能和订票功能 没有相应的机票优惠 另一种是经常旅客 需要办理注册手续 但增加了里程积分功能和积分优惠政策 机场还要有紧急应对措施 在 航班出现延误时 要发送相应的信息 3 实验要求 完成该系统的数据库设计 用 SQL 实现数据库的设计 并在 SQlServcr 上调试通过 A 1 3 实验二实验二 图书管理系统图书管理系统 实验目的实验目的 通过完成从用户需求分析 数据库设计到上机编程 调试和应用等全过程 进一步了解 和掌握本书中所讲解的内容 实验简述 一个简单的图书管理系统包括图书馆内书籍的信息 学校在校学生的信息以及学生的借 阅信息 此系统功能分为面向学生和面向管理员两部分 其中学生可以进行借阅 续借 归 还和查询书籍等操作 管理员可以完成书籍和学生的增加 删除和修改以及对学生 借阅 续借 归还的确认 实验要求 完成该系统的数据库设计 用 SQL 实现数据库的设计 并在 SQ SQLServer 上调试通过 A 2 零件交易中心管理系统实验报告零件交易中心管理系统实验报告 参考答案参考答案 实验目的实验目的 通过完成从用户需求分析 数据库设计到上机编程 调试和应用等全过程 进一步了解 和掌握本书中所讲解的内容 实验简述实验简述 零件交易中心管理系统主要提供顾客和供应商之间完成零件交易的功能 其中包括供应商信息 顾客信息以及零件信息 此系统可以让供应商增加 删除和修改所提供的零件产品 还可以让顾客增加 删除和修改所需求的零件 交易员可以利用顾客提出的需求信息和供应商提出的供应信息来提出交易的建议 由供应商和顾客进行确认后即完成这笔交易 课程设计全过程课程设计全过程 1 需求分析 实际详细调查 2 数据库设计 SQL Server 2000 设计 概念 模型 设计 实际到概念 逻辑设计 逻辑推导 物理设计 理论到实现 SQL 编程 调试 测试验证 实践反复检验 3 应用程序编程 调试 测试 用人机交互前台开发工具 VB NET 开发 windows 和 Web 应用程序 需求分析需求分析 4 详细地调查分析系统对象 功能 性能等需求 详细地调查分析系统对象 功能 性能等需求 l l 供应商供应商 供应商的操作流程图如图 A1 所示 图图 A1A1 供应商操作分类表供应商操作分类表 2 2 顾客 顾客 顾客的地位和供应商几乎是对称的 所以功能分类上也很相似 顾客的操作流程图如图 A2 所示 图图 A2A2 顾客操作分类表顾客操作分类表 3 3 交易员 交易员 交易员的工作就是提出交易和完成交易 这里需要仔细考虑的问题是 一个交易如何产 生 并如何达成 可以用图 A3 来说明这个问题 我们在处理交易的时候可能面临如下问题 1 一个交易只能在交易双方都同意的情况下才可以进行 所以数据库中的供求信息只能作 为达成某个交易的基础 2 交易的双方可能不同时使用这个系统 因此需要系统提供一个双方交换信息的方式 3 系统需要提供一种方便系统 交易员 向用户提出建议来促成交易的途径 并在保证数据 库数据完整性的情况下达成交易 增加供应项修改供应项删除供应项修改个人信息 供 应 项 注册注销 增加需求项修改需求项删除需求项修改个人信息 顾 客 注册注销 交 易 员 协议书草案 供应商以及顾客签字 正式签字 交易员签发完成交易 供应商 提出交易 顾客提出 交易申请 交易员提出 交易建议 5 图图 A A 3 3 交易员操作图交易员操作图 概念模型设计概念模型设计 从实践概括抽象出理论模型 从实践概括抽象出理论模型 E RE R 数据库需要表述的信息有以下几种 1 零件信息 2 供应商信息 3 顾客信息 4 供应商供应商集和零件和零件集之间的联系之间的联系 供应供应 图图 A A 4 4 供应商和零件之间的联系供应商和零件之间的联系 供应供应 E R 模型 5 顾客顾客集和零件和零件集之间的联系之间的联系 求购求购 图图 A A 5 5 顾客和零件之间的联系顾客和零件之间的联系 求购求购 E R 模型 M N 零件 颜色 零件号 零件名 重量 简介 顾客 电话 顾客号 顾客名 地址 求购 数量价格 M N 零件 颜色 零件号 零件名 重量 简介 供应商名 供应商 供应商号 地址 电话 简介 供应 数量价格 6 6 6 交易交易 三元联系三元联系 可以用 E R 模型表述该模型的设计 E R 图如图 A7 所示 图图 A A 7 7 全局全局 E RE R 模型模型 逻辑设计逻辑设计 从理论从理论 E R E R 模型模型 到理论到理论 关系模型关系模型 的整理转换的整理转换 通过 E R 模型到关系模型的转化 可以得到如下关系模式 1 零件实体集转换为 关系 Part IDID Color Name Weight Intro 2 供应商实体集转换为 关系 Provider IDID Name Addtess Tel Intro 3 顾客实体集转换为 关系 Customer IDID Name Addtess Tel 4 供应联系供应联系转换为 关系 Supply PartlDPartlD ProviderlD Price Quantity 5 求购联系联系转换为 关系 OfferToBuy CustomerlDCustomerlD PartID Price Quantity 6 交易联系联系转换为 关系 Business CustomerlDCustomerlD ProviderlDProviderlD PartIDPartID Price Quantity 每个关系模式的主键码都用下划线主键码都用下划线标出 同时 对于从联系导出的关系对于从联系导出的关系 Supply Supply 供应供应 OfferToBuy OfferToBuy 求购求购 和和 Business Business 交易交易 使用与之相联系的实体集的主健码作为自己的键码 必须符合外键码约束使用与之相联系的实体集的主健码作为自己的键码 必须符合外键码约束 对于 Customer 顾客 Provider 供应商 和 Part 零件 之间 不存在直接的约束 所 以可以存在没有供应商供应同时也没有顾客求购的零件 供应商交易 价格 数量 求购 数量 价格 供应商号 供应商名 地址 电话 简介 供应 数量 价格 零件 颜色 零件号 零件名 重量 简介 顾客 电话 顾客号 顾客名 地址 7 物理设计物理设计 从理论 从理论 关系模型关系模型 到实现到实现 实施实施 数据库建立数据库建立 物理文件的安排和建立索引 物理文件的安排和建立索引 1 为了提高在表中搜索元组的速度 在实际实现的时候应该基于键码建立索引是各表中为了提高在表中搜索元组的速度 在实际实现的时候应该基于键码建立索引是各表中 建立索引的表项建立索引的表项 1 part ID 2 Provider ID 3 Customer ID 4 Supply PartID ProviderID 5 OfferTOBuy CustomerID PartID 6 Business CustomerlD ProviderID PartID 2 2 用用 SQLSQL 实现设计实现设计 实现该设计的环境为 Windows 2000 Perfessinal MSSQLServer 2000 0 1 建立 Part 表 CREATE TABLE PartPart IDID smallint IDENTITY 1 1 PRIMARYPRIMARY KEYKEY CLUSTEREDCLUSTERED ColorColor varchar 20 NameName varchar 20 NOT NULL WeightWeight int DEFAULT 0 IntroIntro text 2 建立 Provider 表 CREATE TABLE Provider IDID smallint IDENTITY 1 1 PRIMARYPRIMARY KEYKEY CLUSTERED CLUSTERED NameName varchar 20 NOT NULL passwordpassword varchar 8 NOT NULL AddressAddress varchar 30 TelTel varchar 20 IntroIntro text 3 建立 Customer 表 CREATE TABLE CustomerCustomer IDID Smallint IDENTITY 1 1 PRIMARYPRIMARY KEYKEY CLUSTERED CLUSTERED NameName varchar 20 NOT NULL AddressAddress varchar 30 TeLTeL Varchar 20 4 建立 Supply 表 CREATE TABLE Supply PartID Smallint 8 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 采查询和修改供应商的数 据 9 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 协议书可以由供应商 顾客或者交易员中的任意一个人提出申请 当协议书在双方 10 没有都签字前 协议的双方或者交易员都可以删除这个协议书 但是 当协议书签字完毕后 协议书就不得删除 修改 只能由交易员进行处理 4 协议书有可能在转成交易的过程中失败 因为在交易达成以前 数据库中的数据有 可能因为其他交易而变化 一个协议书可能失效 这是允许的 根据以上分析 对数据库的模型作一些修改 增加协议书表 其关系模式如下 Agreement Agreement CustomerlDCustomerlD ProviderIDProviderID PartIDPartID PricePrice QuantityQuantity CustomerSignCustomerSign ProvideProvide rSign rSign 对应的 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 Supply 需求数据库 需求数据库 OfferToBuy OfferToBuy 交易数据库 交易数据库 Business Business 和协议数据库和协议数据库 Agreement Agreement 作出修改 而且需要保持这些修改的原子性 作出修改 而且需要保持这些修改的原子性 很显然 这些要求正是对于一个事务 transaction 的要求 所以可以用一个事务来完 成签发一个协议的操作 事务的描述如下 CREATE PROC PASS AGREEMENT providerID int customerid int partlD int AS DECLARE TransName VARCHAR 20 11 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 quanti ty 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 的语法 因此以上的数据库 操作都是在 SQLSERVER2000 上测试通过的 实验数据示例 测试阶段实验数据示例 测试阶段 1 1 实验方案设计实验方案设计 2 2 测试 查找错误校正错误 检查是否符合用户的功能性能测试 查找错误校正错误 检查是否符合用户的功能性能 要求要求 1 实验方案设计 1 1 输入数据设计 输入数据设计 12 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 Provider Sign values 1 1 1 20 30 1 1 2 执行交易操作设计执行交易操作设计 1 执行交易存储过程 PASS AGREEMENT 参数为 1 1 1 13 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 附录 交易系统的另一种附录 交易系统的另一种 SQLSQL 语言语言 数据表建立数据表建立 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 14 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 15 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