2022年上半年中級(jí)數(shù)據(jù)庫系統(tǒng)工程師下午真題

2022年上半年中級(jí)數(shù)據(jù)庫系統(tǒng)工程師下午真題(總分：127.50，做題時(shí)間：150分鐘)一、試題一(總題數(shù)：1，分?jǐn)?shù)：15.00)1.

閱讀下列說明，回答問題1至問題3。

【說明】

某市為了規(guī)范疫苗接種工作，提升效率，并為抗擊疫情提供疫苗接種數(shù)據(jù)支撐，需要開發(fā)一個(gè)信息系統(tǒng)。請根據(jù)下述需求描述完成該系統(tǒng)的數(shù)據(jù)庫設(shè)計(jì)。

【需求描述】

(1)記錄疫苗供應(yīng)商的信息，包括供應(yīng)商名稱、地址和一個(gè)電話。

(2)記錄接種醫(yī)院的信息，包括醫(yī)院名稱、地址和一個(gè)電話。

(3)記錄被接種者個(gè)人信息，包括姓名、身份證號(hào)和一個(gè)電話。

(4)記錄接種者的疫苗接種信息，包括接種醫(yī)院信息、被接種者信息、疫苗供應(yīng)商名稱和接種日期。為了提高免疫力，接種者可能需要進(jìn)行多次疫苗接種(每天最多接種一次)，但每次都可以在全市任意一家醫(yī)院進(jìn)行疫苗接種。

【概念模型設(shè)計(jì)】

根據(jù)需求階段收集的信息，設(shè)計(jì)的實(shí)體聯(lián)系圖(不完整)如圖1所示。

圖1

【邏輯結(jié)構(gòu)設(shè)計(jì)】

根據(jù)概念模型設(shè)計(jì)階段完成的實(shí)體聯(lián)系圖，得出如下關(guān)系模式(不完整)：

供應(yīng)商(供應(yīng)商名稱，地址，電話)

醫(yī)院(醫(yī)院名稱，地址，電話)

供貨(供應(yīng)商名稱，(a)，供貨內(nèi)容)

被接種者(姓名，身份證號(hào)，電話)

接種(被接種者身份證號(hào)，(b)，醫(yī)院名稱，供應(yīng)商名稱)

（分?jǐn)?shù)：25.00）(1).

【問題1】

根據(jù)問題描述，補(bǔ)充圖1的實(shí)體聯(lián)系圖(不增加新的實(shí)體)。

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：()解析：(2).

【問題2】

補(bǔ)充邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果中的(a)、(b)兩處空缺，并標(biāo)注主鍵和外鍵完整性約束。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((a)醫(yī)院名稱(b)接種日期供應(yīng)商，主鍵：供應(yīng)商名稱醫(yī)院，主鍵：醫(yī)院名稱供貨，主鍵：供應(yīng)商名稱，醫(yī)院名稱；外鍵：供應(yīng)商名稱，醫(yī)院名稱被接種者，主鍵：被接種者身份證號(hào)接種，主鍵：被接種者身份證號(hào)，接種日期；外鍵：被接種者身份證號(hào)、醫(yī)院名稱、供應(yīng)商名稱)解析：(3).

【問題3】

若醫(yī)院還兼有核酸檢測的業(yè)務(wù)，檢測者可能需要進(jìn)行多次核酸檢測(每天最多檢測1次)，但每次都可以在全市任意一家醫(yī)院進(jìn)行檢測。

請?jiān)趫D1中增加“被檢測者”實(shí)體和相應(yīng)的屬性、醫(yī)院和被檢測者之間的“檢測”聯(lián)系和必要的屬性，并給出新增加的關(guān)系模式。

“被檢測者”實(shí)體包括姓名、身份證號(hào)、住址和一個(gè)電話?！皺z測”聯(lián)系需要包括檢測日期和檢測結(jié)果等。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：(被檢測者(姓名，身份證號(hào)，住址，電話)，主鍵：身份證號(hào)。檢測(被檢測者身份證號(hào)，醫(yī)院名稱，檢測日期，檢測結(jié)果)，主鍵：被檢測者身份證號(hào)、醫(yī)院名稱、檢測日期；外鍵：被檢測者身份證號(hào)、醫(yī)院名稱。)解析：二、試題二(總題數(shù)：1，分?jǐn)?shù)：15.00)2.閱讀下列說明，回答問題1至問題3。

【說明】

某卡丁車場地為了方便車手在線上查詢自己的圈速成績，設(shè)計(jì)了相應(yīng)的關(guān)系模型。模型中有三個(gè)表：

沖線記錄表(序列號(hào)，日期，沖線時(shí)刻，圈速，車號(hào)，組別，手機(jī)號(hào)，車手姓名)；

賽車表(車號(hào)，組別，車型)；

車手表(車手姓名，手機(jī)號(hào)，年齡，性別)。

其中序列號(hào)唯一確定一條沖線記錄，車型和組別可由車號(hào)確定，車手手機(jī)號(hào)唯一，車手姓名可重復(fù)。（分?jǐn)?shù)：25.00）(1).

【問題1】

沖線記錄表中是否含有數(shù)據(jù)冗余?如果存在冗余，請列出冗余屬性。

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：(沖線記錄表中含有數(shù)據(jù)冗余。包含的冗余屬性包括：組別，車手姓名。)解析：(2).

【問題2】

請分別給出沖線記錄表、賽車表和車手表的主碼和外碼。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：(沖線記錄表，主碼：序列號(hào)，外碼：車號(hào)、手機(jī)號(hào)賽車表，主碼：車號(hào)，外碼：無車手表表，主碼：手機(jī)號(hào)，外碼：無)解析：(3).

【問題3】

題干描述的沖線記錄表是否滿足3NF?如果不滿足，請給出導(dǎo)致不滿足3NF的函數(shù)依賴關(guān)系，并用50字以內(nèi)的文字簡要說明解決方案。

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：(沖線記錄表不滿足3NF。導(dǎo)致不滿足3NF的函數(shù)依賴關(guān)系有：序列號(hào)→車號(hào)，車號(hào)→組別，因此存在非主屬性組別傳遞依賴于主屬性序列號(hào)。序列號(hào)→手機(jī)號(hào)，手機(jī)號(hào)→車手姓名，因此存在非主屬性車手姓名傳遞依賴于主屬性序列號(hào)。解決方案：消除沖線記錄表中的傳遞依賴，也就是刪除沖線記錄表中的組別、車手姓名這兩個(gè)字段。)解析：三、試題三(總題數(shù)：1，分?jǐn)?shù)：15.00)3.閱讀下列說明，回答問題1至問題4。

【說明】

某工程項(xiàng)目管理系統(tǒng)的部分?jǐn)?shù)據(jù)庫關(guān)系模式如下：

項(xiàng)目：PROJECT(Jno，Jname，City，Date)，各屬性分別表示項(xiàng)目代碼、項(xiàng)目名稱、項(xiàng)目所在城市和項(xiàng)目開始日期；

零件：PART(Pno，Pname，Color，Sno，Stock)，各屬性分別表示零件代碼、零件名稱、零件顏色、零件所在倉庫代碼及庫存量；

供應(yīng)情況：PJ(Pno，Jno，Qty)，各屬性分別表示零件代碼、項(xiàng)目代碼、使用量；

倉庫：STORE(Sno，Sname，Address)，各屬性分別表示倉庫代碼、倉庫名稱、倉庫地址。

有關(guān)上述關(guān)系模式的說明如下：

(1)下劃線標(biāo)出的屬性是表的主鍵。

(2)零件表的屬性Sno參照了倉庫表的主碼。一種零件只存放在一個(gè)倉庫，庫存量最低為0。

(3)供應(yīng)情況表的屬性Pno和Jno分別參照了零件表和項(xiàng)目表的主碼。

根據(jù)以上描述，回答下列問題，將SQL語句的空缺部分補(bǔ)充完整。（分?jǐn)?shù)：26.25）(1).

【問題1】

請將下面創(chuàng)建零件表PART的SQL語句補(bǔ)充完整，要求定義實(shí)體完整性約束、參照完整性約束，以及其他完整性約束。

CREATE(a)PART(

PnoCHAR(10)(b)

PnameCHAR(20),

ColorCHAR(4),

SnoCHAR(4)REFERENCES(c)(Sno)

StockINT(d));

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：((a)table(b)primarykey(c)store(d)check(stock＞=0))解析：(2).

【問題2】

創(chuàng)建視圖PARTUSED，給出在項(xiàng)目中已使用零件的代碼和使用量。此視圖的創(chuàng)建語句如下，請補(bǔ)全。

CREATEVIEWPARTUSEDASSELECTPno,(e)ASUsageFROMPJ(f)BY(g);

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((e)sum(Qty)(f)group(g)Pno)解析：(3).

【問題3】

在視圖PARTUSED的基礎(chǔ)上，查詢所有零件的信息要求輸出每種零件的零件代碼、零件名、零件顏色和零件總量(使用量與庫存量之和)，查詢結(jié)果按照零件總量降序排列。此功能由下面的SQL語句實(shí)現(xiàn)，請補(bǔ)全。

SELECTPno，Pname,Color,(h)ASTotal

FROMPART,PART_USED

WHEREPART.Pno=PART_USED.Pno

(i)

SELECTPno,Pname,Color,StockASTotal

FROMPART

WHEREPno(j)

(SELECTDISTINCTPnoFROMPJ)

ORDERBYTotal(k)

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((h)Stock+Usage(i)unionall(j)notin(k)desc)解析：(4).

【問題4】

由于某種原因，要拆除代碼為'A006'的倉庫，該倉庫中的零件轉(zhuǎn)入'A002'倉庫存放。據(jù)此更新數(shù)據(jù)庫的功能由下面的SQL語句實(shí)現(xiàn)，請補(bǔ)全。

UPDATE(l)SET(m)WHERESno='A006';

(n)FROM(o)WHERESno='A006'

（分?jǐn)?shù)：3.75）__________________________________________________________________________________________

正確答案：((l)part(m)sno='A002'(n)delete(o)store)解析：四、試題四(總題數(shù)：1，分?jǐn)?shù)：15.00)4.閱讀下列說明，回答問題1至問題3。

【說明】

某銀行賬務(wù)系統(tǒng)的部分簡化后的關(guān)系模式如下：

賬戶表：accounts(a_no,a_name,a_status,a_balop,en_branch_no,open_branch_name,phone_no);屬性含義分別為:賬戶編碼、賬戶名稱、賬戶狀態(tài)(1-正常、2-凍結(jié)、3-掛失)、賬戶余額、開戶網(wǎng)點(diǎn)編碼、開戶網(wǎng)點(diǎn)名稱、賬戶移動(dòng)電話。

賬戶交易明細(xì)表:trade_details(t_date,optr_no,serial_no,t_branch,a_no,t_type,t_amt,t_result);屬性含義分別為:交易日期、操作員編碼、流水號(hào)、交易網(wǎng)點(diǎn)編碼、賬戶編碼、交易類型(1-存款、2-取款)、交易金額、交易結(jié)果(1-成功、2-失敗、3-異常、4-已取消)。

網(wǎng)點(diǎn)當(dāng)日余額匯總表:branch_sum(b_no,b_date,b_name,all_bal);屬性含義分別為:網(wǎng)點(diǎn)編碼、匯總?cè)掌?、網(wǎng)點(diǎn)名稱、網(wǎng)點(diǎn)開戶賬戶的總余額。

系統(tǒng)提供常規(guī)的賬戶存取款交易，并提供賬戶余額變更通知服務(wù)。該賬務(wù)系統(tǒng)是7*24h不間斷的提供服務(wù);網(wǎng)點(diǎn)當(dāng)日余額匯總操作一般在當(dāng)日晚上12點(diǎn)左右，運(yùn)維人員在執(zhí)行日終處理操作中完成。（分?jǐn)?shù)：25.00）(1).

【問題1】

下面是系統(tǒng)日終時(shí)生成網(wǎng)點(diǎn)當(dāng)日余額匯總數(shù)據(jù)的存儲(chǔ)過程程序，請補(bǔ)全空缺處的代碼。

CREATEPROCEDUREBranchBalanceSum(INsdatechar(8))

DECLARE

all_balancenumber(142);

V_brannovarchar(10);

v_brannamevarchar(30);

(a)c_sum_balIS

SELECTopen_branch_no,open_branch_name,sum(a_bal)

FROMaccountsGROUPBYopen_branch_no,open_branch_name;

BEGIN

OPENcsumbal;

LOOP

(b)c_sumbalINTOv_bran_no,_(c)_;

IFc_sum_bal%%NOTFOUNDTHEN//未找到記錄(d);

ENDIF;

INSERTINTObranch_sum

VALUES(v_bran_no,s_date,v_bran_name,all_balance);

ENDLOOP;

CLOSE_(e);COMMIT;

EXCEPTIONWHENOTHERSTHEN_(f)

END;

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：((a)cursor(b)fetch(c)v_bran_name,all_balance(d)exit(e)c_sum_bal(f)rollback)解析：(2).

【問題2】

當(dāng)執(zhí)行存取款交易導(dǎo)致用戶賬戶余額發(fā)生變更時(shí)，賬務(wù)系統(tǒng)需要給用戶發(fā)送余額變更短信通知。通知內(nèi)容為'某時(shí)間您的賬戶執(zhí)行了某交易，交易金額為XX元，交易后賬戶余額為XXX元'。默認(rèn)系統(tǒng)先更新賬戶表，后更新賬戶交易明細(xì)表。

下面是余額變更通知功能對應(yīng)的程序，請補(bǔ)全空缺處的代碼。

CREATETRIGGERBalanceNotice(g)INSERTon(h)(i)WHEN(i)=1

DECLARE

v_phonevarchar(30);

v_typevarchar(30)

v_balnumber(142)

v_msgvarchar(300);

BEGIN

SELECTphone_no,a_balINTOv_phone,v_balFROMaccounts

WHEREano=(k);

IFNEW.ttype=1THENv_type:='存款';ENDIF;

IFNEW.ttype=2THENv_type:=取款;ENDIF;

vmsg:=NEW.tdate||'，您的賬‘||NEWa_no||'上執(zhí)行了'v_type交易，交易金額為'||tostring(NEW.tamt)元，交易后賬戶余額為'||to_string(vbal)||'元';

SendMsg(v_phone，v_msg);//發(fā)送短信END;

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：((g)before(h)trade_details(i)foreachrow(j)t_resulte(k)NEW.a_no)解析：(3).

【問題3】

假設(shè)日終某網(wǎng)點(diǎn)當(dāng)日余額匯總操作和同一網(wǎng)點(diǎn)某賬戶取款交易同一時(shí)間發(fā)生，對應(yīng)事務(wù)的部分調(diào)度序列如下表所示。

(1)在事務(wù)提交讀隔離級(jí)別下，該網(wǎng)點(diǎn)的匯總和取款事務(wù)是否成功結(jié)束?

(2)如果該數(shù)據(jù)庫提供了多版本并發(fā)控制協(xié)議，兩個(gè)事務(wù)是否成功結(jié)束?

（分?jǐn)?shù)：5.00）__________________________________________________________________________________________

正確答案：(不能成功，事務(wù)提交讀隔離級(jí)別時(shí)，匯總事務(wù)讀取數(shù)據(jù)時(shí)先要加s鎖，并求到事務(wù)提交才釋放s鎖，而賬戶取款事務(wù)為寫操作，需要事先加X鎖，但此時(shí)無法加X鎖?？梢浴６喟姹静l(fā)控制，MVCC是一種并發(fā)控制的方法，一般在數(shù)據(jù)庫管理系統(tǒng)中，實(shí)現(xiàn)對數(shù)據(jù)庫的并發(fā)訪問。使用MVCC多版本并發(fā)控制比鎖定模型的主要優(yōu)點(diǎn)是在MVCC里,對檢索(讀)數(shù)據(jù)的鎖要求與寫數(shù)據(jù)的鎖要求不沖突，所以讀不會(huì)阻塞寫，而寫也從不阻塞讀。)解析：五、試題五(總題數(shù)：1，分?jǐn)?shù)：15.00)5.閱讀下列說明，回答問題1至問題4。

【說明】

某數(shù)據(jù)庫系統(tǒng)采用數(shù)據(jù)轉(zhuǎn)儲(chǔ)方式對數(shù)據(jù)和日志文件進(jìn)行離線備份，用檢查點(diǎn)機(jī)制進(jìn)行恢復(fù)。假設(shè)其部分日志文件如下表所示。日志記錄內(nèi)容中：CHECKPOINT表示檢查點(diǎn)，＜Ti，START＞表示事務(wù)Ti開始執(zhí)行，＜Ti，coMMIT＞表示事務(wù)Ti提交，＜Ti，D，V1，V2＞表示事務(wù)Ti將數(shù)據(jù)項(xiàng)D的值由V1改為V2，CRASH表明系統(tǒng)發(fā)生斷電故障。假設(shè)各數(shù)據(jù)項(xiàng)的初始值為：I=3J=6，K=11。

（分?jǐn)?shù)：26.25）(1).

【問題1】

系統(tǒng)發(fā)生故障時(shí)，滿足持久化要求的事務(wù)有哪些?不滿足持久化要求的事務(wù)有哪些?

（分?jǐn)?shù)：15.00）__________________________________________________________________________________________

正確答案：(滿足持