《计算机数据库三级设计与应用题》由会员分享，可在线阅读，更多相关《计算机数据库三级设计与应用题（17页珍藏版）》请在金锄头文库上搜索。
1、设计和应用题1、设某教学管理系统，其查询模块需要提供如下功能：查询系信息，列出各系编号、系名和系办公电话；查询某系教师的信息，列出教师号、教师名、工资和聘用日期；查询某教师讲授的课程信息，列出课程号、课程名和学分；查询讲授某门课程的教师信息，列出教师名和职称；查询某门课程的先修课程信息，列出先修课程号和先修课程名。系统有如下业务规则：一个系可聘用多名教师，一名教师只能受聘于一个系；一名教师可讲授多门课程，一门课程可由多名教师讲授；一门课程可以有多门先修课程，也可以没有先修课程。(1)请根据以上查询功能和业务规则，用ER图描述该系统的概念模型。（5分）(2)将ER图转换为满足3NF的关系模式，并
2、说明每个关系模式的主码和外码。（5分）(1)【解题思路】E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化E-R图的处置，现实世界的事物能作为属性对待的则尽量作为属性对待。实体和属性的划分给出如下两条规则：作为属性，不能再具有需要描述的性质，属性必须是不可分的数据项，不能包含其它属性。属性不能和其它实体有联系，即E-R图中所表示的联系是实体之间的联系。本题中一个系可以聘用多名教师，一名教师只能受聘于一个系，所以系实体和教师实体有联系；一名教师可以讲授多门课程，一门课程可由多名教师讲授，所以教师实体和课程实体有联系，一门课程可以有多门先修课程，所
3、以课程间也有联系。(2)【解题思路】要想使转换生成的关系模式满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。2、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分）【解题思路】存储过程是由PL/SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。使用存储过程具有以下优点：其已经编译和优
4、化过了，所以运行效率高，提供了在服务器端快速执行SQL语句的有效途径；存储过程降低了客户端和服务器之间的通信量；方便实施企业规则，当企业规则发生变化时只要修改存储过程，而无需修改其他应用程序。创建存储过程：create procedure 过程名参数名 类型,参数名 类型 /*过程首部*/AsDeclare/*as下面对应的块为过程体*/.begin.end如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数可以是输入参数或输出参数，默认为输入参数。 3、设某全国性的运输企业建立了大型OLTP系统，并在
5、该系统之上建立了数据仓库。OLTP系统和数据仓库中有如下数据表：运输明细表（运输单ID，发送站ID，终到站ID，货物ID，货物重量，运输价格，发货日期）汇总表1（发送站ID，终到站ID，货物ID，发货日期，总重，总运价）汇总表2（发送站ID，终到地区ID，货物ID，发货日期，总重，总运价）汇总表3（发送站ID，终到站ID，货物ID，发货月份，总重，总运价）汇总表4（发送地区ID，终到地区ID，货物类别ID，发货日期，总重，总运价）该企业管理的货运站约有100个，货物约有500种共10类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其
6、查询结果模式为（发送地区ID，终到站ID，发货月份，总重，总运价），该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分）【解题思路】计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理，也称作OLTP(联机事务处理)和OLAP(联机分析处理)。操作型处理也叫事务处理，是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的
7、安全性、完整性以及事务吞吐量要求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。4、设在SQL Server2008某数据库中有商品表和销售表，两个表的定义如下： (新增题库一)CREATE TABLE 商品表(商品号 char(10) PRIMARY KEY,商品名 varchar(40),类别 varchar(20),进货单价 int );CREATETABLE 销售表(商品号 char(10) REFERENCES 商品表(商品号),销售时间 datetime,销售数
8、量 int,销售单价 int,PRIMARYKEY(商品号,销售时间) );现要创建一个存储过程：查询指定类别的每种商品当前年销售总金额（销售总金额=销售单价* 销售数量）。请补全下列代码。CREATEPROC p_TotalProfitlb varchar(20)ASSELECT 商品名,(SELECTFROM 销售表 t1 WHEREand= year(Getdate() AS 销售总金额FROM 商品表 t2 WHERE【解题思路】整个select语句的含义是：根据where子句的条件表达式，从From子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出
9、元组中的属性值形成结果表。该题中(SELECT FROM 销售表 t1 WHERE and = year(Getdate()是个嵌套，别名叫做销售总金额。语句执行过程：先从商品表中按照第二个where形成一个初步查询结果。由于只有商品表中有类别，因此可知第四空填写：t2.类别=lb(t2是商品表的别名，t1是销售表的别名)。由此可知该步是按给定类别查询。销售总金额语句就应该在第一部的基础上求某年的总额。因此可以推断第三空是获得销售时间语句。因此该空填写t1.销售时间。由于是在第一步基础上的查询，所以应该实行连表查询，第二空应该填写连接的条件，即t1.商品号=t2.商品号。第一空应该是求总额的语
10、句。销售总金额=销售单价* 销售数量，即销售单价*SUM（销售数量)。【参考答案】【第1空】销售单价*SUM（销售数量)【第2空】t1.商品号=t2.商品号【第3空】t1.销售时间【第4空】t2.类别=lb5、设某超市经营管理系统使用SQL Server 2008数据库管理系统，此数据库服务器有2颗CPU、16GB内存、2TB磁盘。上线运行1年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。经技术人员分析，发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到60GB。已知这两个表结构如下：销售单据表（销售单据编号，销售时间，销售人员编号，收银台编号）销售单据明细
11、表（销售单据编号，商品编号，单价，数量）在进行此报表计算时数据库服务器CPU消耗非常高。为了优化此操作，某工程师建议在销售单据表中增加付款总金额属性，取值由触发器自动计算。请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。请判断此方案是否可行，并从时空代价和优化效果方面分析原因。【解题思路】在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。但是在数据库运行阶段要考虑到高效率的进行数据处理。完全规划化的数据库会产生很多表，对于一个频繁使用的查询，如果它要求操作多个相关表中的数据
12、，则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。而反规范化是将规范化的关系转换为非规范化的关系的过程，目的是提高查询的效率。常见的方法有增加派生冗余列，增加冗余列，重新组表，分割表和新增汇总表等方法。该题解决的方式就是增加派生冗余列-付款总金额。派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作，避免使用聚合函数。例如销售单据明细表（销售单据编号，商品编号，单价，数量）中增加付款总金额，因为付款总金额=单价*数量得到，说明付款总金额是派生性冗余列。如果不要该字段，那么每次使用总价时，都要先执
13、行代码计算后才能使用，如果商品数量较多，而且要频繁使用付款总金额时，计算付款总金额时执行的次数也会随着增加，这显然会影响数据库的执行效率。若增加付款总金额这个派生性冗余字段，虽然破坏了规范化原则，但只要执行一次计算付款总金额就可以把商品金额存在数据库中，以后不管什么时候使用付款总金额字段，只需要提取其值就可以了，不必在执行代码了。因此增加付款总金额，虽然提高了磁盘空间的使用，但是可以提高系统执行的效率，达到以空间换时间的目的。由此可以看出，在数据单据表中增加付款总金额不是正确的优化方法，应该在数据单据明细表中增加付款总金额，这样才能提高查询效率。【参考答案】 （新增题库一）此方案不可行。触发器
14、可以通过数据库中的相关表进行层叠更改，这比直接把代码写在前台的做法更安全合理，保证了数据的一致性，但同时增加了磁盘空间的消耗。在超市经营中要批量操作、多次触发的情况下，触发器的效率低，因为它相当于每次都执行一段SQL语句，使cpu的消耗更高。因而从时空代价角度来说并不能达到优化的效果，故该方案不可行。6、设某连锁商店数据库中有关系模式R：R（商店编号，商品编号，库存数量，部门编号，负责人）如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（10分）（1）请根据上述规定，写出关系模式R的函数依赖集；（2）请给出关系模式R的候选码；
15、（3）请说明关系模式R属于第几范式，并给出理由；（4）请将R分解成满足3NF的关系模式。（1）【解题思路】函数依赖定义：设R(U)是属性集U上的关系模式，X，Y是U的子集，若对于R（U）的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，在Y上的属性值不等，则称X函数确定Y或Y函数依赖X，记作X-Y。函数依赖是指关系R的一切关系均要满足的约束条件。（2）【解题思路】设K为R中的属性或属性组合，若U完全依赖于K，则K为R的候选码。（3）【解题思路】关系数据库是要满足一定要求的。满足最低要求的叫第一范式，在第一范式中满足进一步要求的为第二范式，其余以此类推。显然该关系模式满足第一范式，接下来检查其是否满足第二范式。在第二范式中，要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第一空可得如下依赖关系：（部门编号，商店编号）-负责人，所以属于第一范式。它的非主属性有3个（不包含在任何候选码中的属性）：部门
