数据库模拟试题及答案(英文)

1ExamofDatabaseTechnology&Applications1.Describethethreelevelsanddataindependence.2.Whatarekeyconstraintsandforeignconstraints?3.ExplainLEFTJOIN,OUTERJOINandINNERJOIN.4.ForthefollowingrelationschemaandsetsofFD’s:Ris(A,B,C,D,E,F,G)withFD’sA-B,B-C,A-E,CD-G.1)Identifythecandidatekey(s)forR.2)IdentifythebestnormalformthatRsatisfies3)Decomposeitin3NFifnecessary.25.ExplaintheACIDproperties.6.NotownRecordshasdecidedtostoreinformationaboutmusicianswhoperformonitsalbums(aswellasothercompanydata)inadatabase.Thecompanyhaschosentohireyouasadatabasedesigner.EachmusicianthatrecordsatNotownhasanSSN,aname,anaddress,andaphonenumber.EachinstrumentusedinsongsrecordedatNotownhasaname(e.g.,guitar,synthesizer,flute)andamusicalkey(e.g.,C,B-flat,E-flat).EachalbumrecordedontheNotownlabelhasatitle,acopyrightdate,aformat(e.g.,CDorMC),andanalbumidentifier.EachsongrecordedatNotownhasatitleandanauthor.Eachmusicianmayplayseveralinstruments,andagiveninstrumentmaybeplayedbyseveralmusicians.Eachalbumhasanumberofsongsonit,butnosongmayappearonmorethanonealbum.Eachsongisperformedbyoneormoremusicians,andamusicianmayperformanumberofsongs.Eachalbumhasexactlyonemusicianwhoactsasitsproducer.Amusicianmayproduceseveralalbums,ofcourse.1)DefiningthecompletedE-Rdiagram.2)Defininginformationforeachrelation.37.ConsiderthefollowingrelationalschemaandgiveT-SQLexpressionsforthefollowingqueries.Sailors(sid,sname,age)Boats(bid,bname,color)Reservers(sid,bid,day)1）CreatethetableSailors(sid,sname,age).Itincludesthedomainofvaluesassociatedwitheachattributeandintegrityconstraints.sidINTNOTNULLPRIMARYKEYsnameVARCHAR(10)NOTNULLageINTNULL0age1002)ChangetheattributesnameVARCHAR(12).3)DeletealltuplesintheSailorsrelationforsailorswhoseageislessthan18.4)Findthenamesofsailorswhohavereservedaboaton‘2010-1-1’.5)Findthenamesofsailorswhohavereservedaredboat.6)Findthenamesofsailorswhohavereservedatleastoneboat.7)Findthesidofsailorswhohavereservedaredboatandagreenboat.48)Findthenamesofsailorswhohavereservedallboats.8.ConsidertheBuys_computerRelationshowninFigure1.ThefirstfourcolumnsshowtheageandsalaryofapotentialcustomerandtheBuys_computercolumnshowswhetherthepersonbuysacomputer.Wewanttousethisdatatoconstructadecisiontreethathelpspredictwhetherapersonwillbuyacomputer.AgeSalarySubscriptionyouthhighnoyouthhighnomiddle_agedhighyesseniormediumyesseniorlowyesseniorlownomiddle_agedlowyesyouthmediumnoyouthlowyesseniormediumyesyouthmediumyesmiddle_agedmediumyesmiddle_agedhighyesseniormediumnoFigure1TheBuys_computerRelationANSWER1.Thethreelevelsarephysicallevel,logicallevelandviewlevel.Physicalleveldescribesallrelationsthatarestoredinthedatabase.Logicallevelsummarizeshowtherelationsareactuallystoredonsecondarystoragedevices.Eachviewlevelconsistsofacollectionofoneormoreviewsandrelationsfromtheconceptuallevel.Thereareactuallytwomappings:theconceptual/internalmappingandtheexternal/conceptualmapping.Theconceptual/internalmappingliesbetweentheconceptualandinternallevels.Ifthestructureofthestoreddatabaseischanged,thentheconceptual/internalmappingmustalsobechangedaccordinglysothattheviewfromtheconceptuallevelremainsconstant.Itisthis5mappingthatprovidesphysicaldataindependenceforthedatabase.Theexternal/conceptualviewliesbetweentheexternalandconceptuallevels.Ifthestructureofthedatabaseattheconceptuallevelischanged,thentheexternal/conceptualmappingmustchangeaccordinglysotheviewfromtheexternallevelremainsconstant.Itisthismappingthatprovideslogicaldataindependenceforthedatabase.2.Primarykeyconstraintsassurethatthekeysofanytworecordsarenotsameinatable.Theprimarykeyconstraintsenforcetheentityintegrityofthetable.Foreignkeyconstraintscontrolthedatathatcanbestoredintheforeignkeytable,italsocontrolschangestodataintheprimarykeytable.Thisconstraintenforcesreferentialintegritybyensuringthatchangescannotbemadetodataintheprimarykeytableifthosechangesinvalidatethelinktodataintheforeignkeytable.3.TheLEFTOUTERJOINincludesallrowsinthelefttableintheresults,whetherornotthereisamatchonthejoincolumnintherighttable.FULLOUTERJOINincludesallrowsfrombothtables,regardlessofwhetherornottheothertablehasamatchingvalue.ThisINNERJOINisknownasanequi-join.Itreturnsallthecolumnsinbothtables,andreturnsonlytherowsforwhichthereisanequalvalueinthejoincolumn.4.1）(A,D)istheprimarykeyforR2)R∈1NF3)R1(A,B,E),R2(B,C,F),R3(C,D,G)5.Atomicity:Thispropertyguaranteesthatasetofrecordsthatarepartofatransactionisindivisible.Thuseitheralloperationsofthetransactionareproperlyreflectedinthedatabaseornoneare.Consistency:Databaseconsistencyisthepropertythateverytransactionseesaconsistentdatabaseinstance.Databaseconsistencyfollowsfromtransactionatomicity,isolation,andtransactionconsistency.Isolation:Althoughmultipletransactionsmayexecuteconcurrently,eachtransactionmustbeunawareofotherconcurrentlyexecutingtransactions.Intermediatetransactionresultsmustbehiddenfromotherconcurrentlyexecutedtransactions.Durability:Afteratransactioncompletessuccessfully,thechangesithasmadetothedatabasepersist,eveniftherearesystemfailures.6.1)62)musician(SSN,m_na