2012年上半年《数据库系统工程师》案例分析真题

试卷预览

1 问答题 1分

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

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

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

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

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

【问题1】（2分）

假设定义员工关系模式时，没有定义主键和外键。请用SQL语句补充定义员工关系模式的实体完整性约束和参照完整性约束。

(a) ；

(b) ；

【问题2】（13分）

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

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

SELECT部门编号，部门名称，(c) AS平均工资

FROM员工，部门

WHERE (d)

GROUP BY (e)

HAVING (f)

(g)；

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

SELECT员工编号，姓名，工资

FROM员工

WHERE (h)；

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

SELECT部门编号，部门名称

FROM部门

WHERE (i) (SELECT.FROM项目WHERE (i) );

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

SELECT员工编号，

CASE

WHEN教育水平>20 THEN‘研究生’

(k)

(l)

END

FROM员工，部门

WHERE (m) ；

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

SELECT部门编号，部门名称

FROM部门

WHERE部门名称 (n) ；

查看答案 开始考试

正确答案：

本题解析：

【问题1】（2分）

(a) ALTER TABLE员工ADD CONSTRAINT PK员工 PRIMARY KEY(员工编号) (其中PK_员工可以为任何有效的命名）

(b)ALTER TABLE员工ADD CONSTRAINT FK员工 FOREIGN KEY(部门) REFERENCES部门（部门编号）（其中FK员工可以为任何有效的命名）

【问题2】（13分）

（1）(c)AVG(工资)

(d)员工，部门=部门．部门编号AND职务＜>’经理’

(e)部门编号，部门名称

(f) AVG(工资)>3000

(g) ORDER BY3 DESC或ORDER BY平均工资DESC

(2)(h)工资> (SELECT AVG(工资)FROM员工）（2分）

(3)(i) NOTEXISTS

(j)部门编号=所属部门

(4) (k) WHEN教育水平＜=20 AND教育水平>16 THEN’本科生’

(l) ELSE’其他’

(m)员工．部门=部门.部门编号AND部门名称=’研发部’

(5)(m)NOT LIKE ’%处’

2 问答题 1分

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

【需求分析】

（1）该公司招聘的职位有：测试人员、开发人员、文员秘书和销售代表等职位。公司将职位划分为三种专业类型：技术类型、行政类型和销售类型。每个职位对应一种专业类型，如：测试人员职位属于技术类型。每个职位可以属于一个或多个部门。

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

（3）应聘人员可以注册应聘的职位成为候选人，并填报自己的简历信息。一个候选人可以应聘多个职位。系统记录候选人每次应聘的面试时间和面试成绩。初步设计的招聘信息数据库关系模式如图1-1所示。

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

【问题1】（6分）

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

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

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

【问题2】（5分）

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

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

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

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

【问题3】（4分）

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

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

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

查看答案 开始考试

正确答案：

本题解析：

【问题1】（6分）

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

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

由于“候选人“关系的候选码为：身份证号和应聘的职位编码，但又包含函数依赖：身份证号→姓名，性别，联系电话，出生日期，简历信息。

不满足第二范式的要求，即：非主属性不完全依赖于码。

【问题2】（5分）

（1）“面试官”关系不满足第三范式，即：每一个非主属性既不部分依赖于码也不传递依赖于码。会造成：插入异常、删除异常和修改复杂（或修改异常）。

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

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

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

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

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

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

【问题3】 （4分）

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

答：职位编码——属部门

职位编码——面试官工号

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

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

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

3 问答题 1分

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

（1）处理借阅。借阅者要借阅图书时，系统必须对其身份（借阅者ID）进行检查。通过与教务处维护的学生数据库、人事处维护的职工数据库中的数据进行比对，以验证借阅者ID是否合法。若合法，则检查借阅者在逾期未还图书表中是否有逾期未还图书，以及罚金表中的罚金是否超过限额。如果没有逾期未还图书并且罚金未超过限额，则允许借阅图书，更新图书表，并将借阅的图书存入借出图书表。借阅者归还所借图书时，先由图书馆管理员检查图书是否缺失或损坏，若是，则对借阅者处以相应罚金并存入罚金表；然后，检查所还图书是否逾期，若是，执行“处理逾期”操作；最后，更新图书表，删除借出图书表中的相应记录。

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

（3）处理逾期。系统在每周一统计逾期未还图书，逾期未还的图书按规则计算罚金，并记入罚金表，并给有逾期未还图书的借阅者发送提醒消息。借阅者在借阅和归还图书时，若罚金超过限额，管理员收取罚金，并更新罚金表中的罚金额度。

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

【问题1】（4分）

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

【问题2】（4分）

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

【问题3】（5分）

在DFD建模时，需要对有些复杂加工（处理）进行进一步精化，绘制下层数据流图。针对图1-2中的加工“处理借阅”，在1层数据流图中应分解为哪些加工？（使用说明中的术语）

【问题4】（2分）

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

查看答案 开始考试

正确答案：

本题解析：

【问题1】（4分，各1分）

E1：借阅者E2：图书管理员E3/E4： 学生数据库／职工数据库

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

【问题2】（4分，各1分）

D1：图书表D2：借出图书表D3：逾期未还图书表D4：罚金表

【问题3】（5分）

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

【问题4】（2分）

保持父图与子图平衡。父图中某加工的输入输出数据流必须与它的子图的输入输出数据流在数量和名字上相同。如果父图的一个输入（或输出）数据流对应于子图中几个输入（或输出）数据流，而子图中组成这些数据流的数据项全体正好是父图中的这一个数据流，那么它们仍然算是平衡的。

4 问答题 1分

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

（1）转账业务： T1（A，B，50），从账户A向账户B转50元；

（2）计息业务： T2，对当前所有账户的余额计算利息，余额为X*1.01。

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

【问题1】（3分）

假设当前账户A余额为100元，账户B余额为200元。有两个事务分别为 T1 （A，B，50），T2，一种可能的串行执行为：

T1（A， B， 50） → T2结果：A= 50.5 B=252.5 A+B=303

请给出其他的串行执行次序和结果。

【问题2】（8分）

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

（1）上述调度是否正确，为什么？（3分）

（2）引入共享锁指令Slock（　　）、独占锁指令Xlock（　　）和解锁指令Unlock（　　），使上述调度满足两段锁协议，并要求先响应 T1的请求。请给出一个可能的并发调度结果。（5分）

【问题3】（4分）

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

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

查看答案 开始考试

正确答案：

本题解析：

【问题1】（3分）

T2— T1 （A，B，50） 结果：A= 51 B=252 A+B = 303

【问题2】（8分）

（1）调度不正确

结果为：A= 50.5B=252

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

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

【问题3】（4分）

三个事务的串行：

（1）T1 (A，B，50)→T2 (A) →T2 (B)结果：A= 50.5 B=252.5

（2）T2 (A) →T1 (A，B，50)→T2(B)结果：A=51 B=252.5

不正确。计息业务设计为对单个账户的余额计算利息，无法实现对所有账户

统一计息，其间的转账会产生数据错误，会造成银行或客户的损失。

5 问答题 1分

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

【需求分析】

（1）系统登记每个病人的住院信息，包括：病案号、病人的姓名、性别、地址、身份证号、电话号码、入院时间及病床信息等，每个病床有唯一所属的病房及病区。如表1-1所示。其中病案号唯一标识病人本次住院的信息。

（2）在一个病人的一次住院期间，由一名医生对该病人的病情进行诊断，并填写一份诊断书，如表1-2所示。对于需要进行一次或多次手术的病人，系统记录手术名称、手术室、手术日期、手术时间、主刀医生及多名协助医生，每名医生在手术中的责任不同，如表1-3所示，其中手术室包含手术室号、楼层、地点和类型等信息。

（3）护士分为两类：病床护士和手术室护士。每个病床护士负责护理一个病区内的所有病人，每个病区由多名护士负责护理。手术室护士负责手术室的护理工作。每个手术室护士负责多个手术室，每个手术室由多名护士负责，每个护士在手术室中有不同的责任，并由系统记录其责任。

【概念模型设计】

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

【逻辑结构设计】

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

病床（病床号，病房，病房类型，所属病区）

护士（护士编号，姓名，类型，性别，级别）

病床护士（ （1） ）

手术室（手术室号，楼层，地点，类型）

手术室护士（（2） ）

病人（ （3） ，姓名，性别，地址，身份证号，电话号码，入院时间）

医生（医生编号，姓名，性别，职称，所属科室）

诊断书（ （4） ，诊断，诊断时间）

手术安排（病案号，手术室号，手术时间，手术名称）

手术医生安排（ （5） ，医生责任）

【问题1】（7分）

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

【问题2】（5分）

根据图1-1，将逻辑结构设计阶段生成的关系模式中的空（1）～（5）补充完整，并用下划线指出主键。

【问题3】（3分）

如果系统还需要记录医生给病人的用药情况，即记录医生给病人所开处方中药品的名称、用量、价格、药品的生产厂家等信息。请根据该要求，对图1-1进行修改，画出补充后的实体、实体间联系和联系的类型。

查看答案 开始考试

正确答案：

本题解析：

【问题1】（7分）

【问题2】 (5分)

（1）病区，护士号

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

（3）病案号，病床号

（4）病案号，医生编号

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

【问题3】（3分）