哈工大數(shù)據(jù)庫習(xí)題課

1、數(shù)據(jù)庫系統(tǒng)概論習(xí)題課 作為數(shù)據(jù)庫系統(tǒng)概論課程的復(fù)習(xí)內(nèi)容，我們將在這里例舉一些典型的練習(xí)題，作為參考。1 設(shè)有如圖所示的關(guān)系S、SC和C，試用SQL語言完成下列查詢：1. 檢索“程軍”老師所授課程的課程號（C#）和課程名 （CName）；2. 查詢年齡大于21歲的男學(xué)生學(xué)號（S#） 和姓名（SName）；3.查詢至少選修了“程軍”老師所授全部課程的學(xué)生 姓名（SName）；4.查詢“李四”同學(xué)不學(xué)課程的課程號(C#)；5.查詢?nèi)繉W(xué)生都選修的課程的課程號（C#）和 課程名（CName）；6.查詢選修課程包含“程軍”老師所授課程之一的學(xué)生 學(xué)號（S#）；7.查詢選修課程號為K1和K2的學(xué)生學(xué)號（S

2、#）； 2SS#SNameAgeSex001李四23男002劉莉22女003王強(qiáng)22男CC#CNameTeacherK1C語言王旭K2數(shù)據(jù)結(jié)構(gòu)程軍K3操作系統(tǒng)程軍SCS#C#Grade001K183002K185003K192002K290003K284003K3803關(guān)系代數(shù)與SQL語言（續(xù)02） 檢索“程軍”老師所授課程的課程號（C#） 和課程名（CName）；關(guān)系代數(shù)表達(dá)式：SQL語言實現(xiàn)：select C#, Cnamefrom Cwhere Teacher = 程軍4關(guān)系代數(shù)與SQL語言（續(xù)03） 查詢年齡大于21歲的男學(xué)生學(xué)號（S#） 和姓名（SName）；關(guān)系代數(shù)表達(dá)式：SQL語

3、言實現(xiàn)：select S#, SNamefrom Swhere Age 21 and Sex = 男5關(guān)系代數(shù)與SQL語言（續(xù)04） 查詢至少選修了“程軍”老師所授全部課程 的學(xué)生姓名（SName）；關(guān)系代數(shù)表達(dá)式：SQL語言實現(xiàn)：(請參考教材P113的例44)select SName from S, SC SC01 where S.S# = SC01.S# and not exists(select * from C, SC SC02where C.Teacher = 程軍 and not exists(select * from SC SC03 where SC03.S# = SC01.S

4、# and SC03.C# = SC02.C# )6關(guān)系代數(shù)與SQL語言（續(xù)05） 查詢“李四”同學(xué)不學(xué)課程的課程號(C#)；關(guān)系代數(shù)表達(dá)式：SQL語言實現(xiàn)：select C# from C where not exists (select * from S, C where S.Sname = 李四 and S.S# = SC.S# and SC.S# = C.C#)7關(guān)系代數(shù)與SQL語言（續(xù)06） 查詢?nèi)繉W(xué)生都選修的課程的課程號 （C#）和課程名（CName）；關(guān)系代數(shù)表達(dá)式：SQL語言表達(dá)式：（請參考教材P113的例43）select C#, CName from C where no

5、t exists ( select * from S where not exists (select * from SC where S.S# = S# and C# = C.C#);8關(guān)系代數(shù)與SQL語言（續(xù)07） 查詢選修課程包含“程軍”老師所授 課程之一的學(xué)生學(xué)號（S#）；關(guān)系代數(shù)表達(dá)式：SQL語言表達(dá)式：select S# from SC where C# in ( select C# from C where Teacher = 程軍)；9關(guān)系代數(shù)與SQL語言（續(xù)08） 查詢選修課程號為K1和K2的學(xué)生學(xué) 號（S#）；關(guān)系代數(shù)表達(dá)式：SQL語言表達(dá)式：select S# from

6、SC where C# = K1 and C# = K2;10規(guī)范化理論的應(yīng)用實例01 如下圖所示，請問關(guān)系R為第幾范式？是否存在操作異常？若存在，則將其分解為高一級范式。分解完的高級范式中是否可以避免分解前關(guān)系中存在的操作異常呢？11規(guī)范化理論的應(yīng)用實例01 （續(xù)01）R工程號材料號數(shù)量開工日期完工日期價格P1M1498059902250P1M2698059902300P1M31598059902180P2M1698119912250P2M4189811991235012規(guī)范化理論的應(yīng)用實例01 （續(xù)02）解：它為1NF。因為該關(guān)系的候選關(guān)鍵字為（工程號，材料號），而非主屬性開工日期和完工日

7、期部分函數(shù)依賴于候選關(guān)鍵宇的子集工程號，即： 它存在操作異常，如果工程項目確定后，若暫時未用到材料，則該工程的數(shù)據(jù)因缺少關(guān)鍵字的一部分(材料號)而不能插入到該數(shù)據(jù)庫中，出現(xiàn)插入異常。若某工程完工，則刪除該工程的操作也可能丟失材料方面的信息。13規(guī)范化理論的應(yīng)用實例01 （續(xù)03） 將其中的部分函數(shù)依賴分解為一個獨立的關(guān)系，則產(chǎn)生如下圖所示的兩個2NF關(guān)系子模式： 分解后，新工程確定后，盡管還未用到材料，該工程數(shù)據(jù)可在關(guān)系R2中插入。某工程數(shù)據(jù)刪除時，僅對關(guān)系R2操作，也不會造成材料方面的信息丟失。14規(guī)范化理論的應(yīng)用實例01 （續(xù)04）R1工程號材料號數(shù)量價格P1M14250P1M26300P

8、1M315180P2M16250P2M418350R2工程號開工日期完工日期P198059902P29811991215規(guī)范化理論的應(yīng)用實例02 請問如下圖所示的關(guān)系SC為第幾范式？是否存在插入、刪除異常？若存在，則說明示在什么情況下發(fā)生？發(fā)生的原因是什么？將它分解為高一級的范式，分解后的關(guān)系能否解決操作異常問題呢？16規(guī)范化理論的應(yīng)用實例02（續(xù)01） 關(guān)系： SCSNoCNoCTitleTNameTLocGrade80152C1操作系統(tǒng)王平D17080153C2數(shù)據(jù)庫高升D28580154C1操作系統(tǒng)王平D18680154C3算法分析楊楊D37280155C4數(shù)據(jù)結(jié)構(gòu)高升D29217規(guī)范化

9、理論的應(yīng)用實例02（續(xù)02）解：該關(guān)系SC為1NF。 它存在插入、刪除異常操作。當(dāng)增設(shè)一門新課程時，因還沒有學(xué)生選修，則缺少主碼的一部分SNo而不能執(zhí)行插入操作；當(dāng)所有學(xué)生退選某門課程而進(jìn)行刪除操作時，會將不該刪除的課程信息刪除掉。18規(guī)范化理論的應(yīng)用實例02（續(xù)03） SC關(guān)系中存在插入和刪除操作異常的原因在于，該關(guān)系的候選關(guān)鍵字為(SNo，CNo)，其中僅有非主碼屬性Grade完全函數(shù)依賴于(SNo，CNo) ，其他非主碼屬性CTitle、TName、TLoc都只是部分函數(shù)依賴于(SNo，CNo) 。分解后的關(guān)系模式如下圖所示。19規(guī)范化理論的應(yīng)用實例02（續(xù)04）關(guān)系：SGSNoCNoG

10、rade80152C17080153C28580154C18680154C37280155C495關(guān)系：CTCNoCTitleTNameTLocC1操作系統(tǒng)王平D1C2數(shù)據(jù)庫高升D2C3算法分析楊楊D3C4數(shù)據(jù)結(jié)構(gòu)高升D220規(guī)范化理論的應(yīng)用實例02（續(xù)05） 分解后的兩個關(guān)系子模式都為2NF，并且解決了先前存在的插入、刪除異常操作。當(dāng)增設(shè)一門新課程時，可將數(shù)據(jù)插入到CT表中，當(dāng)所有學(xué)生退選某門課程時，只需刪除SG表中的有關(guān)記錄，而該課程的有關(guān)信息仍保留在CT表中。 21規(guī)范化理論的應(yīng)用實例02（續(xù)06）分解成2NF后的CT關(guān)系中仍存在插入、刪除操作異常。若有一個新教師報到，需將其有關(guān)數(shù)據(jù)插入

11、CT表，還是會存在插入異常。當(dāng)取消某門課程而刪除CT表中的一條記錄時，會將不該刪除的教師的有關(guān)信息刪除。CT表中出現(xiàn)操作異常的原因是該關(guān)系中存在非主屬性對候選關(guān)鍵字的傳遞函數(shù)依賴。CNoTName，TNameCNo，TNameTLoc，所以CNoTloc（傳遞函數(shù)依賴）。22規(guī)范化理論的應(yīng)用實例02（續(xù)07） 將CT表進(jìn)一步分解為如下圖所示的兩個關(guān)系，可以解決上述操作中存在的異常。 關(guān)系：CourseCNoCTitleTNameC1操作系統(tǒng)王平C2數(shù)據(jù)庫高升C3算法分析楊楊C4數(shù)據(jù)結(jié)構(gòu)高升關(guān)系：InstructorTNameTLoc王平D1高升D2楊楊D323簡單的數(shù)據(jù)庫設(shè)計實例設(shè)有如下實體：

12、學(xué)生：學(xué)號、單位、姓名、性別、年齡、選修課程名；課程：編號、課程名、開課單位、任課教師號；教師：教師號、姓名、性別、職稱、講授課程編號；單位：單位名稱、電話、教師號、教師名；上述實體中存在如下聯(lián)系：（1）一個學(xué)生可選修多門課程，一門課程可為多個學(xué) 生選修；（2）一個教師可講授多門課程，一門課程可為多個教 師講授；24簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)01）（3）一個單位可有多個教師，一個教師只能屬 于一個單位。試完成如下工作：（1）分別設(shè)計學(xué)生選課和教師任課兩個局部信 息的結(jié)構(gòu)ER圖。（2）將上述設(shè)計完成的ER圖合并成一個全局 ER圖。（3）將該全局ER圖轉(zhuǎn)換為等價的關(guān)系模型表 示的數(shù)據(jù)庫邏輯結(jié)構(gòu)。25

13、簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)02）解：（1）學(xué)生選課局部ER圖如下圖所示：26簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)03）教師任課局部ER圖如下圖所示：27簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)04）合并的全局ER圖如下圖所示：28簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)05） 為逐免圖形復(fù)雜，我們沒有在ER圖中給出每個實體的屬性，所以在這里詳細(xì)地給出各實體屬性：單位：單位名、電話；學(xué)生：學(xué)號、姓名、性別、年齡；教師：教師號、姓名、性別、職稱；課程：編號、課程名；29簡單的數(shù)據(jù)庫設(shè)計實例（續(xù)06） 前面的全局ER圖轉(zhuǎn)換為等價的關(guān)系模型表示的數(shù) 據(jù)庫邏輯結(jié)構(gòu)如下：單位（單位名，電話）教師（教師號、姓名、性別、職稱、單位名）課程（課程編號，課程

14、名，單位名）學(xué)生（學(xué)號，姓名，性別，年齡，單位名）講授（教師號，課程編號）選修（學(xué)號、課程編號）30名詞數(shù)據(jù)獨立性數(shù)據(jù)庫的數(shù)據(jù)獨立性： 數(shù)據(jù)獨立性表示應(yīng)用程序與數(shù)據(jù)庫中存儲的數(shù)據(jù)之間不存在依賴關(guān)系。包括邏輯數(shù)據(jù)獨立性和物理數(shù)據(jù)獨立性。 邏輯數(shù)據(jù)獨立性是指局部邏輯數(shù)據(jù)結(jié)構(gòu)（外視圖）與全局邏輯數(shù)據(jù)結(jié)構(gòu)（概念視圖）之間的獨立性。當(dāng)數(shù)據(jù)庫的全局邏輯數(shù)據(jù)結(jié)構(gòu)（概念視圖）發(fā)生變化（數(shù)據(jù)定義的修改、數(shù)據(jù)之間聯(lián)系的變更或增加新的數(shù)據(jù)類型等）時，它不影響某些局部的邏輯結(jié)構(gòu)的性質(zhì)，應(yīng)用程序不必修改。31名詞數(shù)據(jù)獨立性（續(xù)） 物理數(shù)據(jù)獨立性是指數(shù)據(jù)的存儲結(jié)構(gòu)與存取方法（內(nèi)視圖）改變時，對數(shù)據(jù)庫的全局邏輯結(jié)構(gòu)（概念視

15、圖）和應(yīng)用程序不必作修改的一種特性，也就是說，數(shù)據(jù)庫數(shù)據(jù)的存儲結(jié)構(gòu)與存取方法獨立。 數(shù)據(jù)獨立性的好處是，數(shù)據(jù)的物理存儲設(shè)備更新了，物理表示及存取方法改變了，但數(shù)據(jù)的邏輯模式可以不改變。數(shù)據(jù)的邏輯模式改變了，但用戶的模式可以不改變，因此應(yīng)用程序也可以不變。這將使程序維護(hù)容易。另外，對同一數(shù)據(jù)庫的邏輯模式，可以建立不同的用戶模式，提高數(shù)據(jù)共享性，使數(shù)據(jù)庫系統(tǒng)有較好的可擴(kuò)充性。給DBA維護(hù)、改變數(shù)據(jù)庫的物理存儲提供方便。32簡答DBA的職責(zé)DBA的職責(zé)是： 決定DB中的信息內(nèi)容和結(jié)構(gòu) 決定DB的存儲結(jié)構(gòu)和存取策略 定義數(shù)據(jù)的安全性要求和完整性約束條件 監(jiān)控數(shù)據(jù)哼的使用扣運行33簡答數(shù)據(jù)字典的作用 答

16、：數(shù)據(jù)字典的任務(wù)就是管理有關(guān)數(shù)據(jù)的信息，所以又稱為“數(shù)據(jù)庫的數(shù)據(jù)庫”它的任務(wù)主要有：描述數(shù)據(jù)庫系統(tǒng)的所有對象，井確定其屬性如一個模式中包含的記錄型與一個記錄型包含的數(shù)據(jù)項：用戶的標(biāo)識、口令；物理文件名稱、物理位置及其文件組織方式等。數(shù)據(jù)字典在描述時賦給每個對象一個惟一的標(biāo)識。描述數(shù)據(jù)庫系統(tǒng)對象之間的各種交叉聯(lián)系。如哪個用戶使用哪個子模式，哪些模式或記錄型分配在哪些區(qū)域及對應(yīng)于哪些物理文件、存儲在何種物理設(shè)備上。34簡答數(shù)據(jù)字典的作用（續(xù)） 登記所有對象的完整性及安全性限制等。 對數(shù)據(jù)字典本身的維護(hù)、保護(hù)、查詢與輸出。數(shù)據(jù)字典的主要作用是： 供數(shù)據(jù)庫管理系統(tǒng)快速查找有關(guān)對象的信息。數(shù) 據(jù)庫管理系

17、統(tǒng)在處理用戶存取時，要經(jīng)常查閱數(shù) 據(jù)字典中的用戶表、子模式表和模式表等。 供數(shù)據(jù)庫管理員查詢，以掌握整個系統(tǒng)的運行情 況。 支持?jǐn)?shù)據(jù)庫設(shè)計與系統(tǒng)分析。35簡答自然連接與等值連接 等值連接和自然連接的表示方法相同，都記為：R S；自然連接是除去重復(fù)屬性的等值連接。兩者之間的區(qū)別和聯(lián)系如下： 自然連接一定是等值連接但等值連接不一定是 自然連接。 等值連接要求相等的分量，不一定是公共屬性； 而自然連接要求相等的分量必須是公共屬性。 等值連接不把重復(fù)的屬性除去，而自然連接要把 重復(fù)的屬性除去。36名詞死鎖、活鎖死鎖： 略活鎖：37簡答數(shù)據(jù)庫轉(zhuǎn)儲中日志文件的作用和注意事項 略，請同學(xué)們參考教材。38簡答

18、預(yù)防和解除死鎖的方法 略，請同學(xué)們參考教材。39答疑時間及考試安排答疑時間：12月5號 08:00 16:00答疑地點：G802考試時間：16周的周三 10:00 12:00考試地點：A5240Course is over!Thanks for all!41例：查詢至少選修了1號課程和3號課程的學(xué)生號碼。 Sno,Cno(SC) K Cno 95001 1 3例：查詢選修了全部課程的學(xué)生號碼和姓名。 Sno,Cno(SC) Cno(Course) Sno, Sname (Student)42關(guān)系除運算 除運算適合于一些求包含關(guān)系、或者含有短語“全部”的查詢操作。43除運算實例設(shè)關(guān)系R和S如下：則RS的結(jié)果如下：RABCA1B1C2A2B3C7A3B4C6A1B2C3A4B6C6A2B2C3A1B2C1SBCDB1C2D1B2C1D1B2C3D2RSAA144SS#SNameAgeSex001李四23男002劉莉22女003王強(qiáng)22男CC#CNameTea