數(shù)據(jù)庫課堂練習(xí)全(答案全)

1、一.以下涉及的教學(xué)數(shù)據(jù)庫含有4個關(guān)系，方便起見，其名稱簡化為T、S、C和SC教師關(guān)系T(Tno,Tname,Title)學(xué)生關(guān)系S(Sno,Sname,Age,Sex)課程關(guān)系C(Cno,Cname,Tno)選課關(guān)系SC(Sno,Cno,Score)1 .查詢選修了課程號為C2課程的學(xué)生學(xué)號和姓名2 .查詢至少選修了鐘教授所教課程中一門課程的學(xué)生學(xué)號和姓名。3 .查詢不選修C2課程的學(xué)生姓名和年齡。4 .查詢選修了全部課程的學(xué)生姓名。5 .查詢至少有學(xué)號為S2和S4學(xué)生選修的課程的課程號。解：1.nSno,Sname(6Cno=C2(SESC)2 .nSno,Sname(6Tname=鐘(SX

2、SCR；2c)3 .nSname,Age(S)nSname,Age(6Cno=C2(9xSC)4 .HSname(SK(nSno,Cno(SC)nCno(C)5 .nCno(61.Sno=S2A2.Sno=S4A(SC*SC)二、以下涉及的教學(xué)數(shù)據(jù)庫含有4個關(guān)系，方便起見，其名稱簡化為T、S、C和SC教師關(guān)系T(Tno,Tname,Title)學(xué)生關(guān)系S(Sno,Sname,Age,Sex)課程關(guān)系C(Cno,Cname,Tno)選課關(guān)系SC(Sno,Cno,Score)要求檢索女同學(xué)選修課程的課程名和任課教師名。1 .試寫出該查詢的關(guān)系代數(shù)表達(dá)式ncname,Tname(號sex=女(S二s

3、C-f5二T)2 .畫出查詢表達(dá)式的語法樹3 .使用啟發(fā)式優(yōu)化算法，對語法樹進(jìn)行優(yōu)化，并畫出優(yōu)化后的語法樹7TC.cnaine.t.tnajue冗kjcnai嗚c,Tno)丁X.rro-g，1，-：cr(e.兀（who入tsc.8口三、用SQL完成下面的查詢和操作1 .查詢不選修C2課程的學(xué)生姓名和年齡。2 .查詢至少選修課程號C2和C4的學(xué)生學(xué)號。3 .求每一教師每門課程的學(xué)生選修人數(shù)（超過30人），要求顯示教師工號、課程號和學(xué)生人數(shù)。查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按工號升序、課程號按降序排列。4 .查詢只開設(shè)一門課程的教師工號和姓名。5 .把平均成績大于80分的男同學(xué)的學(xué)號和平均成績存入

4、另一個已存在的表S_Score（Sno,AVG_Score）中。解：1.不選C2的姓名和年齡selectSname,SagefromS,SCWheres.sno=onotinC2;2.至少選修C2,C4的學(xué)號selectA.snofromscasAscasBwhereA.sn=B.snoandA.cno=C2andB.cno=C43.selectTnoc.Cno,count(Sno)fromC,SCwhereC.Cno=SC.CnogroupbyTno,C.Cnohavingcount(s.sno)30orderbycount(s.sno),Tno,C.CnoDESC4.selectTno,T

5、namefromTwhereUnique(selectTnofromCwhereC.Tno=T.Tno)5.InsertintoS_Score(Sno,AVG_Score)selectSno,avg(Score)fromSCwhereSno(selectSnofromSwhereSex=男)groupbySnohavingArg(Score)80四、設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫中有三個實體集。一是“商店”實體集，屬性有商店編號、商店名、地址等；二是“商品”實體集，屬性有商品號、商品名、規(guī)格、單價等；三是“職工”實體集，屬性有職工編號、姓名、性別、業(yè)績等。商店與商品間存在“銷售”聯(lián)系，每個商店可銷售多種商

6、品，每種商品也可放在多個商店銷售，每個商店銷售一種商品，有月銷售量；商店與職工間存在著“聘用”聯(lián)系，每個商店有許多職工，每個職工只能在一個商店工作，商店聘用職工有聘期和月薪。1 .試畫出ER圖，并在圖上注明屬性、聯(lián)系的類型。2.將ER圖轉(zhuǎn)換成關(guān)系模型，并注明主鍵和外鍵。商店商店編R商店名地址商品商品名規(guī)格單價銷售商店編R商品號職，職工編號性別業(yè)績五、查詢計算機(jī)學(xué)生選修的所有課程名稱2 .寫出SQL3 .畫出查詢樹、語法樹，利用代數(shù)優(yōu)化對原始語法樹進(jìn)行優(yōu)化，畫出優(yōu)化語法樹答：1.SQL：selectcnameFroms,sc,co=oandsc.sno=s.snoandsdept=計算機(jī)系2.+

7、-田pTCjjeci、匕natiE.caLsctEdept-計算機(jī)：join(SCisiio=S.Sna)join(SC.i7iic=C.Cha)SC查洵冏tCna；neLnariegS.Sdept計算機(jī)CTSCCno_CCnoIXCt紅.SraS.Sno5S.SnoCOs.m;郎計算機(jī)語法樹sc優(yōu)化語法捌六、設(shè)有關(guān)系模式R（職工編號，日期，日營業(yè)額，部門名，部門經(jīng)理），該模式記錄商店里每個職工的日營業(yè)額以及職工所在的部門和經(jīng)理信息。如果規(guī)定：每個職工只在一個部門工作；每個部門只有一個經(jīng)理。則：1 .根據(jù)上述規(guī)定，寫出模式R的基本FD和碼；關(guān)系模式R（職工編號，日期，日營業(yè)額，部門名，部門經(jīng)理

8、）碼：日期和職工編號2 .說明R不是2NF的理由，并把R分解成2NF模式集非主屬性：部門各部分依賴于碼分解：R1（職工編號，日期，日營業(yè)額）R2（職工編號，部門名，部門經(jīng)理）3 .進(jìn)而分解成3NF模式集R1（職工編號，日期，日營業(yè)額）R2（職工編號，部門名）R3（部門名，部門經(jīng)理）七、設(shè)有關(guān)系模式R（運動員編號，比賽項目，成績，比賽類別，比賽主管）存儲運動員比賽成績及比賽類別、主管等信息。如果規(guī)定：每個比賽項目只屬于一個比賽類別；每個比賽類別只有一個比賽主管。則：1 .根據(jù)上述規(guī)定，寫出模式R的基本FD和碼；關(guān)系模式R（運動員編號，比賽項目，成績，比賽類別，比賽主管）碼：運動員編號，比賽項目2

9、 .說明R不是2NF的理由，并把R分解成2NF模式集非主屬性：比賽類別部分依賴于碼分解：R1（運動員編號，比賽項目，成績）R2（比賽項目，比賽類別，比賽主管）3 .進(jìn)而分解成3NF模式集R1（運動員編號，比賽項目，成績）R2（比賽項目，比賽類別）R3（比賽類別，比賽主管）八、設(shè)有兩個事務(wù)T1:Read(A);Read(B);IfA=0thenB=B+1;Write(B);T2:Read(B);Read(A);IfB=0thenA=A+1;Write(A);給定初始值A(chǔ)=B=0，試給出一種這兩個事務(wù)的并發(fā)執(zhí)行的可串行化調(diào)度，并說明并發(fā)事務(wù)操作是否正確的基本準(zhǔn)則是什么?解答：下面是其中可能的一種并

10、發(fā)操作，而且是一種可串行化的調(diào)度T1T2SlockAY=A=0UnlockAXlockBX=B=0B=X+1=1SlockB寫回B=1等待UnlockB等待Y=B=1UnlockBXlockAA=0(因為Y0,所以A=0)寫回A=0UnlockA并發(fā)事務(wù)操作是否正確的基本準(zhǔn)則是并發(fā)執(zhí)行是否可串行化。九、某一研究所要對科研項目進(jìn)行計算機(jī)管理，該研究所有若干科研人員，每個人員有職工號、姓名、性別、出生年月、專業(yè)和研究方向等，每個科研項目有研究項目編號、項目名稱、起始時間和完成時間、項目經(jīng)費額、經(jīng)費來源、項目負(fù)責(zé)人、參加項目研究人員、每個人員所承擔(dān)的任務(wù)等信息。該研究所規(guī)定：一個科研項目可以有多名研

11、究人員參加，一個研究人員也可以參加多個研究項目。(1)請畫出此管理系統(tǒng)的概念模型(用ER圖表示)；(2)將上述ER圖轉(zhuǎn)換成為關(guān)系模式。解：該管理系統(tǒng)的概念模型如下圖所示科研項目(項目編號，項目名稱,起始時間和完成時間，項目經(jīng)費額，經(jīng)費來源，項目負(fù)責(zé)人)職工(職工號，姓名,性別，出生年月，專業(yè)，研究方向)參加(職工號，項目編號，工作任務(wù))十、設(shè)某“南飛海者加薪F的三個關(guān)系模式如下：圖書Book：書號(Bno)、類別(Bclass)、出版社(Bpublisher)、作者(Bauthor)、書名(Bname)、定價(Bprice)、數(shù)量(Bqty)、購買日期(Bbuydate)、備注(Bremark

12、);讀上Reader：編號(Rno)、地番(Rname)、市位(Runit)、性別(Rsex)、電話(Rtel)借閱Borrow:書號(Bno)、讀者編號(Rno)、借閱日期(Borrowdate)、還書日期(Returndate)(1)寫出查詢請求“被數(shù)據(jù)庫中所有讀者借閱過的圖書書號和書名”所對應(yīng)的關(guān)系代數(shù)表達(dá)式；(2)寫出查詢請求“沒有借閱過計算機(jī)類圖書的所有讀者號和讀者名”所對應(yīng)的關(guān)系代數(shù)表達(dá)式及優(yōu)化后的關(guān)系代數(shù)語法樹；(3)用SQL語句完成下列操作 查詢所有計算機(jī)類圖書的書號、書名、出版社、作者和定價； 查詢借閱過計算機(jī)類圖書的的讀者號、姓名及單位； 查詢沒有借閱過任何圖書的讀者號、姓

13、名和單位；求“機(jī)械工業(yè)出版社”出版的各類圖書的平均定價；新增一個讀者，信息為(“R0468”，“張萍”，“外語學(xué)院”，“女”，7874357”)；H一、學(xué)生、教師和課程的關(guān)系模式STC(SNo,SN,SA,TN,CN,G),其中6個屬性分別為學(xué)生的學(xué)號、姓名、年齡、教師的姓名、課程名以及學(xué)生的成績。假設(shè)學(xué)生有重名，課程名也可能有重名。又假設(shè)每個教師只教一門課，但一門課可有幾個教師開設(shè)。當(dāng)某個學(xué)生選定某門課后，其上課教師就固定了。(1)寫出鍵碼和函數(shù)依賴；(2)分解關(guān)系模式使之屬于BC范式。十：答：(1)(Bno,Rno(Borrow)Rno(Reader)Bno,Bname(Book)(2)

14、Rno,Rname(Reader)Rno,Rname(Bclass計算機(jī)(Book)XBorrow兇Reader)(3)結(jié)果FromBookWhereBclass=計算機(jī)SSelectRno,Rname,RunitFromReaderWhereRnoin(SelectdistinctRnoFromBorrowWhereBnoin(selectBnoFromBookWhereBclass=計算機(jī))DSelectRno,Rname,RunitFromReaderWhereRnonotin(SelectdistinctRnoFromBorrow)Reader,Rname兀Rno,RnameRReader.Rno=Borrow.RnoBBook.Bno=Borrow.BnoReaderBBclass=計算機(jī)BorrowSelectBno,Bname,Bpublisher,Bauthor,B