...数据库工程师 试题及答案与解析-软考考试真题-案例分析

案例分析-答案与解析

试题一（共15分）

阅读下列说明和图，回答问题1至问题4，将解答填入答题纸的对应栏内。

【说明】

某学校欲开发一学生跟踪系统，以便更自动化、更全面地对学生在校情况（到课情况和健康 状态等）进行管理和追踪，使家长能及时了解子女的到课情况和健康状态，并在有健康问题 时及时与医护机构对接。该系统的主要功能是：

采集学生状态。通过学生卡传感器，采集学生心率、体温（摄氏度）等健康指标及其 所在位置等信息并记录。每张学生卡有唯一的标识（ID)与一个学生对应。

健康状态告警。在学生健康状态出问题时，系统向班主任、家长和医护机构健康服务 系统发出健康状态警告，由医护机构健康服务系统通知相关医生进行处理。

到课检查。综合比对学生状态、课表以及所处校园场所之间的信息对学生到课情况进 行判定。对旷课学生，向其家长和班主任发送旷课警告。

汇总在校情况。定期汇总在校情况，并将报告发送给家长和班主任。

家长注册。家长注册使用该系统，指定自己子女，存入家长信息，待审核。

基础信息管理。学校管理人员对学生及其所用学生卡和班主任、课表（班级、上课时 间及场所等）、校园场所（名称和所在位置区域）等基础信息进行管理；对家长注册申请进 行审核，更新家长状态，将家长ID加入学生信息记录中使家长与其子女进行关联，向家长 发送注册结果。一个学生至少有一个家长，可以有多个家长。课表信息包括班级、班主任、 时间和位置等。

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

【问题1】(5分）

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

【参】

E1:学生    

E2:学校管理人员    

E3:班主任    

E4:家长    

E5:医护机构健康服务系统

【问题2】 (4分）

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

【参】

D1:学生状态信息表    

D2:学生基础信息表    

D3:校园场所信息表

D4:课表

【问题3】(3分）

根据说明和图中术语，补充图1-2中缺失的数据流及其起点和终点（三条即可)

【参】

根据说明中的术语，说明明图1-1中数据流“学生状态”和“学生信息”的组成。

【参】

1、学生状态包括:学生ID、学生卡ID、学生心率、体温、所在位置等位置。

2、学生信息包括:学生1D、学生卡1D、家长1D、班主任等信息。

试题二（共15分）

某创业醉化基地管理若干解化公司和创业公司，为规范管理创业顶目技资业务，需要开发一 个倌息系统。请根据下述需求描述完成该系统的数据库设计。

【需求描述】

记录某化公司和创业公司的信息。孵化公司俏息包括公司代码、公司名称、法人代表 名称、注册地址和一个电话；创业公司倌息包括公司代码、@名称和一个电话*孵化公司 和创业公司的公司代码编码不同。

统一管理孵化公司和创业公司的员工。员工信息包括工号、身份证号、姓名、性别、 所属公司代码和一个手机号，工号唯一标识每位员工。

记录投资方信息。投资方信息包括投资方编号、投资方名称和一个电话。

抟资方和创业公司之间依靠醉化公司牵线建立创业顶目合作关系，具体实施由孵化公 司的一位员工负责协调投资方和创业公司的一个创业顶目。一个创业顶目只属于一个创业公 司，但可以接受若干投资方的投资。创业顶目信息包括顶目编号、创业公司代码、投资方编 号和醉化公司员工工号。

【概念模型设计】

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

【逻辑结构设计】

根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完整）： 孵化公司（公司代码，公司名称，法0代表名称，注册地址，电话）

创业公司（公司代码，公司名称，电话）

员工（工号，身份证号，姓幕性别，（a ),手机号）

投资方（投资方编号、投资方名称，电话）

顶目（项目编号，创业公司代码（b )，孵化公司员工号）

【问题1】(5分）

根据问题描述，补充图2-1的实体联系图。

【参】

【问题2】 (4分）

补充逻辑结构设计结果中的（a)、（b)两处空缺及完整性约束关系。

【参】

a.所属公司代码

b.投资方编号

完整性约束:a.主键:工号

b.主键:项目编号，

投资方编号:

外键:所属公司代码外键:创业公司代码

【问题3】(6分）

若创业顶目的信息还需要包括投资额和投资时间，那么：

是否需要增加新的实体来存储投资额和投资时间？

如果埔加新的实体，请给出新实体的关系模式，并对图2-1进行补充。如果不需要增 加新的实体，请将“投资额”和“投资时间”两个属性补充连线到图2-1合适的对象上，并 对变化的关系模式进行修改。

【参】

1.不需要增加新的实体。

2.联系“项目’”’ 增加两个属性:‘

“投资额”和‘投资时间’就可以了。

试题三(共15分）

某快递公司对每个发出的快递进行跟踪管理，需要建立一个快递跟踪管理系统，对该公司 承接的快递业务进行有效管理。

【需求描述】

公司在每个城市的每个街道都设有快递站点。这些站点负责快递的接收和找递。站点信 息包括站点地址、站点名称、责任人、一部联系电话、开始营业时间、结束营业时间。每 个站点每天的营业时间相同。毎个站点只能有一个责任人。

系统内需记录快递员、发件人的基本侑息。这些信息包括姓名、身份证号、一个联系地 址、一部联系电话•快递站点的责任人由快递员兼任，且每个快递站点只有一个责任人。 每个快递员只负责一个快递站点的揽件和快递派送业务。发件人和快递员霖实名认证。

快递需要提供详实的信息，包括发件人姓名、身份证号、一部发件人电话号码、发件人 地址、收件站点、收件人姓名、收件地址、一部收件人电话、投递时间、物品类别、物品 名称及物品价值。毎个发件人和收件人在系统里只能登记一个电话和地址。

每个快递员接手一份快递后，需在系统中录入每个快递的当前状态信息，包括当前位

置、收到时间、当前快递员和上一段快递员。状态信息包括待揽件、投递中、已签收。如 果快递己签收，应记录签收人姓名及一个联系电话*毎个快递在一个站点只能对应一个负 责的快递员。注：试题不需要考虑快递退回的相关问题。

【逻辑结构设计】

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

*快递（快递编号，收件人姓名，收件地址编号，收件人电话，找递时间，物品类别，物 品名称，物品价值)，其中收件地址编号是地址实体的地址编 •快递员（快递员编号，姓名，身份证号，电话号码，联系地址编号，工作站点编号）

*快递站点（站点编号，站点名称，责任人编号，站点地址编号，开始营业时间，联系电 话，结束营业时间）。责任人编号是负责该站点的快递员的身份证号。

*地址（地址编号，所在省，所在市，所在街道，其他)，其他俏息是需补充的地址信 息。

攀快递投递（快递编号，快递员编号^i件人姓名，发件人身份证号，发件人电话号码， 发件人地址编号，揽件站点编号)，其中发件人地址编号为发件人地址的地址编号，揽 件站点编号为接收该快递的站点编号。

籲快递跟踪（快递编号，当前快递员编号，上一段快递员编号，当前状态，收到时间，

当前站点编号）。

*快递签收（快递编号，签收人姓名，签收人联系电话)。

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

【问题1】(6分}

对关系“快递投递”，请回答以下问题：

⑴列举出所有候选键。

⑵它是否为3NF,用100字以内文字简要叙述理由。

⑶将其分解为BCNF,分解后的关系名依次为I快递投递丨，快递投递2>__,并用下划线

标示分解后各关系模式的主键。

【参】

⑴候选键：快递编号

不满足3NF,因为快递投递关系存在^主属性对主属性传递依赖，快递编号->发件人身 份证号，发件人身份证号->(发件人姓名，发件人电话号码，发件人地址编号)。所以不满足 3NF

快递投递快递编号,快递员编号，发件人身份证号}

快递投递2(发件人身份证号，发件人姓名，发件人电话号码，发件人地址编号>

快递投递3(快递员编号,揽件站点编号）注：揽件站点编号应该等于快递员关系中的工 作站点编号，所以该关系其实已经存在。

【问题2】(6分}

对关系“快递跟踪”，请回答以下间题：

⑴列举出所有候选键。

⑵它是否为2NF,用100字以内文字简要叙述理由。

⑶将其分解为BCNF,分解后的关系名依次为：快递跟踪i，快递跟踪2,…，并用下划

线标示分解后各关系模式的主键。

【参】

候选键| (快递编号、当前快递员编号、当前状态)组合键或{快递编号、收到时间）组合键

⑵满足2NF,因为快递跟踪中的每个非主属性都完全函数依赖于候选键（快递编号、当前快 递员编号、当前状态）。

【问题3】(3分}

快递公司会根据快递物品和距离收取快递费，每件快递需由发件人或收件人支付快递费给 公司。同一个发件人同时发起多个快递，必须分别支付。快递公司提供预支付和到付两种 支付方式。为了统计快递费的支付情況(详细金额和时间)，试増加“快递费支付”关系模 式，用100字以内文字简要叙述解决方案。

【参】

快递费支付（支付编号，快递编号，支付方式，支付金额，快递员编号，支付时间，支付人)

试题四(共15分}

某学生信息管理系统的部分数据库关系模式如下：

•学生：Student (stuno,stuname,stuage,stusex,schno)，各属性分别表不学生的学号、姓

名、年龄、性别，以及学生所属学院的编号；

•学院：School (schno,schname,schstunum)i各属性分别表不学院的编号、名称及学生

人数；

•俱乐部：Club(clubno, clubname, clubyear, clubloc)，各属性分别表不俱乐部的编号、名

称、成立年份和活动地点；

*参加：j〇inClub(stuno, clubno, joinyear)，各属性分别表不学号、俱乐部编号，以及学生

加入俱乐部的年份。    胃

有关关系模式的说明如下：

学生的性別取值为T,M(F表示女性，M表示男性)。

⑵删除一个学院的记录时，通过外键约束级联删除该学院的所有学生记录。

学院表中的学生人数值与学生表中的实际人数要完全保持一致*也就是说，当学生表

中增减记录时，就要自动修改相应学院的人数。

根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。

【问题1】(4分}

请将下面创建学生表的SQL语句补充完整，要求定义实体完整性约束、参照完整性约束，

以及其他完整性约束。

CREATE TABLE Student [ stunoCHAR{ll) (    1    ),

stuname VARCHAR(8),

stuageSMALUNT,

stusex CHAR(l) ( 2 ),

schnoCHAR(3) ( 3 ) ON DELETE ( 4 ));

【参】

（1）PRIMARY KEY

（2）CHECKVAUJESIN(.F_,'M')或 CHECiqstusexINCFVM1})

（3）REFERNCES School(schno)

【问题2】(5分)

创建倶乐部人数视图，能统计毎个倶乐部已加入学生的入激，厲性有dubno, dubname和 clubstunurm对于暂时没有学生参加的俱乐部，其人数为0。此视图的创建语句如下，请

补全。

CREATE VIEW CS_NUMBER ( clubno, clubname, clubstunum ) AS SELECTJoinClub.clubno,( 1    )

FROM JoinClub, Club WHERE JoinClub.clubno = Club.clubno i^TS ) BY JoinClub.clubno (4 ) SELECT clubno, clubname, 0 FROM Club WHERE clubno NOT IN

(SELECT DISTINCT clubno FROM (    5 ));

【参】

1.Club.clubname

2.COUNT(DISTINCT (joinClub.stuno]) AS clubstunum

3.GROUP

4.UNION  

5,JoinClub

【问题3】

每当系统中新加或删除一个学生，就需要自动修改相应学院的人数，以便保持系统中学生 人数的完整性与一致性。此功能由下面的触发器实现，请补全。

CREATE TRIGGER STU_NUM_TRG

AFTER INSERT OR DELETE ON (    1 )

REFERENCING new row AS nrow, old row AS orow FOR EACH ( 2 )

BEGIN

IF INSERTING THEN

UPDATE School ( 3 )

END IF;

IF DELETING THEN

UPDATE School ( 4 );

END liF;

END;

【参】

（1）Student    

（2）row^

（3）SET schstunum=schstunum+l where School.schno=nrow.schno

（4）SET schstunum=schstunum-l where School.schno=orow.schno

【问题4】2分

查询年龄小于19岁的学生的学号、姓名及所属学院名，要求输出结果把同一个学院的学生 排在一起。此功能由下面的SQL语句实现，请补全。

SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno = School.schno ANDstuage<19 ( 1 )BY( 2 );

【参】

1.0RDER    

2.Student.schno 或 School.schno

试题五(共15分}

某商业银行账务系统的部分关系模式如下

*账户表：Account (ano,aname, balance),其中属性含义分别为：账户号码，账户名称和

账户余额。

* 交易明细表：TranDetails (too, ano, ttime, toptr, amount, ttype),其中属性分别为：交易编

号，账户号码，交易时间，交易操作员，交易金额，交易类型(1-存款，2通款，3-转

账h

*余额汇总表：AcctSums(adate, atime, allamt)，其中属性分别为：汇总日期，汇总时间，

总余额。

常见的交易规则如下：

«存/取軟交易|操作员核对用户相关信息，在系统上执行存/取款交易。账务系统増加/ 减少该账户余額/并在交易明细表中増加一条存/取軟交易明细。

*转账交易|操作员核对用户相关信息，核对转账交易账户信息，在系统上执行转账交 易*账务系统对转出账户减少其账户余额，对转入账户増加其账户余额，并在交易明细 表中增加一条转账交易明细。

*余额汇总交易：将账户表中所有账户余额累计汇总。

假定当前账户表中的数据记录如表5-1所示。

【问题1】(3分）

假设在正常交易时间，账户上在进行相应存取款或转账操作时，要执行余额汇总交易。下 面是用SQL实现的余额汇总程序，请补全空缺处的代码。要求(不考虑并发性能)在保证余 额汇总交易正确性的前提下，不能影响其他存取款或转账交易的正确性。

CREATE PROCEDURE AcctSum(0UT:Amts DOUBLE)

BEGIN

SET TRANSACTION ISOLATION LEVEL ( 1 );

BEGIN TRANSACTION;

SELECT sum(balance) INTO: Amts FROM AccountsQ^" if error//error是由DBMS提供的上一句SQL的执行状态 BEGIN

ROLLBACK; return -2;

END

INSERT INTO AcctSums

VALUES (getDATE(), getTIMEf), ( 2 )); if error//error是由DBMS提供的上一句SQL的执行状态 BEGIN

retum-3;

END (3 );

END

【参】

1.SERIALIZABLE    

2. Amts    

3.C0MMIT-TRANSACTI0N 或 COMMIT

【问题2】(8分}

引入排它锁指令LX()和解锁指令uxa要求满足两段锁协议和提交谅隔离级别。假设在进行 余额汇总交易的同时，发生了一笔转账交易。从101账户转给104账户400元。这两笔事务的调度如表5-2所示。

表5-2转账汇总部分事务调度表

（2）述调度结束后，汇总得到的总余额是多少？

(3该数据是否正确？请说明原因。

【参】

（1）a.等待 b.UX(lOl), UX(104)

（2）总余额 2000 元

（3）不正确，汇总事务读取101账户余额500元后，转账事务对101账户转账400元给104账 户，而后汇总事务读取了 104账户余额，导致读取到的101账户余额不是最新的数据*所以 [总余额增加了 400。

【问题3】4分

在间题2的基础上，引入共享锁指令和解锁指令US()。对问題2中的调度进行重写， 要求满足两段锁协议。两个事务执行的某种调度顺序如表5-3所示，该调度顺序使得汇总 事务和转账事务形成死锁，请补全表中的空缺处（a）、（b）

表5-3转账汇总部分事务调度表

（a）LX(101)或 LX(101),等待

（b）LX[104)