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

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

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

交易明细表：TranDetails (too, ano, ttime, toptr, amount, ttype),其中属性分别为：交易编号，账户号码，交易时间，交易操作员，交易金额，交易类型(1-存款，2-取款，3-转账)。

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

常见的交易规则如下：

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

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

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

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

表5-1

请根据上述描述，回答以下问题。

【问题1】(3分)

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

CREATE PROCEDURE AcctSum(OUT： Amts DOUBLE)

BEGIN

SET TRANSACTION ISOLATION LEVEL（ a ）;

BEGIN TRANSACTION;

SELECT sum(balance) INTO : Amts FROM Accounts;

if error// error是由DBMS提供的上一句SQL的执行状态

BEGIN

ROLLBACK;

return -2;

END

INSERT INTO AcctSums

VALUES (getDATE（　　）, getTIME（　　）, （ b ）);

if error // error是由DBMS提供的上一句SQL的执行状态

BEGIN

ROLLBACK;

return-3;

END

（ c ）;

END

【问题2】(8分)

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

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

(1)请补全表中的空缺处(a)、(b);

(2)上述调度结束后，汇总得到的总余额是多少？

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

【问题3】(4分)

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

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

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

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

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

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

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

（5）家长注册。家长注册使用该系统，指定自己子女，经学校管理人员审核后，向 家长发送注册结果。

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

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

图1-2 0层数据流图

【问题1】（5分）

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

【问题2】（4分）

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

【问题3】（3分）

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

【问题4】（3分）

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

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

【需求描述】

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

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

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

4.每个快递员接手一份快递后，需在系统中录入每个快递的当前状态信息，包括当前位置、收到时间、当前快递员和上一段快递员。状态信息包括待揽件、投递中、已签收。 如果快递己签收，应记录签收人姓名及一个联系电话。每个快递在一个站点只能对应一个负责的快递员。

注：试题不需要考虑快递退回的相关问题。

【逻辑结构设计】

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

快递（快递编号，收件人姓名，收件地址编号，收件人电话，投递时间，物品类别，物品名称，物品价值），其中收件地址编号是地址实体的地址编号。

快递员（姓名，身份证号,电话号码，联系地址编号，工作站点编号）

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

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

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

快递跟踪（快递编号，当前负责人编号，前一负责人编号，当前状态，收到时间， 当前站点编号）。

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

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

【问题1】(6分)

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

(1) 列举出所有候选键。

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

(3) 将其分解为BCNF,分解后的关系名依次为：快递投递1,快递投递2，…，并 用下划线标示分解后各关系模式的主键。

【问题2】(6分)

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

(1) 列举出所有候选键。

(2) 它是否为2NF，用100字以内文字简要叙述理由。

(3) 将其分解为BCNF，分解后的关系名依次为：快递跟踪1，快递跟踪2，…，并 用下划线标示分解后各关系模式的主键。

【问题3】(3分)

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

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

【需求描述】

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

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

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

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

【概念模型设计】

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

【逻辑结构设计】

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

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

员工（工号，身份证号，姓名，性别，（a），手机号）

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

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

【问题1】（5分）

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

【问题2】（4分）

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

【问题3】（6分）

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

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

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

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

学生：Student ( stuno, stuname, stuage, stusex, schno),各属性分别表示学生的学号、姓名、年龄、性 别，以及学生所属学院的编号；--- stuno 主键

学院：School ( schno， schname, schstunum ),各属性分别表示学院的编号、名称及学生人数；--- schno 主键

俱乐部：Club (clubno,clubname, clubyear, clubloc ),各属性分别表示俱乐部的编号、名称、成立年 份和活动地点； --- clubno 主键

参加：JoinClub ( stuno, clubno. joinyear ),各属性分别表示学号、俱乐部编号，以及学生加入俱乐部 的年份。 --- stuno, clubno 主键

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

(1) 学生的性别取值为‘F’和‘M' (F表示女性，M表示男性)。

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

(3) 学院表中的学生人数值与学生表中的实际人数要完全保持一致。也就是说，当学生表中增减记录时，就要自动修改相应学院的人数。

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

【问题1】(4分)

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

CREATE TABLE Student (

stuno CHAR(ll) （ a ），

stuname VARCHAR,

stuage SMALLINT,

stusex CHAR(l) （ b），

schno CHAR(3) （ c） ON DELETE （ d）)；

【问题2】(5分)

创建倶乐部人数视图，能统计每个倶乐部已加入学生的人数，属性有clubno> clubname 和clubstunum。对于暂时没有学生参加的俱乐部，其人数为0。此视图的创建语句如下， 请补全。

CREATE VIEW CS_NUMBER ( clubno, clubname, clubstunum ) AS

SELECT JoinClub.clubno, （ e ） , （ f ）

FROM JoinClub, Club

WHERE JoinClub.clubno = Club.clubno

（ g ） BY JoinClub.clubno （h ）

SELECT clubno, clubname, 0 FROM Club

WHERE clubno NOT IN

(SELECT DISTINCT clubno FROM （ i）);

【问题3】(4分)

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

CREATE TRIGGER STU_NUM_TRG

AFTER INSERT OR DELETE ON （ j ）

REFERENCING new row AS nrow, old row AS orow FOR EACH（k ）

BEGIN

IF INSERTING THEN

UPDATE School （ l ）

END IF;

IF DELETING THEN

UPDATE School （ m）;

END IF;

END;

【问题4】(2分)

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

SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno = School.schno

AND stuage ＜19 （ n ） BY （ o）;