上半年数据库系统工程师考试真题及答案下午卷.docx

1、上半年数据库系统工程师考试真题及答案下午卷2009上半年数据库系统工程师考试真题及答案-下午卷试题一【说明】假设某大型商业企业由商品配送中心和连锁超市组成，其中商品配送中心包括采购、财务、配送等部门。为实现高效管理，设计了商品配送中心信息管理系统，其主要功能描述如下：1.系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件。2.在接到供货请求后，从商品库存记录文件中进行商品库存信息査询。如果库存满足供货请求，则给配送处理发送配送通知；否则，向采购部门发出缺货通知。3.配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信

2、息至商品配送记录文件。4.采购部门接到缺货通知后，与供货商洽谈，进行商品采购处理，合格商品入库，并记录采购清单至采购清单记录文件、向配送处理发出配送通知，同时通知财务部门给供货商支付货款。 该系统采用结构化方法进行开发，得到待修改的数据流图（如图1-1所示)。【问题1】使用【说明】中的词语，给出图1-1中外部实体E1至E4的名称和数据存储D1至D4的名称。 E1：财务部门 E2：采购部门 E3：连锁超市 E4：配送部门 D1：采购清单记录文件 D2：商品库存记录文件 D3：商品配送记录文件 D4：供货请求记录文件解析：本题考查DFD的分析与设计，问题一主要考査DFD中的外部实体和数据存储，由于

3、在题干中已经提到“系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件”，因此可以明确出“连锁超市”外部实体和“供货请求记录文件”数据存储；对应到DFD图中为E3和D4。描述中的第二项提出“从商品库存记录文件中进行商品库存信息查询。如果库存满足供货请求，则给配送处发送配送通知；否则，向采购部门发出缺货通知”，因为配送通知需要发送到采购部门，因此采购部门将成为系统的外部实体；同时，商品库存记录文件能够提供库存信息，所以DFD图中E2和D2分别为采购部门和商品配送记录文件丨第三项需求“配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的

4、同时记录配送信息至商品配送记录文件”，所以配送处理需要查询供货请求记录文件，更新商品库存记录文件与商品配送记录文件，因此D3为商品配送记录文件：采购处理需要记录采购清单同时通知财务部门，所以E1应该为财务部门，D1为采购清单记录文件，剩下的E4则为配送部门。DFD中出现的错误数据流为：E1到E2, E1与E2的数据流不属于系统的范围；D3到E4,多余的数据流；D2到采购处理，数据流方向错误；D4到供货请求处理，数据流方向错误。需要补充的数据流为：E2到采购处理，因为E2是采购部门，采购部门需要给采购处提供入库商品信息；采购处到D2需要一条数据流，因为采购处理需要更改库存信息；供货请求处理到D4

5、需要一条数据流，因为供货请求处理需要记录供货请求信息。【问题2】图1-1中存在四处错误数据流，请指出各自的起点和终点：若将上述四条错误数据流删除，为保证数据流图的正确性，应补充三条数据流，请给出所补充数据流的起点和终点。（起点和终点请采用数据流图1-1中的符号或名称）解析：本题考查DFD的分析与设计，问题一主要考査DFD中的外部实体和数据存储，由于在题干中已经提到“系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件”，因此可以明确出“连锁超市”外部实体和“供货请求记录文件”数据存储；对应到DFD图中为E3和D4。描述中的第二项提出“从商品库存记录文件中进行商品库存信息查询。如果库存

6、满足供货请求，则给配送处发送配送通知；否则，向采购部门发出缺货通知”，因为配送通知需要发送到采购部门，因此采购部门将成为系统的外部实体；同时，商品库存记录文件能够提供库存信息，所以DFD图中E2和D2分别为采购部门和商品配送记录文件丨第三项需求“配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件”，所以配送处理需要查询供货请求记录文件，更新商品库存记录文件与商品配送记录文件，因此D3为商品配送记录文件：采购处理需要记录采购清单同时通知财务部门，所以E1应该为财务部门，D1为采购清单记录文件，剩下的E4则为配

7、送部门。DFD中出现的错误数据流为：E1到E2, E1与E2的数据流不属于系统的范围；D3到E4,多余的数据流；D2到采购处理，数据流方向错误；D4到供货请求处理，数据流方向错误。需要补充的数据流为：E2到采购处理，因为E2是采购部门，采购部门需要给采购处提供入库商品信息；采购处到D2需要一条数据流，因为采购处理需要更改库存信息；供货请求处理到D4需要一条数据流，因为供货请求处理需要记录供货请求信息。试题二【说明】某网上书店后台数据库的部分关系模式如下：会员（会员编号，用户名，密码，姓名，地址，邮编，电话，消费额，积分）图书（图书编号，类型名称，图书名称，作者，出版社，出版日期，ISBN，价格

8、）订单（订单编号，用户名，销售额，订购日期，出货曰期）订单明细（订单明细编号，订单编号，图书编号，数量） 【问题1】下面是创建订单关系的SQL语句，订单编号唯一识别一个订单，用户名为订购图书的会员用户名，且不能为空。要求订购日期不能大于出货日期。请将空缺部分补充完整。 (a) PRIMARY KEY 或 NOT NULL UNIQUE 或 UNIQUE NOT NULL(b) REFERENCES 会员（用户名）或 REFERENCES 会员(c) CHECK (订购日期 =出货日期或CONSTRAINTchk_dateCHECK (订购日期=出货日期）(注：chk_date可为其他名称)解析

9、：本题考查关系模型的完整性约束。完整性约束包括三类：实体完整性、参照完整性和用户定义的完整性。实体完整性约束规定关系的主属性不能取空值，关系模型中以主码作为唯一性标示。(a)考核的是实体完整性，订单编号是订单关系的主键，用PRIMARY KEY关键字，也可用NOT NULL UNIQUE或UNIQUE NOT NULL来保证一个订单标号唯一识别一个订单；参照完整性约束规定若属性（或属性组）A是关系R上的主码，B是关系S上的外码，A与B相对应（来自相同的域)，则B取值为空或者来自于R上的某个A的值。(b)考查参照完整性，用户名属性对应于会员关系的用户名属性，定义语法为REFERENCES会员（用

10、户名），由于两个关系中的属性名一致，因此也可以用 REFERENCES会员定义；用户定义的完整性约束是针对具体的数据库应用而定义的，它反映该应用所涉及的数据必须满足用户定义的语义要求。(c)考查用户定义的完整性 约束，要求出货日期必须大于等于订购日期，因此答案为CHECK (订购日期=出货日期）或CONSTRAINTchk_date CHECK(订购日期=ALL(4)(k) COUNT(*) 1(l)(COUNT(*)-l)或 COUNT(*)也给分 (m)GROUP BY用户名解析：本题考查查询语句SELECT的语法，查询是SQL的重要内容。(1)考查基本的SQL查询语法，SELECT后是要

11、查询的属性，查询条件是包含“数据库”的图书，因此用LIKE关键字。(2)考查NOT EXISTS语法，（f)处填*。该查询为相关查询，因此（g)处的条件为图书.图书编号=订单明细.图书编号。(3)考査较复杂的嵌套査询、分组查询和ALL关键字。子查询得到所有会员的订购数，题目要求查询订购数最多的会员名和订购数，因此(j)的答案为SUM(数量)=ALL。(4)考查分组查询、聚集函数和CASE语法。平均订购间隔时间对一次购买和多次购买的会员有不同的计算方式，对一次购买的会员，平均订购时间为当前时间-订购时间；而对于多次购买的会员，平均订购时间为（最晚的订购时间-最早的订购时间）/间隔数，间隔数为订购

12、数-1。【问题3】会员订购图书后，将本次订购的销售额累加到该会员的消费额中，并按照本次订单的销售额计算积分累加到该会员的积分中（每20元增加1个积分，不足20元不计入积分)。下面用触发器实现该需求，请填充空缺部分。(n) INSERT ON 订单(o)积分=积分+ INT( NROW.销售额/20)解析：本题考查SQL的触发器机制。触发器是通过事件触发而执行的，事件指的是对某个关系的插入、删除或更新。当触发器被事件激活时，并不是立即执行，而是检测触发器的条件，若条件满足则执行触发器定义的动作。因此，创建一个触发器时，必须指定：触发器名称、在其上定义触发器的关系（表)、触发器将何时激发、触发器执

13、行时应作的动作。本题中触发器定义的事件为会员订购图书，即插入一个订单记录，因此（n)的答案为INSERT ON订单。执行的动作是累加会员的消费额，并按照规则把本次订单的销售额计算为积分，更新会员的积分，因此(o)的答案为积分=积分+INT(NROW.销售额/20)。试题三【试题说明】某集团公司拥有多个大型连锁商场，公司需要构建一个数据库系统便于管理其业务运作活动。【需求分析结果】1.商场需要记录的信息包括商场编号（商场编号不重复)、商场名称、地址和联系电话。某商场信息如下表1所示。2.每个商场包含不同的部门，部门需要记录的信息包括部门编号（不同商场的部门编号不同)、部门名称、位置分布和联系电话

14、。某商场的部门信息如表2所示。3.每个部门雇用了多名员工处理日常事务，每名员工只能属于一个部门（新进员工在培训期不隶属于任何部门）。员工需要记录的信息包括员工编号、姓名、岗位、电话号 码和工资。员工信息如下表3所示。4.每个部门的员工中有一个是经理，每个经理只能管理一个部门。系统要记录每个经理的任职时间。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：【关系模式设计】 【问题1】根据问题描述，补充四个联系，完善图3-1的实体联系图。 解析：由“每个商场包含有不同的部门”可知商场与部门间为1:m联系；由“每个部门雇用了多名员工处理日常事务”可知部门与员工间为l:

15、n联系；由“每个部门的员工中有一个经理，每个经理只能管理一个部门”可知部门与经理间为1:1联系，并且员工是经理的超类型，经理是员工的子类型。完善的实体联系图如下图所示。【问题2】根据实体联系图，将关系模式中的空(a)(c)补充完整，并分别给出部门、员工和经理关系模式的主键和外键。 (a)商场编号 (b)部门编号 (c)员工编号 .部门关系模式的主键：部门编号 外键：商场编号员工关系模式的主键：员工编号 外键：部门编号经理关系模式的主键：员工编号 外键：员工编号 解析：商场的属性信息中，商场编号由集团公司分配，不会重复，可作为商场的主键属性；部门的属性信息中，部门编号由集团公司分配，不会重复，可

16、作为部门的主键属性，商场与部门的联系需要通过将商场的主键（商场编号）加入到部门中来表达；员工的属性信息中，员工编号由集团公司分配，不会重复，可作为员工的主键属性，部门与员工的联系需要通过将部门的主键（部门编号）加入到员工中来表达：经理除了包含员工的属性信息外，还需要任职时间属性。完整的关系模式如下：商场（商场编号，商场名称，地址，联系电话）部门（部门编号，部门名称，位置分布，联系电话，商场编号）员工（员工编号，姓名，岗位，电话号码，工资，部门编号）经理（员工编号，任职时间）【问题3】为了使商场有紧急事务时能联系到轮休的员工，要求每位员工必须且只能登记一位紧急联系人的姓名和联系电话（假设不同员工

17、可以登记相同的紧急联系人）。则在图3-1中还需添加的实体是(d),该实体与图3-1中的员工关系存在(e) 联系。给出该实体的关系模式。(d)紧急联系人 (e) 1:n关系模式：紧急联系人（员工编号，姓名，联系电话）解析：员工的紧急联系人信息通过添加紧急联系人关系来实现，由“每位员工必须且只能登记一位紧急联系人的姓名和联系电话”，但可能存在多位员工登记同一位家属，可知员工与家属间为n:1联系；由“不同员工奇以登记相同的紧急联系人”可知，员工编号可作为家属的主键属性。所以需要添加的关系模式如下：紧急联系人（员工编号，姓名，联系电话）试题四【说明】M公司为某宾馆设计宾馆机票预订系统，初步的需求分析结

18、果如下：(1)客户可以在提前预订或直接入住时向宾馆提供相关信息，宾馆登记的客户信息包括：客户编号，姓名，性别，类型，身份证号，联系方式，预订日期，入住时间和离开时间等信息。其中类型字段说明客户是普通客户或VIP客户，不同的客户类型享受订票的折扣额度不同。直接入住的客户其预订日期取空值。(2)需要预订机票的客户应填写“机票预订”表，提供飞行日期、航班号、出发时间、目的地等信息。宾馆根据客户订票信息购票后，生成“客户订单”表，并根据客户类型确定相应的折扣额度。“机票预订”和“客户订单”表如下表1、表2所示。 【逻辑结构设计】根据需求阶段收集的信息，设计的关系模式如下图所示。关系模式的主要属性、含义

19、及约束如下表3所示。【问题1】对关系“客户”，请回答以下的问题：(1)若选定（客户编号，预订日期）作为主码，未预订而直接入住的客户信息能否录入客户表？如不能，请说明原因。(2)对“客户”关系增加一个流水号属性作为主码，“客户”关系属于第几范式？还存在哪些问题？(3)将增加入住标识属性后的“客户”关系分解为第三范式，分解后的关系名依次取客户1、客户2、。 (1)不能，因为预订日期是主属性，直接入住客户的预订日期应该取空值，这违反实体完整性约束，记录无法插入到客户表。(2) “客户”关系属于2NF,存在数据冗余等问题。若某一客户有多次预订及入住信息，则其姓名等信息将重复多次。(3) “客户”分解为

20、第三范式如下所示：(1)若选定（客户编号，预订日期）作为主码，未预订而直接入住的客户信息是不能记入客户表的。因为预订日期是主属性，直接入住客户的预订日期应该取空值，这违反实体完整性约束，所以对于直接入住的客户信息记录是无法插入到客户表中。(2)对“客户”关系增加一个流水号属性作为主码，“客户”关系属于第二范式，存在数据冗余等问题。如表4所示。从表4所示的例子可以看出，A10001客户有3次预订信息，则其姓名、性别、身份证号、联系方式和类型信息将重复3次。(3)将增加入住标识属性后的“客户”关系分解为第三范式如下所示：从表5和表6可以看出，将“客户”关系分解为第三范式后能消除信息冗余及插入删除的

21、不一致性。【问题2】对关系“航班”，请回答以下问题：(1)列举出“航班”关系中所有不属于任何候选码的属性（非码属性）。(2)该关系模式可达到第几范式？用不超过60个字的内容叙述理由。 (1) “航班”关系模式的候选码为（航班名，飞行日期），非码属性为：航空公司名称，出发地点，出发时间，目的地，到达时间。(2)“航班”是属于1NF的。因为非主属性航空公司名称，出发地点，目的地不完全函数依赖于候选码（航班名，飞行日期)。该关系模式存在如下函数依赖：航班名一航空公司名称，出发地点，目的地；（航班名，飞行日期）出发时间，到达时间。解析：(1)包含在任何一个候选码中的属性叫做主属性，否则叫做非主属性或称

22、为非码属性。对于“航班”关系模式的候选码为（航班名，飞行日期)，故非码属性为：航空公司名称，出发地点，出发时间，目的地，到达时间。(2)若关系模式Re INF,且每一个非主属性完全依赖于码，则关系模式RG2NF。换句话说，当1NF消除了非主属性对码的部分函数依赖，则称为2NF。但是“航班”不属于2NF。因为该关系模式存在航班名一航空公司名称，出发地点，目的地函数依赖，非主属性航空公司名称，出发地点，目的地不完全函数依赖于候选码（航班名，飞行日期)，故“航班，是属于1NF的。【问题3】对于没有预订客房或入住宾馆的客户，需要在（a）关系中修改其（b）属性的值域，以满足这类客户在宾馆预订机票的需求。

23、(a)客户 （b)类型解析：根据题意类型字段说明客户是普通客户或VIP客户，不同的客户类型享受订票的折扣额度不同，这样对于没有预订客房或入住宾馆的客户，需要在“客户”关系中修改其“类型”属性的值域，即可以通过在“类型”属性中增加“非入住”标识属性以满足这类客户在宾馆预订机票的需求。试题五【说明】某停车场有多个入口和出口，车辆进入时从入口处由系统查询可用的停车位，从出口驶出时系统将其刚使用的车位标记为空车位。假设实现停车场管理的伪指令如下表1所示：根据上述描述，在入口处的伪代码程序为： 【问题1】 若两辆车在不同的入口处同时执行上述代码，会出现什么问题？（100字以内描述） 若两辆车在不同的入口

24、处同时执行代码，可能造成两辆车争用同一停车位。即两车同时申请到一个停车位，并置为占用状态。 解析：考査对并发情况下，会产生数据的不一致性问题，要求结合理论知识与实际问题，找出问题所在。两车同时读空车位时，可能会读到同一空车位，进而占用造成冲突，与并发控制中的丢失修改错误相类似。【问题2】XLock(T)对表T进行加锁：Upgrade(T)对表T所加的共享锁升级为独占锁；解锁指令Unlock(T)对表T进行解锁。(1)请修改上述入口处的伪代码程序，使其满足2PL协议。(2)满足2PL协议的入口处的伪代码程序，在并发执行时是否会产生死锁?若是，给出一个产生死锁的调度。 (1)满足2PL协议的伪代码

25、：(2)会产生死锁，调度如下：注：调度次序不局限于本参考答案，但执行语句不能写在一行，必须是并发而不能是串行，且在Upgrade启等待。解析：考查对2PL协议的掌握和实际应用的能力，考生要清楚何时回锁，加什么类型的锁，以及锁的升级等基本概念和方法，同时对2PL协议与死锁的关系要求有明确的认知。2PL规定：读数据前加S锁，事务结束时释放：写数据前加X锁，事务结束时释放；当要修改数据时，事务应对自己所加的S锁升级为X锁。2PL解决的是事务并发时的正确调度，并不能解决死锁，死锁是由DBMS进行检测和解除的。【问题3】若停车位表的关系模式为：park(parkno, isused)，其中parkno为停车位号，isused为停车位标志，0为空，1为非空。解析：考查对2PL协议理论与SQL中的隔离级别，以及嵌入式SQL的编程实践。空缺（a）要补充的是游标定义中的査询部分：空缺（b）要补充的是关闭游标和函数返回值部分。