2024数据库系统工程师模拟题

Caijxlinux
数据库
10天前
42热度
0评论

2024数据库系统工程师模拟题

【说明】

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

【需求分析】

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

球队信息：球队编号、名称、成立时间、人数、主场地址、球队主教练
球员信息：姓名、身份证号、出生日期、身高、家庭住址
主教练信息：姓名、身份证号、出生日期、资格证书号、级别
主裁判信息：姓名、身份证号、出生日期、资格证书号、获取证书时间、级别

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

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

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

【概念模型设计】

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

球员ER

【逻辑结构设计】

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

球队（球队编号，名称，成立时间，人数，主场地址）
球员（姓名，身份证号，出生日期，身高，家庭住址，(a)）
主教练（姓名，身份证号，出生日期。资格证书号，级别，(b)）
主裁判（姓名，身份证号，出生日期，资格证书号，获取证书时间，级别）
比赛（比赛编号，主队编号，客队编号，主裁判身份证号，比分，日期）

问题1：补充图1-1中的联系和联系的类型。图1-1中的联系"比赛"应具有的属性是哪些？

球员ER（完整）

答：比赛具有的属性为：比赛编号、比分和日期。

问题2：根据图1-1，将逻辑结构设计阶段生成的关系模式中的空白处补充完整。

答：(a)：球队编号。(b)：球队编号。

问题3：现在系统要增加赞助商信息，赞助商信息主要包括赞助商名称和赞助商编号。赞助商可以赞助某支球队，一支球队只能有一个赞助商，但赞助商可以赞助多支球队。赞助商也可以单独赞助某些球员，一名球员可以为多个赞助商代言。请根据该要求，对图进行修改，画出修改后的实体间联系和联系的类型。

赞助商ER

【说明】

某航空公司要开发一个订票信息处理系统，该系统的部分关系模式如下

航班（航班编号，航空公司，起飞地，起飞时间，目的地，到达时间，票价）
折扣（航班编号，开始日期，结束日期，折扣）
旅客（身份证号，姓名，性别，出生日期，电话，VIP折扣）
购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）

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

（1）航班表中的起飞时间和到达时间不包含日期，同一航班不会在一天出现两次及两次以上。

（2）各航空公司会根据旅客出行淡旺季适时调整机票的折扣，旅客购买机票的购票金额计算公式为：票价*折扣*VIP折扣，其中旅客的VIP折扣与该旅客已购买过的机票的购票金额总和相关，在旅客每次购票后被修改。VIP折扣的计算由函数 float vip_value(char[18]身份证号)完成。

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

问题1：请将如下创建购票关系的SQL语句的空缺部分补充完整，要求指定关系的主键、外键，以及购票金额大于0的约束。

CREATE TABLE 购票（
购票单号 CHAR(15) (a),
身份证号 CHAR(18),
航班编号 CHAR(6),
搭乘日志 DATE,
购票金额 FLOAT (b),
(c),
(d),
）;

CREATE TABLE 购票（
购票单号 CHAR(15) PRIMARY KEY,
身份证号 CHAR(18),
航班编号 CHAR(6),
搭乘日志 DATE,
购票金额 FLOAT CHECK(购票金额>0),
FOREIGN KEY(身份证号) REFERENCES 旅客(身份证号),
FOREIGN KEY(航班编号) REFERENCES 航班(航班编号),
）;

问题2：身份证号为210000196006189999的客户购买了2013年2月18日CA5302航班的机票，购票单号由系统自动生成。下面的SQL语句将上述购票信息加入系统中，请将空缺部分补充完整。

INSERT INTO 购票(购票单号，身份证号，航班编号，搭乘日期，购票金额)
    SELECT '201303105555','210000196006189999','CA5302','2013/2/18',(e)
    FROM 航班,折扣,旅客
    WHERE (f) AND 航班.航班编号='CA5302'
    AND '2013/2/18' BETWEEN 折扣.开始日期 AND 折扣.结束日期
    AND 旅客.身份证号='210000196006189999';

INSERT INTO 购票(购票单号，身份证号，航班编号，搭乘日期，购票金额)
    SELECT '201303105555','210000196006189999','CA5302','2013/2/18',票价*折扣*VIP折扣
    FROM 航班,折扣,旅客
    WHERE 航班.航班编号=折扣.航班编号 AND 航班.航班编号='CA5302'
    AND '2013/2/18' BETWEEN 折扣.开始日期 AND 折扣.结束日期
    AND 旅客.身份证号='210000196006189999';

（2）需要用触发器来实现VIP折扣的修改，调用函数vip_value()来实现。请将如下SQL语句的空缺部分补充完整。

CREATE TRIGGER VIP_TAG AFTER (g) ON (h)
 REFERENCING new row AS nrow
 FOR EACH ROW
 BEGIN
    UPDATE 旅客
    SET (i)
    WHERE (j);
 END

CREATE TRIGGER VIP_TAG AFTER INSERT ON 购票
 REFERENCING new row AS nrow
 FOR EACH ROW
 BEGIN
    UPDATE 旅客
    SET VIP.折扣=vip_value(nrow.身份证号)
    WHERE 旅客.身份证号=nrow.身份证号;
 END

问题3：查询搭乘日期在2012年1月1日至2012年12月31日之间，且合集购票金额大于等于10000元的所有旅客的身份证号、姓名和购票金额总和，并按购票金额总和降序输出

SELECT 旅客.身份证号,姓名,SUM(购票金额)
FROM 旅客,购票
WHERE (k)
GROUP BY (l);
OERDER BY (m);

SELECT 旅客.身份证号,姓名,SUM(购票金额)
FROM 旅客,购票
WHERE 身份证号=购票.身份证号 AND 搭乘日期 BETWEEN '2012/1/1' AND '2012/12/31'
GROUP BY 旅客.身份证号,姓名 HAVING SUM(购票金额)>=10000;
OERDER BY SUM(购票金额) DESC;

（2）经过中转的航班与相同始发地和目的地的直达航班相比，会享受更低的折扣。查询从广州到北京，经过一次中转的所有航班对，输出广州到中转地的航班编号、中转地、中转地到北京的航班编号。

SELECT (n)
FROM 航班 航班1,航班 航班2
WHERE (o);

SELECT 航班1.航班编号,航班1.目的地,航班2.航班编号
FROM 航班 航班1,航班 航班2
WHERE 航班1.起飞地='广州' AND 航班2.目的地='北京' AND 航班1.目的地=航班2.起飞地;

【说明】

某公司拟开发一套招聘信息管理系统，以便队整个公司的各个部门的招聘信息进行统一管理。

【需求描述】

（1）该公司招聘的职位有：测试人员、开发人员、文员秘书和销售代表等职位。公司将职位划分为三种专业类型：技术类型、行政类型和销售类型。每个职位对应一种专业类型，如测试人员职位属于技术类型。每个职位可以属于一个或多个部门。

（2）面试官由公司员工担任，每个面试官可以负责一个或多个职位的面试。一个职位可由多名面试官负责面试。

（3）应聘人员可以注册应聘的职位称为候选人，并填报自己的简历信息。一个候选人可以应聘多个职位。系统记录候选人每次应聘的面试时间和面试成绩。

【逻辑结构设计】

根据上述需求，初步设计的招聘信息数据库关系模式如下

职位（职位编码，职位名称，级别，专业类型，招聘条件，薪酬范围）
面试官（工号，姓名，专业类型，工作职务，工作部门，部门负责人，部门电话）
招聘安排（职位编码，所属部门，面试官工号）
候选人（身份证号，姓名，性别，联系电话，出生日期，简历信息，应聘的职位编码，面试成绩）

关系模式的主要属性、含义以及约束如下表所示。

属性	含义和约束条件
职位编码	唯一标识一种职位
专业类型	专业类型分为：技术类型，行政类型，销售类型
工号	员工的工号作为面试官的唯一编号
工作职务	员工在部门中的职务
工作部门	部门名称，唯一标识一个部门
部门负责人	部门负责人的工号
所属部门	职位所属于的部门名称
面试官工号	负责招聘某职位的面试官的工号

问题1：对关系"候选人"，请回答以下问题

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

答：姓名、性别、联系电话、出生日期、简历信息、面试成绩。

（2）关系"候选人"可达到第几范式，用60字以内的文字简要叙述理由。

答：候选人关系模式可达到第一范式，由于候选人关系的候选码为身份证号和应聘的职位编码，但又包含函数依赖：身份证->姓名，性别，联系电话，出生日期，简历信息。不满足第二范式要求，即非主属性部分依赖于码。

问题2：对关系"面试官"，请回答以下问题：

（1）针对面试官关系，请用60字以内的文字简要说明会产生什么问题。

答：面试官关系不满足第三范式，即每一个非主属性既不部分依赖于码也不传递依赖于码。会造成：数据冗余、插入异常、删除异常和修改异常。

（2）把"面试官"分解为第二范式，分解后的关系名依次为：面试官1，面试官2...

面试官1（工号，姓名，专业类型，工作职务，工作部门）
面试官2（工作部门，部门负责人，部门电话）

（3）列出修正后的各关系模式的主键。

答：面试官1主键：工号。面试官2主键：工作部门。

问题3：对关系"招聘安排"，请回答以下问题

（1）关系招聘安排是不是第四范式，用60字以内的文字叙述理由。

答：招聘安排不符合第四范式，因为一个职位编码可以对应多个部门，也可以对应多个面试官，存在多值依赖，导致数据冗余。

（2）把招聘安排分解为第四范式，分解后的关系名依次为：招聘安排1，招聘安排2...

招聘安排1（职位编码，所属部门）
招聘安排2（职位编码，面试官工号）

【说明】

某网上商品销售系统的业务流程如下：

（1）将客户的订单记录（订单号，客户ID，商品ID，购买数量）写入订单表；

（2）将库存表（商品ID，库存量）中订购商品的库存量减去该商品的购买数量。

针对上述业务流程，完成下列问题：

问题1：假设库存量有大于等于0的约束，可能出现如下情况，当订单记录写入订单表后，修改库存表时因违反约束而无法执行，该如何处理？（100字以内）

答：将写订单记录和修改库存表作为一个完整的事务来处理，当修改库存表无法执行时，回滚事务，则会撤销写入的订单记录，数据库保持一致。

问题2：引入如下伪指令：将商品A的订单记录插入订单表记为I(A)；读取商品A的库存量到变量x，记为x=R(A)；变量x值写入商品A中的库存量，记为W(A,x)。则客户i的销售业务伪指令序列为：Ii(A),xi=Ri(A),xi=xi-ai,Wi(A,xi)。其中ai为商品的购买数量。假设当前库存足够，不考虑发生修改后库存量小于0的情况。若客户1、客户2同时购买同一种商品时，可能出现的执行序列为

I1(A),I2(A),x1=R1(A),x2=R2(A),x1=x1-a1,W1(A,x1),x2=x2-a2,W2(A,x2)

（1）此时会出现什么问题？（100字以内）

答：出现丢失更新问题。客户1和客户2分别读取了相同的库存量，分别修改后写回，导致客户1的更新被客户2覆盖，最终库存量错误。

（2）为了解决上述问题，引入共享锁指令SLock(A)和独占锁指令XLock(A)对数据A进行加锁，解锁指令Unlock(A)对数据A进行解锁，客户i的加锁指令用SLocki(A)表示，其他类同。插入订单表的操作不需要引入锁指令。请补充上述执行序列，使其满足2PL协议，并使持有锁的时间最短。

2PL协议要求：加锁后统一进行操作，直到释放锁前不能再加新锁。

为了最短持锁时间，应该：
读/修改/写操作尽量靠近锁；
解锁一旦写完马上执行。

答：新序列如下所示

I1(A),I2(A),
XLock1(A),
x1=R1(A),
x1=x1-a1,
W1(A,x1),
Unlock1(A),
XLock2(A),
x2=R2(A),
x2=x2-a2,
W2(A,x2),
Unlock2(A)

问题3：下面是用E-SQL实现的销售业务程序的一部分，请补全空缺处的代码。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO 订单表 VALUES(:OID,:CID,:MID,:qty);
IF ERROR THEN {ROLLBACK;(a)；}
UPDATE 库存表
SET 库存量=库存量-:qty
WHERE (b);
IF ERROR THEN {ROLLBACK;return;}
(c);

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO 订单表 VALUES(:OID,:CID,:MID,:qty);
IF ERROR THEN {ROLLBACK;return；}
UPDATE 库存表
SET 库存量=库存量-:qty
WHERE 商品ID=:MID;
IF ERROR THEN {ROLLBACK;return;}
COMMIT;

【说明】

如果一个数据库采用增量备份机制进行数据库备份。该系统在数据准备完成后，进行了一次静态的完整备份，备份文件为Backup_original；系统在运行过程中，每隔一段时间进行动态增量备份，得到文件为Backup_inc_1，Backup_inc_2；系统日志文件为syslog。所有备份文件、日志文件均存放在联机的其他硬盘中。请根据上述描述，回答以下问题。

问题1：如果当前数据库系统遭遇了不可修复的硬盘故障，导致当前数据库中所有数据丢失，请问应采用什么步骤进行恢复，使得数据库恢复到故障前的状态？

答：首先重新安装原系统的数据库管理系统DBMS。利用完全备份文件Backup_original，将数据库恢复到创建备份文件时的状态。依次利用增量备份Backup_inc_1，Backup_inc_2和系统日志文件syslog进行恢复。通过上述步骤，即可恢复到系统出现故障前的状态。

问题2：如果日志文件syslog如表1-1所示，第一列表示日志记录编号，第二列表示日志记录内容。<\Ti,START>表示事务Ti开始执行，<\Ti,COMMIT>表示事务Ti提交，<\Ti,D,V1,V2>表示事务Ti将数据项D的值由V1修改为V2。

表1-1 日志记录列表

编号	内容
LSN1	<T1,START>
LSN2	<T1,X,100,1>
LSN3	<T2,START>
LSN4	<T2,X,1,3>
LSN5	<T3,START>
LSN6	<T2,Y,50,6>
LSN7	<T3,Y,6,8>
LSN8	<T3,Z,10,9>
LSN9	<T2,Y,8,10>
LSN10	<T1,COMMIT>
LSN11	<T3,Z,9,5>
LSN12	<T3,COMMIT>
LSN13	CRASH