數(shù)據(jù)庫系統(tǒng)原理大題

1、設(shè)有如下實體： （10分）學生：學號、單位、姓名、性別、年齡、選修課程名課程：編號、課程名、開課單位、任課教師號教師：教師號、姓名、性別、職稱、講授課程編號單位：單位名稱、電話、教師號、教師名上述實體中存在如下聯(lián)系:（1） 一個學生可選修多門課程，一門課程可為多個學生選修;（2）一個教師可講授多門課程，一門課程可為多個教師講授;（3）一個單位可有多個教師，一個教師只能屬于一個單位。試完成如下工作:（1）分別設(shè)計學生選課和教師任課兩個局部信息的結(jié)構(gòu)E-R圖。（4分）（2）將上述設(shè)計完成的E-R圖合并成一個全局E-R圖。（3分）（3）將該全局E-R圖轉(zhuǎn)換為等價的關(guān)系模型表示的數(shù)據(jù)庫邏輯結(jié)構(gòu)。（3分）（1）學生選課局部E-R圖如圖2所示，教師任課局部E-R圖如圖3所示。（2）合并的全局E-R圖如圖4所示。為避免圖形復(fù)雜，下面給出各實體屬性:單位：單位名、電話學生：學號、姓名、性別、年齡教師：教師號、姓名、性別、職稱課程：編號、課程名（3）該全局E-R圖轉(zhuǎn)換為等價的關(guān)系模型表示的數(shù)據(jù)庫邏輯結(jié)構(gòu)如下:單位（單位名，電話）教師（教師號，姓名，性別，職稱，單位名）課程（課程編號，課程名，單位名）學生（學號，姓名，性別，年齡，單位名）講授（教師號，課程編號）課程選修（學號，課程編號）n1mn1n學生單位課程名單位名:教師號編號姓名■I課程選修（學號，課程編號）n1mn1n學生單位課程名單位名:教師號編號姓名■I年齡,性別圖2學生選課局部E-R圖圖3教師任課局部E-R圖圖4合并的全局E-R圖2、用關(guān)系代數(shù)檢索既選修了C1課程，又選修了C2課程的學生姓名。兀g9g.CT(S*SC))m ("晏C2(" SC用元組關(guān)系演算表達式檢索年齡大于21的男生的學號和姓名。(t|(3r)(S(r)At[1]=r[1]At[2]=r[2]Ar[3]>21Ar[4]=，男’)}用SQL找出“程序設(shè)計”課程成績在90分以上的學生姓名。解：SELECTSnameFROMS,SC,CWHERES.Sno=SC.SnoANDSC.Cno=C.CnoANDScore>=90ANDCname='程序設(shè)計或者SELECTSnameFROMSWHERERS.SnoIN(SELECTSnoFROMSCWHEREScore>=90ANDC.CnoIN(SELECTCnoFROMCWHERECname='程序設(shè)計')3、設(shè)有關(guān)系R和函數(shù)依賴F：(10分)R(W,X,Y,Z),F={X—Z,WX—Y}。試求下列問題：關(guān)系R屬于第幾范式？(5分)如果關(guān)系R不屬于BCNF，請將關(guān)系R逐步分解為BCNF°(5分)要求：寫出達到每一級范式的分解過程，并指明消除什么類型的函數(shù)依賴。解：R是1NF。侯選碼為WX,則Y,Z為非主屬性，又由于X—Z,因此F中存在非主屬性對侯選碼的部分函數(shù)依賴。將關(guān)系分解為：R1(W,X,Y),F1=(WX—Y}R2(X,Z),F2=(X—Z}消除了非主屬性對碼的部分函數(shù)依賴。F1和F2中的函數(shù)依賴都是非平凡的，并且決定因素是候選碼，所以上述關(guān)系模式是BCNF。1、假定一個圖書館的數(shù)據(jù)庫包括以下的信息： (10分)借閱者的信息：讀者號、姓名、地址、性別、年齡和所在單位。書的信息：書號、書名、作者、出版社。對每本被借出的書有讀者號、借出的日期、應(yīng)還日期。完成如下設(shè)計：設(shè)計該圖書管理系統(tǒng)的E-R圖；(5分)將該E-R圖轉(zhuǎn)換為關(guān)系模型結(jié)構(gòu)；(3分)指出轉(zhuǎn)換結(jié)果中每個關(guān)系模式的候選碼。(2分)解：(1)本題對應(yīng)的E-R圖如圖2所示。

圖2圖2E-R圖(2)對應(yīng)的關(guān)系模型結(jié)構(gòu)如下：讀者(讀者號，姓名，地址，性別，年齡，單位)。書(書號，書名，作者，出版社)。借書(書名，讀者號，借出的日期，應(yīng)還日期)。每個關(guān)系模式的候選碼如(2)所示。每小題設(shè)有關(guān)系S、SC、C，試用關(guān)系代數(shù)、元組關(guān)系演算表達式和SQL完成下列操作。(15分，每小題5分)S(S#,SNAME,AGE,SEX)例：(001,'李強',23,’男')SC(S#,C#,SCORE)例：(003,'C1',83)C(C#,CNAME,TEACHER)例：('C1','數(shù)據(jù)庫原理','王華')用關(guān)系代數(shù)檢索既選修了C1課程，又選修了C2課程的學生姓名。試用元組關(guān)系演算表達式檢索選修了“程軍”老師所授課程之一的學生姓名。用SQL找出選修了“程軍”老師教的所有課程的學生姓名。用關(guān)系代數(shù)檢索既選修了C1課程，又選修了C2課程的學生姓名。兀Snam("g'C1'(' SC))Q兀 ("gC2(' SC》試用元組關(guān)系演算表達式檢索選修了“程軍”老師所授課程之一的學生姓名。{T(1)^(3U)(3V)(3W)(S(U)ASC(V)AC(W)AT[1]=U[1]AU[1]=V[1]AV[2]=W[1]AW[3]='程軍')}用SQL找出選修了“程軍”老師教的所有課程的學生姓名。SELECTS.SNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHEREC.TEACHER='程軍'ANDNOTEXISTS(SELECT*FROMSCWHERESC.C#=C.C#ANDSC.S#=S.S#))3、設(shè)有關(guān)系STUDENT(S#,SNAME,SDEPT,MNAME,CNAME,GRADE),S#,CNAME為候選碼，設(shè)關(guān)系中有如下函數(shù)依賴： (10分)S#,CNAME—SNAME,SDEPT,MNAMES#一SNAME,SDEPT,MNAMES#,CNAME—GRADESDEPT—MNAME試求下列問題：關(guān)系STUDENT屬于第幾范式？(5分)如果關(guān)系STUDENT不屬于BCNF，請將關(guān)系STUDENT逐步分解為BCNF。(5分)要求：寫出達到每一級范式的分解過程，并指明消除什么類型的函數(shù)依賴。解：關(guān)系STUDENT是1NF。首先消除部分函數(shù)依賴{S#,CNAME}—{SNAME,SDEPT,MNAME}將關(guān)系分解為：R1(S#,SNAME,SDEPT,MNAME)R2(S#,CNAME,GRADE)在關(guān)系R1中存在非主屬性對候選碼的傳遞函數(shù)依賴S#—SDEPT,SDEPT—MNAME，所以以上關(guān)系模式還不是BCNF，進一步分解R1：R11(S#,SNAME,SDEPT)R12(SDEPT,MNAME)R11,R12都是3NF。關(guān)系模式R2(S#,CNAME,GRADE)R11(S#,SNAME,SDEPT)R12(SDEPT,MNAME)R2,R11,R12關(guān)系模式存在的函數(shù)依賴S#,CNAME—GRADES#,SNAME—SNAME,SDEPTSDEPT—MNAME上述函數(shù)依賴都是非平凡的，并且決定因素是候選碼，所以上述關(guān)系模式是BCNF。

1、某醫(yī)院病房計算機管理中需要如下信息：（10分）科室：科名，科地址，科電話，醫(yī)生姓名病房：病房號，床位號，所屬科室名醫(yī)生：姓名，職稱，所屬科室名，年齡，工作證號病人：病歷號，姓名，性別，診斷，主管醫(yī)生，病房號其中，一個科室有多個病房、多個醫(yī)生，一個病房只能屬于一個科室，一個醫(yī)生只屬于一個科室，但可負責多個病人的診治，一個病人的主管醫(yī)生只有一個。完成如下設(shè)計：（1） 設(shè)計該計算機管理系統(tǒng)的E-R圖；（5分）（2） 將該E-R圖轉(zhuǎn)換為關(guān)系模型結(jié)構(gòu)；（3分）（3） 指出轉(zhuǎn)換結(jié)果中每個關(guān)系模式的候選碼。（2分）解：（1）本題的E-R圖如圖2所示。（2） 對應(yīng)的關(guān)系模型結(jié)構(gòu)如下：科室（科名，科地址，科電話）病房（病房號，床位號，科室名）醫(yī)生（工作證號，姓名，職稱，科室名，年齡）病人（病歷號，姓名，性別，診治，主管醫(yī)生，病房號）（3） 每個關(guān)系模式的候選碼如下：科室的候選碼是科名；病房的候選碼是科室名十病房號；醫(yī)生的候選碼是工作證號；病人的候選碼是病歷號?？泼輫刂罚菘齐娫捄?-i科名］國地址］科電話虹 -iV #X- 』圖2E-R圖2、設(shè)有關(guān)系S、SC、C，試用關(guān)系代數(shù)、元組關(guān)系演算表達式和SQL完成下列操作。（15分，每小題5分）S(Sno,Sname,Age,Sex)例：(001,'李強',23,’男')SC(Sno,Cno,Score)例：(003,'C1',83)C(Cno,Cname,Teacher)例：('C1','數(shù)據(jù)庫原理','王華')試用關(guān)系代數(shù)檢索選修了“程軍”老師所授課程之一的學生姓名。用元組關(guān)系演算表達式檢索年齡大于21的男生的學號和姓名。用SQL找出“程序設(shè)計”課程成績在90分以上的學生姓名。試用關(guān)系代數(shù)檢索選修了“程軍”老師所授課程之一的學生姓名。Hsname^^E(°TEACHER='程軍6)用元組關(guān)系演算表達式檢索年齡大于21的男生的學號和姓名。(t|(3r)(S(r)At[1]=r[1]At[2]=r[2]Ar[3]>21Ar[4]=，男’)}找出“程序設(shè)計”課程成績在90分以上的學生姓名。SELECTSNAMEFROMS,SC,CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDSCORE>=90ANDCNAME='程序設(shè)計'或者SELECTSNAMEFROMSWHERERS.S#IN(SELECTS#FROMSCWHERESCORE>=90ANDC.C#IN(SELECTC#FROMCWHERECNAME='程序設(shè)計')3、設(shè)有關(guān)系R和函數(shù)依賴F： (10分)R(X,Y,Z),F={Y—Z,XZ—Y}。試求下列問題：關(guān)系R屬于第幾范式？(5分)如果關(guān)系R不屬于BCNF，請將關(guān)系R逐步分解為BCNF°(5分)要求：寫出達到每一級范式的分解過程，并指明消除什么類型的函數(shù)依賴。解：R是3NF。侯選碼為Y和XZ,R中所有屬性都是主屬性，不存在非主屬性對碼的傳遞依賴。

R1(X,Y,Z),F={XZT}R2(Y,Z),F={YfZ}消除了非主屬性對碼的傳遞依賴。F1和F2中的函數(shù)依賴都是非平凡的，并且決定因素是候選碼，所以上述關(guān)系模式是BCNF。4.設(shè)有如下表所示的三個關(guān)系S、C和SC。試用關(guān)系代數(shù)表達式表示下列查詢語句：檢索籍貫為上海的學生的姓名、學號和選修的課程號。檢索選修了全部課程的學生姓名和年齡。S學號姓名年齡性別籍貫98601王曉燕20女北京98602李波23男上海98603陳志堅21男長沙98604張兵20男上海98605張兵22女武漢C課程號課程名教師姓名辦公室C601高等數(shù)學周振興416C602數(shù)