【计算机类职业资格】数据库系统工程师-32及答案解析.doc

1、数据库系统工程师-32 及答案解析(总分：45.00，做题时间：90 分钟)一、试题一(总题数：1，分数：15.00)【说明】 某银行的转账业务分为如下两类操作： (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)。针对上述业务及规则，完成下列问题：（分数：15.00）(1).【问题 1】 根据业务规则，转账业务要么被全部执行，要么全部不执行，应如何保障?假设参与转账的账尸余额有大于等于。的约

2、束，上述伪代码执行中可能出现什么情况，应如何处理?(100 字以内)（分数：5.00）_(2).【问题 2】 若允许对同一账号同时进行转账，要保证转账程序的并发执行，引入共享锁指令 SLock(b)和独占锁指令XLock(A)对数据 A进行加锁，解锁指令 Unlock(A)对数据 A进行解锁。 请补充上述转账业务的伪代码序列，使其满足 2PL协议。（分数：5.00）_(3).【问题 3】 若用 SQL语句编写的转账业务事务程序如下： START TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE UPDATE Accounts S

3、ET 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 为新存入的金额。 该事务程序能否保证数据的一致性?如不能，请说明原因并改正。(100 字以内)（分数：5.00）

4、_二、试题二(总题数：1，分数：15.00)【说明】 某地区举行篮球比赛，需要开发一个比赛信息管理系统来记录比赛的相关信息。【需求分析结果】 1登记参赛球队的信息。记录球队的名称、代表地区、成立时间等信息。系统记录球队的每个队员的姓名、年龄、身高、体重等信息。每个球队有一个教练负责管理球队，一个教练仅负责一个球队。系统记录教练的姓名、年龄等信息。 2安排球队的训练信息。比赛组织者为球队提供了若干个场地，供球队进行适应性训练。系统记录现有的场地信息，包括：场地名称、场地规模、位置等信息。系统可为每个球队安排不同的训练场地，如表 1所示。系统记录训练场地安排的信息。 表 1 训练安排表 球队名称

5、场地名称 训练时间 解放军 一号球场 2008-06-09 14:0018:00 解放军 一号球场 2008-06-12 09:0012:00 解放军 二号球场 2008-06-11 14:0018:00 山西 一号球场 2008-06-10 09:0012:00 3安排比赛。该赛事聘请有专职裁判，每场比赛只安排一个裁判。系统记录裁判的姓名、年龄、级别等信息。系统按照一定的规则，首先分组，然后根据球队、场地和裁判情况，安排比赛(每场比赛的对阵双方分别称为甲队和乙队)。记录参赛球队、比赛时间、比分、场地名称等信息，如表 2所示。 4所有球员、教练和裁判可能出现重名情况。 表 2 比赛安排表 A组

6、： 甲队-乙队 场地名称 比赛时间 裁判 比分 解放军-北京 一号球场 2008-06-17 15:00 李大明 天津-山西 一号球场 2008-06-17 19:00 胡学梅 B组： 甲队-乙队 场地名称 比赛时间 裁判 比分 上海-安徽 二号球场 2008-06-17 15:00 丁鸿平 山东-辽宁 二号球场 2008-06-17 19:00 郭爱琪 【概念模型设计】 根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下： 1实体联系图(图 1) （分数：15.00）(1).【问题 1】 根据问题描述，补充四个联系，完善图 1的实体联系图。（分数：5.00）_(2).【问题 2

7、】 根据你的实体联系图，完成关系模式，并给出训练记录和比赛记录关系模式的主键和外键。（分数：5.00）_(3).【问题 3】 如果考虑记录一些特别资深的热心球迷的情况，每个热心球迷可能支持多个球队。热心球迷的基本信息包括：姓名、住址和喜欢的俱乐部等。根据这一要求修改图 1的实体联系图，给出修改后的关系模式。（分数：5.00）_三、试题三(总题数：1，分数：15.00)【说明】 某论坛的部分关系模式如下： 用户(用户编号，账号，密码，积分，级别) 栏目(栏目编号，父栏目编号，名称，版主，描述) 主题(主题编号，标题，类型，点击率，内容，发布时间，栏目编号，用户编号，附件) 回复主题(回复主题编号

8、，标题，主题编号，内容，发布时间，用户编号，附件) 其中： (1)用户编号唯一标识一个用户。用户的积分根据其发布的主题信息按积分规则计算。级别的值来自集合高级用户，普通用户，初级用户，当用户开始注册时，积分为 100，级别为初级用户；当用户积分到达 1000时，级别为普通用户；当用户积分到达 5000 时，级别为高级用户。 (2)栏目编号唯一标识一个栏目。栏目分两级，包括父栏目和子栏目。每个栏目必须有且仅有一个版主，版主是一个用户。 (3)主题编号唯一标识一个主题。类型的值来自集合精华，置顶，普通。 (4)回复主题编号唯一识别一个回复主题。一个回复主题对应一个主题，而一个主题可以有多个回复主题

9、。（分数：15.00）(1).【问题 1】 请将下列 SQL语句的空缺部分补充完整。 (1)假设已经创建好用户关系，现在想增加一个属性“个性签名”，类型为 VARCHAR(60)，请给出相关的SQL语句。 _a._ ； (2)假设已经创建好用户关系，下面是创建栏目关系的 SQL语句，请将空缺部分补充完整。 CREATE TABLE 栏目( 栏目编号 VARCHAR(8) PRIMARY KEY, 父栏目编号 VARCHAR (8), 名称 VARCHAR(40), 版主 VARCHAR(8) NOT NULL, 描述 VARCHAR(100), _b._ ， _c._ ，（分数：3.75）_(

10、2).【问题 2】 请将下列 SQL语句的空缺部分补充完整。 (1)查询标题或内容包含“SQL”的主题标题，按发布时间降序排序。 SELECT DISTINCT标题 FROM主题 _(d)_ _(e)_ ; (2)查找名称为“数据库技术”的栏目及其子栏目中的精华主题的标题和点击率。 SELECT 标题，点击率 FROM 主题 WHERE 类型=“精华“ AND栏目编号 _(f)_ (SELECT 栏目编号 FROM 栏目 WHERE 名称=“数据库技术“ _(g)_ SELECT 栏目编号 FROM栏目 WHERE _(h)_ (SELECT栏目编号 FROM栏目 WHERE 名称=“数据库技

11、术“);（分数：3.75）_(3).【问题 3】 假设所有关系模式已创建，回复主题关系模式的“主题编号”是外键，参照主题关系模式的“主题编号”。现在要删除编号为“T005”的主题及其相关的回复主题，下面是对应的删除语句，这些语句组成一个事务。DELETE 主题 WHERE 主题编号=T005； DELETE 回复主题 WHERE 主题编号=T005； (1)请问这些删除语句能否完成功能?若不能，请说明为什么? (100 字以内) _(i)_ (2)假设现在希望仅通过“DELETE 主题 WHERE主题编号=T005；”这一条语句就能完成此删除功能，应如何实现? (100 字以内) _(j)_（

12、分数：3.75）_(4).【问题 4】 为了了解每个栏目用户关注的主题，对原创主题创建视图主题 view，属性包括主题编号、标题、用户账号、栏目名称、回复数、点击率和发布时间。 CREATE VIEW 主题 view(主题编号，标题，用户账号，栏目名称，回复数，点击率，发布时间)As SELECT主题主题编号，标题，账号，名称，回复数，点击率，发布时间 FROM主题，用户，栏目，( _(k)_ FROM回复主题 _(l)_ )As A WHERE主题用户编号=用户用户编号 AND主题栏目编号=栏目栏目编号 AND _(m)_（分数：3.75）_数据库系统工程师-32 答案解析(总分：45.00

13、，做题时间：90 分钟)一、试题一(总题数：1，分数：15.00)【说明】 某银行的转账业务分为如下两类操作： (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)。针对上述业务及规则，完成下列问题：（分数：15.00）(1).【问题 1】 根据业务规则，转账业务要么被全部执行，要么全部不执行，应如何保障?假设参与转账的账尸余额有大于等于。的约束，上述伪代码执行中可能出现什么情况，应如何处理?(

14、100 字以内)（分数：5.00）_正确答案：()解析：将转账业务作为一个完整的事务，即能保障所有操作要么全部执行，要么全部不执行。 当 A账户金额不足时转账金额 x时，转账过程中会因为其余额小于。而违反约束，不能被执行，此时需要回滚事务，转胀业务不能被执行。(2).【问题 2】 若允许对同一账号同时进行转账，要保证转账程序的并发执行，引入共享锁指令 SLock(b)和独占锁指令XLock(A)对数据 A进行加锁，解锁指令 Unlock(A)对数据 A进行解锁。 请补充上述转账业务的伪代码序列，使其满足 2PL协议。（分数：5.00）_正确答案：()解析：伪代码程序：XLock(A)，a=R(

15、A)，a=a-x，W(A，a)，XLock(B)，b=R(B)，b=b+x，W(B，b)，Unlock(A)，Unlock(B) 注：保证读取数据对象之前加锁，解锁指令之间没有加锁指令即可给分。(3).【问题 3】 若用 SQL语句编写的转账业务事务程序如下： START TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE UPDATE Accounts SET CurrentBalance=CurrentBalance-Amount WHERE AccountID=A； if error then ROLLBACK； COMMI

16、T； UPDATE Accounts SET CurrentBalance=CurrentBalance+Amount WHERE AccountID=B； if error then ROLLBACK； COMMIT； 其中：Accounts 为账户表，CurrentBalance 为当前余额，Amount 为新存入的金额。 该事务程序能否保证数据的一致性?如不能，请说明原因并改正。(100 字以内)（分数：5.00）_正确答案：()解析：不能实现，程序中第 7行指令 COMMIT为事务结束，此程序为两个事务，不满足转账业务要求。 修改方法：程序中第 7行指令 CONMIT删除即可。 试题五

17、分析 本题考查对事务并发控制概念及编程的掌握。 事务是一个完整的业务逻辑处理，原子性要求是为了保证现实业务的正确执行。事务的开始通常是隐性的，上一事务结束后第一条 SQL语句为事务开始，结束必须以 RollBack 或 Commit显示地标明。 在并发状态下，可能会相互干扰破坏事务的 ACID属性，加锁机制是保障事务正确执行的一种机制。 2PL协议能够保证事务并发状态下调度的正确性，即可串行化的调度。其内容是： 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁； 在释放一个封锁之后，事务不再申请和获得任何其他封锁。 用伪代码描述，即在 R()、W()操作之前分别加指令 SLoek

18、()和 XLock，不再使用数据之后再解锁，且第一个解锁指令后不能再有加锁指令即可。 在 SQL编程中，使用标准 SQL定义的隔离级别来实现事物并发执行下的一致性控制，4 个隔离级别分别为Read Uncommitted、Read committed、Repeatable Read 和 Serializable。 问题 3中的转账程序应为一个完整的现实业务，就满足原子性。而第 7行 COMMIT将程序分为了两个事务，不满足现实业务需求，去掉该行即可。二、试题二(总题数：1，分数：15.00)【说明】 某地区举行篮球比赛，需要开发一个比赛信息管理系统来记录比赛的相关信息。【需求分析结果】 1登记

19、参赛球队的信息。记录球队的名称、代表地区、成立时间等信息。系统记录球队的每个队员的姓名、年龄、身高、体重等信息。每个球队有一个教练负责管理球队，一个教练仅负责一个球队。系统记录教练的姓名、年龄等信息。 2安排球队的训练信息。比赛组织者为球队提供了若干个场地，供球队进行适应性训练。系统记录现有的场地信息，包括：场地名称、场地规模、位置等信息。系统可为每个球队安排不同的训练场地，如表 1所示。系统记录训练场地安排的信息。 表 1 训练安排表 球队名称 场地名称 训练时间 解放军 一号球场 2008-06-09 14:0018:00 解放军 一号球场 2008-06-12 09:0012:00 解放

20、军 二号球场 2008-06-11 14:0018:00 山西 一号球场 2008-06-10 09:0012:00 3安排比赛。该赛事聘请有专职裁判，每场比赛只安排一个裁判。系统记录裁判的姓名、年龄、级别等信息。系统按照一定的规则，首先分组，然后根据球队、场地和裁判情况，安排比赛(每场比赛的对阵双方分别称为甲队和乙队)。记录参赛球队、比赛时间、比分、场地名称等信息，如表 2所示。 4所有球员、教练和裁判可能出现重名情况。 表 2 比赛安排表 A组： 甲队-乙队 场地名称 比赛时间 裁判 比分 解放军-北京 一号球场 2008-06-17 15:00 李大明 天津-山西 一号球场 2008-0

21、6-17 19:00 胡学梅 B组： 甲队-乙队 场地名称 比赛时间 裁判 比分 上海-安徽 二号球场 2008-06-17 15:00 丁鸿平 山东-辽宁 二号球场 2008-06-17 19:00 郭爱琪 【概念模型设计】 根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下： 1实体联系图(图 1) （分数：15.00）(1).【问题 1】 根据问题描述，补充四个联系，完善图 1的实体联系图。（分数：5.00）_正确答案：()解析：(图中的 M、N、P 可表示为*，对联系名称可不做要求，但不能出现重名。) (2).【问题 2】 根据你的实体联系图，完成关系模式，并给出训练记录

22、和比赛记录关系模式的主键和外键。（分数：5.00）_正确答案：()解析：填空： (a)球队名称 (b)教练编号 (c)球队名称，场地名称，开始时间，结束时间 (d)甲队，乙队，比赛时间，场地名称，比分，裁判，分组 主键： 训练记录( 球队，开始时间 )或训练记录( 场地名称，开始时间 ) 或训练记录( 球队，结束时间 )或训练记录( 场地名称，结束时间 ) 比赛记录( 场地名称，比赛时间 )或比赛记录( 裁判，比赛时间 ) 或比赛记录( 甲队，比赛时间 )或比赛记录( 乙队，比赛时间 ) 外键： 训练记录的外键：球队名称，场地名称 比赛记录的外键：甲队，乙队，场地名称，裁判(3).【问题 3】

23、 如果考虑记录一些特别资深的热心球迷的情况，每个热心球迷可能支持多个球队。热心球迷的基本信息包括：姓名、住址和喜欢的俱乐部等。根据这一要求修改图 1的实体联系图，给出修改后的关系模式。（分数：5.00）_正确答案：()解析：三、试题三(总题数：1，分数：15.00)【说明】 某论坛的部分关系模式如下： 用户(用户编号，账号，密码，积分，级别) 栏目(栏目编号，父栏目编号，名称，版主，描述) 主题(主题编号，标题，类型，点击率，内容，发布时间，栏目编号，用户编号，附件) 回复主题(回复主题编号，标题，主题编号，内容，发布时间，用户编号，附件) 其中： (1)用户编号唯一标识一个用户。用户的积分根

24、据其发布的主题信息按积分规则计算。级别的值来自集合高级用户，普通用户，初级用户，当用户开始注册时，积分为 100，级别为初级用户；当用户积分到达 1000时，级别为普通用户；当用户积分到达 5000 时，级别为高级用户。 (2)栏目编号唯一标识一个栏目。栏目分两级，包括父栏目和子栏目。每个栏目必须有且仅有一个版主，版主是一个用户。 (3)主题编号唯一标识一个主题。类型的值来自集合精华，置顶，普通。 (4)回复主题编号唯一识别一个回复主题。一个回复主题对应一个主题，而一个主题可以有多个回复主题。（分数：15.00）(1).【问题 1】 请将下列 SQL语句的空缺部分补充完整。 (1)假设已经创建

25、好用户关系，现在想增加一个属性“个性签名”，类型为 VARCHAR(60)，请给出相关的SQL语句。 _a._ ； (2)假设已经创建好用户关系，下面是创建栏目关系的 SQL语句，请将空缺部分补充完整。 CREATE TABLE 栏目( 栏目编号 VARCHAR(8) PRIMARY KEY, 父栏目编号 VARCHAR (8), 名称 VARCHAR(40), 版主 VARCHAR(8) NOT NULL, 描述 VARCHAR(100), _b._ ， _c._ ，（分数：3.75）_正确答案：()解析：(1)(a)ALTER TABLE 用户 ADD个性签名 VARCHAR(60)； (

26、2)(b)FOREIGN KEY(父栏目编号)REFERENCES 栏目(栏目编号) (c)FOREIGN KEY(版主)REFERENCES 用户(用户编号)(2).【问题 2】 请将下列 SQL语句的空缺部分补充完整。 (1)查询标题或内容包含“SQL”的主题标题，按发布时间降序排序。 SELECT DISTINCT标题 FROM主题 _(d)_ _(e)_ ; (2)查找名称为“数据库技术”的栏目及其子栏目中的精华主题的标题和点击率。 SELECT 标题，点击率 FROM 主题 WHERE 类型=“精华“ AND栏目编号 _(f)_ (SELECT 栏目编号 FROM 栏目 WHERE

27、名称=“数据库技术“ _(g)_ SELECT 栏目编号 FROM栏目 WHERE _(h)_ (SELECT栏目编号 FROM栏目 WHERE 名称=“数据库技术“);（分数：3.75）_正确答案：()解析：(1)(d) WHERE 标题 LIKE %SQL% OR 内容 LIKE %SQL% (e)ORDER BY发布时间 DESC (2)(f)IN (g)UNION (h)父栏目编号 IN(3).【问题 3】 假设所有关系模式已创建，回复主题关系模式的“主题编号”是外键，参照主题关系模式的“主题编号”。现在要删除编号为“T005”的主题及其相关的回复主题，下面是对应的删除语句，这些语句组

28、成一个事务。DELETE 主题 WHERE 主题编号=T005； DELETE 回复主题 WHERE 主题编号=T005； (1)请问这些删除语句能否完成功能?若不能，请说明为什么? (100 字以内) _(i)_ (2)假设现在希望仅通过“DELETE 主题 WHERE主题编号=T005；”这一条语句就能完成此删除功能，应如何实现? (100 字以内) _(j)_（分数：3.75）_正确答案：()解析：(1)(i)不能完成此功能。删除了主题编号为“T005”的主题后，可能会存在该主题的回复主题，违反参照完整性约束。 (2)(j)在创建回复主题关系时指定级联删除，定义主题编号属性的外键约束后加

29、上 ON DELETE CASCADE。 或者是在主题关系上定义触发器，当删除主题时，删除其对应的回复主题。(4).【问题 4】 为了了解每个栏目用户关注的主题，对原创主题创建视图主题 view，属性包括主题编号、标题、用户账号、栏目名称、回复数、点击率和发布时间。 CREATE VIEW 主题 view(主题编号，标题，用户账号，栏目名称，回复数，点击率，发布时间)As SELECT主题主题编号，标题，账号，名称，回复数，点击率，发布时间 FROM主题，用户，栏目，( _(k)_ FROM回复主题 _(l)_ )As A WHERE主题用户编号=用户用户编号 AND主题栏目编号=栏目栏目编号

30、 AND _(m)_（分数：3.75）_正确答案：()解析：(k)SELECT 主题编号，COUNT(*)回复数或 SELECT主题编号，COUNT(*)AS 回复数 (1)GROUP BY主题编号 (m)主题.题编号=A.主题编号 试题二分析 本题考查 SQL语言的知识。 问题 1 本题考查的是 SQL的数据定义语言，即 SQL的 DDL，第(1)题为基本表结构的修改，增加一个新的列用“ALTER.ADD.”语句；第(2)题为参照完整性约束，因为栏目关系中的父栏目编号和版主是外键，应该定义参照完整性约束。 问题 2 本题考查的是 SQL的数据操纵语言，即 SQL的 DML中的 SELECT语

31、句。根据要求完成特定的查询要求，第(1)题考查 LIKE、ORDER BY 关键字的应用方法，第(2)题考查 m和 UNION关键字的应用方法。 数据查询是数据库的核心操作，SELECT 语句是 SQL语言中进行查询的语句。该语句提供丰富的功能和灵活的使用方式。SELECT 语句的一般格式为： SELECT ALL|DISTINCT目标列表达式,目标列表达式. FROM表名或视图名,表名或视图名. WHERE条件表达式 GROUP BY列名 1HAVING条件表达式 ORDER BY列名 2 IASC|DESC； SELECT既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。考生应

32、对 SELECT语句非常熟悉，并能灵活的应用该语句进行各种查询。 问题 3 本题考查的是参照完整性约束，第(1)题给的两个 DELETE语句，删除了主题编号为“T005”的主题后，可能会存在该主题的回复主题，违反参照完整性约束。因此 SQL语句不能完成想要完成的功能，若这两个语句先后顺序调换过来，则可以完成功能。第 (2)题要求仅用一个 DELETE语句就完成该删除要求，数据库提供了一些机制完成这个要求，一个是在定义回复主题关系时指定级联删除，定义主题编号属性的外键约束后加上 ON DELETE CASCADE；或者通过触发器机制，在主题关系上定义触发器，当删除主题时，删除其对应的回复主题，通过 CREATE TRIGGER.语句完成。 问题 4 本题考查的是数据库的视图，视图也是数据库中非常重要的概念，考生应该熟悉这个内容。另外，本题考查的还主要是 SELECT查询语句的应用方法。