1、2008年上半年软件水平考试（中级）数据库系统工程师下午（应用技术）试题真题试卷及答案与解析 一、试题一（ 15分） 1 阅读以下说明和图，回答问题 1至问题 4，将解答填入对应栏内。 【说明】 某音像制品出租商店欲开发一个音像管理信息系统，管理音像制品的租借业务。需求如下； 1系统中的客户信息文件保存了该商店的所有客户的用户名、密码等信息。对于首次来租借的客户，系统会为其生成用户名和初始密码。 2系统中音像制品信息文件记录了商店中所有音像制品的详细信息及其库存数量。 3根据客 户所租借的音像制品的品种，按天收取相应的费用。音像制品的最长租借周期为一周，每位客户每次最多只能租借 6件音像制品。
2、 4客户租借某种音像制品的具体流程为： (1)根据客户提供的用户名和密码，验证客户身份。 (2)若该客户是合法客户，查询音像制品信息文件，查看商店中是否还有这种音像制品。 (3)若还有该音像制品，且客户所要租借的音像制品数小于等于 6个，就可以将该音像制品租借给客户。这时，系统给出相应的租借确认信息，生成一条新的租借记录并将其保存在租借记录文件中。 (4)系统计算租 借费用，将费用信息保存在租借记录文件中并告知客户。 (5)客户付清租借费用之后，系统接收客户付款信息，将音像制品租借给该客户。 5当库存中某音像制品数量不能满足客户的租借请求数量时，系统可以接受客户网上预约租借某种音像制品。系统接
3、收到预约请求后，检查库存信息，验证用户身份，创建相应的预约记录，生成预约流水号给该客户，并将信息保存在预约记录文件中。 6客户归还到期的音像制品，系统修改租借记录文件，并查询预约记录文件和客户信息文件，判定是否有客户预约了这些音像制品。若有，则生成预约提示信息 ，通知系统履行预约服务，系统查询客户信息文件和预约记录文件，通知相关客户前来租借音像制品。 1 图 1-1中只有一个外部实体 E1。使用说明中的词语，给出 E1的名称。2 使用说明中的词语，给出图 1-2中的数据存储 D1 D4的名称。3 数据流图 1-2缺少了三条数据流，根据说明及数据流图 1-l提供的信息，分别指出这三条数据流的起点
4、和终点。 4 在进行系统分析与设计时，面向数据结构的设计方法 (如 Jackson方法 )也被广泛应用。简要说明面向数据结构设计方法的基本思想及其适用场合。 二、试题二（ 15分） 5 阅读下列说明，回答问题 1至问题 4，将解答填入对应栏内。 【说明】 某论坛的部分关系模式如下： 用户 (用户编号，账号，密码，积分，级别 ) 栏目 (栏目编号，父栏目编号，名称，版主，描述 ) 主题 (主题编号，标题，类型，点击率，内容，发布时间，栏目编号，用户编号，附件 ) 回复主题 (回复主题编号，标题，主题编号，内容，发布时间，用户编号，附件 ) 其中： (1)用户编号唯一标识一个用户。用户的积分根据其
5、发布的主题信息按积分规则计算。级别的值来自集合 高级用户 ， 普通用户 ， 初级用户 ，当用户开始注册时，积分为 100，级别为初级用户；当用户积分到达 1000时，级别为普通用户；当用户积分到达 5000时，级别为高级用户。 (2)栏目编号唯一标识一个栏目。栏目分两级，包括父栏目和子栏目。每个栏目必须有且仅有一个版主，版主是一个用户。 (3)主题编号唯一标识一个主题。类型的值来自集合 精华 ， 置顶 ， 普通 。 (4)回复主题编号唯一识别一个回复主题。一个回复主题对应一个主题，而一个主题可以有多个回复主题。 5 请将下列 SQL语句的空 缺部分补充完整。 (1)假设已经创建好用户关系，现在
6、想增加一个属性 “个性签名 ”，类型为 VARCHAR(60)，请给出相关的 SQL语句。 (a)； (2)假设已经创建好用户关系，下面是创建栏目关系的 SQL语句，请将空缺部分补充完整。 CREATE TABLE 栏目 ( 栏目编号 VARCHAR(8) PRIMARY KEY, 父栏目编号 VARCHAR (8), 名称 VARCHAR(40), 版主 VARCHAR(8) NOT NULL, 描述 VARCHAR(100), (b)， (c)， 6 请将下列 SQL语句的空缺部分补充完整。 (1)查询标题或内容包含 “SQL”的主题标题，按发布时间降序排序。 SELECT DISTINC
7、T标题 FROM主题 (d) (e); (2)查找名称为 “数据库技术 ”的栏目及其子栏目中的精华主题的标题和点击率。 SELECT 标题，点击率 FROM 主题 WHERE 类型 =精华 AND栏目编号 (f) (SELECT栏目编号 FROM 栏目 WHERE 名称 =数据库技术 (g) SELECT 栏目编号 FROM栏目 WHERE (h) (SELECT栏目编号 FROM栏目 WHERE 名称 =数据库技术 ); 7 假设所有关系模式已创建，回复主题关系模式的 “主题编号 ”是外键，参照主题关系模式的 “主题编号 ”。现在要删除编号为 “T005”的主题及其相关的回复主题，下面是对应
8、的删除语句，这些语句组成一个事务。 DELETE 主题 WHERE主题编号 =T005； DELETE 回复主题 WHERE主题编号 =T005； (1)请问这些删除语句能否完成功能 ?若不能，请说明为什么 ? (100字以内 ) (i) (2)假设现在希望仅通过 “DELETE主题 WHERE主题编号 =T005； ”这一条语句就能完成此删除功能，应如何实现 ? (100字以内 ) (j) 8 为了了解每个栏目用户关注的主题，对原创主题创建视图主题 view，属性包括主题编号、标题、用户账号、栏目名称、回复数、点击率和发布时间。 CREATE VIEW 主题 view(主题编号，标题，用户账
9、号，栏目名称，回复数，点击率，发布时间 )As SELECT主题主题编号，标题，账号，名称，回复数，点击率，发布时间 FROM主题，用户，栏目， (k) FROM回复主题 (l)As A WHERE主题用户编号 =用户用户编号 AND主题栏目编号 =栏目栏目编号 AND (m) 三、试题三（ 15分） 9 阅读下列说明，回答问题 1至问题 3，将解答填入对应栏内。【说明】 某地区举行篮球比赛，需要开发一个比赛信息管理系统来记录比赛的相关信息。【需求分析结果】 1登记参赛球队的信息。记录球队的名称、代表地区、成立时间等信息。系统记录球队的每个队员的姓名、年龄、身高、体重等信息。每个球队有一个教练
10、负责管理球队，一个教练仅负责一个球队。系统记录教练的姓名、年龄等信息。 2安排球队的训练信息。比赛组织者为球队提供了若干个场地，供球队进行适应性训练。系统记录现有的 场地信息，包括：场地名称、场地规模、位置等信息。系统可为每个球队安排不同的训练场地，如表 3-l所示。系统记录训练场地安排的信息。 3安排比赛。该赛事聘请有专职裁判，每场比赛只安排一个裁判。系统记录裁判的姓名、年龄、级别等信息。系统按照一定的规则，首先分组，然后根据球队、场地和裁判情况，安排比赛 (每场比赛的对阵双方分别称为甲队和乙队 )。记录参赛球队、比赛时间、比分、场地名称等信息，如表 3-2所示。 4所有球员、教练和裁判可能
11、出现重名情况。【概念模型设计】 根据需求阶段收集的信息，设计的实体联系图和关系模 式 (不完整 )如下： 1实体联系图 (图 3-1) 2关系模式 教练 (教练编号，姓名，年龄 ) 队员 (队员编号，姓名，年龄，身高，体重， (a) ) 球队 (球队名称，代表地区，成立时间， (b) ) 场地 (场地名称，场地规模位置 ) 训练记录 ( (c) ) 裁判 (裁判编号，姓名，年龄，级别 ) 比赛记录 ( (d) ) 9 根据问题描述，补充四个联系，完善图 3-1的实体联系图。 10 根据你的实体联系图，完成关系模式，并给出训练记录和比赛记录关系模式的主键和外键。 11 如果考虑记录一些特别资深的
12、热心 球迷的情况，每个热心球迷可能支持多个球队。热心球迷的基本信息包括：姓名、住址和喜欢的俱乐部等。根据这一要求修改图 3-1的实体联系图，给出修改后的关系模式。 四、试题四（ 15分） 12 阅读下列说明，回答问题 1至问题 3，将解答填入对应栏内。【说明】 某企业的生产管理部门拟开发生产计划管理系统，该系统负责管理生产计划信息，记录生产安排和采购的情况。现有的表格信息如表 4-1、表 4-2和表 4-3所示。根据上述需求设计的生产计划数据库的关系模式如图 4-1所示。 关系模式的主要属性、含义及约束如表 4-4所示。该企业的生产管理部门可根据需求制定多个生产计划。每个生产计划包含多个生产产
13、品。一个生产产品可由多个生产小组共同生产。一个产品基于固定数量的用料来生产。企业有多个供应商，每个供应商可以提供若干种材料，每种材料可以由多个供应商提供。企业根据不同生产计划，从供应商处购买材料。 属性间的函数依赖关系如下： 对于 “生产计划 ”关系模式： 生产计划编号 生产计划名称，起始时间，截止时间，预算总金额 生产计划编号，产品编号 生产数量 产品编号 产品名称 生产小组编号 生产小组名称 生产计划编号，生产小组编号，产品编号 安排 人力 生产计划编号，产品编号 生产小组编号，安排人力 对于 “产品用料 ”关系模式： 材料编号 材料名称，单位 产品编号，材料编号 材料数量 对于 “采购
14、”关系模式： 采购单号 供应商，地址，电话，总价格，日期 采购单号，材料编号 数量 供应商，材料编号 单价 供应商 地址，电话 12 对关系 “生产计划 ”，请回答以下问题： (1)关系 “生产计划 ”是否满足第四范式 ?用不超过 200个字的内容叙述理由。 (2)把 “生产计划 ”分解为第四范式，分解后的关系名依次为：生产计划 1，生产计划 2 13 对关 系 “采购 ”，请回答以下问题： (1)若 “采购 ”关系中不考虑折扣情况，则该关系是否存在派生属性 ?若存在，指出其中的派生属性。 (2)针对 “采购 ”关系，用 100字以内文字简要说明会产生什么问题。 (3)分解 “采购 ”关系，分
15、解后的关系名依次为：采购 1，采购 2 14 试分析可否根据图 4-1生产计划数据库，统计出某一个生产计划所采购的某个供应商的总金额 ?并用不超过 100个字的内容叙述理由。 五、试题五（ 15分） 15 阅读下列说明，回答问题 1至问题 3，将解答填入对应栏内。 【说明】 某银行的转账业务分为如下两类操作： (1)读取账户 A余额到变量 x，记为 x=R(A)； (2)将变量 x值写入账户 A中的余额，记为 W(A， x)。 从账户 A向账户 B转账金额 x元的伪代码操作序列为： a=R(A),=a=a-X， w(A， a), b=R(B)， b=b+x， W(B,b)。 针对上述业务及规则
16、，完成下列问题： 15 根据业务规则，转账业务要么被全部执行，要么全部不执行，应如何保障 ?假设参与转账的账尸余额有大于等于。的约束，上述伪代码执行中可能出现什么情况，应如何处理 ?(100字 以内 ) 16 若允许对同一账号同时进行转账，要保证转账程序的并发执行，引入共享锁指令 SLock(b)和独占锁指令 XLock(A)对数据 A进行加锁，解锁指令 Unlock(A)对数据 A进行解锁。 请补充上述转账业务的伪代码序列，使其满足 2PL协议。 17 若用 SQL语句编写的转账业务事务程序如下： START TRANSACTION; SET TRANSACTION ISOLATION LE
17、VEL SERIALIZABLE UPDATE Accounts SET CurrentBalance=CurrentBalance-Amount WHERE AccountID=A； if error then ROLLBACK； COMMIT； UPDATE Accounts SET CurrentBalance=CurrentBalance+Amount WHERE AccountID=B； if error then ROLLBACK； COMMIT； 其中： Accounts为账户表， CurrentBalance为当前余额， Amount为新存入的金额。 该事务程序能否保证数据的一
18、致性 ?如不能，请说明原因并改正。 (100字以内 ) 2008年上半年软件水平考试（中级）数据库系统工程师下午（应用技术）试题真题试卷答案与解析 一、试题一（ 15分） 1 【正确答案】 E1：客户 2 【正确答案】 D1：客户信息文件 D2：音像制品信息文件 D3：租借记录文件 D4：预约记录文件 3 【正确答案】 注意：三条数据流无前后顺序区分 4 【正确答案】 面向数据结构的设计方法以数据结构作为设计的基础，它根据输入 /输出数据结构导出程序的结构。 面向数据结构的设计方法用于规模不大的数据处理系统。 【试题解析】 根据题目说明，本系统的外部实体仅仅涉及到客户，因此系统的顶层数据流图中
19、 E1应该对应为客户。 题目的第二个问题在于识别系统中的数据文件 D1至 D4，根据 0层数据流图中的数据文件与处理之间的关系分析可以得知： D1为创建新客户加工的输出，并且为加工 1、 6和 7的输入，再根据题目中的描述客户信息文件与创建客户信息、预约、归 还和履行预约都相关，因此 D1便是客户信息文件。同理可分析出 D2为音箱制品信息文件、 D3为租借记录文件、 D4为预约记录文件。 图 1-2中缺少了三条数据流，我们首先检查顶层数据流图和 0层数据流是否一致。首先，从顶层数据流图中可以看出，与 E1直接相关的数据流共有 9条，而在0层数据流图中与 E1直接关联的则只有 7条，因此可以直接
20、断定，图 1-2中至少缺少直接与 E1相关的两条数据流，该两条数据流分别为：新客户创建请求和预约流水号。新客户创建请求通过创建新客户加工将客户的信息写到客户信息文件中，因此其起点和终点分别为 ： E1和 4。同理，预约流水号的起点和终点为 5和E1。在说明中，客户归还到期的音像制品，系统修改租借记录文件，并查询预约记录文件和客户信息文件，判定是否有客户预约了这些音像制品。若有，则生成预约提示信息，通知系统履行预约服务，系统查询客户信息文件和预约记录文件，通知相关客户前来租借音像制品。因此，在客户归还和履行预约服务之间存在着数据上的联系。 面向数据结构的设计方法以数据结构作为设计的基础，它根据输
21、入 /输出数据结构导出程序的结构。面向数据结构的设计方法用于规模不大的数据处理系统。 二、试题二（ 15分） 5 【正确答案】 (1)(a)ALTER TABLE用户 ADD个性签名 VARCHAR(60)； (2)(b)FOREIGN KEY(父栏目编号 )REFERENCES栏目 (栏目编号 ) (c)FOREIGN KEY(版主 )REFERENCES 用户 (用户编号 ) 【试题解析】 本题考查的是 SQL的数据定义语言，即 SQL的 DDL，第 (1)题为基本表结构的修改，增加一个新的列用 “ALTER.ADD.”语句；第 (2)题为参照完整性约束，因为栏目关系中的父栏目编号和版主是
22、外键，应该定义参照完 整性约束。 6 【正确答案】 (1)(d) WHERE标题 LIKE %SQL% OR内容 LIKE %SQL% (e)ORDER BY发布时间 DESC (2)(f)IN (g)UNION (h)父栏目编号 IN 【试题解析】 本题考查的是 SQL的数据操纵语言，即 SQL的 DML中的SELECT语句。根据要求完成特定的查询要求，第 (1)题考查 LIKE、 ORDER BY关键字的应用方法，第 (2)题考查 m和 UNION关键字的应用方法。 数据查询是数据库的核心操作， SELECT语句是 SQL语言中进行查询的语句。该语句提供丰富的功能和灵活的使用方式。 SEL
23、ECT语句的一般格式为： SELECT ALL|DISTINCT目标列表达式 ,目标列表达式 . FROM表名或视图名 ,表名或视图名 . WHERE条件表达式 GROUP BY列名 1 HAVING条件表达式 ORDER BY列名 2 IASC|DESC； SELECT既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。考 生应对 SELECT语句非常熟悉，并能灵活的应用该语句进行各种查询。 7 【正确答案】 (1)(i)不能完成此功能。删除了主题编号为 “T005”的主题后，可能会存在该主题的回复主题，违反参照完整性约束。 (2)(j)在创建回复主题关系时指定级联删除，定义主题编
24、号属性的外键约束后加上ON DELETE CASCADE。 或者是在主题关系上定义触发器，当删除主题时，删除其对应的回复主题。 【试题解析】 本题考查的是参照完整性约束，第 (1)题给的两个 DELETE语句，删除了主题编号为 “T005”的主 题后，可能会存在该主题的回复主题，违反参照完整性约束。因此 SQL语句不能完成想要完成的功能，若这两个语句先后顺序调换过来，则可以完成功能。第 (2)题要求仅用一个 DELETE语句就完成该删除要求，数据库提供了一些机制完成这个要求，一个是在定义回复主题关系时指定级联删除，定义主题编号属性的外键约束后加上 ON DELETE CASCADE；或者通过触
25、发器机制，在主题关系上定义触发器，当删除主题时，删除其对应的回复主题，通过 CREATE TRIGGER.语句完成。 8 【正确答案】 (k)SELECT主题 编号， COUNT(*)回复数或 SELECT主题编号，COUNT(*)AS回复数 (1)GROUP BY主题编号 (m)主题 .题编号 =A.主题编号 【试题解析】 本题考查的是数据库的视图，视图也是数据库中非常重要的概念，考生应该熟悉这个内容。另外，本题考查的还主要是 SELECT查询语句的应用方法。 三、试题三（ 15分） 9 【正确答案】 (图中的 M、 N、 P可表示为 *，对联系名称可不做要求，但不能出现重名。 ) 10 【
26、正确答案】 (a)球队名称 (b)教练编号 (c)球队名称，场地名称，开 始时间，结束时间 (d)甲队，乙队，比赛时间，场地名称，比分，裁判，分组 主键： 训练记录 (球队，开始时间 )或训练记录 (场地名称，开始时间 ) 或训练记录 (球队，结束时间 )或训练记录 (场地名称，结束时间 ) 比赛记录 (场地名称，比赛时间 )或比赛记录 (裁判，比赛时间 ) 或比赛记录 (甲队，比赛时间 )或比赛记录 (乙队，比赛时间 ) 外键： 训练记录的外键：球队名称，场地名称 比赛记录的外键：甲队，乙队，场地名称，裁判 11 【正确答案】 关系模式：热心球迷， (球迷编号，姓名，住址 ，俱乐部 )支持球
27、队 (球迷编号，球队 ) 【试题解析】 本题考查数据库概念结构设计及向逻辑结构转换的掌握。 此类题目要求认真阅读题目对现实问题的描述，经过分类、聚集、概括等方法，从中确定实体及其联系。题目已经给出了 4个实体，需要根据需求描述，给出实体间的联系。 由 “每个球队有一个教练负责管理球队，一个教练仅负责一个球队。 ”知球队与教练间为 1:1联系；球队与队员之间应为 1:N联系；多个球队使用多个训练场地，球队与场地之间为 M:M联系；比赛是球队、场地与裁判之间的联系，一个球队会与同组的其他多个队 之间比赛，有多个场地和裁决，一位裁判会对多场比赛判罚，一个场地会有多场比赛，涉及多个球队和裁判，因此球队
28、、场地与裁判之间的比赛关系为 M:N:P联系。 根据补充后的 E-R图，球队与球员之间的 1:N联系应通过将 1端实体 (球员 )的主码 (球队名称 )加入到 N端实体 (球员 )对应的关系中来表达。这类联系也可通过独立的一个关系来表达，如球队 球员 (球队名称，队员编号 )，这样会对查询增加多余的连接操作，因此一般不采用这种方法。 同样，球队与教练之间的 1:1联系也应通过将一方的主码增加到另一方实体对应的关系中，来表 达联系。 训练和比赛为多对多联系，只能独立成一个关系模式，取该联系相关连的务实体的码及联系自有的属性构成。如比分和分组应该是比赛的属性，再加上球队、裁判、场地的码，即构成 “
29、比赛记录 ”的关系模式。 同理，训练是球队和场地的多对多联系，训练开始时间和结束时间为训练的属性，加上球队的码和场地的码，构成 “训练记录 ”关系模式。 球迷与球队之间为多对多联系，需新增球迷实体和球迷与球队之间的支持联系。 四、试题四（ 15分） 12 【正确答案】 (1)“生产计划 ”关系模式，不满足第四范式。 (2)分 解后的关系模式如下： 生产计划 1 (生产计划编号，生产计划名称，起始时间，截止时间，预算总金额 ) 生产计划 2 (生产计划编号，产品编号，生产数量 ) 生产计划 3 (生产计划编号，产品编号，生产小组编号，安排人力 ) 生产计划 4 (产品编号，产品名称 ) 生产计划
30、 5 (生产小组编号，生产小组名称 ) 13 【正确答案】 (1)存在派生属性 “总价格 ”。总价格可以根据数量和单价计算出来。 (2)“采购 ”关系不满足第二范式，即：非主属性不完全依赖于码。 会造成：插入异常、删除异常和修 改复杂 (或修改异常 )。 (3)分解后的关系模式如下： 采购 1(采购单号，总价格，日期 ) 采购 2(供应商，地址，电话 ) 采购 3(供应商，材料编号，单价 ) 采购 4(采购单号，供应商，材料编号，数量 ) 14 【正确答案】 无法统计，需在采购关系模式中，记录采购所对应的生产计划编号。 【试题解析】 本题考查对关系模式规范化理论的掌握。 范式的判定可通过范式定
31、义来实现。应从 1NF入手，然后 2NF、 3NF等一步步判定。 1NF的判定是分析属性类型，如含有组合属性则不属于 1NF。再 根据函数依赖集，确定关系的候选码和非主属性，考查函数依赖集，不存在非主属性对码的部分依赖为 2NF，不存在非主属性对码的传递依赖为 3NF，函数依赖的左部包含码为 BCNF；只有平凡的多值依赖为 4NF。 派生属性是指可以由其他属性经过计算获得的属性，这类属性在设计关系模式中不进行存储，会因为冗余而容易产生不一致性。 不满足 BCNF的关系模式往往存在插入异常、删除异常和修改异常等问题。这也是规范化的根本原因所在。 关系模式的规范化过程是通过对关系模式的分解来实现的
32、，把低一级的关系模式分解为若干个高 一级的关系模式。分解的方法：分析关系模式中的函数依赖，通过投影分解，消除不合理的函数依赖，即对于不满足范式要求的函数依赖，提取其所有属性构成新的关系模式，从原关系模式中去掉被决定属性。依次考察所有函数依赖即可。 五、试题五（ 15分） 15 【正确答案】 将转账业务作为一个完整的事务，即能保障所有操作要么全部执行，要么全部不执行。 当 A账户金额不足时转账金额 x时，转账过程中会因为其余额小于。而违反约束，不能被执行，此时需要回滚事务，转胀业务不能被执行。 16 【正确答案】 伪代码程序： XLock(A)， a=R(A)， a=a-x， W(A， a)，X
33、Lock(B)， b=R(B)， b=b+x， W(B， b)， Unlock(A)， Unlock(B) 注：保证读取数据对象之前加锁，解锁指令之间没有加锁指令即可给分。 17 【正确答案】 不能实现，程序中第 7行指令 COMMIT为事务结束，此程序为两个事务，不满足转账业务要求。 修改方法：程序中第 7行指令 CONMIT删除即可。 【试题解析】 本题考查对事务并发控制概念及编程的掌握。 事务是一个完整的业务逻辑处理，原子性要求是为了保证现实业务 的正确执行。事务的开始通常是隐性的，上一事务结束后第一条 SQL语句为事务开始，结束必须以 RollBack或 Commit显示地标明。 在并
34、发状态下，可能会相互干扰破坏事务的 ACID属性，加锁机制是保障事务正确执行的一种机制。 2PL协议能够保证事务并发状态下调度的正确性，即可串行化的调度。其内容是： 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁； 在释放一个封锁之后，事务不再申请和获得任何其他封锁。 用伪代码描述，即在 R()、 W()操作之前分别加指令 SLoek()和 XLock，不再使用数据之后再解锁，且第一个解锁指令后不能再有加锁指令即可。 在 SQL编程中，使用标准 SQL定义的隔离级别来实现事物并发执行下的一致性控制， 4个隔离级别分别为 Read Uncommitted、 Read committed、 Repeatable Read和 Serializable。 问题 3中的转账程序应为一个完整的现实业务，就满足原子性。而第 7行COMMIT将程序分为了两个事务，不满足现实业务需求，去掉该行即可。
