计算机数据库三级设计与应用题Word格式.docx
《计算机数据库三级设计与应用题Word格式.docx》由会员分享，可在线阅读，更多相关《计算机数据库三级设计与应用题Word格式.docx（22页珍藏版）》请在冰豆网上搜索。
使用存储过程具有以下优点：
其已经编译和优化过了，所以运行效率高,提供了在服务器端快速执行SQL语句的有效途径;
存储过程降低了客户端和服务器之间的通信量;
方便实施企业规则,当企业规则发生变化时只要修改存储过程,而无需修改其他应用程序。
创建存储过程：
createprocedurｅ过程名
＠[参数名］ [类型］，＠[参数名] ［类型] /＊过程首部＊/
As
Ｄeｃｌare/*ａs下面对应的<
PL／SQL>
块为过程体*/
……….
begin
......．..
enｄ
如上所示，存储过程包括过程首部和过程体。
过程名是数据库服务器合法的对象标识；
参数列表:
用名字来标识调用时给出的参数值,必须指定值的数据类型。
参数可以是输入参数或输出参数，默认为输入参数。
3、设某全国性的运输企业建立了大型OLTP系统,并在该系统之上建立了数据仓库。
OLTＰ系统和数据仓库中有如下数据表:
运输明细表（运输单ID，发送站ID,终到站IＤ，货物ID,货物重量,运输价格,发货日期）
汇总表1（发送站ID，终到站ID，货物IＤ，发货日期,总重,总运价）
汇总表２（发送站ID，终到地区ID，货物IＤ，发货日期，总重，总运价）
汇总表３（发送站ID,终到站ＩD，货物ID，发货月份，总重,总运价）
汇总表４（发送地区ＩＤ,终到地区ID，货物类别ID，发货日期,总重，总运价）
该企业管理的货运站约有100个,货物约有50０种共1０类,各汇总表都建有主码,且各表有合理的维护策略，在每次维护后数据能保持一致。
设有视图V,该视图的访问频率很高,其查询结果模式为（发送地区ID,终到站ＩＤ,发货月份,总重,总运价）,该视图现以汇总表1为计算数据源。
经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降,而其它汇总表被访问频率较低。
在不增加汇总表和索引的情况下,请给出一个改善系统服务性能的优化方案，并简要说明理由。
（１0分）
计算机系统中存在着两类不同的数据处理工作：
操作型处理和分析型处理,也称作OLＴP（联机事务处理）和ＯLAP（联机分析处理）。
操作型处理也叫事务处理,是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。
这些系统要求快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要求很高。
结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。
因此本题考察了联机事务处理中的资源调度问题。
4、设在ＳQLSｅrveｒ200８某数据库中有商品表和销售表，两个表的定义如下:
（新增题库一）
CREＡTETAＢＬＥ 商品表（
商品号chａr（１０） ＰRIＭAＲＹＫEY,
商品名ｖａｒchar（4０）,
类别varchar（20）,
进货单价int ）;
CRＥＡＴEＴＡBLE销售表（
商品号char（10）RＥFERＥＮCEＳ商品表（商品号），
销售时间daｔｅtime,
销售数量iｎｔ,
销售单价iｎt,
ＰＲIMＡRYKEY（商品号，销售时间） ）；
现要创建一个存储过程：
查询指定类别的每种商品当前年销售总金额（销售总金额=销售单价*销售数量）。
请补全下列代码。
CREATEPROＣ p_TotalPrｏｆit
@lbvarchaｒ（２0）
AＳ
SELＥCT 商品名，（ＳＥLＥCT
FＲOM 销售表 ｔ1 WHＥRE
and
=yｅａr（Ｇeｔｄate（）））ＡS销售总金额
ＦROM商品表t２ WHEＲE
①整个ｓelect语句的含义是:
根据ｗhere子句的条件表达式,从Frｏm子句指定的基本表或视图中找出满足条件的元组,再按seleｃt子句中的目标列表达式,选出元组中的属性值形成结果表。
②该题中（SEＬECT FRＯM销售表 ｔ１ WHＥRE and =yeaｒ（Ｇetdatｅ（）））是个嵌套，别名叫做销售总金额。
③语句执行过程:
•先从商品表中按照第二个wherｅ形成一个初步查询结果。
由于只有商品表中有类别，因此可知第四空填写:
ｔ2.类别=@lｂ（ｔ２是商品表的别名，t1是销售表的别名）。
由此可知该步是按给定类别查询。
•销售总金额语句就应该在第一部的基础上求某年的总额。
因此可以推断第三空是获得销售时间语句。
因此该空填写t1.销售时间。
由于是在第一步基础上的查询，所以应该实行连表查询,第二空应该填写连接的条件,即t1.商品号＝ｔ2．商品号。
•第一空应该是求总额的语句。
销售总金额＝销售单价*销售数量，即销售单价＊SUM（销售数量）。
【参考答案】
【第1空】销售单价*SUＭ（销售数量）
【第2空】t1．商品号＝t2.商品号
【第3空】ｔ1.销售时间
【第4空】ｔ2.类别=@lｂ
5、设某超市经营管理系统使用ＳＱL Sｅｒver20０8数据库管理系统，此数据库服务器有2颗CPＵ、16ＧB内存、２TB磁盘。
上线运行１年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。
经技术人员分析,发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到６0ＧＢ。
已知这两个表结构如下：
销售单据表（销售单据编号，销售时间，销售人员编号，收银台编号）
销售单据明细表（销售单据编号,商品编号,单价,数量）
在进行此报表计算时数据库服务器CPＵ消耗非常高。
为了优化此操作，某工程师建议在销售单据表中增加＂付款总金额"
属性，取值由触发器自动计算。
请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。
请判断此方案是否可行，并从时空代价和优化效果方面分析原因。
在数据库设计阶段,主要强调的是高效率利用存储空间,减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。
但是在数据库运行阶段要考虑到高效率的进行数据处理。
完全规划化的数据库会产生很多表,对于一个频繁使用的查询，如果它要求操作多个相关表中的数据,则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。
而反规范化是将规范化的关系转换为非规范化的关系的过程,目的是提高查询的效率。
常见的方法有增加派生冗余列,增加冗余列,重新组表,分割表和新增汇总表等方法。
该题解决的方式就是增加派生冗余列－-"
付款总金额"
。
派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作,避免使用聚合函数。
例如销售单据明细表（销售单据编号，商品编号,单价，数量）中增加"
付款总金额＂，因为付款总金额＝单价＊数量得到，说明"
是派生性冗余列。
如果不要该字段，那么每次使用总价时，都要先执行代码计算后才能使用,如果商品数量较多,而且要频繁使用"
时，计算"
付款总金额＂时执行的次数也会随着增加,这显然会影响数据库的执行效率。
若增加＂付款总金额"
这个派生性冗余字段,虽然破坏了规范化原则,但只要执行一次计算"
就可以把商品金额存在数据库中,以后不管什么时候使用＂付款总金额＂字段，只需要提取其值就可以了，不必在执行代码了。
因此增加"
虽然提高了磁盘空间的使用,但是可以提高系统执行的效率,达到以空间换时间的目的。
由此可以看出，在数据单据表中增加＂付款总金额"
不是正确的优化方法,应该在数据单据明细表中增加＂付款总金额"
这样才能提高查询效率。
【参考答案】 （新增题库一）
此方案不可行。
触发器可以通过数据库中的相关表进行层叠更改,这比直接把代码写在前台的做法更安全合理,保证了数据的一致性，但同时增加了磁盘空间的消耗。
在超市经营中要批量操作、多次触发的情况下,触发器的效率低，因为它相当于每次都执行一段SQL语句,使cｐｕ的消耗更高。
因而从时空代价角度来说并不能达到优化的效果，故该方案不可行。
6、设某连锁商店数据库中有关系模式R:
R（商店编号,商品编号,库存数量，部门编号，负责人）
如果规定:
每个商店的每种商品只在一个部门销售,每个商店的每个部门只有一个负责人,每个商店的每种商品只有一个库存数量。
（１０分）
（1）请根据上述规定,写出关系模式R的函数依赖集;
（2）请给出关系模式R的候选码；
（３）请说明关系模式Ｒ属于第几范式，并给出理由;
（4）请将Ｒ分解成满足3NF的关系模式。
函数依赖定义：
设Ｒ（U）是属性集Ｕ上的关系模式，X，Ｙ是Ｕ的子集，若对于R（U）的任意一个可能的关系ｒ,r中不可能存在两个元组在Ｘ上的属性值相等，在Ｙ上的属性值不等,则称X函数确定Y或Y函数依赖X,记作X->
Y。
函数依赖是指关系Ｒ的一切关系均要满足的约束条件。
（２）
设K为Ｒ<
U，F>
中的属性或属性组合，若Ｕ完全依赖于K，则K为Ｒ的候选码。
（3）
关系数据库是要满足一定要求的。
满足最低要求的叫第一范式,在第一范式中满足进一步要求的为第二范式,其余以此类推。
显然该关系模式满足第一范式，接下来检查其是否满足第二范式。
在第二范式中,要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第一空可得如下依赖关系:
（部门编号，商店编号）->
负责人,所以属于第一范式。
它的非主属性有3个（不包含在任何候选码中的属性）：
部门编号、负责人、库存量,并都完全函数依赖于主码。
将（商店编号、商品编号）记作X,（商店编号、部门编号）记作Y，负责人记作Z，由此可以看出,存在传递依赖,故不属于第三范式。
（４）第三范式中要求每一个属性既不部分依赖于码也不传递依赖于码。
７、在某数据库中,相关表的建表语句如下：
（新增题库二）
creａtｅ tableT１（
a1 ｉntpｒiｍarykｅｙ，
a2 inｔ,
a3intforeign keｙrｅｆｅrenｃｅｓＴ２（a3））;
cｒeatｅ tableT２（
ａ3intｐriｍary ｋey,
a４ iｎt,
ａ5ｉntfｏｒeｉgnkey ｒeferｅncｅsT2（a3））；
crｅateｔabｌeＴ３（
a1ｉnt,
a3int,
ａ6inｔ，
primａrykｅｙ （a1,a3）,
a1foreignｋｅyreｆerencesＴ１（a1）,
a３foreｉgnkeyreferｅncesＴ2（a3））;
请画出相应的E-R图，使得可以从该E-R图推导出上述表定义,其中实体和联系的名称可以自定，实体的主码属性请使用下划线标明。
（新增题库二）
8、设某超市经营管理系统使用ＳQL Ｓerｖｅｒ 20０８数据库管理系统。
为了保证数据库可靠运行，数据库管理员设置了每天夜间对数据库一次全备份,备份数据保留２个月的备份策略。
上线运行1年后,SQLSerｖer数据库中数据已经达到近２00GB。
每天夜间要运行3个小时才能将数据库进行一次全备份，影响了夜间统计等业务正常运行。
同时,备份空间也非常紧张。
请解释出现此现象的原因，并提出优化的方法。
产生此现象的原因是系统采用了全备份策略，随着业务的开展，需要备份数据量逐渐增大，备份时间越来越长，占用了系统的资源,从而影响了其它业务。
解决的方法是采用全备份+差异备份+日志备份组合策略备份数据库。
全备份+差异备份＋日志备份组合策略是指在全备份中加一些差异备份，比如每周日０:
00进行一次全备份,然后每天0：
０0点进行一次差异备份，然后再两次差异备份之间增加一些日志备份。
这样做备份和恢复的速度都比较快，而当系统出现故障时,丢失的数据也很少。
备份示意图如下：
如果系统在周二的差异备份之前出现故障,则应首先尝试备份活动日志（日志尾部）,然后再按顺序恢复全备份1，差异备份１，日志备份３和日志备份4,然后再恢复备份的尾部日志。
如果尾部日志备份成功，则数据库可以还原到故障点。
这种备份策略虽然备份频率高，但是备份时间短,占用的备份空间也小，而且不会产生数据丢失。
如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（日志尾部）,然后再按顺序恢复全备份1，差异备份１,日志备份3和日志备份4，然后再恢复备份的尾部日志。
这种备份策略虽然备份频率高,但是备份时间短，占用的备份空间也小,而且不会产生数据丢失。
１、设有商品表（商品号,商品名,单价）和销售表（销售单据号，商品号,销售时间，销售数量,销售单价）。
要求商品号和年份为输入参数,总毛利用输出参数返回。
CREAＴE PROCEDＵREＰRODUＣT@商品号ｉnt，@年份int,@毛利iｎtoｕtput
AS
ＤECLＡRE
@某商品销售量 ｉnｔ,＠某商品进价int,＠某商品销售单价iｎt /*中间变量定义＊／
ＢEGIN
ﻩＳelect @某商品进价=单价froｍ商品表wheｒe@商品号=商品号
ﻩ Selecｔ＠某商品销售单价＝销售单价,＠某商品销售量=couｎt（*）ｆrom销售表ｗherｅ
@商品号＝商品号aｎd销售时间=@年份
IF@某商品进价ｉsＮＵＬＬＴHＥN /*判断该商品是否存在＊／
ROLLＢACK；
ＲＥTURN;
ＥNDＩＦ
ﻩIF@某商品销售单价isNULＬTHEN ／*判断该商品是否可卖*/
ＲOLＬBACK;
RETUＲN;
ﻩENDＩＦ
ﻩSET@毛利=（@某商品销售单价-@某商品进价）*@某商品销售量
GO
2、在SQＬServer２０08中，设某数据库中有商品表（商品号，商品名,进货价格）,商品号为主码；
销售表（商品号,销售时间,销售数量,销售价格，本次利润）,商品号和销售时间为主码,销售价格为本次销售商品的单价。
现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据）,系统自动计算本次销售产生的利润,并将该利润赋给销售表的第5列"
本次利润"
请编写实现上述功能的后触发型触发器代码。
ＣREAＴEＴＲIGGER cａlcu＿prｏduｃt
AＦTEＲINSＥRTON 销售表
FOＲ EACHROW
ﻩASＢEＧIＮ
DＥＣＬARE＠PｕrchａseＰrisefloａｔ/＊对应商品的进价的参数＊/
SELＥCＴ@PurchasｅPrisｅ=进货价格FＲＯM 商品表WHERE商品号=new.商品号
UPDATE销售表SET本次利润=new.销售数量*（ｎew.销售价格-＠ＰurchａsePｒisｅ） WHEＲE商品号=ｎｅｗ．商品号AＮD销售时间=nｅw.销售时间
/＊因为是行级触发器,所以可以使用更新后的新值,用new*/
EＮＤ
3、设在SＱLＳｅrver２008某数据库中，已建立了四个文件组:
fg1、ｆｇ2、fg３和fg4，以及一个分区函数RａngePF1。
ＲangePF1的定义代码如下：
CＲEＡTＥPARTITIONＦＵNＣＴIOＮ ＲａngePF1（int）
ASRＡＮGELEFＴＦOＲVＡLUES（100,20０,３００）
（1）请基于RangeＰF１创建一个分区方案ＲangePS1,每个分区对应一个文件组。
（５分）
createpaｒtｉtioｎ scｈｅmeRanｇePＳ１
ａsparｔitionRaｎｇePF1
to（ｆg1， fg1，ｆｇ1,fg２）
（2）请使用RangePS1创建一个分区表PaｒtitionＴ（Gｉd， ＧoodＮaｍe，Price）,该表基于Gid列创建分区。
其中Giｄ：
ｉnt类型,取值大于等于1;
GoodNamｅ:
统一字符编码可变长类型,最多可存储20个汉字;
Pｒice：
定点小数类型,小数部分1位,整数部分到千位。
创建分区表：
creaｔetableorderｓ
（
GIＤｉntidenｔiｔy（1，1）ｐｒiｍaｒy key,
GooｄNamevarchar（40）,
Priceflｏat
）
onRangｅＰS1（GＩD）
4、 设在SＱＬＳｅｒｖｅr ２008某数据库中有商品表和销售表,两个表的定义如下:
ＣREAＴＥ TABLE商品表（
商品号 ｃｈａｒ（10）PRIＭARYＫEY,
商品名vaｒcｈar（40）,
类别ｖａrchａr（20）,
进货单价int）
CREAＴETＡBLE 销售表（
商品号 ｃｈａr（10）,
销售时间dａtｅtiｍe,
销售数量ｉnt,
销售单价inｔ,
ＰRIMARYKＥＹ（商品号,销售时间））
下面是一个用户定义的多语句表值函数,它接受类别作为输入参数,返回该类别下的每种商品在２012年的销售总利润,并将结果按照销售总利润的降序输出。
请补全该函数定义代码。
（1０分）
CREATEFＵＮCTIOＮf＿Pｒｏfiｔ （＠lb chaｒ（10））【1】@ProｆiｔTable【2】
商品号 ｃｈaｒ（1０），
总利润int）
AS
BEGＩN
IＮSEＲTINTO ＠ProfitTablｅ
【3】
【4】
END
第一空:
RETUＲＮS
第二空:
ｔabｌe
第三空：
aＳＥLEＣＴa．商品号,ＳUＭ（销售数量*（销售单价-进货单价））AS总利润ＦROＭ销售表aＪOIＮ商品表bOＮa.商品号=b.商品号 ＷHERＥａ.商品号IN（SＥＬＥCT 商品号FＲOM商品表WHERE 类别＝@lb）GROUＰBYa.商品号ORDEＲＢY总利润DＥSＣ
第四空：
RＥＴURN@RrｏｆitTabｌe
5、设有图书管理数据库，包含三张表:
图书明细表（图书编号，图书类别,图书名称,作者,出版社,出版日期，定价）;
读者表（借书证号，姓名,系别,办证日期） ;
借出信息表（借出编号,借书证号，图书编号,借书日期）;
完成下列操作:
定义一个多语句表值函数,用于查询学生借书情况，只需提供参数：
借书证号，就可以通过调用函数返回此学生的借书情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日期；
若没有在借书籍,则返回记录为空。
设函数名为:
f_ＢoｒrowBook（@jsｚhｃhar（20））。
ＣＲEＡTEFUNCＴION BoｒｒowBook（＠jｓzｈｃhaｒ（20））
ＲETURNＳ@jsｑkbTABＬE（书籍编号chaｒ（20）,书籍名称cｈar（50）, 定价fｌoat，借书日期 ｄａtetｉme）
BEGIN
IＮSERT@jsqkb
SELECT图书明细表．图书编号，图书名称，定价,借出信息表.借书日期 FＲOM，借出信息表,图书明细表
ＷHＥRE借出信息表.图书编号=图书明细表.图书编号ＡND借出信息表．借书证号=@ｊszh
REＴURN
EＮＤ
6、某书店采用了SＱL Seｒveｒ 2００8数据库管理系统,该书店有一个需求,需要统计指定年份中每一本书的销售总额,例如:
查询2０1２年所有书的销售总额。
已知图书结构如下:
图书表（书号BOOＫ_ＩＤ，书名ＢOOＫ_NAME，单价BOOK＿PＲＩCE）
销售表（书号BOOＫ_IＤ,销售时间SＡLE＿TIＭE，销售数量SALＥ_ＮUM）。
假设单价和销售数量均为int型，书号和书名均为varchar（50）类型，销售时间为dateｔｉme型。
请给出满足如下要求的多语句表值函数,该函数统计指定年份中每本书的销售总额。
BOOK_ＰＲOFIT（@ｙｅaｒiｎｔ）,函数的返回结果格式如下：
书号销售总额
Ｂ0０160000
Ａ00４500０0
……
CREＡTＥFＵNCTＩON ＢOOK_PROFIT（@yearｉｎt）
RＥTＵRNS@f_BOOK_PROＦIＴtablｅ（
书号varchar（50），
销售总额 iｎｔ）
ＢEGIN
INSERＴINＴO＠f_BＯOK＿ＰROFIT
ＳELＥCTａ.书号,SUＭ（a．单价*ｂ．销售数量）
FＲOM图书表aJOＩN销售表b ＯＮ a.书号=b.书号
WHEREｙeaｒ（ｂ.销售时间）=@yeａｒ
GRＯUPBY a.书号
RETURN
ＥND
７、设在采用ＳQＬ Server20０8数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用ＳQＬ语句完成下列两个查询:
（１）ＳEＬECTLOANS.借书证号，姓名，系名,ＣOUNT（*）AS借书数量
FＲＯＭBOＲROWER,LOANS
ＷHERＥBORROWER.借书证号=LOANＳ.借书证号
ＧROUPＢY LＯANS.借书证号
HAVＩNＧCOUＮT（*）>
=5
升级会员 