上半年数据库系统工程师考试真题及答案下午卷.docx
《上半年数据库系统工程师考试真题及答案下午卷.docx》由会员分享，可在线阅读，更多相关《上半年数据库系统工程师考试真题及答案下午卷.docx（15页珍藏版）》请在冰豆网上搜索。
上半年数据库系统工程师考试真题及答案下午卷
2009上半年数据库系统工程师考试真题及答案-下午卷
试题一
【说明】
假设某大型商业企业由商品配送中心和连锁超市组成，其中商品配送中心包括采购、财务、配送等部门。
为实现高效管理，设计了商品配送中心信息管理系统，其主要功能描述如下：
1.系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件。
2.在接到供货请求后，从商品库存记录文件中进行商品库存信息査询。
如果库存满足供货请求，则给配送处理发送配送通知；否则，向采购部门发出缺货通知。
3.配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件。
4.采购部门接到缺货通知后，与供货商洽谈，进行商品采购处理，合格商品入库，并记录采购清单至采购清单记录文件、向配送处理发出配送通知，同时通知财务部门给供货商支付货款。
该系统采用结构化方法进行开发，得到待修改的数据流图（如图1-1所示）。
【问题1】
使用【说明】中的词语，给出图1-1中外部实体E1至E4的名称和数据存储D1至D4的名称。
E1：
财务部门E2：
采购部门
E3：
连锁超市E4：
配送部门
D1：
采购清单记录文件D2：
商品库存记录文件
D3：
商品配送记录文件D4：
供货请求记录文件
解析：
本题考查DFD的分析与设计，问题一主要考査DFD中的外部实体和数据存储，由于在题干中已经提到“系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件”，因此可以明确出“连锁超市”外部实体和“供货请求记录文件”数据存储；对应到DFD图中为E3和D4。
描述中的第二项提出“从商品库存记录文件中进行商品库存信息查询。
如果库存满足供货请求，则给配送处发送配送通知；否则，向采购部门发出缺货通知”，因为配送通知需要发送到采购部门，因此采购部门将成为系统的外部实体；同时，商品库存记录文件能够提供库存信息，所以DFD图中E2和D2分别为采购部门和商品配送记录文件丨第三项需求“配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件”，所以配送处理需要查询供货请求记录文件，更新商品库存记录文件与商品配送记录文件，因此D3为商品配送记录文件：
采购处理需要记录采购清单同时通知财务部门，所以E1应该为财务部门，D1为采购清单记录文件，剩下的E4则为配送部门。
DFD中出现的错误数据流为：
E1到E2,E1与E2的数据流不属于系统的范围；D3到E4,多余的数据流；D2到采购处理，数据流方向错误；D4到供货请求处理，数据流方向错误。
需要补充的数据流为：
E2到采购处理，因为E2是采购部门，采购部门需要给采购处提供入库商品信息；采购处到D2需要一条数据流，因为采购处理需要更改库存信息；供货请求处理到D4需要一条数据流，因为供货请求处理需要记录供货请求信息。
【问题2】
图1-1中存在四处错误数据流，请指出各自的起点和终点：
若将上述四条错误数据流删除，为保证数据流图的正确性，应补充三条数据流，请给出所补充数据流的起点和终点。
（起点和终点请采用数据流图1-1中的符号或名称）
解析：
本题考查DFD的分析与设计，问题一主要考査DFD中的外部实体和数据存储，由于在题干中已经提到“系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件”，因此可以明确出“连锁超市”外部实体和“供货请求记录文件”数据存储；对应到DFD图中为E3和D4。
描述中的第二项提出“从商品库存记录文件中进行商品库存信息查询。
如果库存满足供货请求，则给配送处发送配送通知；否则，向采购部门发出缺货通知”，因为配送通知需要发送到采购部门，因此采购部门将成为系统的外部实体；同时，商品库存记录文件能够提供库存信息，所以DFD图中E2和D2分别为采购部门和商品配送记录文件丨第三项需求“配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件”，所以配送处理需要查询供货请求记录文件，更新商品库存记录文件与商品配送记录文件，因此D3为商品配送记录文件：
采购处理需要记录采购清单同时通知财务部门，所以E1应该为财务部门，D1为采购清单记录文件，剩下的E4则为配送部门。
DFD中出现的错误数据流为：
E1到E2,E1与E2的数据流不属于系统的范围；D3到E4,多余的数据流；D2到采购处理，数据流方向错误；D4到供货请求处理，数据流方向错误。
需要补充的数据流为：
E2到采购处理，因为E2是采购部门，采购部门需要给采购处提供入库商品信息；采购处到D2需要一条数据流，因为采购处理需要更改库存信息；供货请求处理到D4需要一条数据流，因为供货请求处理需要记录供货请求信息。
试题二
【说明】
某网上书店后台数据库的部分关系模式如下：
会员（会员编号，用户名，密码，姓名，地址，邮编，电话，消费额，积分）
图书（图书编号，类型名称，图书名称，作者，出版社，出版日期，ISBN，价格）
订单（订单编号，用户名，销售额，订购日期，出货曰期）
订单明细（订单明细编号，订单编号，图书编号，数量）
【问题1】
下面是创建订单关系的SQL语句，订单编号唯一识别一个订单，用户名为订购图书的会员用户名，且不能为空。
要求订购日期不能大于出货日期。
请将空缺部分补充完整。
（a）PRIMARYKEY或NOTNULLUNIQUE或UNIQUENOTNULL
（b）REFERENCES会员（用户名）或REFERENCES会员
（c）CHECK（订购日期<=出货日期或CONSTRAINTchk_dateCHECK（订购日期<=出货日期）
（注：
chk_date可为其他名称）
解析：
本题考查关系模型的完整性约束。
完整性约束包括三类：
实体完整性、参照完整性和用户定义的完整性。
实体完整性约束规定关系的主属性不能取空值，关系模型中以主码作为唯一性标示。
（a）考核的是实体完整性，订单编号是订单关系的主键，用PRIMARYKEY关键字，也可用NOTNULLUNIQUE或UNIQUENOTNULL来保证一个订单标号唯一识别一个订单；参照完整性约束规定若属性（或属性组）A是关系R上的主码，B是关系S上的外码，A与B相对应（来自相同的域），则B取值为空或者来自于R上的某个A的值。
（b）考查参照完整性，用户名属性对应于会员关系的用户名属性，定义语法为REFERENCES会员（用户名），由于两个关系中的属性名一致，因此也可以用REFERENCES会员定义；用户定义的完整性约束是针对具体的数据库应用而定义的，它反映该应用所涉及的数据必须满足用户定义的语义要求。
（c）考查用户定义的完整性约束，要求出货日期必须大于等于订购日期，因此答案为CHECK（订购日期<=出货日期）或CONSTRAINTchk_dateCHECK（订购日期<=出货日期），此处，chk_date可以为其他的约束名。
【问题2】
请完成下列查询的SQL语句。
（1）查询名称中包含“数据库”的图书的图书名称，作者，出版社和出版日期。
（2）查询提供销售（图书表中有）但没有销售过（没在订单明细表中出现）的图书名称和出版社。
（4）为了统计会员的购买行为信息，实施有意义的客户关怀策略，査询会员的平均订购间隔时间，考虑多次购买图书和一次购买图书的情况（其中，DATEDIFF函数表示两个日期之间的天数）。
（1）（d）图书名称，作者，出版社，出版日期
（e）LIKE％数据库％
（2）（f）*
（g）图书.图书编号=订单明细.图书编号
（3）（h）SUM（数量）
（i）订单.订单编号=订单明细.订单编号
（j）SUM（数量）>=ALL
（4）（k）COUNT（*）>1
（l）（COUNT（*）-l）或COUNT（*）也给分
（m）GROUPBY用户名
解析：
本题考查查询语句SELECT的语法，查询是SQL的重要内容。
（1）考查基本的SQL查询语法，SELECT后是要查询的属性，查询条件是包含“数据库”的图书，因此用LIKE关键字。
（2）考查NOTEXISTS语法，（f）处填*。
该查询为相关查询，因此（g）处的条件为图书.图书编号=订单明细.图书编号。
（3）考査较复杂的嵌套査询、分组查询和ALL关键字。
子查询得到所有会员的订购数，题目要求查询订购数最多的会员名和订购数，因此（j）的答案为SUM（数量）>=ALL。
（4）考查分组查询、聚集函数和CASE语法。
平均订购间隔时间对一次购买和多次购买的会员有不同的计算方式，对一次购买的会员，平均订购时间为当前时间-订购时间；而对于多次购买的会员，平均订购时间为（最晚的订购时间-最早的订购时间）/间隔数，间隔数为订购数-1。
【问题3】
会员订购图书后，将本次订购的销售额累加到该会员的消费额中，并按照本次订单的销售额计算积分累加到该会员的积分中（每20元增加1个积分，不足20元不计入积分）。
下面用触发器实现该需求，请填充空缺部分。
（n）INSERTON订单
（o）积分=积分+INT（NROW.销售额/20）
解析：
本题考查SQL的触发器机制。
触发器是通过事件触发而执行的，事件指的是对某个关系的插入、删除或更新。
当触发器被事件激活时，并不是立即执行，而是检测触发器的条件，若条件满足则执行触发器定义的动作。
因此，创建一个触发器时，必须指定：
触发器名称、在其上定义触发器的关系（表）、触发器将何时激发、触发器执行时应作的动作。
本题中触发器定义的事件为会员订购图书，即插入一个订单记录，因此（n）的答案为INSERTON订单。
执行的动作是累加会员的消费额，并按照规则把本次订单的销售额计算为积分，更新会员的积分，因此（o）的答案为积分=积分+INT（NROW.销售额/20）。
试题三
【试题说明】
某集团公司拥有多个大型连锁商场，公司需要构建一个数据库系统便于管理其业务运作活动。
【需求分析结果】
1.商场需要记录的信息包括商场编号（商场编号不重复）、商场名称、地址和联系电话。
某商场信息如下表1所示。
2.每个商场包含不同的部门，部门需要记录的信息包括部门编号（不同商场的部门编号不同）、部门名称、位置分布和联系电话。
某商场的部门信息如表2所示。
3.每个部门雇用了多名员工处理日常事务，每名员工只能属于一个部门（新进员工在培训期不隶属于任何部门）。
员工需要记录的信息包括员工编号、姓名、岗位、电话号码和工资。
员工信息如下表3所示。
4.每个部门的员工中有一个是经理，每个经理只能管理一个部门。
系统要记录每个经理的任职时间。
【概念模型设计】
根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：
【关系模式设计】
【问题1】
根据问题描述，补充四个联系，完善图3-1的实体联系图。
解析：
由“每个商场包含有不同的部门”可知商场与部门间为1:
m联系；由“每个部门雇用了多名员工处理日常事务”可知部门与员工间为l:
n联系；由“每个部门的员工中有一个经理……，每个经理只能管理一个部门”可知部门与经理间为1:
1联系，并且员工是经理的超类型，经理是员工的子类型。
完善的实体联系图如下图所示。
【问题2】
根据实体联系图，将关系模式中的空（a）〜（c）补充完整，并分别给出部门、员工和经理关系模式的主键和外键。
（a）商场编号（b）部门编号（c）员工编号.
部门关系模式的主键：
部门编号外键：
商场编号
员工关系模式的主键：
员工编号外键：
部门编号
经理关系模式的主键：
员工编号外键：
员工编号
解析：
商场的属性信息中，商场编号由集团公司分配，不会重复，可作为商场的主键属性；部门的属性信息中，部门编号由集团公司分配，不会重复，可作为部门的主键属性，商场与部门的联系需要通过将商场的主键（商场编号）加入到部门中来表达；员工的属性信息中，员工编号由集团公司分配，不会重复，可作为员工的主键属性，部门与员工的联系需要通过将部门的主键（部门编号）加入到员工中来表达：
经理除了包含员工的属性信息外，还需要任职时间属性。
完整的关系模式如下：
商场（商场编号，商场名称，地址，联系电话）
部门（部门编号，部门名称，位置分布，联系电话，商场编号）
员工（员工编号，姓名，岗位，电话号码，工资，部门编号）
经理（员工编号，任职时间）
【问题3】
为了使商场有紧急事务时能联系到轮休的员工，要求每位员工必须且只能登记一位紧急联系人的姓名和联系电话（假设不同员工可以登记相同的紧急联系人）。
则在图3-1中还需添加的实体是（d）,该实体与图3-1中的员工关系存在（e）联系。
给出该实体的关系模式。
（d）紧急联系人（e）1:
n
关系模式：
紧急联系人（员工编号，姓名，联系电话）
解析：
员工的紧急联系人信息通过添加紧急联系人关系来实现，由“每位员工必须且只能登记一位紧急联系人的姓名和联系电话”，但可能存在多位员工登记同一位家属，可知员工与家属间为n:
1联系；由“不同员工奇以登记相同的紧急联系人”可知，员工编号可作为家属的主键属性。
所以需要添加的关系模式如下：
紧急联系人（员工编号，姓名，联系电话）
试题四
【说明】
M公司为某宾馆设计宾馆机票预订系统，初步的需求分析结果如下：
（1）客户可以在提前预订或直接入住时向宾馆提供相关信息，宾馆登记的客户信息包括：
客户编号，姓名，性别，类型，身份证号，联系方式，预订日期，入住时间和离开时间等信息。
其中类型字段说明客户是普通客户或VIP客户，不同的客户类型享受订票的折扣额度不同。
直接入住的客户其预订日期取空值。
（2）需要预订机票的客户应填写“机票预订”表，提供飞行日期、航班号、出发时间、目的地等信息。
宾馆根据客户订票信息购票后，生成“客户订单”表，并根据客户类型确定相应的折扣额度。
“机票预订”和“客户订单”表如下表1、表2所示。
【逻辑结构设计】
根据需求阶段收集的信息，设计的关系模式如下图所示。
关系模式的主要属性、含义及约束如下表3所示。
【问题1】
对关系“客户”，请回答以下的问题：
（1）若选定（客户编号，预订日期）作为主码，未预订而直接入住的客户信息能否录入客户表？
如不能，请说明原因。
（2）对“客户”关系增加一个流水号属性作为主码，“客户”关系属于第几范式？
还存在哪些问题？
（3）将增加入住标识属性后的“客户”关系分解为第三范式，分解后的关系名依次取客户1、客户2、…。
（1）不能，因为预订日期是主属性，直接入住客户的预订日期应该取空值，这违反实体完整性约束，记录无法插入到客户表。
（2）“客户”关系属于2NF,存在数据冗余等问题。
若某一客户有多次预订及入住信息，则其姓名等信息将重复多次。
（3）“客户”分解为第三范式如下所示：
（1）若选定（客户编号，预订日期）作为主码，未预订而直接入住的客户信息是不能记入客户表的。
因为预订日期是主属性，直接入住客户的预订日期应该取空值，这违反实体完整性约束，所以对于直接入住的客户信息记录是无法插入到客户表中。
（2）对“客户”关系增加一个流水号属性作为主码，“客户”关系属于第二范式，存在数据冗余等问题。
如表4所示。
从表4所示的例子可以看出，A10001客户有3次预订信息，则其姓名、性别、身份证号、联系方式和类型信息将重复3次。
（3）将增加入住标识属性后的“客户”关系分解为第三范式如下所示：
从表5和表6可以看出，将“客户”关系分解为第三范式后能消除信息冗余及插入删除的不一致性。
【问题2】
对关系“航班”，请回答以下问题：
（1）列举出“航班”关系中所有不属于任何候选码的属性（非码属性）。
（2）该关系模式可达到第几范式？
用不超过60个字的内容叙述理由。
（1）“航班”关系模式的候选码为（航班名，飞行日期），非码属性为：
航空公司名称，出发地点，出发时间，目的地，到达时间。
（2）“航班”是属于1NF的。
因为非主属性航空公司名称，出发地点，目的地不完全函数依赖于候选码（航班名，飞行日期）。
该关系模式存在如下函数依赖：
航班名一航空公司名称，出发地点，目的地；（航班名，飞行日期）—出发时间，到达时间。
解析：
（1）包含在任何一个候选码中的属性叫做主属性，否则叫做非主属性或称为非码属性。
对于“航班”关系模式的候选码为（航班名，飞行日期），故非码属性为：
航空公司名称，出发地点，出发时间，目的地，到达时间。
（2）若关系模式ReINF,且每一个非主属性完全依赖于码，则关系模式RG2NF。
换句话说，当1NF消除了非主属性对码的部分函数依赖，则称为2NF。
但是“航班”不属于2NF。
因为该关系模式存在{航班名一航空公司名称，出发地点，目的地}函数依赖，非主属性航空公司名称，出发地点，目的地不完全函数依赖于候选码（航班名，飞行日期），故“航班’，是属于1NF的。
【问题3】
对于没有预订客房或入住宾馆的客户，需要在（a）关系中修改其（b）属性的值域，以满足这类客户在宾馆预订机票的需求。
（a）客户（b）类型
解析：
根据题意类型字段说明客户是普通客户或VIP客户，不同的客户类型享受订票的折扣额度不同，这样对于没有预订客房或入住宾馆的客户，需要在“客户”关系中修改其“类型”属性的值域，即可以通过在“类型”属性中增加“非入住”标识属性以满足这类客户在宾馆预订机票的需求。
试题五
【说明】
某停车场有多个入口和出口，车辆进入时从入口处由系统查询可用的停车位，从出口驶出时系统将其刚使用的车位标记为空车位。
假设实现停车场管理的伪指令如下表1所示：
根据上述描述，在入口处的伪代码程序为：
【问题1】
若两辆车在不同的入口处同时执行上述代码，会出现什么问题？
（100字以内描述）
若两辆车在不同的入口处同时执行代码，可能造成两辆车争用同一停车位。
即两车同时申请到一个停车位，并置为占用状态。
解析：
考査对并发情况下，会产生数据的不一致性问题，要求结合理论知识与实际问题，找出问题所在。
两车同时读空车位时，可能会读到同一空车位，进而占用造成冲突，与并发控制中的丢失修改错误相类似。
【问题2】
XLock（T）对表T进行加锁：
Upgrade（T）对表T所加的共享锁升级为独占锁；解锁指令Unlock（T）对表T进行解锁。
（1）请修改上述入口处的伪代码程序，使其满足2PL协议。
（2）满足2PL协议的入口处的伪代码程序，在并发执行时是否会产生死锁?
若是，给出一个产生死锁的调度。
（1）满足2PL协议的伪代码：
（2）会产生死锁，调度如下：
注：
调度次序不局限于本参考答案，但执行语句不能写在一行，必须是并发而不能是串行，且在Upgrade启等待。
解析：
考查对2PL协议的掌握和实际应用的能力，考生要清楚何时回锁，加什么类型的锁，以及锁的升级等基本概念和方法，同时对2PL协议与死锁的关系要求有明确的认知。
2PL规定：
读数据前加S锁，事务结束时释放：
写数据前加X锁，事务结束时释放；当要修改数据时，事务应对自己所加的S锁升级为X锁。
2PL解决的是事务并发时的正确调度，并不能解决死锁，死锁是由DBMS进行检测和解除的。
【问题3】
若停车位表的关系模式为：
park（parkno,isused），其中parkno为停车位号，isused为停车位标志，0为空，1为非空。
解析：
考查对2PL协议理论与SQL中的隔离级别，以及嵌入式SQL的编程实践。
空缺（a）要补充的是游标定义中的査询部分：
空缺（b）要补充的是关闭游标和函数返回值部分。
升级会员 