2025软考中级《数据库系统工程师》案例模拟题合集

1.【说明】某销售企业正在实施电商平台项目，主要功能模块包括用户中心、商品中心、交易中心、支付中心、营销中心等。其中支付中心模块包括了一个账户表：Account (ano, aname, balance)，其中属性含义分别为：账户号，账户名称和账户余额。 假设现在存在事务Ti对账户表Account进行余额的修改，系统记录了修改账户表的日志记录，假设更新日志记录表示为 <Ti，A，V1，V2>,表明事务Ti对账户A执行了一个写操作，写操作前账户A的余额是V1，写操作后账户A的余额为V2。＜Ti，D，V＞表示将事务Ti回滚，数据项D的值回滚到V。＜Ti， abort＞表示事务Ti回滚结束。CRASH表示系统磁盘出错。 在经过系统执行一系列并发事务操作之后，在系统崩溃之前，日志文件中记录了如下的一系列动作： <T0 start > <T0，B，200，250> <T1 start> <checkpoint {T0，T1}> <T1，C，700，600>

<T1 commit> <T2 start> <T2 ，A，500，400> <T0 abort > <T0 abort > <crash> （a） （b）

【问题1】（9分） （1）根据题干中描述信息，请给出Undo恢复的补偿日志记录，填写空白（a）和 (b)。（6分） （2）根据第（1）步的日志信息，最终账户A、B、C当前的余额分别是多少？（3分） 【问题2】（6分） 请用50字以内的文字简单介绍通过检查点的恢复机制。

答案：

【问题1】 （1） <T2 ，A，500> <T2 ，abort > （2）A当前的余额是500；B当前的余额是200；C当前的余额是600。 【问题2】 在故障恢复时候，只需要redo/undo最近的一次checkpoint之后的提交和未提交的事务。步骤为： 步骤1：反向扫描日志文件，确定需要redo的事务和需要undo的事务 步骤2：对需要undo的事务撤销已经执行的操作 步骤3：对需要redo的事务重新执行已执行的操作

解析：需要Redo的事务是T0，T1 ，需要 Undo的事务是T2。因为＜Ti，D，V＞表示将事务Ti回滚，数据项D的值回滚到V。＜Ti， abort＞表示事务Ti回滚结束。所以日志的后面是<T2 ，A，500><T2 ，abort >。 在故障恢复时候，只需要redo/undo最近的一次checkpoint之后的提交和未提交的事务。反向扫描日志文件，确定需要redo的事务和需要undo的事务。对需要undo的事务撤销已经执行的操作。对需要redo的事务重新执行已执行的操作

2.【说明】某高铁站售票系统负责所有本地高铁的高铁票的销售，并设有多个高铁票的销售网点。各售票网点使用相同的售票程序。假设售票程序中用到的伪指令为：R(A,X)，该指令表示返回高铁A当前的剩余高铁票数给变量X；W(A,X)，该指令表示当前数据库中高铁A的剩余高铁票数置为X。 假设某售票网点一次售出a张高铁A 的高铁票，则售票程序的伪指令序列为：R(A, x)；W(A,x-a)。根据上述业务及规则，完成下列问题：

【问题1】（5 分） 假设当前高铁 A 剩余 10 张高铁票，其中Ri(A， x)，Wi(A， x)分别表示第i个销售网点的读写操作。若两个售票网点同时销售高铁A的高铁票，在数据库服务器端可能出现如下的调度。

添加图片注释，不超过 140 字（可选）

（1）分析上述调度执行完后的剩余票数是多少（也就是调度结果是多少）？ （2）该调度的结果是否是错误的？如果是错误的，产生错误的原因是什么？

【问题2】（6 分） （1）判定事务并发执行正确性的准则是什么？如何保证并发事务正确地执行？ （2）引入相应的加解锁指令，重写售票程序的伪指令序列，以保证正确的并发调度。 【问题3】（4 分） 下面是用存储过程实现的高铁票销售程序的一部分，变量flightAId是某高铁号， 变量aa是需要购买高铁票数。请补全空缺处(a)(b)(c)(d)的代码。 CREATE PROCEDURE work( in flightAId char(8),（a）aa number(4) ) DECLARE balance_a number(4)； bb NUMBER（4）； Begin SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT balance INTO balance_a FROM tickets WHERE flight = flightAId; dbms_output.put_line (‘高铁’|| flightAId||‘当前剩余高铁票数为：’||balance_a ); bb = balance_a – aa ; if (bb<0) { dbms_output.put_line (‘票数不够，购票失败！’); rollback； } else UPDATE tickets SET （b） ; (c) EXCEPTION WHEN OTHERS THEN (d); End

答案：

【问题1】 （1）剩余票数：8 （2）调度结果错误，因为破坏了事务的隔离性。一个事务的执行结果被另一个所覆盖。 【问题2】 （l）判定事务并发执行正确性的准则是满足可串行化调度。要保证并发事务正确地执行，采用两段锁协议（2PL）。 （2）重写后的售票程序伪指令序列：Xlock（A）；R（A，x）；W（A，x-a）；Unlock（A）； 【问题3】 （a）in （b）balance＝bb WHERE flight＝flightAId （c）COMMIT （d） ROLLBACK

解析：本题考查的是并发控制的概念和应用。 【问题1】 本问题考查并发情况规下不同的调度可能产生不同结果的情况。针对两个并发执行的售票程序，会相互影响从而得到错误的结果。 【问题2】 本问题考查对事务并发控制的相关知识的理解掌握。事务并发调度是否正确，可通过对非冲突语句进行交换，若最终将并发调度通过交换非冲突语句转换成串行调度，则该并发调度为可串行化调度，可串行化调度被作为事务并发执行正确性的准则。 为保证可串行化调度，在事物执行过程中引入相应指令进行控制，即两段锁协议（2PL），即对数据读之前先加读锁，写前加写锁，事务只有获得相应的锁才能操作数据，加解锁过程分为两个阶段，前一阶段只能加锁，后一阶段只能解锁，不允许有交叉。两段锁协议是保证并发事务可串行化调度的充分条件。 针对给出的伪指令操作序列，在事务写数据之前加Xlock()指令，并保证读／写锁不交叉，即满足两段锁协议。 【问题3】 CREATE PROCEDURE work( in flightAId char(8),in aa number(4) ) DECLARE balance_a number(4)； bb NUMBER（4）； Begin SELECT balance INTO balance_a FROM tickets WHERE flight = flightAId; dbms_output.put_line (‘高铁’|| flightAId||‘当前剩余高铁票数为：’||balance_a ); bb = balance_a – aa ; if (bb<0) { dbms_output.put_line (‘票数不够，购票失败！’); ROLLBACK ； } else UPDATE tickets SET balance＝bb WHERE flight＝flightAId ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK;

End

3.【说明】某庞大的电商公司中涉及到以下几个关系模式： 书籍 Books(Bid, Bname, Price, Author, Publisher)。其中各属性的含义是：Bid 书籍编号，Price 单价，Author 作者，Publisher 出版商。 订单 Orders(Orderid, Orderdate, Cid)。其中各属性的含义是：Orderid 订单编号，Orderdate 下订日期，Cid 客户编号。 订单明细 Orderlist(Orderid, Bid, Qty)。其中各属性的含义是：Orderid 订单编号，Bid 书籍编号，Qty 数量。 为丰富员工生活，该电商公司还设计了一个运动会管理系统，设计了如下关系模式： ATHLETE（ANO，ANAME，ASEX，ATEAM)；其中表和属性含义为：ATHLETE 为运动员，ANO 运动员号，ANAME 运动员名称，ASEX 性别，ATEAM队名。ANO为主键。 ITEM(INO ，INAME ，ITIME，IPLACE）；其中表和属性含义为：ITEM 为项目，INO 项目号 ，INAME 项目名 ，ITIME 比赛时，IPLACE 比赛场。INO为主键。 GAMES（ANO，INO，SCORRE）；其中表和属性含义为：GAMES 参赛，ANO 运动员号，INO 项目号，SCORRE 成绩。 有关关系模式的说明如下： （1）每张订单具有唯一的订单编号；每张订单编号中可包含多种书籍，但每种书籍的编号仅允许出现一次。请将下面的SQL语句空缺部分补充完整。 （2）运动员的性别取值为‘F’和‘M’ （F表示女性，M表示男性）。 （3）删除一个订单的记录时，通过外键约束级联删除该订单的所有订单明细记录。 （4）每当一个运动会ATHLETE表删除一个运动员后，他的参赛记录GAMES就自动删除。

【问题】根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。填充下列SQL程序1～6中的(1)～(15)，使它们分别完成相应的功能。（15分） 程序1：统计参加比赛时男运动员人数。 SELECT (1) FROM ATHLETE WHERE ASEX=’M’； 程序2：查100872号运动员参加的所有项目及其比赛时间和地点。 SELECT ITEM，INO，INAME，ITIME，IPLACE FROM GAMES，ITEM WHERE (2) ； AND (3) ； 程序3：查参加100035项目的所有运动员名单。 SELECT ANO，ANAME，ATEAM FROM ATHLETE WHERE (4) (SELECT (5) FROM GAMES WHERE GAMES.ANO=ATHLETE.ANO AND INO=’100035’)； 程序4：建立运动员成绩视图。 (6) ATHLETE-SCORE AS SELECT ATHLETE，ANO，ANAME，ATEAM，INAME，SCORE FORM (7) WHERE ATHLETE.ANO=GAMES. ANO AND （8）； 程序5，每当一个运动会ATHLETE表删除一个运动员后，他的参赛记录GAMES就自动删除。通过一个触发器来实现。语句如下： CREATE TRIGGER ATHLETE_GAMES_TRG AFTER DELETE ON （ 9 ） REFERENCING new row AS nrow, old row AS orow FOR EACH（ 10 ） BEGIN delete from GAMES where ano =（11）; END; 程序6，请将下面创建学生表的SQL语句补充完整，要求定义实体完整性约束、参照完整性 约束，以及其他完整性约束。 CREATE TABLE ORDERLIST( ORDERID CHAR(20) ON DELETE （12）， BID CHAR(6) （13）, QTY NUMBERIC(9), （14） (ORDERID,BID), （15） (ORDERID))；

答案：

1. COUNT(*)或count(1) 或 count(ANO) (2)GAMES.INO=ITEM.INO (3)GAMES.ANO=‘100872’(注：(2)、(3)可互换、INO无前缀得1分) (4)EXISTS (5)*或ANO或INO或SCORE或后3个列名的任意组合 注：(4)、(5)也可为 (4)ANO IN (5)ANO (6)CREATE VIEW (7)ATHLETE，ITEM，GAMES(3项可交换。) (8)GAMES.INO=ITEM.INO (9) ATHLETE (10) ROW (11) orow. ano (12) CASCADE (13) REFERENCES BOOKS (BID) (14) PRIMARY KEY (15) FOREIGN KEY(ORDERID) REFERENCES ORDERS

解析：程序1统计参加比赛的男运动员人数，也就是表ATHLETE中， AEX=’M’的记录的个数，所以要用到库函数COUNT(*)。程序2统计100872号运动员参加的所有项目及比赛时间和地点，所以SELECT后面的内容是项目编号ITEM.INO、项目名称INAME时间ITIME及地点IPLACE。统计涉及比赛表GAMES和项目表ITEM，所以FROM后面的内容为GAMES、ITEM。本题考的是连接查询，所谓连接查询指的是涉及两个以上的表的查询。由于是统计100872号运动员参加的所有项目及比赛时间和地点，所以查询条件中必然有 GAMES.INO=’100872’(程序中引用到字段时，若字段名在各个表中是唯一的，则可以把字段名前的表名去掉，否则，应当加上表名作为前缀，以免引起混淆)。由于GAMES表中只有比赛的成绩，那些关于项目的数据必须从项目表ITEM中取得，所以还应该有两个表之间的关联，即 GAMES.INO=TYEM.INO。所以填空2和3可交换，不影响查询结果。 程序3要求查参加100035项目的所有运动员名单。分析查询表达式，必须首先查询GAMES表，找出参加100035项目的那些运动员的编号ANO，即GAMES.ANO=ATHLETE.ANO AND INO=’100035’，然后再根据查询到的运动员号ANO从ATHLETE表中抽取运动员的数据。所以填空4的答案为“EXISTS”或“ANO IN”，填空5的答案为“ANO”。 程序4要求建立运动员成绩视图。建立视图的命令为CREATE VIEW，所以填空6的答案一定是“CREATE VIEW”。建立的是运动员成绩视图，那么一定涉及运动员情况、运动员参加的项目情况和该项目的成绩，所以要用到ATHLETE、ITEM和GAMES这3个表，因此FROM子句后为ATHLETE、GAMES、ITEM，3个表可以是任意次序，不影响结果。 程序5，每当一个运动会ATHLETE表删除一个运动员后，他的参赛记录GAMES就自动删除。通过一个触发器来实现。 CREATE TRIGGER ATHLETE_GAMES_TRG AFTER DELETE ON ATHLETE REFERENCING new row AS nrow, old row AS orow FOR EACH ROW BEGIN delete from GAMES where ano = orow. ano; END; 程序6：每张订单具有唯一的订单编号，所以订单编号即为“订单”实体的主键；每张订单编号中可包含多种书籍，但每种书籍的编号仅允许出现一次，所以其主键为(Orderid,Bid)。ORDERLIST 的主键为(Orderid, Bid) ， 所以在CREATE 语句中应有PRIMARY KEY(ORDERID,BID)；外键为ORDERID 和BID，分别参考“ORDER”中的ORDERID 和“BOOKS”中的BID，所以在ORDERLIST 的创建语句中还应有“REFERENCES BOOKS(BID) ” 和 “FOREIGN KEY(ORDERID) REFERENCES ORDERS(ORDERID)”。完整语句如下： CREATE TABLE ORDERLIST( ORDERID CHAR(20) ON DELETE CASCADE, BID CHAR(6) REFERENCES BOOKS (BID), QTY NUMBERIC(9), PRIMARY KEY (ORDERID,BID), FOREIGN KEY(ORDERID) REFERENCES ORDERS (ORDERID))

4.【说明】某公司实施电商平台系统，其中一个模块包括用户中心，设计了相应的数据库，其中一个表记录了用户基本信息，表结构如下： 用户信息（用户标识、用户姓名、手机号码、邮件地址、收货地址、登录方式、用户分类）。其中用户标识满足唯一性，用户分类包括买方和卖方等，不用的用户分类设置不同的登录方式。

【问题1】(7分) 用户信息表是否存在传递依赖？用100字以内文字说明理由。 【问题2】(8分) 用户信息表是否满足3NF?如果不满足，请对其进行模式分解，使分解后的关系模式满足3NF，并标记出主键和外键。分解后的关系模式用用户信息表1,2,3……。

答案：

【问题1】 用户信息表存在传递依赖。因为用户标识->用户分类，用户分类->登录方式。根据Armstrong公理系统的传递律规律规则，用户标识->登录方式。 【问题2】 不满足。 分解为二个关系模式，分别是： 用户信息表1（用户标识，用户姓名，手机号码，邮件地址，收货地址，用户分类）。主键是用户标识，外键是用户分类。 用户信息表1（用户分类，登录方式）。主键是用户分类，外键无。

解析：根据题干信息可以得到用户信息表的主键是用户标识，另外函数的依赖关系有：用户标识->用户分类，用户分类->登录方式。属于传递依赖。因为存在传递依赖关系，所以用户信息表不满足3NF，需要分解为二个关系模式，分别是： 用户信息表1（用户标识，用户姓名，手机号码，邮件地址，收货地址，用户分类）。主键是用户标识，外键是用户分类。用户信息表2（用户分类，登录方式）。主键是用户分类，外键无。分解后的关系模式都满足3NF。