数据库系统工程师下午试题.docx
《数据库系统工程师下午试题.docx》由会员分享,可在线阅读,更多相关《数据库系统工程师下午试题.docx(12页珍藏版)》请在冰豆网上搜索。
数据库系统工程师下午试题
2004年11月数据库系统工程师下午试题
试题二(20分)
阅读下列说明,回答问题1至问题5。
将解答填入答题纸的对应栏内。
【说明】
某工厂的信息管理数据库的部分关系模式如下所示:
职工(职工号,姓名,年龄,月工资,部门号,电话,办公室)
部门(部门号,部门名,负责人代码,任职时间)
关系模式的主要属性、含义及约束如表2-1所示,“职工”和“部门”的关系示例分别如表2-2和表2-3所示。
表2-1主要属性、含义及约束
表2-2“职工”关系
表2-3“部门”关系
【问题1】
根据上述说明,由SQL定义的“职工”和“部门”的关系模式,以及统计各部门的人数C、工资总数Totals、平均工资Averages的D_S视图如下所示,请在空缺处填入正确的内容。
(6分)
CreateTable部门(部门号CHAR
(1)(a),
部门名CHAR(16),
负责人代码CHAR(4),
任职时间DATE,
(b)(职工号));
CreateTable职工(职工号CHAR(4),
姓名CHAR(8),
年龄NUMDER(3),
月工资NUMDER(4),
部门号CHAR
(1),
电话CHAR(8),
办公室CHAR(8),
(a)(职工号),
(c)(部门号),
CHECK((d)));
CreateViewD_S(D,C,Totals,Averages)As
(Select部门号,(e)
from职工
(f);
【问题2】
对于表2-2、表2-3所示的“职工”和“部门”关系,请指出下列各行是否可以插入,为什么?
(3分)
(1)1001王新军28100018001234主楼201
(2)2003李力281000
(3)5802赵晓啸361500680015683号楼503
【问题3】
在问题1定义的视图D_S上,下面哪个查询或更新是允许执行的,为什么?
(3分)
(1)UpdateD_SsetD=3whereD=4;
(2)DeletefromD_SwhereC>4;
(3)SelectD,AveragesfromD_S
whereC>(SelectCfromD_SwhereD=:
dept);
(4)SelectD,CfromD_S
whereTotals>10000;
(5)Select*fromD_S;
【问题4】
查询每个部门中月工资最高的“职工号”的SQL查询语句如下:
Select职工号from职工E
Where月工资=(SelectMax(月工资)
from职工asM
whereM.部门号=E.部门号)
(1)请用30字以内文字简要说明该查询语句对查询效率的影响。
(3分)
(2)对该查询语句进行修改,使它既可以完成相同功能,又可以提高查询效率。
(3分)
【问题5】
假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引,如下的Select查询语句可能不会促使查询优化器使用索引,从而降低查询效率,请写出既可以完成相同功能又可以提高查询效率的SQL语句(2分)
Select姓名,年龄,月工资from职工
where年龄>45or月工资<1000;
试题三(20分)
阅读下列说明,回答问题1至问题5,将解答填入答题纸的对应栏内。
【说明】
某仓储超市采用POS(pointofSale)收银机负责前台的销售收款,为及时掌握销售信息,并依此指导进货,拟建立商品进、销、存数据库管理系统。
该系统的需求分析已经基本完成,下面将时入概念模型的设计。
【需求分析结果】
1、销售业务由POS收银机来辅助实现。
POS机外接条码阅读器,结帐时收银员将商品的条码通过阅读输入器输入POS机中。
所售商品数量默认值为1,可以由收银员修改。
POS机根据输入的商品信息,打印出图3-1所示的购物清单。
2、将经销的商品分为直销商品和库存商品两大类。
直销商品的保质期较短,如食品类,由供应商直接送达超市,管理员将过期的商品返还给供应商处理;库存商品由采购员向供应商提交订购单,供应商根据订购单送货。
超市会不定期对库存商品按照折扣率进行打优惠。
直销商品和库存商品的送货单样表分别如图3-2、图3-3所示,其中直销商品生产批号的前六位表示生产日期。
3、超市的硬件拓扑结构如图3-4所示。
4、业务处理过程:
由POS机存储每一笔销售记录,在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总);根据当日的销售日汇总更新存货表;每笔进货记入进货表中,并及时更新存货表。
【概念模型设计】
根据需求阶段收集的信息,设计的实体联系图和关系模式(不完整)如下:
1、实体联系图
2、关系模式
销售详单(销售流水号,商品编码,数量,金额,收银员,时间)
销售日汇总(日期,商品编码,数量)
存货表(商品编码,数量)
进货表(送货号码,商品编码,数量,日期)
商品((b))
【问题1】
对直销商品和库存商品进行概括,给出超类和子类,填入图3-5中(a)处所示的虚线框内,并被充联系。
(5分)
【问题2】
根据你的实体联系图,完成(b)处的商品关系模式,并增加子类型的实体关系模式。
(3分)
【问题3】
对所有关系模式,以下划线指出各关系模式的主键。
(4分)
【问题4】
如果将商品信息只存储在中心数据库中,与在各POS机上存储其备份相比,从前台销售效率和更新商品库两方面论述各自的优缺点(不超过300字)。
(4分)
【问题5】
如果考虑引入积分卡,根据累积消费金额计算积分点,再根据和分点在顾客购物时进行现金返还,并修改顾客的累积消费金额和积分点。
请给出新增加的积分卡关系模式,并对销售详单关系模式进行修正,指出修正后关系模式和新增关系模式的候选键和外键。
(4分)
试题四(20分)
阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
M公司为某旅游公司设计机票销售专用数据库,其关系模式如图4-1所示。
图4-1机票销售专用数据库
关系模式的主要属性、含义及约束如表4-1所示,属性间的函数依赖关系如图4-2所示,属性间函数依赖的标记方法如图4-3所示。
表4-1主要属性、含义及约束
旅客旅行前需要向旅行社提出申请,说明要参加的旅行团队。
旅行社建立的旅行申请包括,旅行出发日期和到达日期的机票预订、购票等信息。
旅行社还需要为每个团队制定“旅程”和“搭乘航班”表。
有关“旅程”和“搭乘航班”的示例如表4-2、表4-3所示。
表4-2“旅程”示例
【问题1】
对关系“航班”请回答以下问题:
(6分)
(1)列举出所有不属于任何候选键的属性(非键属性)。
(2)关系“航班”可达到第几范式,用不超过60个字的内容叙述理由。
【问题2】
对关系“旅客”,请回答以下的问题:
(6分)
(1)针对“旅客”关系,用100字以内文字简要说明会产生什么问题,并加以修正。
(2)列出修正后的关系模式的所有候选键。
(3)把“旅客”分解为第三范式,并用图4-1所示的关系模式的形式表示,分解后的关系名依次取旅客1、旅客2、…。
【问题3】
对关系“搭乘航班”,请回答以下的问题:
(8分)
(1)把非平凡的多值依赖属性(图4-2中没有表示)的例子用满足图4-3的方式表示出来。
(2)关系“搭乘航班”是boycecodd范式而不是第四范式,请用200字以内文字阐述理由。
(3)把“搭乘航班”关系分解成第四范式,并采用图1-4所示的关系模式的形式表示,分解后的关系名依次取搭乘航班1、搭乘航班2、…。
试题二
[问题1]解答
(a)PRIMARYKEY
(b)FOREIGNKEY(负贵人代码)REFERENCES职工
(c)FOREIGNKEY(部门号)REFERENCES部门
(d)月工资>=500AND月工资<=5000
(e)coumt(*),Sum(月工资),Avg(月工资)
(f)Croupby部门号
[问题2]解答
(1)该行不能插入“职工”关系,它违反了实体完整性中主码必须唯一区分关系中的每一个属性。
(2)该行可以插入“职工”关系,尽管部门号、电话和办公室为空,但是它表示该雇员没有分配到某个部门。
(3)该行不能插入“职工”关系,它违反了参照完整性。
因为6在关系“部门’中不存在。
[问题3]解答
此间考查的是对视图定义的掌握。
(1)和
(2)都不能更新,因为使用分组合聚集函数定义的视图是不可更新的。
(3)不一定,视子查询的返回值而定,(4)和(5)允许查询。
[问题4]解答
此间考察的是查询效率的问题。
在涉及相关查询的某些情形中,构造临时关系可以提高查询效率。
(1)对于外层的职工关系E中的每一个元组,都要对内层的整个职工关系M进行检索,因此查询效率不高。
(2)解答一
改正后的SQL语句使用了临时表:
SelectMax(月工资)as最高工资,部门号intoTempfrom职工
Groupby部门号
Select职工号from职工,Temp
where月工资=最高工资and职工.部门号=Temp.部门号
解答二
Select职工号from职工,(SelectMax(月工资)as最高工资,部门号
Groupby部门号)asdepMax
where月工资=最高工资and职工.部门号=depMax.部门号
[问题5]解答
此问主要考察在查询中注意where子句中使用索引的问题。
Select姓名,年龄,月工资from职工
where年龄>45;
union
Select姓名,年龄,月工资from职工
where年龄月工资<1000;
试题三
[问题1]解答
[问题2]解答
商品(商品编号,商品名称,供应商,单价)
直销商品(商品编号,生产批号,消费期限)
库存商品(商品编号,折扣率)
[问题3]解答
销售详单(销售流水号,商品编码,数量,金额,收银员,时间)
销售日汇总(日期,商品编码,数量)
存货表(商品编码,数量)
进货表(送货号码,商品编码,数量,日期)
商品(商品编码,商品名称,供应商,单价)
直销商品(商品编号,生产批号,消费期限)
库存商品(商品编号,折扣率)
[问题4]解答
1.采用商品信息集中存储在中心数据库中,则在销售前台的每笔计费中,都必须从中心数据库提取商品名称和单价,增加网络的负载,在业务繁忙时直接影响到前台的销售效率;同时,如果发生网络故障,则该POS机不能工作。
采用这种方式,对商品库的更新,如引入新的商品和修改商品价格,会及时体现在前台的销售业务中。
2.采用商品信息存储在中心数据库中,各Pos机存储商品表的备份,POS机直接从本地读取商品信息,减少了网络的负载,可以提高交易的效率;同时即使有短时间的网络故障,也不影响该POS机的正常使用,只有当存在商品信息变更时才需要与中心数据库同步。
采用这种方式,必须在每次商品信息变更时同步各POS机的数据。
[问题5]解答
1.对销售详单做如下的修改,增加积分卡号属性。
销售详单(销售流水号,商品编号,数量,金额,收银员,时间,积分卡号)
2.加积分卡关系:
积分卡(积分卡号,累积消费金额,积分点)
试题四
[问题1]解答
(1)“航班”关系模式的候选键为(航班名,飞行日期),非键属性为;航空公司名称,出发地点,出发时间,目的地,到达时间。
(2)“航班”是属于1NF的。
因为非主属性航空公司名称,出发地点,目的地不完全函数依赖于候选键(航班名,飞行日期)。
该关系模式存在如下函数依赖:
航班名→航空公司名称,出发地点,目的地;
(航班名,飞行日期)→出发时间,到达时间。
[问题2]解答
参考答案1
(1)在题中给出的“旅客”关系中,不同的团队会有相同的旅客编号,所以,旅客编号不能作为候选键,如果同一旅客不同时间参加不同的团队将导致“身份证号无法确定关系中的每一个元组,所以“身份证号”也不能作为候选键。
为此,需要增加一个“团队编号”的属性。
又由于{(身份证号,团队编号)→旅客编号;(旅客编号,团队编号)→身份证号;身份证号一(姓名,联系方法,出生日期,性别)},所以该关系模式存在部分函数依赖,导致旅客参加多少次团队,其姓名等信息将重复多少次。
(2)候选键:
(身份证号,团队编号)和(旅客编号,团队编号)
(3)“旅客”分解为第三范式如下所示:
旅客1(身份证号,姓名,联系方法,出生日期,性别)
旅客2(旅客编号,身份证号,团队编号)
参考答案2
(1)在题中给出的“旅客”关系中,不同的团队会有相同的旅客编号,所以,旅客编号不能作为候选键,如果同一旅客不同时间参加不同的团队将导致“身份证号”无法确定关系中的每一个元组,所以“身份证号”也不能作为候选键。
为此,可以通过修改“旅客编号”属性的定义加以解决,旅客编号由“团队编号+队内编号”来解决。
这时关系的候选键为“旅客编号”,该关系模式存在传递依赖,导致旅客参加多少次团队,其姓名等信息将重复多少次。
(2)候选键;旅客编号。
(3)“旅客”分解为第三范式如下所示:
旅客1(身份证号,姓名,联系方法,出生日期,性别)
旅客2(旅客编号,身份证号)
[问题3]解答
(1)旅程编号→→旅客编号,旅程编号→→{搭乘日期,航班名}
(2)在“搭乘航班”关系中,存在着非平凡的多值依赖,旅程编号→旅客编号,旅程编号→→{搭乘日期,航班名},而该关系模式的候选键为(旅程编号,旅客编号,搭乘日期,航班名),所以,根据第四范式的定义,该关系模式BCNP不是第四范式。
(3)把分解成第四范式的结果,用与图4-1所示的关系模式的形式表示出来。
搭乘航班1(旅程编号,旅客编号)
搭乘航班2(旅程编号,航班名,搭乘日期)