2018
阅读下列说明，回答问题1至问题4，将解答填入答题纸的对应栏内。
【说明】
某汽车租赁公司建立汽车租赁管理系统，其数据库的部分关系模式如下：
用户： USERS（ Userid，Name， Balance），各属性分别表示用户编号、姓名、余额；
汽车：CARS（Cid， Ctype， CPrice，CStatus）各属性分别表示汽车编号、型号、价格（日租金）、状态；
租用记录： BORROWS（BRid， Userid，Cid， STime， ETime），各属性分别表示租用编号、用户编号、汽车编号、租用时间、归还时间；
不良记录：BADS（Bid， Userid．BRid， BTime），各属性分别表示不良记录编号、用户编号、租用编号、不良记录时间。
相关关系模式的属性及说明如下
（1）用户租用汽车时，其用户表中的余额不能小于500，否则不能租用。
（2）汽车状态为待租和已租，待租汽车可以被用户租用，已租汽车不能租用。
（3）用户每租用一次汽车，向租用记录中添加一条租用记录，租用时间默认为系统当前时间，归还时间为空值，并将所租汽车状态变为已租。用户还车时，修改归还时间为系统当前时间，并将该汽车状态改为待租。要求用户不能同时租用两辆及以上汽车.
（4）租金从租用时间起按日自动扣除.
根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。
【问题1】（4分）
请将下面建立租用记录表的SQL语句补充完整，要求定义主码完整性约束和引用完整性约束。
CREATE TABLE BORROWS(
BRID CHAR(20) (a) ,
UserId CHAR(10) (b) ,
Cld CHAR(10) (c) ,
STime DATETIME (d) ,
ETime DATETIME,
);
【问题2】（4分）
当归还时间为空值时，表示用户还未还车，系统每天调用事务程序从用户余额中自动扣除当日租金，每个事务修改一条用户记录中的余额值。由用户表上的触发器实现业务：如用户当日余额不足，不扣除当日租金，自动向不良记录表中加入一条记录，记录中的BId取值由 UserId＋系统当前日期构成， Btime采用 GETDATE()函数取系统当前时间。补全创建触发器Bad_TRG的SQL语句。
CREATE TRIGGER Bad_TRG (e) UPDATE of Balance ON USERS
Referencing new row as nrow
For each row
When nrow.Balance< 0
BEGIN
(f) ;
//插入不良记录
INSERT INTO BADS
SELECT CONCAT(BORROWs. UserId, CONVERT(varchar(100),
GETDATE(), 10)), BORROWS UserId, BRID, (g)
// CONVERT()函数将日期型数据改为字符串型
// CONCAT()函数实现字符串拼接
FROM BORROWS
WHERE (h) AND ETime IS NULL;
END
【问题3】（4分）
不良记录是按日记录的，因此用户一次租车可能会产生多条不良记录。创建不良记录单视图 BADS_Detail，统计每次租车产生的不良记录租金费用总和大于200的记录，属性有 UserId、Name、BRId、CId、 Stime、 Etime和 total（表示未缴纳租金总和）。补全创建视图 BADS_Detail的SQL语句。
CREATE VIEW (i) AS
SELECT BADS. UserId, USERS. Name, BADS.BRId, CARS. Cld, Stime, Etime,
(j) AS total
FROM BORROWS，BADS， CARS， USERS
WHERE BORROWS.BRId=BADS. BRId
AND BORROWS.Cid=CARS. Cld
AND (k) =BADS. UserId
GROUP BY BADS. UserId, USERS. Name, BADS.BRID, CARS. CId, Stime, Etime
HAVING (l) ;
【问题4】（3分）
查询租用了型号为“A8”且不良记录次数大于等于2的用户，输出用户编号、姓名，并按用户姓名降序排序输出。
SELECT USERS. UserId, Name
FROM USERS，BORROWS, CARS
WHERE USERS. UserId= BORROWS. UserId AND BORROWS.Cid= CARS. CId
AND (m) AND EXISTS(
SELECT FROM BADS
WHERE BADS. UserId=BORROWS.UserId AND (n)
GROUP BY UserId
HAVING COUNT()>= 2)
ORDER BY （O） ；
答案与解析
试题难度：较难
知识点：SQL语言>其它
试题答案：
【问题1】
(a) PRIMARY KEY
(b) REFERENCES USERS(UserId)
(c) REFERENCES CARS(CId)
(d) DEFAULT GETDATE()
【问题2】
(e) after
(f) rollback
(g) GETDATE()
(h) BORROWS.UserId=
nrow.UserId
【问题3】
(i) DADS_Detail（userid,name,brid,cid,stime,etime,toltal)
(j) sum(cars.CPrice)
(k) USERS.UserId
(l) sum(CARS.CPrice) > 200
【问题4】
(m)
CARS.Ctype = ‘A8 ‘
(n)
BADS.BRId = BORROWS.BRId
(o) Name DESC
</div>
试题解析：暂无解析。
2019
阅读下列说明，回答问题1至问题3,将解答填入答题纸的对应栏内。
[说明]
某商业银行账务系统的部分关系模式如下:
账户表: Account (ano, aname, balance), 其中属性含义分别为:账户号码，账户名称和账户余额。
交易明细表: TranDetails (tno, ano, time, toptr, amount, ttype)， 其中属性分别为:
交易编号，账户号码，交易时间，交易操作员，交易金额，交易类型(1-存款，2-取款，3-转账)。
余额汇总表: AcctSums (adate， atime, allamt)， 其中属性分别为:汇总日期，汇总时间，总余额。
常见的交易规则如下:
存/取款交易:操作员核对用户相关信息，在系统上执行存/取款交易。账务系统增加/减少该账户余额，并在交易明细表中增加一条存/取款交易明细。
转账交易:操作员核对用户相关信息，核对转账交易账户信息，在系统上执行转账交易。账务系统对转出账户减少其账户余额，对转入账户增加其账户余额，并在交易明细表
中增加一条转账交易明细。
余额汇总交易:将账户表中所有账户余额累计汇总。
假定当前账户表中的数据记录如表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 rror/l/ error是由DBMS提供的上-句SQL的执行状态
BEGIN
ROLLBACK;
return -2;
END
INSERT INTO AcctSums
VALUES ( getDATE(，getTIME(),_ (b) );
ifrror1/error是由DBMS提供的上一句SQL的执行状态
BEGIN
ROLLBACK;
return -3;
END
END
[问题2] (8 分)
引入排它锁指令LX0)和解锁指令UX0,要求满足两段锁协议和提交读隔离级别。假
设在进行余额汇总交易的同时，发生了一笔转账交易。从101账户转给104账户400元。
这两笔事务的调度如表5-2所示。
1)请补全表中的空缺处(a)、(b);
(2)上述调度结束后，汇总得到的总余额是多少?
(3)该数据是否正确?请说明原因。
[问题3] (4分)
在[问题2]的基础上，引入共享锁指令LSO和解锁指令USO。对[问题2]中的调
度进行重写，要求满足两段锁协议。两个事务执行的某种调度顺序如表5-3所示，该调度
顺序使得汇总事务和转账事务形成死锁。请补全表中的空缺处(a)、(b)。
2020
阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。
【说明】
某网上销售系统的部分关系模式如下：
订单表：orders（o_no, o_date, o_time, p_no, m no, p_price, nums, amt, status）。其中属性含义分别为：订单号、订单日期、订单时间、产品编码、供应商编码、产品价格、产品数量、订单金额、订单状态（0-未处理、1-已处理、 2-已取消）。
产品表：products（p_no, p_name, p_type, price, m_no, p_nums）。其中属性含义分别为：产品编码、产品名称、产品类型、产品价格、供应商编码、库存数量。
【问题1】(5分)
节假日时，由供应商提供商品打折后的新价格，数据存放在临时表中，该临时表的表名为tmp_prices（不同供应商有不同的临时表），其关系模式如下：
后台维护人员需要根据供应商填写在tmp prices中的数据来更新产品表中某些产品的价格。下面是基于游标，用SQL实现的价格更新程序，请补全空缺处的代码。
【问题2】(6分)
假设用户1和用户2同时购买1份A商品，用户3查询和浏览A商品。三个用户对应事务的部分调度序列如表4-1所示（事务中未进行并发控制），其中TO时刻该A商品的库存数量p_nums为100。
请说明T4、T7时刻，用户3事务读取到的p_nums 数值分别是多少。请说明T8时刻事务调度结果是否正确？若不正确请说明属于哪一种数据不一致性。
【问题3】(4分)
为保证并发事务的正确性，系统要求所有事务需遵循两段锁协议。
（1）请用100字以内的文字简要解释两段锁协议，并说明“两段”的含义。
（2）请说明两段锁协议是否可以避免死锁?如不能避免，应采取什么措施解决死锁问题。
答案与解析
试题难度：一般
知识点：事务管理>并发操作及问题
试题答案：
【问题1】
（a）cursor
（b）open
（c）Pno, Pprice, Mno
（d）commit
【问题2】
T4时刻，p_nums的值为100。
T7时刻，p_nums的值为99。
事务调度结果不正确。
丢失修改。
【问题3】
（1）两段锁协议是指对任何数据进行读写之前必须对数据加锁；在释放一个封锁之后，事务不再申请和获得任何其他锁。
“两段”的含义是：事务分为两个阶段，第一阶段是获得封锁，称为扩展阶段；第二阶段是释放封锁，称为收缩阶段。
（2）两段锁协议不能避免死锁。
解决措施是采用死锁检测机制，发现后按照一定算法解除死锁。
2021
阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。
【说明】
某企业网上书城系统的部分关系模式如下:
书籍信息表: books(book no, book name, press no, ISBN, price, sale type, all nums),其中属性含义分别为:书籍编码、书籍名称、出版商编码、ISBN、 销售价格、销售分类、当前库存数量:
书籍销售订单表: orders(order no, book no, book nums, book price, order date,amount)，其中属性分别为:订单编码、书籍编码、书籍数量、书籍价格、订单日期和总金额。
书籍再购额度表: booklimit(book no, sale_ type, limitamount),其中属性含义分别为:
书籍编码、销售分类、再购额度；
书籍最低库存表: bookminlevel(book no, leve) ，其中属性含义分别为:书籍编码，书籍最低库存数量；
书籍采购表: bookorders(book no, order. _amount)，其中属性含义分别为:书籍编码和采购数量。
有关关系模式的说明如下:
(1)下划线标出的属性是表的主码。
(2)根据书籍销售情况来确定书籍的销售分类：销售数量小于1万的为普通类型，其值为0；1万及以上的为热销类型，其值为1。
(3)系统具备书籍自动补货功能，涉及到的关系模式有:书籍再购额度表、书籍最低库存表、书籍采购表。其业务逻辑是:当某书籍库存小于其最低库存数量时，根据书籍的销售分类以及书籍再购额度表中的再购额度，生成书籍采购表中的采购订单，完成自动补货操作。
【问题1】(5分)
系统定期扫描书籍销售订单表，根据书籍总的销售情况来确定书籍的销售类别。下面是系统中设置某书籍销售类别的存储过程，结束时需显式提交返回。请补全空缺处的代码。
CREATE PROCEDURE UpdateBookSaleType(IN bno varchar(20))
DECLARE
all_nums number(6);
BEGIN
SELECT (a) (book_nums) INTO all_nums FROM orders
WHERE book_no = (b) ;
IF all_nums < (c) THEN
UPDATE books SET sale_type = 0 WHERE book_no = bno;
ELSE
UPDATE books SET sale_type = (d) WHERE book_no = bno;
END IF;
(e) ;
END;
【问题2】 (6分)
下面是系统中自动补货功能对应的触发器，请补全空缺处的代码。
CREATE TRIGGER BookOrdersTrigger （f） update
of (g) on books
(h)
WHEN (i) <(SELECT level FROM bookminlevel
WHERE bookminlevel.book_no = OLD.book_no)
AND (j) >=(SELECT level FROM bookminlevel
WHERE bookminlevel.book_no = OLD.book_mo)
BEGIN
INSERT INTO (k)
(SELECT book_no,limit_amount
FROM booklimit as TMP
WHERE TMP.book_no = OLD.book_no
AND TMP.sale_type = OLD.sale_type);
END;
【问题3】 (4分)
假设用户1和用户2同时购买同一书籍，对应事务的部分调度序列如表4-1所示(事务中未进行并发控制)，其中T0时刻该书籍的库存数量all nums=500。
请说明T4时刻，用户2事务读取到的all nums数值是多少?请说明T8时刻，all nums数据是否出现不一致性问题?如出现，请说明属于哪一种数据不一致性。
答案与解析
试题难度：一般
知识点：事务管理>并发操作设计
试题答案：
【问题1】
a: sum
b:bno
c:10000
d:1
e:commit
【问题2】
f： after
g： all_nums
h: for each row
i: NEW.all_nums
j: OLD.all_nums
k: bookorders
【问题3】
说明T4时刻，用户2事务读取到的all_nums数值是498。
在T8时刻，all_nums数据会出现不一致性的问题，由于用户2事务读到了用户1修改过的all_nums,然后在T7时刻用户1事务回滚了之前对all_nums的修改，把all_nums恢复到了500。最终用户2事务读到的数据是498，读到的是脏数据。所以是属于读脏数据的不一致。
2022
【51CTO学院-学员回忆版】试题四（共15分)
阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。
【说明】
某银行账务系统的部分简化后的关系模式如下：
账户表： accounts(a_no, a_name,a_status, a_bal, open_branch_no,open_branch_ name,phone_no)；属性含义分别为:账户编码、账户名称、账户状态(1-正常、2-冻结、3-挂失)、账户余额、开户网点编码、开户网点名称、账户移动电话。
账户交易明细表:trade_details(t_date, optr_no, serial_no, t_branch,a_no,t_type,t_amt,t_result);属性含义分别为:交易日期、操作员编码、流水号、交易网点编码、账户编码、交易类型(1-存款、2-取款)、交易金额、交易结果(1-成功、2-失败、3-异常、4-已取消)。
网点当日余额汇总表:branch_sum(b_no, b_date, b_name, all_bal);属性含义分别为:网点编码、汇总日期、网点名称、网点开户账户的总余额。
系统提供常规的账户存取款交易,并提供账户余额变更通知服务。该账务系统是7*24h不间断的提供服务;网点当日余额汇总操作一般在当日晚上12点左右，运维人员在执行日终处理操作中完成。
【问题1】(6分)
下面是系统日终时生成网点当日余额汇总数据的存储过程程序,请补全空缺处的代码。
CREATE PROCEDURE BranchBalanceSum(IN s date char( 8)）
DECLARE
all_balancenumber(14,2);
v_bran_no varchar(10);
v_bran name varchar(30);
（a)c_sum_bal IS
SELECT open_branch_no, open_branch_name, sum(a_bal)
FROM accounts GROUP BY open_branch_no, open_branch_name;
BEGIN
OPEN c sum bal;
LOOP
(b)c_sum bal INTO v_bran no,(c)_；
IF c_sum_bal%%NOTFOUND THEN //未找到记录
(d)；
END IF;
INSERT INTO branch_sum
VALUES(v_bran_no,s_date, v_bran_name, all_balance);
END LOOP；
CLOSE_ (e）;
COMMIT;
EXCEPTION WHEN OTHERS THEN _ (f）
END;
【问题2】（5分）
当执行存取款交易导致用户账户余额发生变更时，账务系统需要给用户发送余额变更短信通知。通知内容为"某时间您的账户执行了某交易，交易金额为XX元，交易后账户余额为XXX元"。默认系统先更新账户表，后更新账户交易明细表。
下面是余额变更通知功能对应的程序，请补全空缺处的代码。
CREATE TRIGGER BalanceNotice(g) INSERT on(h) (i)
WHEN(j)=1
DECLARE
v_phonevarchar(30);
v_type varchar(30);
v_bal number(14,2);
v_msg varchar(300);
BEGIN
SELECT phone_no,a_bal INTO v_phone,v_bal FROM accounts
WHERE a_no =(k)；
IF NEW.t_type=1 THEN v_type ∶='存款';END IF;
IFNEW.t_type=2 THEN v_type ∶='取款';ENDIF;
v_msg∶=NEW.t_date ||'，您的账户'||NEW.a_no||'上执行了'||v_type||'交易，交易金额为"||to_string（NEW.t_amt)||'元，交易后账户余额为'||to_string（v_bal）||'元';
SendMsg（v_phone，v_msg）; // 发送短信
END;
【问题3】（4分）
假设日终某网点当日余额汇总操作和同一网点某账户取款交易同一时间发生，对应事务的部分调度序列如表4-1 所示。
（1）在事务提交读隔离级别下，该网点的汇总和取款事务是否成功结束?
（2）如果该数据库提供了多版本并发控制协议，两个事务是否成功结束?
你的答案:
1
未作答
参考答案:
【问题1】【问题2】
a cursor
b fetch
c v_bran_name, all_balance
d exit
e c_sum_bal
f rollback
g before
h trade_details
i for each row
j t_resulte
k NEW.a_no
【问题3】
不能成功，事务提交读隔离级别时，汇总事务读取数据时先要加S锁，并求到事务提交才释放S锁，而 账户取款事务 为写操作，需要事先加X锁，但此时无法加X锁。
可以。多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。使用MVCC多版本并发控制比锁定模型的主要优点是在MVCC里， 对检索（读）数据的锁要求与写数据的锁要求不冲突， 所以读不会阻塞写，而写也从不阻塞读。
2023
【说明】
某企业内部信息系统部分简化后的关系模式如下：
员工表：EMPLOYEES(Eid, Ename, Address, Phone, Jid)：属性含义分别为：员工编码、员工姓名、家庭住址、联系电话、岗位级别编码。
岗位级别表：JOB_LEVELS(Jid, Jname, Jbase_salary)：属性含义分别为：岗位级别编码、岗位名称、岗位基本工资。
员工工资表：SALARY(Eid, attendance_wage, merit_pay, overtime_wage, salary, tax, year, month)：属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。
业务规则：
该企业在每月 25 日计算员工的工资。首先是根据考勤系统以及绩效系统中的数据，计算出员工的考勤、绩效和加班工资，存入到员工工资表；其次结合员工的岗位基本工资，计算出最终工资，完成对员工工资表记录的更新。最后依据员工工资表完成工资的发放。
【问题 1】(6 分)
下面是月底 25 日计算某员工最终工资的存储过程程序，请补全空缺处的代码。
CREATE PROCEDURE SalaryCalculation((a) empId char(8), IN iYear number(4), IN iMonth number(2))DECLARE
attendance number(14,2);
merit number(14,2);
overtime number(14,2);
base number(14,2);
all_salary number(14,2);BEGIN
SELECT attendance_wage, merit_pay, overtime_wage INTO (b)
FROM SALARY
WHERE Eid = empId FOR UPDATE;
SELECT Jbase_salary INTO :base
FROM EMPLOYEES T1, (c)
WHERE T1.Jid = T2.Jid AND T1.Eid = empId;
all_salary := attendance + merit + overtime + base;
UPDATE SALARY
SET salary = :all_salary
WHERE (d) AND year = iYear AND month = iMonth;
(e);
EXCEPTION
WHEN OTHERS THEN
(f);END;
【问题 2】(5 分)
为了防止对员工工资表的非法修改（包括内部犯罪），系统特意规定了员工工资表修改的业务规则：对员工工资表的修改只能在每月 25 日的上班时间进行。
下面是员工工资表修改业务规则对应的程序，请补全空缺处的代码。
CREATE TRIGGER CheckBusinessRule(g) INSERT OR DELETE OR (h) ON SALARYFOR EACH (i)BEGIN
IF (TO_CHAR(sysdate, 'DD') <> (j))
OR (to_number(TO_CHAR(sysdate, 'HH24')) (k) BETWEEN 8 AND 18) THEN
Raise_Error; -- 抛出异常
END IF;END;
【问题 3】(4 分)
人事部门具有每月对员工进行额外奖罚的权限，该奖罚也反应到员工的最终工资上。假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励 2000 元的事务操作，对应事务的部分调度序列如表 4-1 所示。
请说明该事务调度存在哪种并发问题？
采用 2PL 是否可以解决该并发问题？是否会产生死锁？