數(shù)據(jù)庫考試大題

假定教課管理規(guī)定：①一個學(xué)生可選修多門課，一門課有若干學(xué)生選修；②一個教師可講解多門課，一門課只有一個教師講解；③一個學(xué)生選修一門課，僅有一個成績。學(xué)生的屬性有學(xué)號、學(xué)生姓名；教師的屬性有教師編號，教師姓名；課程的屬性有課程號、課程名。要求：依據(jù)上述語義畫出ER圖，要求在圖中畫出實體的屬性并注明聯(lián)系的種類；解答：三、應(yīng)用題：設(shè)有以下所示的關(guān)系S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和SC(S#,C#,GRADE)，試用關(guān)系代數(shù)表達(dá)式表示以下查問語句：(1)檢索“程軍”老師所講課程的課程號(C#)和課程名(CNAME)。檢索年紀(jì)大于21的男學(xué)生學(xué)號(S#)和姓名(SNAME)。檢索起碼選修“程軍”老師所授所有課程的學(xué)生姓名(SNAME)。檢索”李強(qiáng)”同學(xué)不學(xué)課程的課程號(C#)。(5)檢索起碼選修兩門課程的學(xué)生學(xué)號(S#)。檢索所有學(xué)生都選修的課程的課程號(C#)和課程名(CNAME)。(7)檢索選修課程包含“程軍”老師所講課程之一的學(xué)生學(xué)號(S#)。檢索選修課程號為k1和k5的學(xué)生學(xué)號(S#)。檢索選修所有課程的學(xué)生姓名(SNAME)。檢索選修課程包含學(xué)號為2的學(xué)生所修課程的學(xué)生學(xué)號(S#)。檢索選修課程名為“C語言”的學(xué)生學(xué)號(S#)和姓名(SNAME)。解：此題各個查問語句對應(yīng)的關(guān)系代數(shù)表達(dá)式表示以下：(1).∏C#,CNAME(σTEACHER=‘程軍’(C))(2).∏S#,SNAME(σAGE>21∧SEX=”男”(C))(3).∏SNAME{s[∏S#,C#（sc）÷∏C#(σTEACHER=‘程軍’(C))]}(4).∏C#(C)-∏C#(σSNAME=‘李強(qiáng)’(S)SC)(5).∏S#(σ[1]=[4]∧[2]≠[5](SC×SC))(6).∏C#,CNAME(C(∏S#,C#(sc)÷∏S#(S)))(7).∏S#(SC∏C#(σTEACHER=‘程軍’(C)))(8).∏S#,C#（sc）÷∏C#(σC#=’k1’∨C#=’k5’(C))(9).∏SNAME{s[∏S#,C#（sc）÷∏C#(C)]}(10).∏S#,C#（sc）÷∏C#(σS#=’2’(SC))(11).∏S#,SNAME{s[∏S#(SCσCNAME=‘C語言’(C))]}3、設(shè)有以下關(guān)系表R：R(No，NAME，SEX，AGE，CLASS)主重點字是NO此中NO為學(xué)號，NAME為姓名，SEX為性別，AGE為年紀(jì)，CLASS為班號。寫出實現(xiàn)以下功能的SQL語句。①插入一個記錄(25，“李明”，“男”，21，“95031”)；。②插入“95031”班學(xué)號為30、姓名為“鄭和”的學(xué)生記錄；。③將學(xué)號為10的學(xué)生姓名改為“王華”；。④將所有“95101”班號改為“95091”；。⑤刪除學(xué)號為20的學(xué)生記錄；。⑥刪除姓“王”的學(xué)生記錄；。答案：INSERTINTORVALUES(25，“李明”，“男”，21，“95031”)INSERTINTOR(NO，NAME，CLASS)VALUES(30，“鄭和”，“95031”)UPDATERSETNAME=“王華”WHERENO＝10UPDATERSETCLASS＝“95091”WHERECLASS＝“95101”⑤DELETEFROMRWHERENO=20DELETEFROMRWHERENAMELIKE“王％”1、設(shè)學(xué)生課程數(shù)據(jù)庫中有三個關(guān)系：學(xué)生關(guān)系S（S#，SNAME，AGE，SEX）學(xué)習(xí)關(guān)系SC（S#，C#，GRADE）課程關(guān)系C（C#，CNAME）此中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分別表示學(xué)號、課程號、姓名、年紀(jì)、性別、成績和課程名。用SQL語句表達(dá)以下操作1）檢索選修課程名稱為“MATHS”的學(xué)生的學(xué)號與姓名2）檢索起碼學(xué)習(xí)了課程號為“C1”和“C2”的學(xué)生的學(xué)號3）檢索年紀(jì)在18到20之間（含18和20）的女生的學(xué)號、姓名和年紀(jì)4）檢索均勻成績超出80分的學(xué)生學(xué)號和均勻成績5）檢索選修了所有課程的學(xué)生姓名6）檢索選修了三門課以上的學(xué)生的姓名答案：1）SELECTSNAME,AGEFROMS,SC,CWHERE#=#AND#=#ANDCNAME=’MATHS’SELECTS#FROMSCWHERECNO=’C1’ANDS#IN(SELECTS#FROMSCWHERECNO=(3)SELECTS#,SNAME,AGEFROMSWHEREAGEBETWEEN18AND20

’C2’)(4)SELECTS#,AVG(GRADE)‘均勻成績’FROMSCSELECTSNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERES#=#ANDC#=#))SELECTSNAMEFROMS,SCWHERE#=#GROUPBYSNAMEHAVINGCOUNT(*)>32、設(shè)學(xué)生-課程數(shù)據(jù)庫中包含三個表：學(xué)生表：Student（Sno，Sname，Sex，Sage，Sdept）課程表：Course（Cno，Cname，Ccredit）學(xué)生選課表：SC（Sno，Cno，Grade）此中Sno、Sname、Sex、Sage、Sdept、Cno、Cname、Ccredit、Grade分別表示學(xué)號、姓名、性別、年紀(jì)、所在系名、課程號、課程名、學(xué)分和成績。試用SQL語言達(dá)成以下項操作：（1）查問選修課程包含“1042”號學(xué)生所學(xué)的課程的學(xué)生學(xué)號2）創(chuàng)立一個計科系學(xué)生信息視圖S_CS_VIEW，包含Sno學(xué)號、Sname姓名、Sex性別;3）經(jīng)過上邊第2題創(chuàng)立的視圖改正數(shù)據(jù)，把王平的名字改為王慧平4）創(chuàng)立一選修數(shù)據(jù)庫課程信息的視圖，視圖名稱為datascore_view，包含學(xué)號、姓名、成績。答案：SELECTDISTINCTSNOFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERE='1042'ANDNOTEXISTS(SELECT*FROMSCSCZWHERE=AND=)；CREATEVIEWS_CS_VIEWASSELECTSNO,SNAME,SEXFROMSTUDENTWHERESdept=’CS’(3)UPDATES_CS_VIEWSETSNAME=’王慧平’WHERESNAME=’王平’CREATEVIEWdatascore_viewASSELECTSNO學(xué)號、SNAME姓名、GRADE成績FROMSTUDENT,SC,COURSEWHERE=AND=ANDCNAME=’數(shù)據(jù)庫’三、設(shè)計題在學(xué)生課程管理數(shù)據(jù)庫中創(chuàng)立一觸發(fā)器，當(dāng)向?qū)W生選課表插入記錄時，檢查該記錄的學(xué)號在學(xué)生表中能否存在，檢查該記錄的課程號在課程表中能否存在，及選課成績能否在0到100范圍，如有一項為否，則不同意插入。答案：createtriggerstu_ins_trionscforinsertasbegindeclare@s#char(6),@c#char(5),@gradeintselect@s#=sno,@c#=cno,@grade=scorefrominsertedif(@s#notin(selectsnofromstudent))or(@c#notin(selectcnofromcourse))or(@gradenotbetween0and100)rollbacktransactionelseprint'成功插入'end四、綜合練習(xí)1、已知學(xué)生關(guān)系模式S(Sno，Sname，SD，Sdname，Course，Grade)此中：Sno學(xué)號、Sname姓名、SD系名、Sdname系主任名、Course課程、Grade成績。寫出關(guān)系模式S的基本函數(shù)依靠和主碼。原關(guān)系模式S為幾范式為何分解成高一級范式，并說明為何將關(guān)系模式分解成3NF，并說明為何寫出關(guān)系模式S的基本函數(shù)依靠和主碼。答:關(guān)系模式S的基本函數(shù)依靠以下：Sno→Sname，SD→Sdname，Sno→SD，(Sno，Course)→Grade關(guān)系模式S的碼為：（Sno，Course）。原關(guān)系模式S為幾范式為何分解成高一級范式，并說明為何答:原關(guān)系模式S是屬于1NF的，碼為(Sno，Course)，非主屬性中的成績完好依靠于碼，而其余非主屬性對碼的函數(shù)依靠為部分函數(shù)依靠，所以不屬于2NF。除去非主屬性對碼的函數(shù)依靠為部分函數(shù)依靠，將關(guān)系模式分解成2NF以下：S1(Sno，Sname，SD，Sdname)S2(Sno，Course，Grade)將關(guān)系模式分解成3NF，并說明為何答:將上述關(guān)系模式分解成3NF以下：關(guān)系模式

S1中存在

Sno→SD，SD→Sdname，即非主屬性

Sdname傳達(dá)依靠于

Sno，所以

S1不是

3NF。進(jìn)一步分解以下：S11(Sno

，Sname,SD)

S12(SD

，Sdname)分解后的關(guān)系模式

S11、S12知足

3NF。對關(guān)系模式

S2不存在非主屬性對碼的傳達(dá)依靠，故屬于

3NF。所以，原模式

S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解知足3NF。S11(Sno，Sname，SD)S12(SD，Sdname)S2(Sno，Course，Grade)2、設(shè)有以下關(guān)系R它為第幾范式為何能否存在刪除操作異樣若存在，則說明是在什么狀況下發(fā)生的將它分解為高一級范式，分解后的關(guān)系是怎樣解決分解前可能存在的刪除操作異樣問題(1)解:它是

2NF。由于

R的候選重點字為“課程名”。

依靠關(guān)系

:

課程名→教師名，教師名

→課程名，教師名→教師地點，所以課程名→教師地點。即存在非主屬性“教師地點”對候選重點字課程名的傳達(dá)函數(shù)，所以

R不是

3NF。但：由于不存在非主屬性對候選重點字的部分函數(shù)依靠，所以

R是

2NF。解:存在。當(dāng)刪除某門課程時會刪除不應(yīng)刪除的教師的相關(guān)信息。解:分解為高一級范式以下圖。R1以下:R2以下:分解后，若刪除課程數(shù)據(jù)時，僅對關(guān)系R1操作，教師地點信息在關(guān)系R2中仍舊保存，不會丟掉教師方面的信息。3、設(shè)某商業(yè)公司數(shù)據(jù)庫中有一關(guān)系模式R以下：（商鋪編號，商品編號，數(shù)目，部門編號，負(fù)責(zé)人）假如規(guī)定：（1）每個商鋪的每種商品只在一個部門銷售；（2）每個商鋪的每個部門只有一個負(fù)責(zé)人；（3）每個商鋪的每種商品只有一個庫存數(shù)目。試回答以下問題：（1）依據(jù)上述規(guī)定，寫出關(guān)系模式R的基本函數(shù)依靠；答:關(guān)系模式S的基本函數(shù)依靠以下：(商鋪編號，商品編號)→部門編號，（商鋪編號，部門編號）→負(fù)責(zé)人，(商鋪編號，商品編號)→數(shù)目2）找出關(guān)系模式R的候選碼；答:關(guān)系模式R的碼為：（商鋪編號，商品編號,部門編號）。3）試問關(guān)系模式R最高已經(jīng)達(dá)到第幾范式為何答:原關(guān)系模式R是屬于1NF的，碼為（商鋪編號，商品編號,部門編號），非主屬性對碼的函數(shù)依靠全為部分函數(shù)依靠，所以不屬于2NF。除去非主屬性對碼的函數(shù)依靠為部分函數(shù)依靠，將關(guān)系模式分解成2NF以下：R1(商鋪編號，商品編號,部門編號,數(shù)目)R2(商鋪編號，部門編號,負(fù)責(zé)人)4）假如R不屬于3NF，請將R分解成3NF模式集。答：將R分解為R1(商鋪編號，商品編號,部門編號,數(shù)目)R2(商鋪編號，部門編號,負(fù)責(zé)人)分解后的R不存在傳達(dá)的函數(shù)依靠，所以分解后的R已經(jīng)是第3NF三、應(yīng)用題設(shè)有以下實體：學(xué)生：學(xué)號、單位、姓名、性別、年紀(jì)、選修課程名課程：編號、課程名、開課單位、任課教師號教師：教師號、姓名、性別、職稱、講解課程編號單位：單位名稱、電話、教師號、教師名上述實體中存在以下聯(lián)系：1）．一個學(xué)生可選修多門課程，一門課程可為多個學(xué)生選修；（2）．一個教師可講解多門課程，一門課程可為多個教師講解；（3）．一個單位可有多個教師，一個教師只好屬于一個單位。試達(dá)成以下工作：（1）．分別設(shè)計學(xué)生選課和教師任課兩個局部信息的構(gòu)造E-R圖。（2）．將上述設(shè)計達(dá)成的E-R圖歸并成一個全局E-R圖。（3）．將該全局E-R圖變換為等價的關(guān)系模型表示的數(shù)據(jù)庫邏輯構(gòu)造。解：（1）．學(xué)生選課、教師任課局部E-R圖以下所示。學(xué)生選課局部E-R圖教師講課局部E-R圖（2）．歸并后的全局E-R圖以下所示。全局E-R圖為防止圖形復(fù)雜，下邊給出各實體屬性：單位：單位名、電話學(xué)生：學(xué)號、姓名、性別、年紀(jì)教師：教師號、姓名、性別、職稱課程：編號、課程號3）．該全局E-R圖變換為等價的關(guān)系模型表示的數(shù)據(jù)庫邏輯構(gòu)造以下：單位（單位名，電話）教師（教師號，姓名，性別，職稱，單位名）課程（課程編號，課程名，單位名）學(xué)生（學(xué)號，姓名，性別，年紀(jì)，單位名）講解（教師號，課程編號）選修（學(xué)號，課程編號）四、簡答題數(shù)據(jù)庫設(shè)計一般分為哪幾個階段，每個階段的主要任務(wù)是什么解答：（1）數(shù)據(jù)庫設(shè)計分為6個階段：需求剖析、觀點構(gòu)造設(shè)計、邏輯構(gòu)造設(shè)計、物理構(gòu)造設(shè)計、數(shù)據(jù)庫實行、數(shù)據(jù)庫運(yùn)轉(zhuǎn)和保護(hù)。（2）各階段任務(wù)以下：①需求剖析：正確認(rèn)識與剖析用戶需求（包含數(shù)據(jù)與辦理）。②觀點構(gòu)造設(shè)計：經(jīng)過對用戶需求進(jìn)行綜合、歸納與抽象，形成一個獨(dú)立于詳細(xì)DBMS的觀點模型。③邏輯構(gòu)造設(shè)計：將觀點構(gòu)造變換為某個DBMS所支持的數(shù)據(jù)模型，并對其進(jìn)行優(yōu)化。④數(shù)據(jù)庫物理設(shè)計：為邏輯數(shù)據(jù)模型選用一個最合適應(yīng)用環(huán)境的物理構(gòu)造（包含儲存構(gòu)造和存取方法）。⑤數(shù)據(jù)庫實行：設(shè)計人員運(yùn)用DBMS供給的數(shù)據(jù)語言、工具及宿主語言，依據(jù)邏輯設(shè)計和物理設(shè)計的結(jié)果成立數(shù)據(jù)庫，編制與調(diào)試應(yīng)用程序，組織數(shù)據(jù)入庫，并進(jìn)行試運(yùn)轉(zhuǎn)。⑥數(shù)據(jù)庫運(yùn)轉(zhuǎn)和保護(hù)：在數(shù)據(jù)庫系統(tǒng)運(yùn)轉(zhuǎn)過程中對其進(jìn)行評論、調(diào)整與改正。設(shè)計題1、今有兩個關(guān)系模式：員工（員工號，姓名，年紀(jì)，職務(wù)，薪資，部門號）

Person(pno,pname,page,ptitle,psal,dno)

部門（部門號，名稱，經(jīng)理名，地點，電話號）

Depart(dno,dname,dmanager,daddress,dtel)

請用SQL的GRANT和REVOKE語句(加上視圖體制)達(dá)成以下受權(quán)定義或存取控制功能：(1)用戶yong對兩個表有INSERT和DELETE權(quán)利。GRANTINSERT,DELETEONPerson,DepartTOyong(2)用戶liu對員工表有SELECT權(quán)利，對薪資字段擁有更新權(quán)利。GRANTSELECT,UPDATE(psal)ONPersonTOliu(3)用戶zhang擁有改正這兩個表的構(gòu)造的權(quán)利。GRANTALTERTABLEONPerson,DepartTOzhang;用戶yang擁有從每個部門員工中SELECT最高薪資、最低薪資、均勻薪資的權(quán)利，他不可以查察每一個人的薪資，并擁有給其余用戶受權(quán)的權(quán)利。CREATEVIEWpsalASSELECT,MAX(psal),MIN(psal),AVG(psal)FROMPerson,DepartWHERE=GROUPBYGRANTSELECTONpsalTOyangwithgrantoption;撤除各用戶yang所授與的權(quán)利REVOKEALLPRIVILIGESONPerson,DepartFROMYANG;2、假定有下邊兩個關(guān)系模式：員工（員工號，姓名，年紀(jì)，職務(wù)，薪資，部門號），此中員工號為主碼；部門（部門號，名稱，經(jīng)理名，電話），此中部門號為主碼。用SQL語言定義這兩個關(guān)系模式，要求在模式中達(dá)成以下完好性拘束條件的定義：定義每個模式的主碼；定義參照完好性；定義員工年紀(jì)不得超出60歲。答CREATETABLEDEPT(DeptnoNUMBER(2),DeptnameVARCHAR(10),ManagerVARCHAR(10),PhoneNumberChar(12)CONSTRAINTPK_SCRIMARYKEY(Deptno));CREATETABLEEMP(EmpnoNUMBER(4),EnameVARCHAR(10),AgeNUMBER(2),CONSTRAINTC1CHECK(Aage<=60),JobVARCHAR(9),SalNUMBER(7,2),DeptnoNUMBER(2),CONSTRAINTFK_DEPTNOFOREIGNKEY(Deptno)REFFERENCESDEPT(Deptno));3、設(shè)某商業(yè)公司對于商鋪銷售商品的數(shù)據(jù)庫中有三個基本表：商鋪SHOP（S#，SNAME，AREA，MGR_NAME）其屬性是商鋪編號，商鋪名稱，地區(qū)名，經(jīng)理姓名。銷售SALE（S#，G#，QUANTITY）其屬性是商鋪編號，商品編號，銷售數(shù)目。商品GOODS（G#，GNAME，PRICE）其屬性是商品編號，商品名稱，單價。?（1）試寫出以下查問的關(guān)系代數(shù)表達(dá)式、元組表達(dá)式和關(guān)系邏輯規(guī)則：檢索銷售“冰箱”的商鋪的編號和商鋪名稱。解：關(guān)系代數(shù)表達(dá)式：π（σGNAME='冰箱'（SHOP?SALE?GOODS））S#，SNAME元組表達(dá)式：{t|(u)(v)(w)(SHOP(u)∧SALE(v)∧GOODS(w)∧u[1]=v[1]∧v[2]=w[1]∧w[2]='冰箱'∧t[1]=u[1]∧t[2]=u[2])}關(guān)系邏輯規(guī)則：W(u,u)SHOP(u,u,u3,u)∧SALE(u,v,v)∧GOODS(v,'冰箱',w3)121241232?（2）試寫出上邊第(1)問的SELECT語句表達(dá)形式。解：SELECT語句以下：SELE