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