搜索
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=‘f’;b)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=‘Disney’ANDgenre=‘comedy’;Exercise8.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+96p16–2p1–2p2Exercise8.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=‘newTitle’wheretitle=’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#;Exercise8.5.2Insertions,deletions,andupdatestothebasetablesProductandPCwouldrequireamodificationofthematerializedview.InsertionsintoProductwithtypeequalto‘pc’:INSERTINTONewPCSELECTmaker,model,speed,ram,hd,priceFROMProduct,PCWHEREProduct.model=newModelandProduct.model=PC.model;InsertionsintoPC:INSERTINTONewPCSELECTmaker,‘newModel’,‘newSpeed’,‘newRam’,‘newHd’,‘newPrice’FROMProductWHEREmodel=‘newModel’;DeletionsfromProductwithtypeequalto‘pc’:DELETEFROMNewPCWHEREmaker=‘deletedMaker’ANDmodel=’deletedModel’;DeletionsfromPC:DELETEFROMNewPCWHEREmodel=‘deletedModel’;UpdatestoPC:UpdateNewPCSETspeed=PC.speed,ram=PC.ram,hd=PC.hd,price=PC.priceFROMPCwheremodel=pc.model;Updatetotheattribute‘model’needstobetreatedasadeleteandaninsert.UpdatestoProduct:AnychangestoaProducttuplewhosetypeis‘pc’needtobetreatedasadeleteoraninsert,orboth.Exercise8.5.3Modificationstothebasetablesthatwouldrequireamodificationtothematerializedview:insertsanddeletesfromShips,deletesfromclass,updatestoaClass’displacement.DeletionsfromShip:UPDATEShipStatsSETdisplacement=((displacement*count)–(SELECTdisplacementFROMClasssesWHEREclass=‘DeletedShipClass’))/(count–1),count=count–1WHEREcountry=(SELECTcountryFROMClassesWHEREclass=’DeletedShipClass’);InsertionsintoShip:UpdateShipStatSETdisplacement=((displacement*count)+(SELECTdisplacementFROMClassesWHEREclass=’InsertedShipClass’))/(count+1),count=count+1WHEREcountry=(SELECTcountryFROMClassesWHEREclasses=’InsertedShipClass);DeletesfromClasses:NumRowsDeleted=SELECTcount(*)FROMshipsWHEREclass=‘DeletedClass’;UPDATEShipStatsSETdisplacement=(displacement*count)-(DeletedClassDisplacement*NumRowsDeleted))/(count–NumRowsDeleted),count=count–NumRowsDeletedWHEREcountry=‘DeletedClassCountry’;UpdatetoaClass’displ