计算机数据库三级设计与应用题.docx
- 文档编号:27004328
- 上传时间:2023-06-25
- 格式:DOCX
- 页数:29
- 大小:209.96KB
计算机数据库三级设计与应用题.docx
《计算机数据库三级设计与应用题.docx》由会员分享,可在线阅读,更多相关《计算机数据库三级设计与应用题.docx(29页珍藏版)》请在冰豆网上搜索。
计算机数据库三级设计与应用题
设计与应用题
1、设某教学管理系统,其查询模块需要提供如下功能:
Ⅰ.查询系信息,列出各系编号、系名和系办公电话;
Ⅱ.查询某系教师的信息,列出教师号、教师名、工资和聘用日期;
Ⅲ.查询某教师讲授的课程信息,列出课程号、课程名和学分;
Ⅳ.查询讲授某门课程的教师信息,列出教师名和职称;
Ⅴ.查询某门课程的先修课程信息,列出先修课程号和先修课程名。
系统有如下业务规则:
Ⅰ.一个系可聘用多名教师,一名教师只能受聘于一个系;
Ⅱ.一名教师可讲授多门课程,一门课程可由多名教师讲授;
Ⅲ.一门课程可以有多门先修课程,也可以没有先修课程。
(1)请根据以上查询功能与业务规则,用ER图描述该系统的概念模型。
(5分)
(2)将ER图转换为满足3NF的关系模式,并说明每个关系模式的主码和外码。
(5分)
(1)【解题思路】
E-R图也称实体-联系图,提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型。
为了简化E-R图的处置,现实世界的事物能作为属性对待的则尽量作为属性对待。
实体与属性的划分给出如下两条规则:
①作为"属性",不能再具有需要描述的性质,"属性"必须是不可分的数据项,不能包含其它属性。
②"属性"不能与其它实体有联系,即E-R图中所表示的联系是实体之间的联系。
本题中一个系可以聘用多名教师,一名教师只能受聘于一个系,所以系实体与教师实体有联系;一名教师可以讲授多门课程,一门课程可由多名教师讲授,所以教师实体与课程实体有联系,一门课程可以有多门先修课程,所以课程间也有联系。
(2)【解题思路】
要想使转换生成的关系模式满足3NF,则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。
2、设有商品表(商品号,商品名,单价)和销售表(销售单据号,商品号,销售时间,销售数量,销售单价)。
其中,商品号代表一类商品,商品号、单价、销售数量和销售单价均为整型。
请编写查询某年某商品的销售总毛利的存储过程,毛利=销售数量×(销售单价-单价)。
要求商品号和年份为输入参数,总毛利用输出参数返回。
(10分)
【解题思路】
存储过程是由PL/SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,使用时只要调用即可。
使用存储过程具有以下优点:
其已经编译和优化过了,所以运行效率高,提供了在服务器端快速执行SQL语句的有效途径;存储过程降低了客户端和服务器之间的通信量;方便实施企业规则,当企业规则发生变化时只要修改存储过程,而无需修改其他应用程序。
创建存储过程:
createprocedure过程名
@[参数名][类型],@[参数名][类型]/*过程首部*/
As
Declare/*as下面对应的
……….
begin
.........
end
如上所示,存储过程包括过程首部和过程体。
过程名是数据库服务器合法的对象标识;参数列表:
用名字来标识调用时给出的参数值,必须指定值的数据类型。
参数可以是输入参数或输出参数,默认为输入参数。
3、设某全国性的运输企业建立了大型OLTP系统,并在该系统之上建立了数据仓库。
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,该视图的访问频率很高,其查询结果模式为(发送地区ID,终到站ID,发货月份,总重,总运价),该视图现以汇总表1为计算数据源。
经监控发现,汇总表1的被访问频率过高,导致系统整体性能下降,而其它汇总表被访问频率较低。
在不增加汇总表和索引的情况下,请给出一个改善系统服务性能的优化方案,并简要说明理由。
(10分)
【解题思路】
计算机系统中存在着两类不同的数据处理工作:
操作型处理和分析型处理,也称作OLTP(联机事务处理)和OLAP(联机分析处理)。
操作型处理也叫事务处理,是指对数据库联机的日常操作,通常是对一个或一组纪录的查询或修改,例如火车售票系统、银行通存通兑系统、税务征收管理系统等。
这些系统要求快速响应用户请求,对数据的安全性、完整性以及事务吞吐量要求很高。
结合本题中存在的问题,视图本身的访问量很高,而又仅仅以汇总表1为计算数据源,而其它汇总表访问率低,导致了资源利用不合理。
因此本题考察了联机事务处理中的资源调度问题。
4、设在SQLServer2008某数据库中有商品表和销售表,两个表的定义如下:
(新增题库一)
CREATETABLE商品表(
商品号char(10)PRIMARYKEY,
商品名varchar(40),
类别varchar(20),
进货单价int);
CREATETABLE销售表(
商品号char(10)REFERENCES商品表(商品号),
销售时间datetime,
销售数量int,
销售单价int,
PRIMARYKEY(商品号,销售时间));
现要创建一个存储过程:
查询指定类别的每种商品当前年销售总金额(销售总金额=销售单价*销售数量)。
请补全下列代码。
CREATEPROCp_TotalProfit
@lbvarchar(20)
AS
SELECT商品名,(SELECT
FROM销售表t1WHERE
and
=year(Getdate()))AS销售总金额
FROM商品表t2WHERE
【解题思路】
①整个select语句的含义是:
根据where子句的条件表达式,从From子句指定的基本表或视图中找出满足条件的元组,再按select子句中的目标列表达式,选出元组中的属性值形成结果表。
②该题中(SELECTFROM销售表t1WHEREand=year(Getdate()))是个嵌套,别名叫做销售总金额。
③语句执行过程:
•先从商品表中按照第二个where形成一个初步查询结果。
由于只有商品表中有类别,因此可知第四空填写:
t2.类别=@lb(t2是商品表的别名,t1是销售表的别名)。
由此可知该步是按给定类别查询。
•销售总金额语句就应该在第一部的基础上求某年的总额。
因此可以推断第三空是获得销售时间语句。
因此该空填写t1.销售时间。
由于是在第一步基础上的查询,所以应该实行连表查询,第二空应该填写连接的条件,即t1.商品号=t2.商品号。
•第一空应该是求总额的语句。
销售总金额=销售单价*销售数量,即销售单价*SUM(销售数量)。
【参考答案】
【第1空】销售单价*SUM(销售数量)
【第2空】t1.商品号=t2.商品号
【第3空】t1.销售时间
【第4空】t2.类别=@lb
5、设某超市经营管理系统使用SQLServer2008数据库管理系统,此数据库服务器有2颗CPU、16GB内存、2TB磁盘。
上线运行1年后,用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。
经技术人员分析,发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到60GB。
已知这两个表结构如下:
销售单据表(销售单据编号,销售时间,销售人员编号,收银台编号)
销售单据明细表(销售单据编号,商品编号,单价,数量)
在进行此报表计算时数据库服务器CPU消耗非常高。
为了优化此操作,某工程师建议在销售单据表中增加"付款总金额"属性,取值由触发器自动计算。
请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。
请判断此方案是否可行,并从时空代价和优化效果方面分析原因。
【解题思路】
在数据库设计阶段,主要强调的是高效率利用存储空间,减少数据的冗余,减少数据的不一致性,这个过程也就是规范化的过程。
但是在数据库运行阶段要考虑到高效率的进行数据处理。
完全规划化的数据库会产生很多表,对于一个频繁使用的查询,如果它要求操作多个相关表中的数据,则每次为生成需要的查询结果而在连接多个表中相关行时,数据库管理系统就会消耗更多的计算资源,因为连接操作非常耗时。
而反规范化是将规范化的关系转换为非规范化的关系的过程,目的是提高查询的效率。
常见的方法有增加派生冗余列,增加冗余列,重新组表,分割表和新增汇总表等方法。
该题解决的方式就是增加派生冗余列--"付款总金额"。
派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成,它的作用是查询时减少连接操作,避免使用聚合函数。
例如销售单据明细表(销售单据编号,商品编号,单价,数量)中增加"付款总金额",因为付款总金额=单价*数量得到,说明"付款总金额"是派生性冗余列。
如果不要该字段,那么每次使用总价时,都要先执行代码计算后才能使用,如果商品数量较多,而且要频繁使用"付款总金额"时,计算"付款总金额"时执行的次数也会随着增加,这显然会影响数据库的执行效率。
若增加"付款总金额"这个派生性冗余字段,虽然破坏了规范化原则,但只要执行一次计算"付款总金额"就可以把商品金额存在数据库中,以后不管什么时候使用"付款总金额"字段,只需要提取其值就可以了,不必在执行代码了。
因此增加"付款总金额",虽然提高了磁盘空间的使用,但是可以提高系统执行的效率,达到以空间换时间的目的。
由此可以看出,在数据单据表中增加"付款总金额"不是正确的优化方法,应该在数据单据明细表中增加"付款总金额",这样才能提高查询效率。
【参考答案】(新增题库一)
此方案不可行。
触发器可以通过数据库中的相关表进行层叠更改,这比直接把代码写在前台的做法更安全合理,保证了数据的一致性,但同时增加了磁盘空间的消耗。
在超市经营中要批量操作、多次触发的情况下,触发器的效率低,因为它相当于每次都执行一段SQL语句,使cpu的消耗更高。
因而从时空代价角度来说并不能达到优化的效果,故该方案不可行。
6、设某连锁商店数据库中有关系模式R:
R(商店编号,商品编号,库存数量,部门编号,负责人)
如果规定:
每个商店的每种商品只在一个部门销售,每个商店的每个部门只有一个负责人,每个商店的每种商品只有一个库存数量。
(10分)
(1)请根据上述规定,写出关系模式R的函数依赖集;
(2)请给出关系模式R的候选码;
(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个(不包含在任何候选码中的属性):
部门编号、负责人、库存量,并都完全函数依赖于主码。
将(商店编号、商品编号)记作X,(商店编号、部门编号)记作Y,负责人记作Z,由此可以看出,存在传递依赖,故不属于第三范式。
(4)第三范式中要求每一个属性既不部分依赖于码也不传递依赖于码。
7、在某数据库中,相关表的建表语句如下:
(新增题库二)
createtableT1(
a1intprimarykey,
a2int,
a3intforeignkeyreferencesT2(a3));
createtableT2(
a3intprimarykey,
a4int,
a5intforeignkeyreferencesT2(a3));
createtableT3(
a1int,
a3int,
a6int,
primarykey(a1,a3),
a1foreignkeyreferencesT1(a1),
a3foreignkeyreferencesT2(a3));
请画出相应的E-R图,使得可以从该E-R图推导出上述表定义,其中实体和联系的名称可以自定,实体的主码属性请使用下划线标明。
【参考答案】
(新增题库二)
8、设某超市经营管理系统使用SQLServer2008数据库管理系统。
为了保证数据库可靠运行,数据库管理员设置了每天夜间对数据库一次全备份,备份数据保留2个月的备份策略。
上线运行1年后,SQLServer数据库中数据已经达到近200GB。
每天夜间要运行3个小时才能将数据库进行一次全备份,影响了夜间统计等业务正常运行。
同时,备份空间也非常紧张。
请解释出现此现象的原因,并提出优化的方法。
【参考答案】
产生此现象的原因是系统采用了全备份策略,随着业务的开展,需要备份数据量逐渐增大,备份时间越来越长,占用了系统的资源,从而影响了其它业务。
解决的方法是采用全备份+差异备份+日志备份组合策略备份数据库。
全备份+差异备份+日志备份组合策略是指在全备份中加一些差异备份,比如每周日0:
00进行一次全备份,然后每天0:
00点进行一次差异备份,然后再两次差异备份之间增加一些日志备份。
这样做备份和恢复的速度都比较快,而当系统出现故障时,丢失的数据也很少。
备份示意图如下:
如果系统在周二的差异备份之前出现故障,则应首先尝试备份活动日志(日志尾部),然后再按顺序恢复全备份1,差异备份1,日志备份3和日志备份4,然后再恢复备份的尾部日志。
如果尾部日志备份成功,则数据库可以还原到故障点。
这种备份策略虽然备份频率高,但是备份时间短,占用的备份空间也小,而且不会产生数据丢失。
如果系统在周二的差异备份之前出现故障,则应首先尝试备份活动日志(日志尾部),然后再按顺序恢复全备份1,差异备份1,日志备份3和日志备份4,然后再恢复备份的尾部日志。
如果尾部日志备份成功,则数据库可以还原到故障点。
这种备份策略虽然备份频率高,但是备份时间短,占用的备份空间也小,而且不会产生数据丢失。
1、设有商品表(商品号,商品名,单价)和销售表(销售单据号,商品号,销售时间,销售数量,销售单价)。
其中,商品号代表一类商品,商品号、单价、销售数量和销售单价均为整型。
请编写查询某年某商品的销售总毛利的存储过程,毛利=销售数量×(销售单价-单价)。
要求商品号和年份为输入参数,总毛利用输出参数返回。
(10分)
CREATEPROCEDUREPRODUCT@商品号int,@年份int,@毛利intoutput
AS
DECLARE
@某商品销售量int,@某商品进价int,@某商品销售单价int/*中间变量定义*/
BEGIN
Select@某商品进价=单价from商品表where@商品号=商品号
Select@某商品销售单价=销售单价,@某商品销售量=count(*)from销售表where
@商品号=商品号and销售时间=@年份
IF@某商品进价isNULLTHEN/*判断该商品是否存在*/
ROLLBACK;
RETURN;
ENDIF
IF@某商品销售单价isNULLTHEN/*判断该商品是否可卖*/
ROLLBACK;
RETURN;
ENDIF
SET@毛利=(@某商品销售单价-@某商品进价)*@某商品销售量
GO
2、在SQLServer2008中,设某数据库中有商品表(商品号,商品名,进货价格),商品号为主码;销售表(商品号,销售时间,销售数量,销售价格,本次利润),商品号和销售时间为主码,销售价格为本次销售商品的单价。
现要求每当在销售表中插入前4列数据时(假设一次只插入一行数据),系统自动计算本次销售产生的利润,并将该利润赋给销售表的第5列"本次利润"。
请编写实现上述功能的后触发型触发器代码。
(10分)
CREATETRIGGERcalcu_product
AFTERINSERTON销售表
FOREACHROW
ASBEGIN
DECLARE@PurchasePrisefloat/*对应商品的进价的参数*/
SELECT@PurchasePrise=进货价格FROM商品表WHERE商品号=new.商品号
UPDATE销售表SET本次利润=new.销售数量*(new.销售价格-@PurchasePrise)WHERE商品号=new.商品号AND销售时间=new.销售时间
/*因为是行级触发器,所以可以使用更新后的新值,用new*/
END
3、设在SQLServer2008某数据库中,已建立了四个文件组:
fg1、fg2、fg3和fg4,以及一个分区函数RangePF1。
RangePF1的定义代码如下:
CREATEPARTITIONFUNCTIONRangePF1(int)
ASRANGELEFTFORVALUES(100,200,300)
(1)请基于RangePF1创建一个分区方案RangePS1,每个分区对应一个文件组。
(5分)
createpartitionschemeRangePS1
aspartitionRangePF1
to(fg1,fg1,fg1,fg2)
(2)请使用RangePS1创建一个分区表PartitionT(Gid,GoodName,Price),该表基于Gid列创建分区。
其中Gid:
int类型,取值大于等于1;GoodName:
统一字符编码可变长类型,最多可存储20个汉字;Price:
定点小数类型,小数部分1位,整数部分到千位。
(5分)
创建分区表:
createtableorders
(
GIDintidentity(1,1)primarykey,
GoodNamevarchar(40),
Pricefloat
)
onRangePS1(GID)
4、设在SQLServer2008某数据库中有商品表和销售表,两个表的定义如下:
CREATETABLE商品表(
商品号char(10)PRIMARYKEY,
商品名varchar(40),
类别varchar(20),
进货单价int)
CREATETABLE销售表(
商品号char(10),
销售时间datetime,
销售数量int,
销售单价int,
PRIMARYKEY(商品号,销售时间))
下面是一个用户定义的多语句表值函数,它接受类别作为输入参数,返回该类别下的每种商品在2012年的销售总利润,并将结果按照销售总利润的降序输出。
请补全该函数定义代码。
(10分)
CREATEFUNCTIONf_Profit(@lbchar(10))【1】@ProfitTable【2】(
商品号char(10),
总利润int)
AS
BEGIN
INSERTINTO@ProfitTable
【3】
【4】
END
第一空:
RETURNS
第二空:
table
第三空:
aSELECTa.商品号,SUM(销售数量*(销售单价-进货单价))AS总利润FROM销售表aJOIN商品表bONa.商品号=b.商品号WHEREa.商品号IN(SELECT商品号FROM商品表WHERE类别=@lb)GROUPBYa.商品号ORDERBY总利润DESC
第四空:
RETURN@RrofitTable
5、设有图书管理数据库,包含三张表:
图书明细表(图书编号,图书类别,图书名称,作者,出版社,出版日期,定价);
读者表(借书证号,姓名,系别,办证日期);
借出信息表(借出编号,借书证号,图书编号,借书日期);
完成下列操作:
(10分)
定义一个多语句表值函数,用于查询学生借书情况,只需提供参数:
借书证号,就可以通过调用函数返回此学生的借书情况,若有借书籍,则返回所借书籍的编号、书籍名称、定价和借书日期;若没有在借书籍,则返回记录为空。
设函数名为:
f_BorrowBook(@jszhchar(20))。
CREATEFUNCTIONBorrowBook(@jszhchar(20))
RETURNS@jsqkbTABLE(书籍编号char(20),书籍名称char(50),定价float,借书日期datetime)
AS
BEGIN
INSERT@jsqkb
SELECT图书明细表.图书编号,图书名称,定价,借出信息表.借书日期FROM,借出信息表,图书明细表
WHERE借出信息表.图书编号=图书明细表.图书编号AND借出信息表.借书证号=@jszh
RETURN
END
6、某书店采用了SQLServer2008数据库管理系统,该书店有一个需求,需要统计指定年份中每一本书的销售总额,例如:
查询2012年所有书的销售总额。
已知图书结构如下:
图书表(书号BOOK_ID,书名BOOK_NAME,单价BOOK_PRICE)
销售表(书号BOOK_ID,销售时间SALE_TIME,销售数量SALE_NUM)。
假设单价和销售数量均为int型,书号和书名均为varchar(50)类型,销售时间为datetime型。
请给出满足如下要求的多语句表值函数,该函数统计指定年份中每本书的销售总额。
(10分)
设函数名为:
BOOK_PROFIT(@yearint),函数的返回结果格式如下:
书号销售总额
B00160000
A00450000
……
CREATEFUNCTIONBOOK_PROFIT(@yearint)
RETURNS@f_BOOK_PROFITtable(
书号varchar(50),
销售总额int)
AS
BEGIN
INSERTINTO@f_BOOK_PROFIT
SELECTa.书号,SUM(a.单价*b.销售数量)
FROM图书表aJOIN销售表bONa.书号=b.书号
WHEREyear(b.销售时间)=@year
GROUPBYa.书号
RETURN
END
7、设在采用SQLServer2008数据库的图书馆应用系统中有三个基本表,表结构如下所示,请用SQL语句完成下列两个查询:
(1)SELECTLOANS.借书证号,姓名,系名,COUNT(*)AS借书数量
FROMBORROWER,LOANS
WHEREBORROWER.借书证号=LOANS.借书证号
GROUPBYLOANS.借书证号
HAVINGCOUNT(*)>=5;
(2)SELECT姓名,系名,书名,借书日期
FROMBORROW
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 计算机 数据库 三级 设计 应用题