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

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

【需求分析】

（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进行修改，画出修改后的实体间联系和联系的类型。

某航空售票系统负责所有本地起飞航班的机票销售，并设有多个机票销售网点。以下为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．每位求职人员需填写《求职信息登记表》（如表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）考试设置。教师制定试题（题目和答案），制定考试说明、考试时间和提醒时间等考试信息，录入参加考试的学生信息，并分别进行存储。

（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），将分解出的加工和数据流填入答题纸的对应栏内。（注：数据流的起点和终点须使用加工的名称描述）

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

员工表：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=‘中国银行北京分行’

);