數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程第一版數(shù)據(jù)庫習(xí)題答案ch8

Section1CREATEVIEWRichExecASSELECT*FROMMovieExecWHEREnetWorth>=10000000;CREATEVIEWStudioPres(name,address,cert#)ASSELECTMovieE,MovieExec.address,MovieExec.cert#FROMMovieExec,StudioWHEREMovieExec.cert#=Studio.presC#;CREATEVIEWExecutiveStar(name,address,gender,birthdate,cert#,netWorth)ASSELECT,star.address,star.gender,star.birthdate,exec.cert#,WorthFROMMovieStarstar,MovieExecexecWHERE=ANDstar,address=exec.address;SELECTnamefromExecutiveStarWHEREgender=f;SELECTRichEfromRichExec,StudioPreswhereRichE=StudioP;SELECTExecutiveSfromExecutiveStar,StudioPresWHEREExecutiveSWorth>=50000000ANDStudioPres.cert#=RichExec.cert#;Section2NamesofproducersthatacertainstarhasworkedwithSELECTnameFROMMovies,MovieExec,StarslnWhereproducerC#=cert#ANDtitle=movieTitleANDyear=movieYearANDstarName=5CarrieFisher';ThenumberofmoviesproducedbygivenproducerSELECTcount(*)FROMMovies,MovieExecWHEREproducerC#=cert#ANDname="GeorgeLucas";NamesofproducerswhoalsostarredintheirownmoviesSELECTnameFROMMovies,Starsln,MovieExecWHEREproducerC#=cert#ANDmovieTitle二titleANDmovieYear=yearANDMovieE二starName;ThenumberofstarsthathavestarredinmoviesproducedbyacertainproducerSELECTcount(DISTINCTstarName)FROMMovies,Starsln,MovieExecWHEREproducerC#=cert#ANDmovieTitle=titleANDmovieYear=yearANDname"GeorgeLucas';ThenumberofmoviesproducedbyeachproducerSELECTname,count(*)FROMMovies,MovieExecWHEREproducerC#=cert#GROUPBYnameTheviewsRichExecandStudioPresareupdatable;however,theStudioPresviewneedstobecreatedwithasubquery.CREATEVIEWStudioPres(name,address,cert#)ASSELECTMovieE,MovieExec.address,MovieExec.cert#FROMMovieExecWHEREMovieExec.cert#IN(SELECTpresCt#fromStudio);Yes,theviewisupdatable.CREATETRIGGERDisneyComedylnsertINSTEADOFINSERTONDisneyComediesREFERENCINGNEWROWASNewRowFOREACHROWINSERTINTOMovies(title,year,length,studioName,genre)VALUES(NewRow.title,NewRow.year,NewYear.lengthJDisney'「comedy');CREATETRIGGERDisneyComedyUpdateINSTEADOFUPDATEONDisneyComediesREFERENCINGNEWROWASNewRowFOREACHROWUPDATEMoviesSETlengthNewRow.lengthWHEREtitle=NewRow.titleANDyear=NEWROW.yearANDstudionName='Disney'ANDgenre="comedy9;No,theviewisnotupdatablesinceitisconstructedfromtwodifferentrelations.CREATETRIGGERNewPCInsertINSTEADOFINSERTONNewPCREFERENCINGNEWROWASNewRowFOREACHROW(INSERTINTOProductVALUES(NewRow.maker,NewRow.modelJpc'))(INSERTINTOPCVALUES(NewRow.model,NewRow.speed,NewRow.ram,NewRow.hd,NewRow.price));CREATETRIGGERNewPCUpdateINSTEADOFUPDATEONNewPCREFERENCINGNEWROWASNewRowFOREACHROWUPDATEPCSETprice=NewPC.pricewheremodel=NewPC.model;CREATETRIGGERNewPCDeleteINSTEADOFDELETEONNeePCREFERENCINGOLDROWASOldRowFOREACHROW(DELETEFROMProductWHEREmodel二OldRow.model)(DELETEFROMPCwheremodel=OldRow.model);Section3a)CREATEINDEXNameindexonStudio(name);CREATEINDEXAddressindexonMovieExec(address);CREATEINDEXGenreindexonMovies(genre,length);ActionNoIndexStarIndexMovieIndexBothIndexesQl10041004Q210010044I2446Average2+98Pl+98P24+96/724+96pi6-2pi-2P2QI=SELECT*FROMShipsWHEREname=n;Q2=SELECT*FROMShipsWHEREclass=c;Q3=SELECT*FROMShipsWHERElaunched=y;I=InsertsIndexesNoneNameClassLaunchedName&Name&Class&ThreeActionsClassLaunchedLaunchedIndexesQl502505022502Q211212122Q35050502650262626

I24446668Average2+4+4+4+46Pl6-4Pl6-4pi-6-44pi-8-6p\-48pi-46p346pi--3P2+-4P2+5P2+20/734P2+20.36P2+18.3pi+-2p\2P2+22/7344P348P3-3pi46〃3Thebestchoiceofindexes(nameandlaunched)hasanaveragecostof6-4pi-5P2+20/73peroperation.UpdatestomoviesthatinvolvestitleoryearUPDATEMovieProdSETtitle="newTitle9wheretitle='oldTitle'ANDyear=oldYear;UPDATEMovieProdSETyear=newYearwheretitle=,oldYitle,ANDyear=oldYear;UpdatetoMovieExecinvolvingcert#DELETEFROMMovieProdWHERE(title,year)IN(SELECTtitle,yearFROMMovies,MovieExecWHEREcert#二oldCert#ANDcert#二producerC#);INSERTINTOMovieProdSELECTtitle,year,nameFROMMovies,MovieExecWHEREcert#二newCert#ANDcert#二producerC#;Insertions,deletions,andupdatestothebasetablesProductandPCwouldrequireamodificationofthematerializedview.InsertionsintoProductwithtypeequalto'pc’:INSERTINTONewPCSELECTmaker,model,speed,ram,hd,priceFROMProduct,PCWHEREProduct.model=newModelandProduct.model=PC.model;InsertionsintoPC:INSERTINTONewPCSELECTmakerJnewModel'JnewSpeed'JnewRam'JriewHd'JnewPrice'FROMProductWHEREmodel='newModel';DeletionsfromProductwithtypeequalto'pc':DELETEFROMNewPCWHEREmaker=cdeletedMaker,ANDmodel=,deletedModer；DeletionsfromPC:DELETEFROMNewPCWHEREmodel=MeletedModeP;UpdatestoPC:UpdateNewPCSETspeed二PC.speed,ram二PC.ram,hd二PC.hd,price二PC.priceFROMPCwheremodel=pc.model;Updatetotheattribute'model'needstobetreatedasadeleteandaninsert.UpdatestoProduct:AnychangestoaProducttuplewhosetypeis'pc'needtobetreatedasadeleteoraninsert,orboth.Modificationstothebasetablesthatwouldrequireamodificationtothematerializedview:insertsanddeletesfromShips,deletesfromclass,updatestoaClass5displacement.DeletionsfromShip:UPDATEShipStatsSETdisplacement=((displacement*count)-(SELECTdisplacementFROMClasssesWHEREclass='DeletedShipClass'))/(count-1),count=count-1WHEREcountry=(SELECTcountryFROMClassesWHEREclass=9DeletedShipClass9;InsertionsintoShip:UpdateShipStatSETdisplacement=((displacement*count)+(SELECTdisplacementFROMClassesWHEREclass=,InsertedShipClass,))/(count+1),count二count+1WHEREcountry=(SELECTcountryFROMClassesWHEREclasses=,InsertedShipClass);DeletesfromClasses:NumRowsDeleted二SELECTcount(*)FROMshipsWHEREclass="DeletedClass9;UPDATEShipStatsSETdisplacement=(displacement*count)-(DeletedClassDisplacement*NumRowsDeleted))/(count-NumRowsDeleted),cou