数据库复习题2(答案)分析解析

复习题（2）1、试分别判断下列图中G1和G2是否互模拟(bisimulation)，并说明理由:答案：(1)在图中标出各点的状态，我们构造关系，可知G2可以模拟G1，下面我们讨论abcabccG1G2dddaaabccbG1=G2=是否可模拟，在G2中有一个a变换可对应到G1中2个变换，即，。但有两个变换b，c，而在G1中仅存在只有b或只有c的状态点，可知G1和G2不能互模拟。(2)如图，标出各状态点，构造有关系可知其中G1中的点均可由G2中的点模拟，下面我们考虑可知同样其中G2中的点均可由G1中的点模拟.所以G1和G2为互模拟的。2、给定如下数据图(DataGraph)：r1c1c2s2s3s6s7s10companycompanynameaddressnameurladdress“Widget”“Trenton”“Gadget”“”“Paris”r1c1c2s2s3s6s7s10companycompanynameaddressnameurladdress“Widget”“Trenton”“Gadget”“”“Paris”p2p1p3s0s1s4s5s8s9personpersonperson“Smith”namepositionnamephonenameposition“Manager”“Jones”“5552121”“Dupont”“Sales”employeemanagesceoworks-forworks-forworks-forceo试给出其StrongDataGuide图答案：r1p1,p2,p3c1,c2s0,s4,s8s1,s9s5s2,s6s3,s7s10p2p1,p3personnamepositionphonenameaddressurlceoemployeemanagesworks-forStrongDataGuide图3、Considertherelation,r,showninFigure5.27.Givetheresultofthefollowingquery:Figure5.27Query1:selectbuilding,roomnumber,time_slo_id,count(*)fromrgroupbyrollup(building,roomnumber,time_slo_id)Query1:selectbuilding,roomnumber,time_slo_id,count(*)fromrgroupbycube(building,roomnumber,time_slo_id)答案：Query1返回结果集：为以下四种分组统计结果集的并集且未去掉重复数据。buildingroomnumbertime_slo_idcount(*)产生的分组种数：4种；第一种：groupbyA,B,CGarfield359A1Garfield359B1Saucon651A1Saucon550C1Painter705D1Painter403D1第二种：groupbyA,BGarfield359A2Garfield359B2Saucon651A1Saucon550C1Painter705D1Painter403D1第三种：groupbyAGarfield359A2Garfield359B2Saucon651A2Saucon550C2Painter705D2Painter403D2第四种：groupbyNULL。本没有groupbyNULL的写法，在这里指是为了方便说明，而采用之。含义是：没有分组，也就是所有数据做一个统计。例如聚合函数是SUM的话，那就是对所有满足条件的数据进行求和。Garfield359A6Garfield359B6Saucon651A6Saucon550C6Painter705D6Painter403D6Query2:groupby后带rollup子句与groupby后带cube子句的唯一区别就是：带cube子句的groupby会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。返回结果集：为以下八种分组统计结果集的并集且未去掉重复数据。buildingroomnumbertime_slo_idcount(*)产生的分组种数：8种第一种：groupbyA,B,CGarfield359A1Garfield359B1Saucon651A1Saucon550C1Painter705D1Painter403D1第二种：groupbyA,BGarfield359A2Garfield359B2Saucon651A1Saucon550C1Painter705D1Painter403D1第三种：groupbyA,CGarfield359A1Garfield359B1Saucon651A1Saucon550C1Painter705D2Painter403D2第四种：groupbyB,CGarfield359A2Garfield359B2Saucon651A1Saucon550C1Painter705D1Painter403D1第五种：groupbyAGarfield359A2Garfield359B2Saucon651A2Saucon550C2Painter705D2Painter403D2第六种：groupbyBGarfield359A2Garfield359B2Saucon651A1Saucon550C1Painter705D1Painter403D1第七种：groupbyCGarfield359A2Garfield359B1Saucon651A2Saucon550C1Painter705D2Painter403D2第八种：groupbyNULLGarfield359A6Garfield359B6Saucon651A6Saucon550C6Painter705D6Painter403D64、[DisksandAccessTime]Consideradiskwithasector扇区sizeof512bytes,63sectorspertrack磁道,16,383trackspersurface盘面,8double-sidedplatters柱面(i.e.,16surfaces).Thediskplattersrotateat7,200rpm(revolutionsperminute).Theaverageseektimeis9msec,whereasthetrack-to-trackseektimeis1msec.Supposethatapagesizeof4096bytesischosen.Supposethatafilecontaining1,000,000recordsof256byteseachistobestoredonsuchadisk.Norecordisallowedtospantwopages(usethesenumbersinappropriateplacesinyourcalculation).(a)Whatisthecapacityofthedisk?(b)Ifthefileisarrangedsequentiallyonthedisk,howmanycylindersareneeded?(c)Howmuchtimeisrequiredtoreadthisfilesequentially?(d)Howmuchtimeisneededtoread10%ofthepagesinthefilerandomly?Answer:(a)Capacity=sectorsize*num.ofsectorspertrack*num.oftrackspersurface*numofsurfaces=512*63*16383*16=8455200768(b)File:1,000,000recordsof256byteseachNumofrecordsperpage:4096/256=161,000,000/16=62,500pagesor62,500*8=500,000sectorsEachcylinderhas63*16=1,008sectorsSoweneed496.031746cylinders.(c)Weanalyzethecostusingthefollowingthreecomponents:Seektime:Thisaccessseekstheinitialpositionofthefile(whosecostcanbeapproximatedusingtheaverageseektime)andthenseeksbetweenadjacenttracks496times(whosecostisthetrack-to-trackseektime).Sotheseektimeis0.009+496*0.001=0.505seconds.Rotationaldelay:Thetransfertimeofonetrackofdatais1/(7200/60)=0.0083seconds.Forthisquestion,weuse0.0083/2asanestimateoftherotationaldelay(othernumbersbetween0and0.00415arealsofine).Sotherotationaldelayfor497seeksis0.00415*497=2.06255.Transfertime:Ittakes0.0083*(500000/63)=65.8730159secondstotransferdatain500,000sectors.Therefore,totalaccesstimeis0.505+2.06255+65.8730159=68.4405659seconds.(d)numberofpages=6250timecostperpage:0.009(seek)+0.0083/2(rotationaldelay)+0.0083*8/63(transfer)=0.0142secondstotalcost=6250*0.0142=88.77seconds5、[DiskPageLayout]Thefigurebelowshowsapagecontainingvariablelengthrecords.Thepagesizeis1KB(1024bytes).Itcontains3records,somefreespace,andaslotdirectoryinthatorder.Eachrecordhasitsrecordid,intheformofRid=(pageid,slotnumber),aswellasitsstartandendaddressesinthepage,asshowninthefigure.Nowanewrecordofsize200bytesneedstobeinsertedintothispage.Applytherecordinsertionoperationwithpagecompaction,ifnecessary.Showthecontentoftheslotdirectoryafterthenewrecordisinserted.Assumethatyouhaveonlythepage,notanyothertemporaryspace,toworkwith.Answer:Contentoftheslotdirectory,fromlefttoright,is:[(650,200),(0,200),(500,150),(200,300)],4,8506、[BufferManagementforFileandIndexAccesses]Considerthefollowingtworelations:student(snum:int