2025年計(jì)算機(jī)技術(shù)與軟件《中級(jí)數(shù)據(jù)庫(kù)系統(tǒng)工程師（下午卷）》試題（網(wǎng)友回憶版）含答案一、數(shù)據(jù)庫(kù)設(shè)計(jì)與ER模型轉(zhuǎn)換（共20分）某醫(yī)院擬開發(fā)門診管理系統(tǒng)，需求如下：患者信息包括：患者ID（唯一）、姓名、性別、年齡、聯(lián)系方式。醫(yī)生信息包括：醫(yī)生ID（唯一）、姓名、職稱、所屬科室?？剖倚畔ǎ嚎剖襂D（唯一）、科室名稱、科室位置、主任醫(yī)生ID（關(guān)聯(lián)醫(yī)生ID）。掛號(hào)記錄包括：記錄ID（唯一）、患者ID、醫(yī)生ID、掛號(hào)時(shí)間、掛號(hào)類型（普通/專家）、狀態(tài)（未支付/已支付/已取消）。檢查報(bào)告為弱實(shí)體集，依賴于掛號(hào)記錄，屬性包括：報(bào)告ID、檢查項(xiàng)目、檢查結(jié)果、檢查時(shí)間，其中報(bào)告ID不能唯一標(biāo)識(shí)檢查報(bào)告。（1）請(qǐng)用文字描述該系統(tǒng)的ER模型，包括實(shí)體、屬性及聯(lián)系（聯(lián)系需注明類型）。（6分）（2）將ER模型轉(zhuǎn)換為關(guān)系模式，要求注明主鍵（PK）和外鍵（FK）。（8分）（3）說(shuō)明弱實(shí)體集“檢查報(bào)告”的關(guān)系模式設(shè)計(jì)方法，并給出其主鍵。（6分）答案：（1）實(shí)體及屬性：患者（患者ID，姓名，性別，年齡，聯(lián)系方式）醫(yī)生（醫(yī)生ID，姓名，職稱）科室（科室ID，科室名稱，科室位置）掛號(hào)記錄（記錄ID，掛號(hào)時(shí)間，掛號(hào)類型，狀態(tài)）檢查報(bào)告（報(bào)告ID，檢查項(xiàng)目，檢查結(jié)果，檢查時(shí)間）聯(lián)系及類型：患者與掛號(hào)記錄：1:n（1個(gè)患者可有多條掛號(hào)記錄）醫(yī)生與掛號(hào)記錄：1:n（1個(gè)醫(yī)生可有多條掛號(hào)記錄）科室與醫(yī)生：1:n（1個(gè)科室有多個(gè)醫(yī)生）科室與主任醫(yī)生：1:1（1個(gè)科室有1個(gè)主任醫(yī)生，1個(gè)主任醫(yī)生僅負(fù)責(zé)1個(gè)科室）掛號(hào)記錄與檢查1:n（1條掛號(hào)記錄對(duì)應(yīng)多個(gè)檢查報(bào)告，檢查報(bào)告為弱實(shí)體集，依賴掛號(hào)記錄存在）（2）關(guān)系模式：患者（患者ID(PK)，姓名，性別，年齡，聯(lián)系方式）醫(yī)生（醫(yī)生ID(PK)，姓名，職稱，科室ID(FK，引用科室.科室ID)）科室（科室ID(PK)，科室名稱，科室位置，主任醫(yī)生ID(FK，引用醫(yī)生.醫(yī)生ID)）掛號(hào)記錄（記錄ID(PK)，患者ID(FK，引用患者.患者ID)，醫(yī)生ID(FK，引用醫(yī)生.醫(yī)生ID)，掛號(hào)時(shí)間，掛號(hào)類型，狀態(tài)）檢查報(bào)告（記錄ID(FK，引用掛號(hào)記錄.記錄ID)，報(bào)告ID(PK)，檢查項(xiàng)目，檢查結(jié)果，檢查時(shí)間）（注：弱實(shí)體集主鍵由標(biāo)識(shí)實(shí)體的主鍵+自身部分屬性組成，此處為記錄ID+報(bào)告ID）（3）弱實(shí)體集“檢查報(bào)告”無(wú)法通過(guò)自身屬性唯一標(biāo)識(shí)，需依賴父實(shí)體“掛號(hào)記錄”的主鍵（記錄ID）作為外鍵，其主鍵為父實(shí)體主鍵（記錄ID）與自身部分唯一屬性（報(bào)告ID）的組合，即（記錄ID，報(bào)告ID）。二、SQL語(yǔ)句編寫與數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)（共25分）基于第一題的數(shù)據(jù)庫(kù)，完成以下SQL操作：（1）查詢2025年1月所有“專家號(hào)”中，每個(gè)科室的掛號(hào)數(shù)量（要求顯示科室名稱、掛號(hào)數(shù)量，按數(shù)量降序排列）。（5分）（2）創(chuàng)建視圖V_Doctor_Reg，顯示醫(yī)生姓名、科室名稱、2025年1月的專家號(hào)掛號(hào)量（若未掛號(hào)則顯示0）。（6分）（3）編寫存儲(chǔ)過(guò)程Proc_GetRecentReg，輸入患者ID，輸出其最近3次掛號(hào)記錄（包含醫(yī)生姓名、科室名稱、掛號(hào)時(shí)間、狀態(tài)）。（7分）（4）設(shè)計(jì)觸發(fā)器Trg_ArchiveReg，當(dāng)掛號(hào)記錄的狀態(tài)更新為“已完成”時(shí)，自動(dòng)將該記錄插入歷史表Reg_History（結(jié)構(gòu)與掛號(hào)記錄表相同，增加歸檔時(shí)間字段）。（7分）答案：（1）```sqlSELECTd.科室名稱,COUNT()AS掛號(hào)數(shù)量FROM掛號(hào)記錄rJOIN醫(yī)生docONr.醫(yī)生ID=doc.醫(yī)生IDJOIN科室dONdoc.科室ID=d.科室IDWHEREr.掛號(hào)類型='專家'ANDr.掛號(hào)時(shí)間BETWEEN'20250101'AND'20250131'GROUPBYd.科室名稱ORDERBY掛號(hào)數(shù)量DESC;```（2）```sqlCREATEVIEWV_Doctor_RegASSELECTdoc.姓名AS醫(yī)生姓名,d.科室名稱,COALESCE(COUNT(r.記錄ID),0)AS專家號(hào)掛號(hào)量FROM醫(yī)生docLEFTJOIN科室dONdoc.科室ID=d.科室IDLEFTJOIN掛號(hào)記錄rONdoc.醫(yī)生ID=r.醫(yī)生IDANDr.掛號(hào)類型='專家'ANDr.掛號(hào)時(shí)間BETWEEN'20250101'AND'20250131'GROUPBYdoc.姓名,d.科室名稱;```（3）```sqlDELIMITER$$CREATEPROCEDUREProc_GetRecentReg(INp_patientIDVARCHAR(20))BEGINSELECTdoc.姓名AS醫(yī)生姓名,d.科室名稱,r.掛號(hào)時(shí)間,r.狀態(tài)FROM掛號(hào)記錄rJOIN醫(yī)生docONr.醫(yī)生ID=doc.醫(yī)生IDJOIN科室dONdoc.科室ID=d.科室IDWHEREr.患者ID=p_patientIDORDERBYr.掛號(hào)時(shí)間DESCLIMIT3;END$$DELIMITER;```（4）```sqlCREATETRIGGERTrg_ArchiveRegAFTERUPDATEON掛號(hào)記錄FOREACHROWBEGINIFNEW.狀態(tài)='已完成'THENINSERTINTOReg_History(記錄ID,患者ID,醫(yī)生ID,掛號(hào)時(shí)間,掛號(hào)類型,狀態(tài),歸檔時(shí)間)VALUES(OLD.記錄ID,OLD.患者ID,OLD.醫(yī)生ID,OLD.掛號(hào)時(shí)間,OLD.掛號(hào)類型,OLD.狀態(tài),NOW());ENDIF;END;```三、關(guān)系模式規(guī)范化（共20分）某醫(yī)院處方管理初始關(guān)系模式為：處方（處方ID，患者ID，患者姓名，醫(yī)生ID，醫(yī)生姓名，科室ID，科室名稱，藥品ID，藥品名稱，數(shù)量，單價(jià)，總金額）已知函數(shù)依賴：處方ID→患者ID，患者ID→患者姓名處方ID→醫(yī)生ID，醫(yī)生ID→醫(yī)生姓名，醫(yī)生ID→科室ID，科室ID→科室名稱（處方ID，藥品ID）→數(shù)量藥品ID→藥品名稱，藥品ID→單價(jià)總金額=數(shù)量×單價(jià)（推導(dǎo)依賴）（1）分析該模式存在的冗余和更新異常問(wèn)題。（6分）（2）判斷該模式屬于第幾范式（NF），說(shuō)明理由。（6分）（3）將其分解為3NF，要求無(wú)損連接且保持函數(shù)依賴。（8分）答案：（1）冗余問(wèn)題：患者姓名、醫(yī)生姓名、科室名稱、藥品名稱、單價(jià)等屬性會(huì)因處方中多次使用同一患者/醫(yī)生/藥品而重復(fù)存儲(chǔ)。更新異常：修改異常：修改患者姓名需更新所有該患者的處方記錄，否則數(shù)據(jù)不一致。插入異常：新增藥品（未提供處方）時(shí)，無(wú)法插入藥品ID、名稱、單價(jià)到處方表。刪除異常：刪除某處方若為某藥品的唯一記錄，會(huì)丟失該藥品的基本信息。（2）屬于1NF，不屬于2NF。理由：主鍵為（處方ID，藥品ID），存在部分函數(shù)依賴：處方ID→患者ID（非主屬性患者ID部分依賴于主鍵）、醫(yī)生ID→醫(yī)生姓名（非主屬性醫(yī)生姓名通過(guò)醫(yī)生ID部分依賴于主鍵）、藥品ID→藥品名稱（非主屬性藥品名稱部分依賴于主鍵）。部分依賴違反2NF要求。（3）分解步驟：①分解出患者信息：患者（患者ID(PK)，患者姓名）②分解出醫(yī)生信息：醫(yī)生（醫(yī)生ID(PK)，醫(yī)生姓名，科室ID(FK)）③分解出科室信息：科室（科室ID(PK)，科室名稱）④分解出藥品信息：藥品（藥品ID(PK)，藥品名稱，單價(jià)）⑤保留處方與藥品的關(guān)聯(lián)：處方藥品（處方ID(PK)，藥品ID(PK)，數(shù)量，總金額）（總金額由數(shù)量×單價(jià)推導(dǎo)，可保留或計(jì)算時(shí)提供）⑥處方主表：處方主（處方ID(PK)，患者ID(FK)，醫(yī)生ID(FK)）驗(yàn)證：無(wú)損連接：分解后的關(guān)系模式通過(guò)主鍵/外鍵關(guān)聯(lián)，可通過(guò)自然連接恢復(fù)原關(guān)系。保持函數(shù)依賴：所有原始函數(shù)依賴在分解后的模式中均被保留（如患者ID→患者姓名在患者表，醫(yī)生ID→科室ID在醫(yī)生表等）。四、事務(wù)管理與并發(fā)控制（共20分）某數(shù)據(jù)庫(kù)系統(tǒng)中，事務(wù)T1和T2并發(fā)操作掛號(hào)記錄，相關(guān)操作如下：T1：讀取患者A的掛號(hào)狀態(tài)（S1）→若為“未支付”，則更新為“已支付”（U1）。T2：讀取患者A的掛號(hào)狀態(tài)（S2）→若為“未支付”，則更新為“已取消”（U2）。（1）假設(shè)T1和T2按S1→S2→U1→U2順序執(zhí)行，分析可能出現(xiàn)的并發(fā)問(wèn)題。（5分）（2）若采用封鎖協(xié)議，設(shè)計(jì)T1和T2的讀寫鎖（S鎖/X鎖）獲取與釋放順序，避免上述問(wèn)題。（7分）（3）若數(shù)據(jù)庫(kù)采用時(shí)間戳調(diào)度（T1時(shí)間戳100，T2時(shí)間戳200），判斷T1和T2的執(zhí)行順序，說(shuō)明沖突操作的處理方式。（8分）答案：（1）丟失修改問(wèn)題。T1和T2均讀取到“未支付”狀態(tài)（S1和S2），T1先更新為“已支付”（U1），但T2后續(xù)更新為“已取消”（U2），覆蓋了T1的修改，導(dǎo)致T1的修改丟失。（2）采用二級(jí)封鎖協(xié)議（或嚴(yán)格兩階段封鎖）：T1執(zhí)行S1前申請(qǐng)X鎖（因需更新，直接加X(jué)鎖），讀取后不釋放（保持到事務(wù)結(jié)束），執(zhí)行U1后釋放X鎖。T2執(zhí)行S2前申請(qǐng)X鎖，若T1已持有X鎖則等待，直到T1釋放后獲取X鎖，讀取并執(zhí)行U2后釋放。具體順序：T1：LOCKX(患者A)→S1→U1→UNLOCKX(患者A)T2：等待T1釋放X鎖→LOCKX(患者A)→S2→U2→UNLOCKX(患者A)（或T1先加X(jué)鎖，T2因無(wú)法獲取X鎖而阻塞，避免并發(fā)修改）（3）時(shí)間戳調(diào)度中，事務(wù)按時(shí)間戳順序執(zhí)行，T1（100）早于T2（200）。沖突操作：T1的寫（U1）與T2的讀（S2）、T1的寫（U1）與T2的寫（U2）均為沖突操作。處理方式：T1執(zhí)行S1（讀）時(shí)，檢查T2的寫時(shí)間戳（初始為0），允許讀取，更新T1的讀時(shí)間戳為100。T1執(zhí)行U1（寫）時(shí)，檢查T2的讀時(shí)間戳（若T2未讀則為0），允許寫入，更新患者A的寫時(shí)間戳為100。T2執(zhí)行S2（讀）時(shí)，患者A的寫時(shí)間戳（100）<T2時(shí)間戳（200），允許讀取，更新T2的讀時(shí)間戳為200。T2執(zhí)行U2（寫）時(shí)，患者A的寫時(shí)間戳（100）<T2時(shí)間戳（200），但T2的寫操作會(huì)覆蓋T1的寫，需檢查是否有更年輕的事務(wù)已讀該數(shù)據(jù)。若T2的寫時(shí)間戳（200）>患者A的當(dāng)前寫時(shí)間戳（100），允許寫入，更新寫時(shí)間戳為200。最終T2的修改生效，T1的修改被覆蓋（符合時(shí)間戳順序）。五、索引設(shè)計(jì)與性能優(yōu)化（共15分）某數(shù)據(jù)庫(kù)查詢語(yǔ)句如下：```sqlSELECTP.患者姓名,D.醫(yī)生姓名,R.掛號(hào)時(shí)間FROM掛號(hào)記錄RJOIN患者PONR.患者ID=P.患者IDJOIN醫(yī)生DONR.醫(yī)生ID=D.醫(yī)生IDWHERER.掛號(hào)時(shí)間BETWEEN'20250101'AND'20250131'ANDD.科室ID=101ORDERBYR.掛號(hào)時(shí)間DESC;```（1）分析當(dāng)前查詢可能的性能瓶頸。（5分）（2）建議創(chuàng)建哪些索引（包括索引類型和列順序），并說(shuō)明理由。（7分）（3）說(shuō)明覆蓋索引在此查詢中的作用，是否適用？（3分）答案：（1）性能瓶頸：掛號(hào)記錄表（R）的WHERE條件涉及“掛號(hào)時(shí)間”范圍查詢和醫(yī)生表（D）的“科室ID”等值查詢，若未建立索引，需全表掃描。連接操作（R與P、R與D）需逐行匹配患者ID和醫(yī)生ID，無(wú)索引時(shí)連接效率低。ORDERBY“掛號(hào)時(shí)間”需排序，無(wú)索引時(shí)可能使用文件排序（filesort），消耗額外資源。（2）建議索引：對(duì)掛號(hào)記錄表創(chuàng)建復(fù)合B+樹索引（科室ID,掛號(hào)時(shí)間DESC）：理由：醫(yī)生表通過(guò)科室ID=101過(guò)濾后，需關(guān)聯(lián)掛號(hào)記錄的醫(yī)生ID，可將醫(yī)生表的科室ID與掛號(hào)記錄的醫(yī)生ID關(guān)聯(lián)，轉(zhuǎn)化為掛號(hào)記錄中醫(yī)生對(duì)應(yīng)的科室ID=101。但更直接的是，在掛號(hào)記錄表中，通過(guò)醫(yī)生ID關(guān)聯(lián)醫(yī)生表后，實(shí)際需篩選科室ID=101的醫(yī)生對(duì)應(yīng)的掛號(hào)記錄。因此，可在醫(yī)生表創(chuàng)建（醫(yī)生ID,科室ID）索引，快速獲取科室ID=101的醫(yī)生ID列表；同時(shí)在掛號(hào)記錄表創(chuàng)建（醫(yī)生ID,掛號(hào)時(shí)間DESC）索引，加速根據(jù)醫(yī)生ID篩選和排序?；蚋鼉?yōu)化的復(fù)合索引：掛號(hào)記錄表（醫(yī)生ID,掛號(hào)時(shí)間）INCLUDE（患者ID），其中醫(yī)生ID用于關(guān)聯(lián)醫(yī)生表并篩選科室ID=101，掛號(hào)時(shí)間用于范圍查詢和排序，患者ID用于關(guān)聯(lián)患者表?；颊弑韯?chuàng)建（患者ID）主鍵索引（已存在，無(wú)需額外創(chuàng)建）。醫(yī)生表創(chuàng)建（科室ID,醫(yī)生ID）索引：快速獲取科室ID=101的所有醫(yī)生ID，減少醫(yī)生表的掃描范圍。（3）覆蓋索引作用：若索引包含查詢所需的所有列，可避免回表查詢，直接從索引中獲取數(shù)據(jù)。此查詢中，若掛號(hào)記錄表的索引包含（醫(yī)生ID,掛號(hào)時(shí)間,患者ID），則可通過(guò)索引獲取連接所需的患者ID和醫(yī)生ID，以及排序字段掛號(hào)時(shí)間，無(wú)需訪問(wèn)數(shù)據(jù)行，提升效率。因此適用覆蓋索引。六、數(shù)據(jù)安全與備份恢復(fù)（共20分）某醫(yī)院數(shù)據(jù)庫(kù)需加強(qiáng)安全與備份管理，需求如下：限制“門診管理員”角色僅能查詢、更新掛號(hào)記錄，插入新掛號(hào)。數(shù)據(jù)庫(kù)需支持故障后恢復(fù)到最近10分鐘的狀態(tài)。（1）寫出創(chuàng)建“門診管理員”角色并授權(quán)的SQL語(yǔ)句。（5分）（2）設(shè)計(jì)備份策略（包括完全備份、差異備份、