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

某学校拟开发一套实验管理系统，对各课程的实验安排情况进行管理。

【需求分析】

一个实验室可进行多种类型不同的实验。由于实验室和实验员资源有限，需根据学生人数分批次安排实验室和实验员。一门课程可以为多个班级开设，每个班级每学期可以开设多门课程。一门课程的一种实验可以根据人数、实验室的可容纳人数和实验类型，分批次开设在多个实验室的不同时间段。一个实验室的一次实验可以分配多个实验员负责辅导实验，实验员给出学生的每次实验成绩。

（1）课程信息包括：课程编号、课程名称、实验学时、授课学期和开课的班级等信息；实验信息记录该课程的实验进度信息，包括：实验名、实验类型、学时、安排周次等信息，如表1-1所示。

（2）以课程为单位制定实验安排计划信息，包括：实验地点，实验时间、实验员等信息，实验计划如表1-2所示。

（3）由实验员给出每个学生每次实验的成绩，包括：实验名、学号、姓名、班级、实验成绩等信息，实验成绩如表1-3所示。

（4）学生的实验课程总成绩根据每次实验的成绩以及每次实验的难度来计算。

【概念模型设计】

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

【逻辑结构设计】

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

课程（课程编号，课程名称，授课院系，实验学时）

班级（班级号，专业，所属系）

开课情况（ （1） ，授课学期）

实验（ （2） ，实验类型，难度，学时，安排周次）

实验计划（ （3） ，实验时间，人数）

实验员（ （4） ，级别）

实验室（实验室编号，地点，开放时间，可容纳人数，实验类型）

学生（ （5） ，姓名，年龄，性别）

实验成绩（ （6） ，实验成绩，评分实验员）

【问题1】（6 分）

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

【问题2】（6 分）

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

【问题3】（3 分）

如果需要记录课程的授课教师，新增加“授课教师”实体。请对图3-1进行修改，画出修改后的实体间联系和联系的类型。

某旅行社拟开发一套旅游管理系统，以便管理旅游相关的信息。

1．旅行社可发布旅游线路的信息，包含：线路的价格、天数、住宿情况，以及具体的行程安排等。不同的线路参观的景点及住宿情况不相同，如表1-1所示。

2．游客与旅行社沟通，选择适合自己的线路，并由旅行社为其生成订单，以记录游客联系人的姓名、身份证号、联系方式、人数、所选线路、导游安排和票务信息。旅行社为游客在行程中的每个城市安排一个负责导游，负责游客在该城市的具体旅行安排。同一城市的负责导游相同，不同城市的负责导游有可能不同。

3．旅行社的每位员工只属于一种固定的员工类别，系统可记录员工的多部手机号。旅行社按月统计导游每月的带团人数和游客投诉次数，以计算导游的当月月薪。

根据上述需求，初步设计了旅游信息数据库，其关系模式如图1-1所示。

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

【问题1】（3 分）

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

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

（2）关系“线路信息”是否为BCNF范式，用60字以内文字简要叙述理由。

【问题2】（7 分）

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

（1）“订单信息”是否为2NF范式，用100字以内文字简要说明会产生什么问题。

（2）把“订单信息”分解为第三范式，分解后的关系名依次为：订单信息1，订单信息2，……

（3）列出分解后的各关系模式的主键。

【问题 3】（5 分）

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

（1）关系“员工信息”是不是第四范式，用100字以内文字叙述理由。

（2）若 “员工信息”不是第四范式，将其分解为第四范式，分解后的关系名依次为：员工信息1，员工信息2，……

天津市某银行信息系统的数据库部分关系模式如下所示：

客户（客户号，姓名，性别，地址，邮编，电话）

账户（账户号，客户号，开户支行号，余额）

支行（支行号，支行名称，城市，资产总额）

交易（交易号，账户号，业务金额，交易日期）

其中，业务金额为正值表示客户向账户存款；为负值表示取款。

【问题1】

以下是创建账户关系的SQL语句，账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。账户余额不能小于1.00元。请将空缺部分补充完整。

CREATE TABLE 账户(

账户号 CHAR(19) （a） ，

客户号 CHAR(10) （b） ，

开户支行号 CHAR(6) NOT NULL ，

余额 NUMBER(8，2) （c） );

【问题2】

（1）现银行决策者希望查看在天津市各支行开户且2009年9月使用了银行存取服务的所有客户的详细信息，请补充完整相应的查询语句。 (交易日期形式为’2000-01-01’）

SELECT DISTINCT 客户.*

FROM 客户， 账户， 支行， 交易

WHERE 客户.客户号 = 账户.客户号 AND

账户.开户支行号 = 支行.支行号 AND

（d） AND

交易.账户号 = 账户.账户号 AND

（e） ;

上述查询优化后的语句如下，请补充完整。

SELECT DISTINCT 客户.*

FROM 客户， 账户， （f） AS 新支行， （g） AS 新交易

WHERE 客户.客户号 = 账户.客户号 AND

账户.开户支行号 = 新支行.支行号 AND

新交易.账户号 = 账户.账户号

（2）假定一名客户可以申请多个账户，给出在该银行当前所有账户余额之和超过百万的客户信息并按客户号降序排列。

SELECT *

FROM 客户

WHERE （h）

(SELECT 客户号 FROM 账户 GROUP BY 客户号 （i） )

ORDER BY （j） ;

【问题3】

（1）为账户关系增加一个属性“账户标记” ，缺省值为0，取值类型为整数；并将当前账户关系中所有记录的“账户标记”属性值修改为0。请补充相关SQL语句。

ALTER TABLE 账户 （k） DEFAULT 0 ;

UPDATE 账户 （l） ;

（2）对于每笔金额超过10万元的交易，其对应账户标记属性值加1，给出触发器实现的方案。

CREATE TRIGGER 交易_触发器 （m） ON 交易

REFERENCING NEW ROW AS 新交易

FOR EACH ROW

WHEN （n）

BEGIN ATOMIC

UPDATE 账户 SET 账户标记 = 账户标记 + 1

WHERE （o） ;

COMMIT WORK ;

END ;

某航空售票系统负责所有本地起飞航班的机票销售，并设有多个机票销售网点。各售票网点使用相同的售票程序。假设售票程序中用到的伪指令如下表1-1所示：

假设某售票网点一次售出a张航班A 的机票，则售票程序的伪指令序列为：R(A, x);W(A,x-a)。根据上述业务及规则，完成下列问题：

【问题1】（5 分）

若两个售票网点同时销售航班A的机票，在数据库服务器端可能出现如下的调度：

A：R1(A， x)，R2(A， x)，W1(A， x–1)，W2(A， x–2)；

B：R1(A， x)，R2(A， x)，W2(A， x–2)，W1(A， x–1)；

C：R1(A， x)，W1(A， x–1)，R2(A， x），W2(A， x–2)；

其中Ri(A， x)，Wi(A， x)分别表示第i个销售网点的读写操作，其余类同。

假设当前航班 A 剩余 10 张机票，分析上述三个调度各自执行完后的剩余票数，并指出错误的调度及产生错误的原因。

【问题2】（6 分）

（1）判定事务并发执行正确性的准则是什么？如何保证并发事务正确地执行？

（2）引入相应的加解锁指令，重写售票程序的伪指令序列，以保证正确的并发调度。

【问题3】（4 分）

下面是用E-SQL实现的机票销售程序的一部分，请补全空缺处的代码。

EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

EXEC SQL SELECT balance INTO :x FROM tickets WHERE flight = ’A’ ;

printf("航班A 当前剩余机票数为：%d\n请输入购票数："， x );

scanf("%d"， &a);

x = x – a ;

if (x＜0)

EXEC SQL ROLLBACK WORK;

printf("票数不够，购票失败！");

else{

EXEC SQL UPDATE tickets SET （a） ;

if (SQLCA．sqlcode ＜> SUCCESS)

EXEC SQL ROLLBACK WORK;

else

（b） ;

}