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

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

《上半年数据库系统工程师考试真题及答案下午卷.docx》由会员分享，可在线阅读，更多相关《上半年数据库系统工程师考试真题及答案下午卷.docx（21页珍藏版）》请在冰豆网上搜索。

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

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

试题一

某学校欲开发图书管理系统，以记录图书馆所藏图书及其借出和归还情况，提供给借阅者借阅图书功能，提供给图书馆管理员管理和定期更新图书表功能。

主要功能的具体描述如下：

（1）处理借阅。

借阅者要借阅图书时，系统必须对其身份（借阅者ID）进行检查。

通过与教务处维护的学生数据库、人事处维护的职工数据库中的数据进行比对，以验证借阅者ID是否合法。

若合法，则检查借阅者在逾期未还图书表中是否有逾期未还图书，以及罚金表中的罚金是否超过限额。

如果没有逾期未还图书并且罚金未超过限额，则允许借阅图书，更新图书表，并将借阅的图书存入借出图书表。

借阅者归还所借图书时，先由图书馆管理员检查图书是否缺失或损坏，若是，则对借阅者处以相应罚金并存入罚金表；然后，检查所还图书是否逾期，若是，执行“处理逾期”操作；最后，更新图书表，删除借出图书表中的相应记录。

（2）维护图书。

图书馆管理员查询图书信息；在新进图书时录入图书信息，存入图书表；在图书丢失或损坏严重时，从图书表中删除该图书记录。

（3）处理逾期。

系统在每周一统计逾期未还图书，逾期未还的图书按规则计算罚金，并记入罚金表，并给有逾期未还图书的借阅者发送提醒消息。

借阅者在借阅和归还图书时，若罚金超过限额，管理员收取罚金，并更新罚金表中的罚金额度。

现采用结构化方法对该图书管理系统进行分析与设计，获得如图1-1所示的顶层数据流图和如图1-2所示的0层数据流图。

【问题1】

使用说明中的词语，给出图1-1中的实体E1〜E4的名称。

E1：

借阅者

E2：

图书管理员

E3/E4:

学生数据库/职工数据库

注：

E3和E4不分顺序，但必须不同。

本题考查顶层DFD。

顶层DFI）—般用来确定系统边界，将待开发系统看作一个加工，图中只有唯一的一个处理和一些外部实体，以及这两者之间的输入输出数据流。

题目要求根据描述确定图中的外部实体。

分析题目中描述，并结合已经在顶层数据流图中给出的数据流进行分析。

从题目的说明中可以看出：

和系统的交互者包括图书管理员、借阅者两类人，图书管理员需要维护图书信息、得到查询所得的图书信息，借阅者提供借阅者ID、借阅与归还的图书。

还有通过与教务处维护的学生数据库、人事处维护的职工数据库中的数据进行比对以验证借阅者ID是否合法的两个数据库作为外部实体。

对应图1-1中数据流和实体的对应关系，可知E]为借阅者，E2为图书管理员，E3和E4为学生数据库和职工数据库。

【问题2】

使用说明中的词语，给出图1-2中的数据存储D1〜D4的名称。

D1:

图书表

D2:

借出图书表

D3:

逾期未还图书表

D4:

罚金表

本题考查0层DFD中数据存储的确定。

说明中描述维护图书信息主要存储或者更新图书表；借阅时需要检查逾期未还图书表是否有逾期未还图书以及罚金表中的罚金限额，归还时出现缺失和损坏需要处以罚金并存入罚金表；借阅与归还图书时需要存入借出图书表和更新借出图书表。

在处理逾期时需要将罚金记入罚金表，要检杳和更新罚金限额。

根据描述和图1-2中的数据存储的输入输出数据流提示，可知：

D1为图书表，D2为借出图书表，D3为逾期未还图书表，D4为罚金表。

【问题3】

在DFD建模时，需要对有些复杂加工（处理）进行进.一步精化，绘制下层数据流图。

针对图1-2中的加工“处理借阅”，在1层数据流图中应分解为哪些加丁？

（使用说明中的术语）

检查借阅者身份或检查借阅者ID;检查逾期未还图书；检查罚金是否超过限额；借阅图书；归还图书。

本题对0层DFD中的处理进一步精化建模，绘制下层数据流图。

从说明中对“处理借阅”的描述和图1-2可知，处理借阅需要检查借阅者身份、检查逾期未还图书、检査罚金是否超过限额、借阅图书和归还图书。

描述中：

检査所还图书是杏逾期，若是，执行“处理逾期”操作。

这里处理逾期明确说明是一个操作，而且在描述（3）中单独描述，在图1-2中已经建模为单独一个处理，所以在本问题中仍然不分解为一个处理。

【问题4】

说明【问题3】中绘制1层数据流图时要注意的问题。

保持父图与子图平衡。

父图中某加工的输入输出数据流必须与它的子图的输入输出数据流在数量和名字上相同。

如果父图的一个输入（或输出）数据流对应于子图中儿个输入（或输出）数据流，而子图中组成这些数据流的数据项全体正好是父图中的这一个数据流，那么它们仍然算是平衡的。

本题考查在绘制下层数据流图时需要注意的问题。

问题3明确给出是对复杂处理进行进一步精化，绘制下层数据流图，因此需要注意的问题是绘制下层数据流图时要保持父图与子图平衡。

父图中某加工的输入输山数据流必须与它的子图的输入输出数据流在数量和名字上相同。

如果父图的一个输入（或输出）数据流对应于子图中儿个输入（或输出）数据流，而子图屮组成这些数据流的数据项全体正好是父图中的这一个数据流，那么它们仍然算是平衡的。

试题二

某企业信息系统的部分关系模式及属性说明如下：

（1）员工关系模式：

员工（员工编号，姓名，部门，工资，职务，教育水平），其中员工编号是主键，部门是外键，参照部门关系模式的部门编号属性。

（2）部门关系模式：

部门（部门编号，部门名称，经理），其中部门编号是主键，经理是外键，参照员工关系模式的员工编号属性。

（3）项目关系模式：

项目（项目编号，项目名称，所属部门，负责人），其中项目编号是主键，所属部门和负责人是外键，分别参照部门关系模式和员工关系模式的部门编号和员工编号属性。

（4）员工项目关系模式：

员工项目（员工编号，项目编号），其中员工编号和项目编号是主键，同时员工编号和项目编号也是外键，分别参照员工关系模式的员工编号和项目关系模式的项目编号。

【问题1】

假设定义员工关系模式时，没有定义主键和外键。

请用SQL语句补充定义员工关系模式的实体完整性约束和参照完整性约束。

（a）;

（b）；

（a）ALTERTABLE员工ADDCONSTRAINTPK一员工PRIMARYKEY（员工编号）

（其中PK—员工可以为任何有效的命名）

（b）ALTERTABLE员工ADDCONSTRAINTFK—员工FOREIGNKEY（部门）REFERENCES部门（部门编号）（其中FK—员工可以为任何有效的命名）

考査SQL中的数据定义语言DDL和完整性约束。

根据题意，巳经用CREATE语句来定义员工关系模式的基本结构，因此应该用ALTER来增加员工关系模式的实体完整性约泶和参照完整性约束，对应的语法为：

员工编号为员工关系模式的实体完整性约束，其语句为：

部门为员工关系模式的参照完整性约束，参照部门关系模式的部门编号，其语句为：

【问题2】

请将下列SQL查询语句补充完整。

（1）査询平均工资（不包含职务为经理的员工）超过3000的部门的编号，部门名称及其平均工资，并按平均工资从高到低排序。

SELECT部门编号，部门名称，（c）AS平均工资

FROM员工，部门

（2）查询工资大于全体员工平均工资的员工编号，姓名和工资。

SELECT员工编号，姓名，工资

FROM员工

WHERE（h）:

（3）查询没有承担任何项目的部门编号和部门名称。

SELECT部门编号，部门名称

FROM部门

WHERE（i）（SELECT*FROM项目WHERE（j）

（4）查询研发部所有员工的员工编号和教育水平，若教育水平大于20,则输出研究生；若教育水平小于等于20,并大于16,则输出本科生；否则输出其他。

SELECT员工编号，

（5）查询部门名称不以“处”结尾的部门编号和部门名称。

SELECT部门编号，部门名称

FROM部门

WHERE部门名称（n）;

c）AVG（工资）

（d）员工.部门=部门.部门编号AND职务◊’经理'

（e）部门编号，部门名称

（f）AVG（工资）>3000

（g）ORDERBY3DESC或ORDERBY平均工资DESC

（2）（h）工资>（SELECTAVG（工资）FROM员工）

（3）（i）NOTEXISTS

（j）部门编号=所属部门

（4）（k）WHEN教育水平<=:

20AND教育水平>16THEN’本科生’

（1）ELSE’其他，

（m）员工.部门=部门.部门编号AND部门名称='研发部，

（5）（n）NOTLIKE'%处，

考查SQL中的数据操纵语言DML。

（1）本题考查一个较完整的查询语句，包括的知识点有多表查询、聚集函数、分组、分组条件和排序查询结果。

查询涉及员工和部门关系模式，用聚集函数AVG（工资）求平均工资，若有GROUPBY子句，则聚集函数作用在每个分组上，且GROUPBY后应包含除了聚集函数之外的所有结果列。

若GROUPBY子句后跟有HAVING短语，则只有满足条件的分组才会输出。

“0110£118丫列名[八8（：

|0£8<：

]”对输出结果进行升序或降序排序，若不明确指定升序或降序，则默认升序排序。

（2）本题考查子査询和聚集函数。

聚集函数AVG用于求均值，而聚集函数只能出现在SELECT和HAVING子句中，不能在其他地方出现，因此此处需要用子查询。

（3）本题考查带有EXISTS谓词的子查询，该查询不返回任何数据，只有逻辑真“true”和逻辑假“false”。

本题要查询没有承担任何项目的部门编号和部门名称，则可以在项目关系模式中查询到承担项目的部门编号，用NOTEXISTS关键字来获得要查询的信息。

（4）本题考查用关键字CASE-END来根据条件进行搜索。

WHEN后面跟的是条件，THEN是满足条件后对应该列的值，ELSE是不满足上述所有条件对应该列的值。

根据题意，若教育水平小于等于20且大于16,则输出本科生，对应的SQL表示为“WHEN教水平<=20AND教育水平>16,THEN'本科生其他情况，即教育水平小于等于16的，输出其他，对应的SQL表示为“ELSE'其他'”。

查询涉及员工和部门关系模式，查询条件为“员工.部门=部门.部门编号AND部门名称=’研发部’”。

（5）本题考查用关键字LIKE进行字符匹配。

LIKE的语法为：

[NOT]LIKE，〈匹配串>'

其中，匹配串可以是一个完整的字符串，也可以含有通配符％和_，其中％代表任意长度（包括0长度）的字符串，_代表任意单个字符。

不以“处”结尾对应的表示为“NOTLIKE，％处，”。

试题三

某医院拟开发一套住院病人信息管理系统，以方便对住院病人、医生、护士和手术等信息进行管理。

【需求分析】

（1）系统登记每个病人的住院信息，包括：

病案号、病人的姓名、性别、地址、身份证号、电话号码、入院时间及病床信息等，每个病床有唯一所属的病房及病区。

如表3-1所示。

其中病案号唯一标识病人本次住院的信息。

（2）在一个病人的一次住院期间，由一名医生对该病人的病情进行诊断，并填写一份诊断书，如表3-2所示。

对于需要进行一次或多次手术的病人，系统记录手术名称、手术室、手术日期、手术时间、主刀医生及多名协助医生，每名医生在手术中的责任不同，如表3-3所示，其中手术室包含手术室号、楼层、地点和类型等信息。

（3）护士分为两类：

病床护士和手术室护士。

每个病床护士负责护理一个病区内的所有病人，每个病区由多名护士负责护理。

手术室护士负责手术室的护理工作。

每个手术室护士负责多个手术室，每个手术室由多名护士负责，每个护士在手术室中有不同的责任，并由系统记录其责任。

【概念模型设计】

根据需求阶段收集的信息，设计的实体联系图（不完整）如图3-1所示。

【逻辑结构设计】

根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完整）：

【问题1】

补充图3-1中的联系和联系的类型。

本题考查数据库的概念结构设计，题目要求补充完整实体联系图中的联系和联系的

类型。

根据题目的需求描述可知，一名病人在一次住院期间对应一张病床，而一个病床可以有多名病人曾经住过。

所以，病床实体和病人实体之间存在“住院”联系，联系的类型为多对一，表示为*:

.1。

根据题目的需求描述可知，一名病人在一次住院期间，由一名医生做出诊断，并给出一份诊断书。

所以，病人实体和医生实体之间存在“诊断”联系，联系的类型为多对多，表示为*:

1。

根据题目的需求描述可知，一名病人在一次住院期间可以进行多次手术，一次手术安排在一个手术室，由多名医生参与。

所以，病人实体与医生实体和手术室实体三者之间存在“手术”联系，三者之间联系的类型为多对多对多，表示为*:

*:

*

根据题目的需求描述可知，一名手术室护士负责多个手术室，每个手术室由多名护士负责。

所以，护士实体和手术室实体之间存在“负责”联系，联系的类型为多对多，表示为*:

*。

【问题2】

根据图3-1，将逻辑结构设计阶段生成的关系模式中的空

（1）〜（5）补充完整，并用下划线指出主键。

（1）病区，护士号

（2）手术室号，护士号,责任

（3）病案号，病床号

（4）病案号，医生编号

（5）病案号，手术室号，手术时间，医生编号

本题考查数据库的逻辑结构设计，题目要求补充完整各关系模式，并给出各关系模式的主键。

根据实体联系图和需求描述，每个病床护士负责护理一个病区内的所有病人，每个病区由多名护士负责护理。

系统记录每个病床护士所负责护理的病区。

所以，对于“病床护士”关系模式需填写的属性为：

適M，护士号。

根据实体联系图和需求描述，每个手术室护士负责多个手术室，每个手术室由多名护士负责，每个护士在手术室中有不同的责任。

因此，对于“手术室护士”关系模式，需填写的属性为：

手术室号，护士号，责任。

根据实体联系图和需求描述，病案号唯一标识病人本次住院的信息。

病人的住院信息包括病床信息。

所以，对于“病人”关系模式需补充的属性为：

病案号，病床号。

根据实体联系图和需求描述，一名病人在一次住院期间，由一名医生做出诊断，并给出一份诊断书。

所以，对于“诊断”关系模式需补充的属性为：

病案号，医生编号。

根据实体联系图和需求描述，一名病人在一次住院期间，可能需要进行一次或多次手术，每次手术安排在一间手术室，由多名医生（包括主刀医生）参与。

所以，对于“手术医生安排”关系模式需补充的属性为：

病案号，手术室号，手术吋间，医生编号。

病床护士关系模式的主键：

病区，护士号

手术室护士关系模式的主键：

手术室号，护士号

病人关系模式的主键：

病案号

诊断书关系模式的主键：

病案号

采购订单关系模式的主键：

订单编码

手术医生安排关系模式的主病案号，手术室号，手术时间，医生编号码

【问题3】

如果系统还需要记录医生给病人的用药情况，即记录医生给病人所幵处方中药品的名称、用量、价格、药品的生产厂家等信息。

请根据该要求，对图3-1进行修改，画出补充后的实体、实体间联系和联系的类型。

本题考查数据库的概念结构设计，根据新增的需求新增实体联系图中的实体及联系和联系的类型。

根据问题描述，系统需记录医生给病人开处方的药品信息，则需新增“药品”实体，并在病人实体与医生实体和药品实体三者之间存在“处方”联系，联系的类型是多对多对多（*:

*:

*）。

试题四

某公司拟开发一套招聘信息管理系统，以便对整个公司的各个部门的招聘信息进行统一管理。

【需求分析】

（1）该公司招聘的职位有：

测试人员、开发人员、文员秘书和销售代表等职位。

公司将职位划分为三种专业类型：

技术类型、行政类型和销售类型。

每个职位对应一种专业类型，如测试人员职位属于技术类型。

每个职位可以属于一个或多个部门.

（2）面试官由公司员工担任，每个面试官可以负责一个或多个职位的面试，一个职位可由多名面试官负责面试。

（3）应聘人员可以注册应聘的职位成为候选人，并填报自己的简历信息。

一个候选人可以应聘多个职位。

系统记录候选人每次应聘的面试时间和面试成绩。

初步设计的招聘信息数据库关系模式如图4-1所示。

关系模式的主要属性、含义及约束如表4-1所示。

【问题1】

对关系“候选人”，请回答以下问题：

（1）列举出所有不属于任何候选键的属性（非键属性）。

（2）关系“候选人”可达到第几范式，用60字以内文字简要叙述理由。

（1）姓名，性别，联系电话，出生日期，简历信息，面试成绩。

（2）“候选人”关系模式不满足第二范式（或答：

属于第一范式）。

由于“候选人“关系的候选码为：

身份证号和应聘的职位编码，但又包含函数依赖:

身份证号→姓名，性别，联系电话，出生日期，简历信息。

不满足第二范式的要求，即：

非主属性不完全依赖于码。

本题考查非主属性和第二范式。

"候选人"关系的候选码为：

身份证号、应聘的职位编码。

“候选人”关系的函数依赖集F如下：

F={（身份证号，应聘的职位编码）→姓名，性别，联系电话，出生日期，简历信息，面试成绩；

身份证号→姓名，性别，联系电话，出生H期，简历信息}

显然，“候选人”关系非键属性为姓名、性别、联系电话、出生日期、简历信息和面试成绩，它们不完全函数依赖于码（身份证号，应聘的职位编码），而是部分依赖于码。

根据第二范式的定义，每一个非主属性完全函数依赖于码，所以“候选人”关系模式不满足第二范式。

【问题2】

对关系“面试官”，请回答以下问题：

（1）针对“面试官”关系，用60字以内文字简要说明会产生什么问题。

（2）把“面试官”分解为第二范式，分解后的关系名依次为：

面试官1，面试官2,……

（3）列出修正后的各关系模式的主键。

（1）“面试官”关系不满足第三范式，即每一个非主属性既不部分依赖于码也不传递依赖于码。

会造成：

插入异常、删除异常和修改复杂（或修改异常）。

（2）分解后的关系模式如下：

面试官1（工号，姓名，专业类型，工作职务，工作部门）

面试官2（工作部门，部门负责人，部门电话）

（3）修正后关系模式的主键如下：

面试官1（工号，姓名，专业类型，工作职务，工作部门）

面试官2（工作部门，部门负责人，部门电话）

本题考查第三范式的概念和应用。

“面试官”关系的候选码为：

工号。

根据题目的需求描述可知，工号作为面试官的唯一编号，工作部门唯一标识一个邹门。

“面试官”关系的函数依赖F如下：

F={工号，姓名，专业类型，工作职务，工作部门，部门负责人，部门电话

工作部门→部门负责人，部门电话}

从F中可以得出：

工号→工作部门，工作部门→部门负责人，部门电话。

可见，“面试官”关系模式存在传递依赖，故“面试官”关系模式属于第三范式。

【问题3】

对关系“招聘安排”，请回答以下问题：

（1）关系“招聘安排”是不是第四范式，用60字以内文字叙述理由。

（2）把“招聘安排”分解为第四范式，分解后的关系名依次为：

招聘安排1，招聘安排2,……

（1）“招聘安排”关系模式，不满足第四范式。

职位编码→→所属部门

职位编码→→面试官工号

（2）分解后的关系模式如下：

招聘安排1（职位编码，所属部门）

招聘安排2（职位编码，面试官工号

本题考査第四范式的概念和应用。

“招聘安排”关系的候选码为：

职位编码、所属部门、面试官工号。

根据题目的需求描述可知，每个职位可以属于一个或多个部门，一个职位可由多名面试官负责面试，可以得出“招聘安排”关系的函数依赖F如下：

F={职位编码→→所属部门，职位编码→→面试官工号}

根据第四范式的要求：

不允许有非平凡且非函数依赖的多值依赖，而“招聘安排”关系模式存在多值依赖，故不属于第四范式。

试题五

假设有两项业务对应的事务Tl、T2与存款关系有关：

•转账业务：

Tl（A,B,50）,从账户A向账户B转50元；

•计息业务：

T2,对当前所有账户的余额计算利息，余额为X*1.01。

针对上述业务流程，回答下列问题：

【问题1】

假设当前账户A余额为100元，账户B余额为200元。

有两个事务分别为Tl（A，B,50）,T2,一种可能的串行执行为：

Tl（A,B，50）—>T2结果：

A=50.5B=252.5A+B=303请给出其他的串行执行次序和结果。

T2->T1（A,B,50）结果：

A=51B=252A+B=303

本题考查对事务设计、并发控制的理解和掌握。

两个事务T1、T2的串行执行只有两种方式：

T1执行完执行T2（记为：

T1->T2）和T2执行完执行T1（记为：

T2->T1）,结合A、B的初值，即可计算出T2->T1的执行结果。

根据A、B的初值，按照给定的调度，获得执行结果为：

A=50.5，B=252,与任何一个串行执行的结果都不同，为错误的调度，事实上会造成储户的无端损失。

引入两段锁协议后可保证调度的正确。

根据锁类型和加解锁的要求，本题中所有的读取随后即要修改，对应了SQL中的UPDATE指令，可直接加X锁，具体参见参考答案。

若将计息业务T2改为对单个账户的余额计算利息，根据提示的情况，调度结果可能存在不一定性，这样的事务设计是错误的。

【问题2】

若上述两个事务的一个并发调度结果如下：

（1）上述凋度是否正确，为什么？

（3分）

（2）引入共享锁指令Slock（）、独占锁指令Xlock（）和解锁指令Unlock（），使上述调度满足两段锁协议，并要求先响应T1的请求。

请给出一个可能的并发调度结果。

（5分）

（1）调度不正确

结果为：

A=50.5B=252

原因：

与任何一个串行结果都不同。

（2）满足两段锁协议的调度：

本题考查对事务设计、并发控制的理解和掌握。

两个事务T1、T2的串行执行只有两种方式：

T1执行完执行T2（记为：

T1->T2）和T2执行完执行T1（记为：

T2->T1）,结合A、B的初值，即可计算出T2->T1的执行结果。

根据A、B的初值，按照给定的调度，获得执行结果为：

A=50.5，B=252,与任何一个串行执行的结果都不同，为错误的调度，事实上会造成储户的无端损失。

引入两段锁协议后可保证调度的正确。

根据锁类型和加解锁的要求，本题中所有的读取随后即要修改，对应了SQL中的UPDATE指令，可直接加X锁，具体参见参考答案。

若将计息业务T2改为对单个账户的余额计算利息，根据提示的情况，调度结果可能存在不一定性，这样的事务设计是错误的。

【问题3】

若将计息业务T2改为对单个账户的余额计算利息，即T2（A）余额为A*1.01，请给出串行调度T1（A，B,50）->T2（A）->T2（B）和串行调度T2（A）->T1（A,B,50）->T2（B）的执行结果。

若将计息业务设计为对单个账户的余额计算利息，这种方案是否正确，为什么？

三个事务的串行：

（1）T1（A,B,50）->T2（A）->T2（B）结果：

A=50.5B=252.5

（2）T2（A）->T1（A,B,50）->T2（B）结果：

A=51B=252.5

不正确。

计息业务设计为对单个账户的余额计算利息，无法实现对所有账户的锁定和统一计息，其间的转账会产生数据错误，会造成银行或客户的损失。

本题考查对事务设计、并发控制的理解和掌握。

两个事务T1、T2的串行执行只有两种方式：

T1执行完执行T2（记为：

T1->T2）和T2执行完执行T1（记为：

T2->T1）,结合A、B的初值，即可计算出T2->T1的执行结果。

根据A、B的初值，按照给定的调度，获得执行结果为：

A=50.5，B=252,与任何一个串行执行的结果都不同，为错误的调度，事实上会造成储户的无端损失。

引入两段锁协议后可保证调度的正确。

根据锁类型和加解锁的要求，本题中所有的读取随后即要修改，对应了SQL中的UPDATE指令，可直接加X锁，具体参见参考答案。

若将计息业