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

试卷预览

1 问答题 1分

某大型集团公司的数据库的部分关系模式如下：

员工表：EMP(Eno,Ename,Age,Sex,Title)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”；

公司表：COMPANY(Cno,Cname,City)，各属性分别表示公司编号、名称和所在城市；

工作表：WORKS(Eno,Cno,Salary)，各属性分别表示职工工号、工作的公司编号和工资。

有关关系模式的属性及相关说明如下：

（1）允许一个员工在多家公司工作，使用身份证号作为工号值。

（2）工资不能低于1500元。

根据以上描述，回答下列问题：

【问题1】（4分）

请将下面创建工作关系的SQL语句的空缺部分补充完整，要求指定关系的主码、外码，以及工资不能低于1500元的约束。

CREATE TABLE WORKS(

Eno CHAR(10) （a） ,

Cno CHAR(4) （b） ,

Salary int （c） ,

PRIMARY KEY （d） ,

);

【问题2】（6分）

（1）创建女员工信息的视图FemaleEMP，属性有Eno、Ename、Cno、Cname和Salary，请将下面SQL语句的空缺部分补充完整。

CREATE （e）

AS

SELECT EMP.Eno,Ename,COMPANY.Cno,Cname,Salary

FROM EMP, COMPANY, WORKS

WHERE （f） ;

（2）员工的工资由职称级别的修改自动调整，需要用触发器来实现员工工资的自动维护，函数float Salary_value(char(10) Eno)依据员工号计算员工新的工资。请将下面SQL语句的空缺部分补充完整。

CREATE （g） Salary_TRG AFTER （h） ON EMP

REFERENCING new row AS nrow

FOR EACH ROW

BEGIN

UPDATE WORKS

SET （i）

WHERE （j） ;

END

【问题3】（5分）

请将下面SQL语句的空缺部分补充完整。

（1）查询员工最多的公司编号和公司名称。

SELECT COMPANY.Cno,Cname

FROM COMPANY,WORKS

WHERE COMPANY.Cno= WORKS.Cno

GROUP BY （k）

HAVING （l） ( SELECT COUNT(*)

FROM WORKS

GROUP BY Cno

);

（2）查询所有不在“中国银行北京分行”工作的员工工号和姓名。

SELECT Eno,Ename

FROM EMP

WHERE Eno （m） (

SELECT Eno

FROM （n）

WHERE （o）

AND Cname=‘中国银行北京分行’

);

查看答案 开始考试

正确答案：

本题解析：

本题考查 SQL 语句的应用。

此类题目要求考生掌握 SQL 语句的基本语法和结构，认真阅读题目给出的关系模式，针对题目的要求具体分析并解答。本试题已经给出了 3 个关系模式，需要分析每个实体的属性特征及实体之间的联系，补充完整 SQL 语句。

【问题1】

由题目说明可知， Eno 和 Cno 两个属性组合是 WORKS 关系表的主键，所以在 PRIMARY KEY 后填的应该是 (Eno，Cno) 组合; Eno 和 Cno 分别作为外键引用到 EMP 和 COMPANY 关系表的主键，因此需要用 REFERENCES 对这两个属性进行外键约束:

由"工资不能低于 1500 元"的要求，可知需要限制账户余额属性值的范围，通过CHECK约束来实现。从上述分析可知，完整的SQL 语句如下:

CREATE TABLE WORKS (

Eno CHAR (10) REFERENCES EMP(Eno) ，

Cno CHAR(4) REFERENCES COMPANY(Cno) ，

Salary int CHECK(Salary >=1500)，

PRIMARY KEY (Eno，Cno) ，

)；

【问题 2 】

（1）创建视图需要通过 CREATE VIEW 语句来实现，由题目可知视图的属性有 (Eno， Ename，Cno，Cname，Salary); 通过公共属性列 Eno 和 Cno 对使用的三个基本表进行连接: 由于只创建女员工的视图，所以还要在 WHERE 后加入 "Sex='女'"的条件。从上分析可 见，完整的 SQL 语句如下:

CREATE VIEW FemaleEMP(Eno ，Ename ，Cno ，Cname ，Sa1ary)

AS

SELECT EMP.Eno ，Ename ，COMPANY.Cno ，Cname，Sa1ary

FROM EMP ，COMPANY ，WORKS

WHERE EMP.Eno = WORKS.Eno AND COMPANY.Cno = WORKS.Cno AND Sex=‘女’；

（2）创建触发器可通过 CREATE TRIGGER 语句实现，要求考生掌握触发器的基本 语法结构。按照问题要求，在工资关系中更新职工职称级别时触发器应自动执行，故需要创建基于四DATE 类型的触发器，其触发条件是更新职工职称级别:最后添加表连接条件。完整的触发器实现的方案如下:

CREATE TRIGGER Salary_TRG AFTER UPDATE OF Title ON EMP

REFERENCING new row AS nrow

FOR EACH ROW

BEGIN

UPDATE WORKS

SET Salary = Salary value(nrow.Eno)

WHERE WORKS. Eno= nrow. Eno;

END

【问题 3】

SQL 查询通过 SELECT 语句实现。

（1）根据问题要求，可通过子查询实现"查询员工最多的公司编号和公司名称"的查询:对COUNT 函数计算的结果应通过 HAVING 条件语句进行约束:通过 Cno 和 Cname的组合来进行分组查询。完整的 SQL 语句如下:

SELECT COMPANY.Cno , Cname FROM COMPANY ，WORKS

WHERE COMPANY.Cno = WORKS. Cno

GROUP BY COMPANY.Cno ，Cname

HAVING COUNT(*) >= ALL (SELECT COUNT(*)

FROM WORKS

GROUP BY Cno

)；

（2）根据问题要求，需要使用嵌套查询。先将 WORKS 和 COMPANY 表进行连接， 查找出所有在"中国银行北京分行"工作的员工:然后在雇员表中使用 "NOTIN" 或者 "＜>ANY" 查询不在前述结果里面的员工即可。完整的 SQL 语句如下:

SELECT Eno ，Ename FROM EMP

WHERE Eno NOT IN 或 ＜>ANY (

SELECT Eno

FROM WORKS ，COMPANY

WHERE WORKS.Cno = COMPANY. Cno

AND Cname =‘中国银行北京分行'

)；

2 问答题 1分

某大学为进一步推进无纸化考试，欲开发一考试系统。系统管理员能够创建包括专业方向、课程编号、任课教师等相关考试基础信息，教师和学生进行考试相关的工作。系统与考试有关的主要功能如下。

（1）考试设置。教师制定试题（题目和答案），制定考试说明、考试时间和提醒时间等考试信息，录入参加考试的学生信息，并分别进行存储。

（2）显示并接收解答。根据教师设定的考试信息，在考试有效时间内向学生显示考试说明和题目，根据设定的考试提醒时间进行提醒，并接收学生的解答。

（3）处理解答。根据答案对接收到的解答数据进行处理，然后将解答结果进行存储。

（4）生成成绩报告。根据解答结果生成学生个人成绩报告，供学生查看。

（5）生成成绩单。对解答结果进行核算后生成课程成绩单供教师查看。

（6）发送通知。根据成绩报告数据，创建通知数据并将通知发送给学生；根据成绩单数据，创建通知数据并将通知发送给教师。

现采用结构化方法对考试系统进行分析与设计，获得如图1-1所示的上下文数据流图和图1-2所示的0层数据流图。

图1-1 上下文数据流图

图1-2 0层数据流图

【问题1】（2分）

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

【问题2】（4分）

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

【问题3】（4分）

根据说明和图中词语，补充图1-2中缺失的数据流及其起点和终点。

【问题4】（5分）

图1-2所示的数据流图中，功能（6）发送通知包含创建通知并发送给学生或老师。请分解图1-2中加工（6），将分解出的加工和数据流填入答题纸的对应栏内。（注：数据流的起点和终点须使用加工的名称描述）

查看答案 开始考试

正确答案：

本题解析：

【问题1】

E1：教师

E2：学生

【问题2】

D1：试题

D2：学生信息

D3：考试信息

D4：解答结果

【问题3】

数据流名称：题目；起点：D1；终点：2 显示并接收解答

数据流名称：答案；起点：D1；终点：3 处理解答

【问题4】

新版章节练习，考前压卷，完整优质题库+考生笔记分享，实时更新，软件，

分解为加工：发送通知和加工：创建通知

报告数据：起点：生成成绩报告；终点：创建通知

成绩单数据：起点：生成成绩单；终点：创建通知

通知数据：起点：创建通知；终点：发生通知

3 问答题 1分

某地人才交流中心为加强当地企业与求职人员的沟通，促进当地人力资源的合理配置，拟建立人才交流信息网。

【需求描述】

1．每位求职人员需填写《求职信息登记表》（如表4-1所示），并出示相关证件，经工作人员审核后录入求职人员信息。表中毕业证书编号为国家机关统一编码，编号具有唯一性。每个求职人员只能填写一部联系电话。

2．每家招聘企业需填写《招聘信息登记表》（如表4-2所示），并出示相关证明及复印件，经工作人员核实后录入招聘企业信息。表中企业编号由系统自动生成，每个联系人只能填写一部联系电话。

3．求职人员和招聘企业的基本信息会在系统长期保存，并分配给求职人员和招聘企业用于登录的用户名和密码。求职人员登录系统后可登记自己的从业经历、个人简历及特长，发布自己的求职意向信息；招聘企业的工作人员登录系统后可维护本企业的基本信息，发布本企业的岗位需求信息。

4．求职人员可通过人才交流信息网查询企业的招聘信息并进行线下联系；招聘企业的工作人员也可通过人才交流信息网查询相关的求职人员信息并进行线下联系。

5．求职人员入职后应修改自己的就业状态（在岗/求职）；招聘企业在发布需求岗位有人员到岗后也应该及时修改需求人数。

表4-1

表4-2

【逻辑结构设计】

根据上述需求，设计出如下关系模式：

个人信息（身份证号，姓名，性别，出生日期，毕业院校，专业名称，学历，毕业证书编号，联系电话，电子邮件，个人简历及特长）

从业经历（身份证号，起止时间，企业名称，职位）

求职意向（身份证号，职位名称，最低薪水）

企业信息（企业编号，企业名称，地址，企业网址，联系人，联系电话，电子邮件，企业简介）

岗位需求（企业编号，职位，专业，学历，薪水，人数，备注）

【问题1】（6分）

对关系“个人信息”，请回答以下问题：

（1）列举出所有候选键。

（2）它是否为3NF，用60字以内文字简要叙述理由。

（3）将其分解为BC范式，分解后的关系名依次为：个人信息1，个人信息2，…，并用下划线标示分解后的各关系模式的主键。

【问题2】（6分）

对关系“企业信息”，请回答以下问题：

（1）列举出所有候选键。

（2）它是否为2NF，用60字以内文字简要叙述理由。

（3）将其分解为BC范式，分解后的关系名依次为：企业信息1，企业信息2，…，并用下划线标示分解后的各关系模式的主键。

【问题3】（3分）

若要求个人的求职信息一经发布，即由系统自动查找符合求职要求的企业信息，填入表R（身份证号，企业编号），在不修改系统应用程序的前提下，应采取什么方法来实现，用100字以内文字简要叙述解决方案。

查看答案 开始考试

正确答案：

本题解析：

本题考查数据库逻辑结构设计及应用。

此类题目要求考生认真阅读题目对现实问题的描述，对题目给出的关系模式进行分析并解决问题。

【问题 1】

根据题目描述和表 4-1 求职信息登记表所给出的内容，求职人员的身份证号、姓名、 性别、出生日期、联系电话、电子邮件、个人简历及特长等为基本属性，每个求职者在这些属性上取单一值：而每个求职者在毕业院校、专业名称、学历、毕业证书编号等属性上可以取多个值，其中毕业证书编号具有唯一性，可以唯一决定毕业院校、专业名称、学历和求职者个人信息。因此，“个人信息”关系的函数依赖集为{毕业证书编号→（毕业院 校，专业名称，学历，身份证号），身份证号→（姓名，性别，出生日期，联系电话，电 子邮件，个人简历及特长）。

由函数依赖集可知，“个人信息”关系的候选键为毕业证书编号，存在非主属性对候选键的传递依赖，如：毕业证书编号传递决定姓名（毕业证书编号→身份证号，身份证号→姓名〉。故 “个人信息”关系不属于 3NF。

根据分解规则，将函数依赖：身份证号→（姓名，性别，出生日期，联系电话，电子邮件，个人简历及特长）中的所有属性独立出来做一个关系模式，为 BCNF；从原关系模式中去掉上述函数依赖的右部属性，得到关系模式（毕业证书编号，身份证号，毕业院校，专业名称，学历），函数依赖集为{毕业证书编号→（毕业院校，专业名称，学历， 身份证号），也为 BCNF 。

【问题 2】

根据题目描述和表 4-2 招聘信息登记表所给出的内容，每个企业有多个联系人，每个联系人登记一个电话和一个电子邮件。存在函数依赖： { （企业编号，联系人）→（联系电话，电子邮件），企业编号→（企业名称，地址，企业网址，企业简介）}，故“企业信息”关系的候选键为（企业编号，联系人）。而候选键（企业编号，联系人）→企业名称为部分依赖，故“个人信息”关系不属于2NF。 根据分解规则，将函数依赖：企业编号→（企业名称，地址，企业网址，企业简介）中的所有属性独立成一个关系模式，属于BCNF，从原关系中去掉上述函数依赖的右部属性，得到关系模式（企业编号，联系人，联系电话，电子邮件），也是BCNF。

【问题 3】

求职信息录入后，由系统根据求职意向查找符合的信息需求，从数据库端应采用触发器技术，在“求职意向”"表上添加触发器程序，当有插入新的求职意见记录时，根据求职表意向中的职位名称，在“岗位需求”表中查找相同职位的记录，即得到需求该职位的企业编号，用相应的 SQL 语句实现查询结果插入到指定表中。

4 问答题 1分

某航空售票系统负责所有本地起飞航班的机票销售，并设有多个机票销售网点。以下为E-SQL编写的部分售票代码：

请根据上述描述，完成下列问题：

【问题1】（5分）

上述售票程序，在并发状态下，可能发生什么错误？产生这种错误的原因是什么？

【问题2】（6分）

若将上述代码封装成一个完整的事务，则：

（1）在并发请求下的响应效率会存在什么问题？

（2）分析产生效率问题的原因。

（3）给出解决方案。

【问题3】（4分）

下面是改写的存储过程，其中flightno为航班号；a为购票数；result为执行状态：1表示成功，0表示失败；表tickets中的剩余机票数balance具有大于等于零约束。请补充完整。

CREATE PROCEDRUE buy_ticket(char[] flightno IN, （a） , int resuit OUT)

AS

BEGIN

.........

UPDATE tickets SET balance= （b）

WHERE flight= flightno;

if (SQLcode＜>SUCCESS) { //SQLcode为SQL语句的执行状态

（c） ;

result = 0; return;

｝

COMMIT;

（d）

END

查看答案 开始考试

正确答案：

本题解析：

本题考查事务基本概念及编程应用。

此类题目要求考生认真阅读题目对实际问题的描述，分析现实业务中存在的问题，并以事务的方式提出解决方案及编程中的处理方式。

【问题 1】

根据题目描述的售票程序及部分代码，程序的逻辑是正确的，但在并发状态下，可能会产生错误。修改指令 UPDATE 会被分解为读取剩余票数到变量、修改变量、写入数据库几个步骤，并发时存在指令交叉，造成一个程序的修改被其他程序所覆盖，称为丢失修改错误。如下面所示的一个并发调度：

若上面所示的两个售票程序并发执行，两个程序先读取剩余票数据 A（ 当前值为 16）T1购票1张后，写入剩余票数为 15，T2 购票 2 张后，写入剩余票数为 14。T1写入的 15被T2写入的 14 所覆盖，T1所做的修改丢失。

上述两个程序单独执行或串行执行都不会出现这种错误，错误产生的原因在于事务并发执行时受到另一事务的干扰，破坏了事务的隔离性。

【问题 2】

封装的事务由两条 SQL 语句构成，中间存在与用户的交互，等待用户输入购票张数， 会造成长事务，加锁状态下，其他购票事务程序会长时间等待，严重影响系统的响应速度。

应将查询票数从事务中分离出来，UPDATE 指令独立构成一个事务 。

【问题3】

这是一个用存储过程实现购票的事务程序，存储过程参数部分给出了航班号和执行状态返回参数，应加入购票张数作为参数：修改剩余票数的 UPDATE语句处需补充的是当前票数减去购票张数;； SQL 语句执行错误时应该进行事务回滚并退出程序，所有事务代码执行完成后提交，并返回。

5 问答题 1分

某省针对每年举行的足球联赛，拟开发一套信息管理系统，以方便管理球队、球员、主教练、主裁判、比赛等信息。

【需求分析】

（1）系统需要维护球队、球员、主教练、主裁判、比赛等信息。

球队信息主要包括：球队编号、名称、成立时间、人数、主场地址、球队主教练。

球员信息主要包括：姓名、身份证号、出生日期、身高、家庭住址。

主教练信息主要包括：姓名、身份证号、出生日期、资格证书号、级别。

主裁判信息主要包括：姓名、身份证号、出生日期、资格证书号、获取证书时间、级别。

（2）每支球队有一名主教练和若干名球员。一名主教练只能受聘于一支球队，一名球员只能效力于一支球队。每支球队都有自己的唯一主场场地，且场地不能共用。

（3）足球联赛采用主客场循环制，一周进行一轮比赛，一轮的所有比赛同时进行。

（4）一场比赛有两支球队参加，一支球队作为主队身份、另一支作为客队身份参与比赛。一场比赛只能有一名主裁判，每场比赛有唯一的比赛编码，每场比赛都记录比分和日期。

【概念结构设计】

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

图3-1 实体联系图

【逻辑结构设计】

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

球队（球队编号，名称，成立时间，人数，主场地址）

球员（姓名，身份证号，出生日期，身高，家庭住址， （1） ）

主教练（姓名，身份证号，出生日期，资格证书号，级别， （2） ）

主裁判（姓名，身份证号，出生日期，资格证书号，获取证书时间，级别）

比赛（比赛编码，主队编号，客队编号，主裁判身份证号，比分，日期）

【问题1】（6分）

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

图3-1中的联系“比赛”应具有的属性是哪些？

【问题2】（4分）

根据图3-1，将逻辑结构设计阶段生成的关系模式中的空（1）～（2）补充完整。

【问题3】（5分）

现在系统要增加赞助商信息，赞助商信息主要包括赞助商名称和赞助商编号。

赞助商可以赞助某支球队，一支球队只能有一个赞助商，但赞助商可以赞助多支球队。赞助商也可以单独赞助某些球员，一名球员可以为多个赞助商代言。请根据该要求，对图3-1进行修改，画出修改后的实体间联系和联系的类型。

查看答案 开始考试

正确答案：

本题解析：

【问题1】

比赛联系应具有的属性包括：比赛编码，比分，日期。

【问题2】

（1）球队编号

（2）球队编号

【问题3】