2025年山東科技大學數(shù)據(jù)庫課后習題答案1.關系代數(shù)與SQL綜合應用題已知學生表S（SnoCHAR(8),SnameVARCHAR(20),SageINT,SdeptVARCHAR(20)），課程表C（CnoCHAR(4),CnameVARCHAR(30),CpnoCHAR(4),CcreditTINYINT），選課表SC（SnoCHAR(8),CnoCHAR(4),GradeSMALLINT）。其中Sno為主鍵，Cno為主鍵，SC的主鍵為(Sno,Cno)，且Sno是S的外鍵，Cno是C的外鍵。（1）用關系代數(shù)表達式表示：查詢選修了課程號為'C001'且成績在85分以上的學生學號、姓名及所在院系。解答：首先通過σ操作篩選SC表中滿足Cno='C001'且Grade>85的記錄，得到中間表SC1；然后將SC1與S表進行自然連接（?），關聯(lián)學生學號；最后通過投影（π）提取Sno、Sname、Sdept屬性。表達式為：πSno,Sname,Sdept(σCno='C001'∧Grade>85(SC?S))（2）用SQL語句實現(xiàn)：查詢所有選修了“數(shù)據(jù)庫系統(tǒng)”課程且成績及格（≥60分）的學生姓名、課程名及成績，結果按成績降序排列，若成績相同則按姓名升序排列。解答：首先通過JOIN操作連接C、SC、S三張表，其中C.Cname='數(shù)據(jù)庫系統(tǒng)'且SC.Grade≥60；然后選擇Sname、Cname、Grade字段；最后使用ORDERBY子句指定排序規(guī)則。SQL語句如下：SELECTS.Sname,C.Cname,SC.GradeFROMSJOINSCONS.Sno=SC.SnoJOINCONSC.Cno=C.CnoWHEREC.Cname='數(shù)據(jù)庫系統(tǒng)'ANDSC.Grade>=60ORDERBYSC.GradeDESC,S.SnameASC;（3）用SQL語句實現(xiàn)：統(tǒng)計各院系學生的平均年齡（保留2位小數(shù)），僅顯示平均年齡超過20歲的院系，結果按平均年齡升序排列。解答：使用GROUPBY按Sdept分組，計算AVG(Sage)并四舍五入保留2位小數(shù)；通過HAVING子句篩選平均年齡>20的院系；最后排序。SQL語句如下：SELECTSdept,ROUND(AVG(Sage),2)ASAvgAgeFROMSGROUPBYSdeptHAVINGAVG(Sage)>20ORDERBYAvgAgeASC;2.關系數(shù)據(jù)庫規(guī)范化理論分析題給定關系模式R(U,F)，其中U={A,B,C,D,E,G}，函數(shù)依賴集F={AB→C,C→D,D→E,B→G,E→B}。（1）求R的候選碼。解答：候選碼是能唯一決定所有屬性的最小屬性集。首先計算AB的閉包：AB+=AB（初始）由AB→C，得AB+=ABC由C→D，得AB+=ABCD由D→E，得AB+=ABCDE由E→B，得AB+=ABCDE（B已存在）由B→G，得AB+=ABCDEG（包含所有屬性）因此AB是候選碼。檢查是否存在更小的子集：A+=A（無法推導其他屬性）B+=BG（無法推導C、D、E）故AB是唯一候選碼。（2）判斷R屬于第幾范式（1NF/2NF/3NF/BCNF），并說明理由。解答：1NF：所有屬性不可再分，滿足。2NF：要求不存在非主屬性對候選碼的部分函數(shù)依賴。候選碼是AB，非主屬性為C、D、E、G。AB→C（完全依賴，因為A或B單獨無法決定C）但B→G（G是非主屬性，B是候選碼的真子集），存在部分函數(shù)依賴（B→G），因此不滿足2NF。綜上，R僅滿足1NF。（3）將R分解為3NF，保持函數(shù)依賴且無損連接。解答：首先分解部分依賴：將B→G單獨作為關系模式R1(B,G)，主碼為B。剩余函數(shù)依賴：AB→C,C→D,D→E,E→B。此時原關系模式剩余屬性為A,B,C,D,E。檢查是否存在傳遞依賴：AB→C→D→E→B。其中AB→C（完全依賴），C→D（非主屬性對主屬性的傳遞），D→E（傳遞），E→B（B是主屬性，傳遞依賴到主屬性）。分解傳遞依賴：保留AB→C，得到R2(A,B,C)，主碼AB。C→D，得到R3(C,D)，主碼C。D→E，得到R4(D,E)，主碼D。E→B，得到R5(E,B)，主碼E。驗證是否保持函數(shù)依賴：所有原函數(shù)依賴AB→C、C→D、D→E、E→B、B→G均被包含在分解后的關系中。驗證無損連接：分解后的關系通過自然連接可恢復原關系，例如R2與R3連接（C），再與R4連接（D），再與R5連接（E），最后與R1連接（B），可恢復原屬性集。最終3NF分解為：R1(B,G),R2(A,B,C),R3(C,D),R4(D,E),R5(E,B)。3.數(shù)據(jù)庫設計與E-R模型轉換題某高校需設計“教師科研項目管理”數(shù)據(jù)庫，需求如下：教師（工號、姓名、職稱、所屬院系）科研項目（項目編號、項目名稱、類別（縱向/橫向）、經(jīng)費、起始時間、結束時間）教師可參與多個項目，每個項目有多名教師參與，需記錄教師在項目中的角色（如負責人、成員）和貢獻度（百分比）。（1）繪制簡化的E-R圖（實體-聯(lián)系-屬性）。解答：實體1：教師（工號，姓名，職稱，所屬院系），主碼：工號。實體2：項目（項目編號，項目名稱，類別，經(jīng)費，起始時間，結束時間），主碼：項目編號。聯(lián)系：參與（教師，項目），類型為多對多（M:N），屬性：角色、貢獻度。（2）將E-R圖轉換為關系模型，注明主碼和外碼。解答：教師表T（Tno,Tname,Title,Dept），主碼Tno。項目表P（Pno,Pname,Ptype,Fund,StartDate,EndDate），主碼Pno。參與表TP（Tno,Pno,Role,Contribution），主碼(Tno,Pno)（聯(lián)合主碼），外碼Tno引用T.Tno，外碼Pno引用P.Pno。4.事務管理與并發(fā)控制題假設銀行數(shù)據(jù)庫中有賬戶表Account（AccNo,Balance），主碼AccNo?，F(xiàn)有兩個事務T1和T2，操作如下：T1：BEGIN;UPDATEAccountSETBalance=Balance+100WHEREAccNo='A001';UPDATEAccountSETBalance=Balance-100WHEREAccNo='A002';COMMIT;T2：BEGIN;SELECTBalanceFROMAccountWHEREAccNo='A001';SELECTBalanceFROMAccountWHEREAccNo='A002';COMMIT;（1）若T1和T2并發(fā)執(zhí)行，可能產生哪些并發(fā)問題？舉例說明。解答：可能產生“不可重復讀”問題。例如：T1先執(zhí)行第一個UPDATE（A001余額+100），但未提交；T2此時讀取A001的余額（已更新后的值），然后T1回滾或繼續(xù)執(zhí)行第二個UPDATE（A002余額-100）；T2再次讀取A002的余額（可能已被T1修改），導致T2兩次讀取的A001或A002余額不一致，違反可重復讀。（2）若采用封鎖機制，如何設置鎖的類型和粒度以避免上述問題？解答：T1在更新A001和A002時需加排他鎖（X鎖），且保持到事務結束（兩階段鎖協(xié)議）。T2在讀取A001和A002時需加共享鎖（S鎖），若T1已持有X鎖，T2需等待T1釋放鎖后再獲取S鎖，從而保證T2讀取的是T1提交后的數(shù)據(jù)，避免不可重復讀。5.索引設計與查詢優(yōu)化題某電商數(shù)據(jù)庫中有訂單表Order（OrderID,UserID,OrderTime,TotalAmount,Status），其中OrderID為主鍵，UserID為用戶編號，OrderTime為下單時間，TotalAmount為訂單總金額，Status為訂單狀態(tài)（0-未支付，1-已支付，2-已發(fā)貨等）。（1）分析以下查詢場景，建議是否創(chuàng)建索引并說明理由：①查詢某用戶（UserID='U1001'）的所有已支付（Status=1）訂單，按OrderTime降序排列。解答：建議在(UserID,Status,OrderTime)上創(chuàng)建復合索引。理由：查詢條件包含UserID和Status的等值匹配，且需要按OrderTime排序。復合索引可直接覆蓋查詢條件，避免全表掃描，同時排序操作可利用索引的有序性，減少排序開銷。②統(tǒng)計近30天內（OrderTime≥當前時間-30天）所有訂單的總金額（TotalAmount）。解答：建議在OrderTime上創(chuàng)建索引。理由：查詢條件是OrderTime的范圍查詢（近30天），索引可快速定位到符合條件的記錄，減少掃描的數(shù)據(jù)量；統(tǒng)計總金額需遍歷這些記錄，索引可加速范圍掃描。（2）若執(zhí)行SQL語句“SELECTUserID,AVG(TotalAmount)FROMOrderWHEREStatus=1GROUPBYUserID;”，如何優(yōu)化其執(zhí)行效率？解答：優(yōu)化方法：在(Status,UserID)上創(chuàng)建復合索引，包含TotalAmount（覆蓋索引）。這樣查詢時可直接通過索引獲取Status=1的記錄，并按UserID分組計算平均值，避免回表查詢TotalAmount，減少I/O消耗。若數(shù)據(jù)量極大，可考慮定期匯總提供物化視圖（如每日統(tǒng)計各用戶已支付訂單的總金額和數(shù)量），查詢時直接從物化視圖獲取AVG值，降低實時計算成本。6.數(shù)據(jù)庫安全與完整性控制題設計某醫(yī)院信息系統(tǒng)的患者表Patient（PID,Pname,Gender,Age,Disease,DoctorID），其中PID為主鍵，DoctorID為負責醫(yī)生的工號（引用醫(yī)生表Doctor的DID）。（1）說明需定義的完整性約束（至少3種）。解答：實體完整性：PID為主鍵，需定義NOTNULL且UNIQUE。參照完整性：DoctorID為外碼，需引用Doctor表的DID，可設置ONDELETESETNULL（若醫(yī)生離職，患者的負責醫(yī)生置空）或ONDELETERESTRICT（禁止刪除有患者關聯(lián)的醫(yī)生）。用戶定義完整性：Gender只能取'男'或'女'（CHECK(GenderIN('男','女'))）；Age需大于0且小于150（CHECK(Age>0ANDAge<150)）；Disease不能為空（DiseaseNOTNULL）。（2）若需限制“主任醫(yī)師（職稱）可查看所有患者信息，主治醫(yī)師僅能查看自己負責患者的信息”，如何通過SQL實現(xiàn)？解答：創(chuàng)建角色：CREATEROLE主任醫(yī)師;CREATEROLE主治醫(yī)師;為主任醫(yī)師授予Patient表的SELECT權限：GRANTSELECTONPa