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