搜索
SolutionsManualChapter10Section10.1Exercise10.1.1(a)SELECTonMovieStar,SELECTonMovieExec.(b)SELECTonMovieExec,SELECTonMovies,SELECTonStarsIn.(c)SELECTonMovies,SELECTonStudio,INSERTonStudio(orINSERT(name)onStudio).(d)DELETEonStarsIn.(e)UPDATEonMovieExec(orUPDATE(name)onMovieExec).(f)REFERENCESonMovieStar(orREFERNCES(gender,name)onMovieS-tar).(g)REFERENCESonStudio,REFERENCESonMovieExec(orREFERENCES(name,presC#)onStudio,REFERENCES(cert#,netWorth)onMovieExec).2Exercise10.1.2Afterstep(4),thegrantdiagramisasfollows:Ap∗∗-?Bp∗-CpDp∗-?Cp∗Ep∗Afterstep(5),thegrantdiagramisasfollows:Ap∗∗-?Bp∗CpAfterstep(6),thegrantdiagramisasfollows:Ap∗∗-Bp∗3Exercise10.1.3Afterstep(5),thegrantdiagramisasfollows:Ap∗∗-?Bp∗-Cp∗-Dp∗Ep∗-:CpAfterstep(6),thegrantdiagramisasfollows:Ap∗∗?Dp∗Ep∗-:CpExercise10.1.4Thegrantdiagramafterthefinalstepisasfollows:Ap∗∗4Section10.2Exercise10.2.1(a)Therulesfortripsthathavereasonableconnectionsare:Trips(x,y,dep,arr)←Flights(,x,y,dep,arr)Trips(x,y,dep,arr)←Trips(x,z,dep1,arr1)ANDTrips(z,y,dep2,arr2)ANDarr16dep2−100(b)Usingthebook’ssyntax,theSQLis:WITHRECURSIVETrips(frm,to,dep,arr)AS(SELECTfrm,to,dep,arrFROMFlights)UNION(SELECTT.frm,F.to,T.dep,F.arrFROMTripsT,FlightsFWHERET.to=F.fromANDT.arr=F.dep-100)SELECT*FROMTrips;Exercise10.2.2BecauseFROMisoneoftheSQLreservedwords,usingitasanidentifierisnotrecommended.Notethatmostmajorvendorsdonotprohibittheuseofreservedwordswhentheuseisnotambiguous(e.g.SELECTFROMFROMFROMisnotambiguousandwillwork),butsuchuseishighlydiscouragedforreadabilityandportabilityreasons.5Exercise10.2.3(a)FollowOn(x,y)←SequelOf(x,y)FollowOn(x,y)←FollowOn(x,z)ANDSequelOf(z,y)(b)Usingthebook’ssyntax,theSQLis:WITHRECURSIVEFollowOn(movie,followOn)AS(SELECTmovie,sequelFROMSequelOf)UNION(SELECTF.movie,S.sequelFROMFollowOnF,SequelSWHEREF.followOn=S.movie)SELECT*FROMFollowOn;(c)Usingthebook’ssyntax,theSQLis:WITHRECURSIVEFollowOn(movie,followOn)AS(SELECTmovie,sequelFROMSequelOf)UNION(SELECTF.movie,S.sequelFROMFollowOnF,SequelSWHEREF.followOn=S.movie)SELECTmovie,followOnFROMFollowOnEXCEPTSELECTmovie,sequelFROMSequelOf;(Similarly,NOTINorNOTEXISTScanbeusedinsteadofEXCEPT).6(d)OneofthewaysistofirstgetalloftherecursivetuplesasfortheoriginalFollowOnin(a),andthensubtractthethosetuplesthatrepresentsequelorsequelofasequel.Usingthebook’ssyntax,theSQLwouldbe:WITHRECURSIVEFollowOn(movie,followOn)AS(SELECTmovie,sequelFROMSequelOf)UNION(SELECTF.movie,S.sequelFROMFollowOnF,SequelSWHEREF.followOn=S.movie)SELECTmovie,followOnFROMFollowOnEXCEPT(SELECTmovie,sequelFROMSequelOfUNIONSELECTX.movie,Y.sequelFROMSequelX,SequelYWHEREX.sequel=Y.movie);AnotherwaywouldbetostartFollowOntuplesonlyfromthetuplesofmoviesthathavemorethantwosequels(usingajoinsimilartotheoneabovebutwiththreeSequeltables).(e)Wesimplyneedtocountthenumberoffollowonvaluespermovie.Usingthebook’ssyntax,theSQLwouldbe:WITHRECURSIVEFollowOn(movie,followOn)AS(SELECTmovie,sequelFROMSequelOf)UNION(SELECTF.movie,S.sequelFROMFollowOnF,SequelSWHEREF.followOn=S.movie)SELECTmovie7FROMFollowOnGROUPBYmovieHAVINGCOUNT(followon)=2;(f)Thisis,inasense,areverseof(e)above,becausetohaveatmostonefol-lowonmeansthatthetotalcountofthetuplesgroupedbythegivenmoviexmustbenogreaterthan2(oneforthemovieanditssequel,andtheotherforthesequelanditssequel).Usingthebook’ssyntax,theSQLwouldbe:WITHRECURSIVEFollowOn(movie,followOn)AS(SELECTmovie,sequelFROMSequelOf)UNION(SELECTF.movie,S.sequelFROMFollowOnF,SequelSWHEREF.followOn=S.movie)SELECTmovie,followonFROMFollowOnWHEREmovieIN(SELECTmovieFROMFollowOnGROUPBYmovieHAVINGCOUNT(followon)=2);Exercise10.2.4(a)WITHRECURSIVEPath(class,rclass)AS(SELECTclass,rclassFROMRel)UNION(SELECTPath.class,Rel.rclassFROMPath,RelWHEREPath.rclass=Rel.class)SELECT*FROMPath;(b)WITHRECURSIVEPath(class,rclass)AS(SELECTclass,rclass8FROMRelWHEREmult=’single’)UNION(SELECTPath.class,Rel.rclassFROMPath,RelWHEREPath.rclass=Rel.classANDRel.mult=’single’)SELECT*FROMPath;(c)WITHRECURSIVEPath(class,rclass)AS(SELECTclass,rclassFROMRelWHEREmult=’multi’)UNION(SELECTPath.class,Rel.rclassFROMPath,RelWHEREPath.rclass=Rel.class)UNION(SELECTRel.class,Path.rclassFROMPath,RelWHERERel.rclass=Path.class)SELECT*FROMPath;(d)Thiscouldbeviewedasrelationfrom(a)EXCEPTrelationfrom(b).WITHRECURSIVEPathAll(class,rclass)AS(SELECTclass,rclassFROMRel)UNION(SELECTPathAll.class,Rel.rclassFROMPathAll,RelWHEREPathAll.rclass=Rel.class),RECURSIVEPathSingle(class,rclass)AS(SELECTclass,rclassFROMRel9WHEREmult=’single’)UNION(SELECTPathSingle.class,Rel.rclassFROMPathSingle,RelWHEREPathSingle.rclass=Rel.classANDRel.mult=’single’)SELECTclass,rclassFROMPathAllEXCEPTSELECTclass,rclassFROMPathSingle;(e)Weincludetheedgelabelaspartoftherecursiverelationandthen,basi-cally,webuildthepathasin(a)exceptweonlyaddedgesthathaveanoppositelabel.WITHRECURSIVEPath(class,rclass,mult)AS(SELECTclass,rclass,multFROMRel)UNION(SELECTPath.class,Rel.rclass,Rel.multFROMPath,RelWHEREPath.rclass=Rel.classANDPath.multRel.mult)SELECT*FROMPath;(f)WITHRECURSIVEPath(class,rclass)AS(SELECTclass,rclassFROMRelWHEREmult=’single’)UNION(SELECTPath.class,Rel.rcl