计算机数据库三级设计与应用题Word格式.docx

1、使用存储过程具有以下优点：其已经编译和优化过了，所以运行效率高,提供了在服务器端快速执行SQL语句的有效途径;存储过程降低了客户端和服务器之间的通信量;方便实施企业规则,当企业规则发生变化时只要修改存储过程,而无需修改其他应用程序。创建存储过程：create procedur 过程名参数名类型，参数名类型/过程首部/Aseare/*s下面对应的块为过程体*/.begin.en如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表:用名字来标识调用时给出的参数值,必须指定值的数据类型。参数可以是输入参数或输出参数，默认为输入参数。3、设某全国性的运输企业建立了大型O

2、LTP系统,并在该系统之上建立了数据仓库。OLT系统和数据仓库中有如下数据表:运输明细表（运输单ID，发送站ID,终到站I，货物ID,货物重量,运输价格,发货日期）汇总表1（发送站ID，终到站ID，货物I，发货日期,总重,总运价）汇总表（发送站ID，终到地区ID，货物I，发货日期，总重，总运价）汇总表（发送站ID,终到站D，货物ID，发货月份，总重,总运价）汇总表（发送地区,终到地区ID，货物类别ID，发货日期,总重，总运价）该企业管理的货运站约有100个,货物约有50种共1类,各汇总表都建有主码,且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V,该视图的访问频率很高,其查询结果

3、模式为（发送地区ID,终到站,发货月份,总重,总运价）,该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降,而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下,请给出一个改善系统服务性能的优化方案，并简要说明理由。（0分）计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理,也称作OLP（联机事务处理）和LAP（联机分析处理）。操作型处理也叫事务处理,是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要

4、求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。4、设在QL Srve200某数据库中有商品表和销售表，两个表的定义如下: （新增题库一）CRETE TA商品表（商品号 chr（）RIA EY,商品名 char（4）,类别 varchar（20）,进货单价 int）;CREBLE 销售表（商品号 char（10） RFERCE 商品表（商品号），销售时间 datime,销售数量 i,销售单价 it,IMRYKEY（商品号，销售时间）；现要创建一个存储过程：查询指定类别的每

5、种商品当前年销售总金额（销售总金额=销售单价* 销售数量）。请补全下列代码。CREATEPROp_TotalPritlb varcha（0）ASELCT商品名，（LCTFOM销售表1WHREand= yr（eate（） S 销售总金额ROM 商品表 tWHEE整个elect语句的含义是:根据here子句的条件表达式,从Frm子句指定的基本表或视图中找出满足条件的元组,再按selet子句中的目标列表达式,选出元组中的属性值形成结果表。该题中（SEECT FRM 销售表WHRE and = yea（etdat（）是个嵌套，别名叫做销售总金额。语句执行过程:先从商品表中按照第二个wher形成一个初步

6、查询结果。由于只有商品表中有类别，因此可知第四空填写:2.类别=l（是商品表的别名，t1是销售表的别名）。由此可知该步是按给定类别查询。销售总金额语句就应该在第一部的基础上求某年的总额。因此可以推断第三空是获得销售时间语句。因此该空填写t1.销售时间。由于是在第一步基础上的查询，所以应该实行连表查询,第二空应该填写连接的条件,即t1.商品号2商品号。第一空应该是求总额的语句。销售总金额销售单价* 销售数量，即销售单价SUM（销售数量）。【参考答案】【第1空】销售单价*SU（销售数量）【第2空】t1商品号t2.商品号【第3空】1.销售时间【第4空】2.类别=l5、设某超市经营管理系统使用LSve

7、r 208数据库管理系统，此数据库服务器有2颗CP、16B内存、TB磁盘。上线运行年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。经技术人员分析,发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到0。已知这两个表结构如下：销售单据表（销售单据编号，销售时间，销售人员编号，收银台编号）销售单据明细表（销售单据编号,商品编号,单价,数量）在进行此报表计算时数据库服务器CP消耗非常高。为了优化此操作，某工程师建议在销售单据表中增加付款总金额属性，取值由触发器自动计算。请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方

8、案优劣。请判断此方案是否可行，并从时空代价和优化效果方面分析原因。在数据库设计阶段,主要强调的是高效率利用存储空间,减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。但是在数据库运行阶段要考虑到高效率的进行数据处理。完全规划化的数据库会产生很多表,对于一个频繁使用的查询，如果它要求操作多个相关表中的数据,则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。而反规范化是将规范化的关系转换为非规范化的关系的过程,目的是提高查询的效率。常见的方法有增加派生冗余列,增加冗余列,重新组表,分割表和新增汇总表等方法。该题解决的方式就

9、是增加派生冗余列-付款总金额。派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作,避免使用聚合函数。例如销售单据明细表（销售单据编号，商品编号,单价，数量）中增加付款总金额，因为付款总金额单价数量得到，说明是派生性冗余列。如果不要该字段，那么每次使用总价时，都要先执行代码计算后才能使用,如果商品数量较多,而且要频繁使用时，计算付款总金额时执行的次数也会随着增加,这显然会影响数据库的执行效率。若增加付款总金额这个派生性冗余字段,虽然破坏了规范化原则,但只要执行一次计算就可以把商品金额存在数据库中,以后不管什么时候使用付款总金额字段，只需要提取其值就可以了，不

10、必在执行代码了。因此增加,虽然提高了磁盘空间的使用,但是可以提高系统执行的效率,达到以空间换时间的目的。由此可以看出，在数据单据表中增加付款总金额不是正确的优化方法,应该在数据单据明细表中增加付款总金额,这样才能提高查询效率。【参考答案】 （新增题库一）此方案不可行。触发器可以通过数据库中的相关表进行层叠更改,这比直接把代码写在前台的做法更安全合理,保证了数据的一致性，但同时增加了磁盘空间的消耗。在超市经营中要批量操作、多次触发的情况下,触发器的效率低，因为它相当于每次都执行一段SQL语句,使c的消耗更高。因而从时空代价角度来说并不能达到优化的效果，故该方案不可行。6、设某连锁商店数据库中有关

11、系模式R:R（商店编号,商品编号,库存数量，部门编号，负责人）如果规定:每个商店的每种商品只在一个部门销售,每个商店的每个部门只有一个负责人,每个商店的每种商品只有一个库存数量。（分）（1）请根据上述规定,写出关系模式R的函数依赖集;（2）请给出关系模式R的候选码；（）请说明关系模式属于第几范式，并给出理由;（4）请将分解成满足3NF的关系模式。函数依赖定义：设（U）是属性集上的关系模式，X，是的子集，若对于R（U）的任意一个可能的关系,r中不可能存在两个元组在上的属性值相等，在上的属性值不等,则称X函数确定Y或Y函数依赖X,记作X-Y。函数依赖是指关系的一切关系均要满足的约束条件。（）设K为

12、中的属性或属性组合，若完全依赖于K，则K为的候选码。（3）关系数据库是要满足一定要求的。满足最低要求的叫第一范式,在第一范式中满足进一步要求的为第二范式,其余以此类推。显然该关系模式满足第一范式，接下来检查其是否满足第二范式。在第二范式中,要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第一空可得如下依赖关系:（部门编号，商店编号）-负责人,所以属于第一范式。它的非主属性有3个（不包含在任何候选码中的属性）：部门编号、负责人、库存量,并都完全函数依赖于主码。将（商店编号、商品编号）记作X,（商店编号、部门编号）记作Y，负责人记作Z，由此可以看出,存在传递依赖,故不属于第三范式。

13、（）第三范式中要求每一个属性既不部分依赖于码也不传递依赖于码。、在某数据库中,相关表的建表语句如下： （新增题库二）crettableT（ a1nt piary k， a2in,a3 int foreignke rren （a3）;ceattableT（ 3 int riaryey, ait,5 nt fegn keyeferncs T2（a3）；crate abe（ a1 nt, a3 int, 6 in，primry k（a1, a3）,a1 foreign y reerences （a1）,a foregn key refernces 2（a3）;请画出相应的E-R图，使得可以从该E-R

14、图推导出上述表定义,其中实体和联系的名称可以自定，实体的主码属性请使用下划线标明。（新增题库二）8、设某超市经营管理系统使用QLer20数据库管理系统。为了保证数据库可靠运行，数据库管理员设置了每天夜间对数据库一次全备份,备份数据保留个月的备份策略。上线运行1年后,SQL Serer数据库中数据已经达到近00GB。每天夜间要运行3个小时才能将数据库进行一次全备份，影响了夜间统计等业务正常运行。同时,备份空间也非常紧张。请解释出现此现象的原因，并提出优化的方法。产生此现象的原因是系统采用了全备份策略，随着业务的开展，需要备份数据量逐渐增大，备份时间越来越长，占用了系统的资源,从而影响了其它业务。

15、解决的方法是采用全备份+差异备份+日志备份组合策略备份数据库。全备份+差异备份日志备份组合策略是指在全备份中加一些差异备份，比如每周日:00进行一次全备份,然后每天0：0点进行一次差异备份，然后再两次差异备份之间增加一些日志备份。这样做备份和恢复的速度都比较快，而当系统出现故障时,丢失的数据也很少。备份示意图如下：如果系统在周二的差异备份之前出现故障,则应首先尝试备份活动日志（日志尾部）,然后再按顺序恢复全备份1，差异备份，日志备份和日志备份4,然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。这种备份策略虽然备份频率高，但是备份时间短,占用的备份空间也小，而且不会产生

16、数据丢失。如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（日志尾部）,然后再按顺序恢复全备份1，差异备份,日志备份3和日志备份4，然后再恢复备份的尾部日志。这种备份策略虽然备份频率高,但是备份时间短，占用的备份空间也小,而且不会产生数据丢失。、设有商品表（商品号,商品名,单价）和销售表（销售单据号，商品号,销售时间，销售数量,销售单价）。要求商品号和年份为输入参数,总毛利用输出参数返回。CREAEPROCEDRE RODUT 商品号 nt，年份 int,毛利 it otputASECLRE 某商品销售量n,某商品进价 int,某商品销售单价 it /*中间变量定义 EGIN e

17、lect某商品进价=单价 fro 商品表 whee 商品号=商品号Selec 某商品销售单价销售单价,某商品销售量=cout（*） rom 销售表 her 商品号商品号 ad 销售时间=年份 IF 某商品进价 s HN /*判断该商品是否存在ROLLACK；TURN; ND IF 某商品销售单价 is NUL THEN *判断该商品是否可卖*/OLBACK;RETUN;END SET 毛利=（某商品销售单价-某商品进价）*某商品销售量GO2、在SQ Server 08中，设某数据库中有商品表（商品号，商品名,进货价格）,商品号为主码；销售表（商品号,销售时间,销售数量,销售价格，本次利润）,商

18、品号和销售时间为主码,销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据）,系统自动计算本次销售产生的利润,并将该利润赋给销售表的第5列本次利润请编写实现上述功能的后触发型触发器代码。REAE IGGERclcuprdutATE INSRT ON销售表 FOEACH ROWAS EIDARE Prchserise flo /对应商品的进价的参数/SELC PurchasPris=进货价格 FM商品表 WHERE 商品号=new.商品号 UPDATE 销售表 SET 本次利润=new.销售数量*（ew.销售价格-urchsePis）WHEE 商品号=商品号

19、AD 销售时间=nw.销售时间 /因为是行级触发器,所以可以使用更新后的新值,用new*/ E3、设在SL rver 008某数据库中，已建立了四个文件组:fg1、2、fg和fg4，以及一个分区函数RngePF1。angePF1的定义代码如下： CET PARTITION NIOngePF1（int） AS RGE LEF O VLUES（100,20,）（1）请基于RangeF创建一个分区方案angePS1,每个分区对应一个文件组。（分）create pattioscme RanePs parition RaePF1to （g1，fg1， 1, fg）（2）请使用RangePS1创建一个分区

20、表Patition（Gd，oodae， Price）,该表基于Gid列创建分区。其中Gi：nt类型,取值大于等于1;GoodNam:统一字符编码可变长类型,最多可存储20个汉字;Pice：定点小数类型,小数部分1位,整数部分到千位。创建分区表：creae table order（GI nt ideniy（1，1） iaykey,GooName varchar（40）,Price flat）on RangS1（GD）4、设在S r008某数据库中有商品表和销售表,两个表的定义如下:REATABLE 商品表（商品号 （10）PRIARY EY,商品名 vacar（40）,类别 rchr（20）,进

21、货单价 int ）CREAE TBLE销售表（商品号 r（10）,销售时间 dttie,销售数量 nt,销售单价 in,RIMARY K（商品号,销售时间）下面是一个用户定义的多语句表值函数,它接受类别作为输入参数,返回该类别下的每种商品在012年的销售总利润,并将结果按照销售总利润的降序输出。请补全该函数定义代码。（1分）CREATE FCTIO fPfi（lbcha（10） 【1】ProiTable【2】商品号a（1），总利润 int ）ASBEGN ISET INTOProfitTabl 【3】【4】END第一空:RETUS第二空:abe第三空：a LE a商品号,U（销售数量*（销售单

22、价-进货单价） AS总利润 RO 销售表 a OI商品表b O a. 商品号=b. 商品号HER .商品号 IN（SCT商品号 FOM 商品表 WHERE类别lb） GROU BY a. 商品号ORDE Y 总利润 DS第四空：RURNRrit Tabe5、设有图书管理数据库，包含三张表: 图书明细表（图书编号，图书类别,图书名称,作者,出版社,出版日期，定价）;读者表（借书证号，姓名,系别,办证日期）;借出信息表（借出编号,借书证号，图书编号,借书日期）;完成下列操作:定义一个多语句表值函数,用于查询学生借书情况，只需提供参数：借书证号，就可以通过调用函数返回此学生的借书情况，若有借书籍，则

23、返回所借书籍的编号、书籍名称、定价和借书日期；若没有在借书籍,则返回记录为空。设函数名为:f_orowBook（jsh har（20）。ETE FUNCIONBoowBook （jz ha（20）ETURN jskb TABE （书籍编号 cha（20）, 书籍名称 car（50）,定价 foat， 借书日期tetme）BEGIN ISERT jsqkb SELECT图书明细表图书编号，图书名称，定价,借出信息表.借书日期 FOM，借出信息表,图书明细表 HRE借出信息表.图书编号=图书明细表.图书编号 ND借出信息表借书证号=szh REURNE6、某书店采用了SLSeve28数据库管理系统

24、,该书店有一个需求,需要统计指定年份中每一本书的销售总额,例如:查询21年所有书的销售总额。已知图书结构如下:图书表（书号BOO_，书名OO_NAME，单价BOOKPCE）销售表（书号BOO_I,销售时间SLETIE，销售数量SAL_UM）。假设单价和销售数量均为int型，书号和书名均为varchar（50）类型，销售时间为dateme型。请给出满足如下要求的多语句表值函数,该函数统计指定年份中每本书的销售总额。BOOK_OFIT（a i）,函数的返回结果格式如下：书号 销售总额01 6000000 5000 CRET FNCTONOOK_PROFIT （year t）RTRNS f_ BOO

25、K_PROI tabl（书号 varchar（50），销售总额i）EGININSER INO f_ BOKROFITELCT .书号,SU（a单价*销售数量）FOM 图书表 a JON 销售表 ba.书号=b.书号WHERE ea（.销售时间）=yeGRUP BYa.书号RETURNND、设在采用QServer 208数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用Q语句完成下列两个查询:（）EECT LOANS.借书证号，姓名，系名,OUNT（*） AS 借书数量F BOROWER,LOANSHER BORROWER.借书证号=LOAN.借书证号ROUP YLANS.借书证号HAVN COUT（*）=5