数据库试题参考Word下载.docx

数据库试题参考Word下载.docx

《数据库试题参考Word下载.docx》由会员分享，可在线阅读，更多相关《数据库试题参考Word下载.docx（11页珍藏版）》请在冰豆网上搜索。

A）B（d,e,f）B）C（g,e）C）A（d,f,g）D）B（d,f）

3.Whichofthefollowingfourexpressionsofrelationalalgebraisnotequivalenttotheotherthree?

TheyareallbasedontherelationsR（A,B）andS（B,C）.

A）

B）

C）

D）

4.AssumeRandSareeachrelationswithattributesaandbonly.

Q1:

Q2:

Whichofthefollowingiscorrect?

第1页

A）Q1andQ2producethesameanswer.

B）TheanswertoQ1isalwayscontainedintheanswertoQ2.

C）TheanswertoQ2isalwayscontainedintheanswertoQ1.

D）Q1andQ2producecompletelydifferentanswers.

5.SupposetherelationR（A,B,C）hasthefollowingfourtuples:

A

B

C

3

1

2

4

WhichofthefollowingfunctionaldependenciescanyouinferdoesnotholdinR?

A）A->

BB）BC->

AC）C->

BD）AB->

ThefollowingtwoquestionsrefertoarelationR（A,B,C,D,E）withfunctionaldependenciesABC->

DE,andD->

AB.

6.WhichofthefollowingisthehighestnormalformofR?

A）1NFB）2NFC）3NFD）BCNF

7.ThenumberofsuperkeysofRis:

A）2B）7C）10D）12

8.Inthefollowing,assumeaisanattributeofsomecharacter-stringtype,e.g.CHAR（10）,andthatitmaybeNULL.

SELECT*FROMRWHEREaISNULL;

SELECT*FROMRWHEREaNOTLIKE'

%'

;

A） 

Q1andQ2producethesameanswer.

B）TheanswertoQ1isalwayscontainedintheanswertoQ2.

C）TheanswertoQ2isalwayscontainedintheanswertoQ1.

D）Q1andQ2producedifferentanswers.

9.Inthisquestion,R（x）istheschemaofrelationR.

SELECTxFROMRrr

WHERENOTEXISTS（SELECT*FROMRWHEREx>

rr.x）;

SELECTMAX（x）FROMR;

共5页

10.InSQL,anALTERstatement:

A）Maybeusedtoaddaview.

B）Maybeusedtoaddaconstraint.

C）Maybeusedtodropatable.

D）Maybeusedtodropaview.

11.InSQL,anUPDATEstatementwithoutaWHEREclause:

A）ResultsinaCartesianproduct.

B）Updatesnorowsinatable.

C）Updateseverycolumninatable.

D）Updateseveryrowinatable.

12.Considerrelation'

G'

andthequerygivenbelow:

student

grade

NULL

SELECTgrade,COUNT（*）FROMG

GROUPBYgrade;

Howmanytuplesarereturned?

A）1B）2C）3D）4

13.Inthe3-valuedlogic,thevalueofexpression

R.a>

=R.bORR.a<

=0ORR.b>

=0

canbe:

A）OnlyTRUEorFALSE

B）OnlyFALSEorUNKNOWN

C）OnlyTRUEorUNKNOWN

D）AnyofTRUE,FALSE,orUNKNOWN

14.Uniqueconstraints:

A）RequirecolumnsthathaveNOTNULLconstraints

B）Mayonlybedefinedoncepertable

C）Maybeviolatedbyainsertion

D）Areidenticaltoprimarykeyconstraints

第2页

15.Supposewehavethefollowingtabledeclarations:

CREATETABLEA（wINTPRIMARYKEY）;

CREATETABLEB（xINTPRIMARYKEY

REFERENCESA（w）ONDELETESETNULL）;

CREATETABLEC（yINTREFERENCESA（w））;

CREATETABLED（z1INTREFERENCESB（x）ONDELETESETNULL,

z2INTREFERENCESA（w）ONUPDATECASCADE）;

Considerthefollowingoperations:

I.DELETEFROMC;

DELETEFROMB;

DELETEFROMA;

DELETEFROMD;

II.DELETEFROMC;

III.DELETEFROMB;

DELETEFROMC;

Whichoftheaboveoperationswillemptyallfourtableswithouterror?

A）IIIonlyB）Ionly

C）IIandIIIonlyD）IandIIIonly

Thefollowingtwoquestionsconcerntherelations:

Emps（id,name,dept,salary）

Managers（dept,mgr）

ThefirstgivestheemployeeID,theirname,department,andsalary;

thesecondgivesforeachdepartment,themanagerofthatdepartment,whichistheemployeeIDofthepersonmanagingthedepartment.

16.WewishtoconstraintherelationssothatinthemgrattributeofaManagerstupletheremustappeartheIDofanemployeeinEmps.Whichofthefollowingchanges,byitself,enforcesthatconstraint?

A）InthedeclarationofManagers,addforattributemgrtheattribute-basedcheckCHECK（EXISTS（SELECT*FROMEmpsWHEREid=mgr））.

B）InthedeclarationofEmps,addtheconstraintFOREIGNKEYidREFERENCESManagers（mgr）.

C）InthedeclarationofManagers,addtheconstraintFOREIGNKEYmgrREFERENCESemps（id）.

D）Morethanoneoftheabove.

17.Supposewewishtoconstrainthedatasothatinnodepartmentcantheemployeeshaveatotalsalarygreaterthan$1,000,000.Thefollowingisaframeworkforanassertionthatwillenforcethisconstraint:

CREATEASSERTIONcheapCHECK（NOTEXISTS（Q））;

WhichqueryQbestenforcesthisconstraint?

A）SELECT*FROMEmpsWHERESUM（salary）>

1000000

B）SELECTdept,SUM（salary）FROMEmpsGROUPBYdept

C）SELECTSUM（salary）FROMEmps,ManagersWHEREid=mgr

GROUPBYEmps.deptHAVINGSUM（salary）>

D）SELECTdeptFROMEmps

GROUPBYdeptHAVINGSUM（salary）>

18.Aisaunary（one-column）relationdeclaredby

CREATETABLEA（iINT）;

Belowisantrigger:

CREATETRIGGERMystery

AFTERINSERTORUPDATEONA

REFERENCINGOLDASOldRow,NEWASNewRow

FOREACHROW

WHEN（10>

（SELECTMAX（i）FROMA））

UPDATEASETi=i+1;

WithAempty,weexecutethestatement:

INSERTINTOAVALUES

（1）;

Theresultofthisstatementis:

A）Aisleftwithasingletuplewithvalue10

B）Aisleftwithasingletuplewithvalue2

C）Aisleftwithtuples2,3,…,10

D）Thetriggerneverstopsmodifyingtuples

19.RelationR（a,b,c）currentlyhasthefollowinginstance:

{（1,2,3）,（3,4,2）,（2,6,1）}

Wemakethefollowingviewdefinitions:

CREATEVIEWVASSELECTa*bASd,cFROMR;

CREATEVIEWWASSELECTd,SUM（c）ASeFROMVGROUPBYd;

Whatisthesumofallthecomponentsofallthetuplesofthefollowingquery?

SELECTAVG（d）,eFROMWGROUPBYe;

A）10B）17C）23D）28

20.Initially,userAistheownerofrelationR,andnootheruserholdsprivilegesonR.

Thefollowingareexecuted:

byA:

GRANTUPDATEONRTOB

GRANTUPDATE（a）ONRTOCWITHGRANTOPTION

byC:

GRANTUPDATE（a）ONRTOBWITHGRANTOPTION

REVOKEUPDATE（a）ONRFROMCCASCADE

第3页

WhichofthefollowingbestdescribesthestatusofB'

sprivilegesonR?

（A）BcanupdateanyattributeofRexcepta,butcannotgrantthatprivilege.

（B）BhasnoprivilegesonRandcannotgrantany.

（C）BcanupdateanyattributeofRexcepta,butcangrantotherstheprivilegetoupdateR.a.

（D）BcanperformanyupdateonRbutcannotgrantthatprivilege.

Question2（22points）

1.（5points）Considerthefollowingtwotables:

T1:

D

E

T2:

10

a

5

b

6

15

8

25

c

ShowtheresultsofthefollowingalgebraicorSQLexpressions:

a）

（1points）

b）SELECT*FROMT1NATRUALLEFTOUTERJOINT2ONE<

C;

（2points）

c）SELECT*FROMT1NATRUALFULLOUTERJOINT2WHEREE<

2.（7points）SupposewearebuildingaWebcrawler（网络搜索器）toindexpages.Weneedtokeeptrackofthefollowingattributes:

R（docID,docURL,docTitle,docDate,linkedDocID,linkText,wordID,wordText）

whereeachdocumenthasan（integer）ID,URL,title,anddate;

documentslinktootherdocumentswithaparticulartextlabelontheirlink;

anddocumentscontainwords（eachofwhichhasawordID）andtext（whichispotentiallydifferentforeachwordID）.

YouaregiventhefollowingFDs:

docID->

docURL,docTitle,docDate

docID,linkedDocID->

linkText

wordID->

wordText

a）SpecifyallkeysofR.（1points）

b）IfweprojecttheseFD’sontorelationS（docID,linkedDocID,linkText,wordID,wordText）,giveaminimalbasisfortheFD’sthatholdinS.（2points）

c）GivethehighestnormalformofRandS,respectively.（2points）

d）Usethe3NFsynthesisalgorithmtofindalossless-join,dependency-preservingdecompositionofRinto3NFrelations.（2points）

3.（5points）Thefollowingsetofrequirementsareforauniversitydatabasethatisusedtokeeptrackofstudent'

stranscripts.

I）Theuniversitykeepstrackofeachstudent'

sname,student'

snumber,socialsecuritynumber,currentaddress,phonenumber,permanentaddress,birthdate,sex,class,degree.Bothsocialsecuritynumberandstudentnumberhaveuniquevaluesforeachstudent.

II）Eachdepartmentisdescribedbyaname,departmentcode,officenumber,officephoneandcollege.Bothnameandcodehaveuniquevaluesforeachdepartment.

III）Eachcoursehasacoursename,description,coursenumber,numberofsemesterhoursandofferingdepartment.Thevalueofcoursenumberisunique.

IV）Eachsectionhasaninstructor,semester,year,courseandsectionnumber.Thesectionnumberdistinguishessectionsofthesamecoursethataretaughtduringthesamesemester/year;

itsvaluesare1,2,3...uptothenumberofsectionstaughtduringeachsemester.

V）Agradereporthasastudent,asection,andagrade.

DesignanE/Rdiagramforaboverequirements,andspecifythekeys.

4.（5points）ConvertthefollowingE/Rdiagramtoarelationaldatabaseschema,indicatingtheprimarykeysfortherelations.

第4页

Question3（28points）

Thefollowingfivetablesareforacompanymanagementsystem:

EMPLOYEE（SSN,Fname,Lname,Bdate,Address,Sex,Salary,Dnumber）

DEPARTMENT（Dnumber,Dname,MgrSSN）