搜索
Section1Exercise8.1.1a)CREATEVIEWRichExecASSELECT*FROMMovieExecWHEREnetWorth=10000000;b)CREATEVIEWStudioPres(name,address,cert#)ASSELECTMovieExec.name,MovieExec.address,MovieExec.cert#FROMMovieExec,StudioWHEREMovieExec.cert#=Studio.presC#;c)CREATEVIEWExecutiveStar(name,address,gender,birthdate,cert#,netWorth)ASSELECTstar.name,star.address,star.gender,star.birthdate,exec.cert#,exec.netWorthFROMMovieStarstar,MovieExecexecWHEREstar.name=exec.nameANDstar.address=exec.address;Exercise8.1.2a)SELECTnamefromExecutiveStarWHEREgender=fb)SELECTRichExec.namefromRichExec,StudioPreswhereRichExec.name=StudioPres.name;c)SELECTExecutiveStar.namefromExecutiveStar,StudioPresWHEREExecutiveStar.netWorth=50000000ANDStudioPres.cert#=RichExec.cert#;Section2Exercise8.2.1TheviewsRichExecandStudioPresareupdatable;however,theStudioPresviewneedstobecreatedwithasubquery.CREATEVIEWStudioPres(name,address,cert#)ASSELECTMovieExec.name,MovieExec.address,MovieExec.cert#FROMMovieExecWHEREMovieExec.cert#IN(SELECTpresCt#fromStudio);Exercise8.2.2a)Yes,theviewisupdatable.b)CREATETRIGGERDisneyComedyInsertINSTEADOFINSERTONDisneyComediesREFERENCINGNEWROWASNewRowFOREACHROWINSERTINTOMovies(title,year,length,studioName,genre)VALUES(NewRow.title,NewRow.year,NewYear.length,Disney,comedy);c)CREATETRIGGERDisneyComedyUpdateINSTEADOFUPDATEONDisneyComediesREFERENCINGNEWROWASNewRowFOREACHROWUPDATEMoviesSETlengthNewRow.lengthWHEREtitle=NewRow.titleANDyear=NEWROW.yearANDstudionName=DisneyANDgenre=comedyExercise8.2.3a)No,theviewisnotupdatablesinceitisconstructedfromtwodifferentrelations.b)CREATETRIGGERNewPCInsertINSTEADOFINSERTONNewPCREFERENCINGNEWROWASNewRowFOREACHROW(INSERTINTOProductVALUES(NewRow.maker,NewRow.model,pc)(INSERTINTOPCVALUES(NewRow.model,NewRow.speed,NewRow.ram,NewRow.hd,NewRow.price));c)CREATETRIGGERNewPCUpdateINSTEADOFUPDATEONNewPCREFERENCINGNEWROWASNewRowFOREACHROWUPDATEPCSETprice=NewPC.pricewheremodel=NewPC.model;d)CREATETRIGGERNewPCDeleteINSTEADOFDELETEONNeePCREFERENCINGOLDROWASOldRowFOREACHROW(DELETEFROMProductWHEREmodel=OldRow.model)(DELETEFROMPCwheremodel=OldRow.model);Section3Exercise8.3.1a)CREATEINDEXNameIndexonStudio(name);b)CREATEINDEXAddressIndexonMovieExec(address);c)CREATEINDEXGenreIndexonMovies(genre,length);Section4Exercise8.4.1ActionNoIndexStarIndexMovieIndexBothIndexesQ110041004Q210010044I2446Average2+98p1+98p24+96p24+96p162p12p2Exercise8.4.2Q1=SELECT*FROMShipsWHEREname=n;Q2=SELECT*FROMShipsWHEREclass=c;Q3=SELECT*FROMShipsWHERElaunched=y;I=InsertsIndexesActionsNoneNameClassLaunchedName&ClassName&LaunchedClass&LaunchedThreeIndexesQ1502505022502Q211212122Q35050502650262626I24446668Average2+48p1-p2+48p34+46p3-2p1-3p24+46p1-2p2+46p34+46p1-3p2+22p36-4p1-4p2+44p36-4p1-5p2+20p36-44p1-4p2+20p38-6p1-6p2+18p3Thebestchoiceofindexes(nameandlaunched)hasanaveragecostof6-4p1-5p2+20p3peroperation.Section5Exercise8.5.1UpdatestomoviesthatinvolvestitleoryearUPDATEMovieProdSETtitle=newTitlewheretitle=oldTitleANDyear=oldYear;UPDATEMovieProdSETyear=newYearwheretitle=oldYitleANDyear=oldYear;UpdatetoMovieExecinvolvingcert#DELETEFROMMovieProdWHERE(title,year)IN(SELECTtitle,yearFROMMovies,MovieExecWHEREcert#=oldCert#ANDcert#=producerC#);INSERTINTOMovieProdSELECTtitle,year,nameFROMMovies,MovieExecWHEREcert#=newCert#ANDcert#=producerC#;Exercise8.5.2Insertions,deletions,andupdatestothebasetablesProductandPCwouldrequireamodificationofthematerializedview.InsertionsintoProductwithtypeequaltopcINSERTINTONewPCSELECTmaker,model,speed,ram,hd,priceFROMProduct,PCWHEREProduct.model=newModelandProduct.model=PC.model;InsertionsintoPC:INSERTINTONewPCSELECTmaker,newModel,newSpeed,newRam,newHd,newPriceFROMProductWHEREmodel=newModelDeletionsfromProductwithtypeequaltopcDELETEFROMNewPCWHEREmaker=deletedMakerANDmodel=deletedModelDeletionsfromPC:DELETEFROMNewPCWHEREmodel=deletedModelUpdatestoPC:UpdateNewPCSETspeed=PC.speed,ram=PC.ram,hd=PC.hd,price=PC.priceFROMPCwheremodel=pc.model;Updatetotheattributemodelneedstobetreatedasadeleteandaninsert.UpdatestoProduct:AnychangestoaProducttuplewhosetypeispcneedtobetreatedasadeleteoraninsert,orboth.Exercise8.5.3Modificationstothebasetablesthatwouldrequireamodificationtothematerializedview:insertsanddeletesfromShips,deletesfromclass,updatestoaClassdisplacement.DeletionsfromShip:UPDATEShipStatsSETdisplacement=((displacement*count)(SELECTdisplacementFROMClasssesWHEREclass=DeletedShipClass)/(count1),count=count1WHEREcountry=(SELECTcountryFROMClassesWHEREclass=DeletedShipClass);InsertionsintoShip:UpdateShipStatSETdisplacement=((displacement*count)+(SELECTdisplacementFROMClassesWHEREclass=InsertedShipClass)/(count+1),count=count+1WHEREcountry=(SELECTcountryFROMClassesWHEREclasses=InsertedShipClass);DeletesfromClasses:NumRowsDeleted=SELECTcount(*)FROMshipsWHEREclass=DeletedClassUPDATEShipStatsSETdisplacement=(displacement*count)-(DeletedClassDisplacement*NumRowsDeleted))/(countNumRowsDeleted),count=countNumRowsDeletedWHEREcountry=DeletedClassCountryUpdatetoaClassdisplacement:N=SELECTcount(*)FROMShipswhereclass=UpdatedClassUPDATEShipsStatSETdi