ANameCityBirthDatePUBLISHERPId PNameAddressPCityBOOKISBN TitleAIdPIdTypePriceSALESISBN Date SoldCopyNumberAssumethefollowingcardinalitiescardAUTHOR105tuplesMINBirthDate11 ID: 367337
Download Pdf The PPT/PDF document "DatabaseManagementSystemsFebruary22nd201..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
DatabaseManagementSystemsFebruary22nd20111.(6Points)Thefollowingrelationsaregiven(primarykeysareunderlined):AUTHOR(AId ,AName,City,BirthDate)PUBLISHER(PId ,PName,Address,PCity)BOOK(ISBN ,Title,AId,PId,Type,Price)SALES(ISBN ,Date ,SoldCopyNumber)Assumethefollowingcardinalities:card(AUTHOR)=105tuples,MIN(BirthDate)=1-1-1949,MAX(BirthDate)=31-12-1978,numberofCity'10,card(PUBLISHER)=103tuples,numberofPCity'10,card(BOOK)=107tuples,MIN(Price)=5,MAX(Price)=44,numberofType'20,card(SALES)=109tuples,MIN(Date)=01-01-2010,MAX(Date)=31-12-2010,Furthermore,assumethefollowingreductionfactorforthegroupbycondition:havingsum(SoldCopyNumber)1.000'1 10.ConsiderthefollowingSQLquery:selectB1.ISBN,B1.Title,A.AnamefromBOOKB1,AUTHORAwhereB1.AId=A.AIdandA.City='Milan'andB1.Type&#x-0.9;畵'Historicalnovel'andB1.ISBNNOTIN(selectS.ISBNfromSALESS,BOOKBwhereS.ISBN=B.ISBNandB.Price&#x-0.9;畵40andS.Date01/04/2010andS.Date30/04/2010groupbyS.ISBNhavingsum(SoldCopyNumber)1.000)FortheSQLquery:(a)Reportthecorrespondingalgebraicexpressionandspecifythecardinalityofeachnode(repre-sentinganintermediateresultoraleaf).Ifnecessary,assumeadatadistribution.Alsoanalyzethegroupbyanticipation.(b)Selectoneormoresecondaryphysicalstructurestoincreasequeryperformance.Justifyyourchoiceandreportthecorrespondingexecutionplan(joinorders,accessmethods,etc.). 2.(7Points)Thefollowingrelationsaregiven(primarykeysareunderlined,optionalattributesaredenotedwith*):JURYMAN(JuryManCode ,Name,City)JURYMAN EVALUATION(JuryManCode ,SongCode ,Score)TOTAL SCORE FOR EACH SONG(SongCode ,NumberOfJurymen,TotalScore,IsWinner)Writethetriggersmanagingthefollowingactivitiesforasongcompetition.Duringthecompetition,14di erentsongsarepresented.Thesesongsareevaluatedbyajury,whichvotesthewinningsong.(1)Integrityconstraintonthecompositionofthejury.TheJURYMANtablecontainsthecompositionofthejury.Thejurymustincludeatmost300jurymen.Allmodi cationoperationsontheJURYMANtableviolatingtheintegrityconstraintmustnotbeexecuted.Writethetriggerenforcingthisintegrityconstraint.(2)Selectionofthewinningsong.Eachjurymanassignsascoretoeverysong.Foreachsong,theTOTAL SCORE FOR EACH SONGtablecontainsthenumberofjurymenwhohaveassignedascoretothesong(attributeNumberOfJurymen)andthetotalscoreassignedbythem(attributeTotalScore).Theevaluationprocessisconcludedwhen,forall14songs,alljurymenhaveassignedtheirscore.Atthispoint,thesongwiththehighesttotalscoreisselected.Thisisthewinningsong.Writethetriggermanagingthefollowingactivities.Whenajurymanassignsascoretoasong(anewrecordisinsertedinJURYMAN EVALUATION),thetriggerpropagatesthemodi cationsontheJURYMAN EVALUATIONtabletotheTOTAL SCORE FOR EACH SONGtable.Then,thetriggerchecksiftheevaluationprocessisconcluded.Inthiscase,forthewinningsong,theIsWinnerattributeissetto'Yes'.2 3.DataWarehousedesignProblemspeci cationsAmarketanalysiscompanyisinterestedinanalyzingtheactivitiesofsocialnetworkusers.Thecompanywantstoevaluatesomestatisticsaboutregisteredusers(bothnewlyregisteredandtotalusers),andsomespeci cactivities.Inparticular,theactivitiesofinterestaretheadditionofanotherusertotheowncontactlist(friendsorfollowers).Thecompanyhasbeenallowedaccesstothedatabasesofthemostpopularsocialnetworkingsites.Thesedatabasesshouldbeintegratedinadatawarehousethatallowsthefollowinganalysestobeecientlyperformed.Thecompanywouldliketoanalyzethenumberoftotalusers,thenumberofnewlyregisteredusersandtheaveragenumberofcontacts(friendsorfollowers)foreachuser,accordingto:thedayoftheyear(1-366),thedayoftheweek(Monday-Sunday),thedayofthemonth(1-31),workingdaysorholidays,weekoftheyear(1-52);thedate,themonth,the2-monthperiod,thetrimesterandtheyear;thesocialnetworkingsite,theyearofitsfoundation,thenationinwhichthewebsitewasfounded;thebirthyear,thegenderandthehomenationoftheusers.Thedatawarehousewillstoreinformationaboutyears2006-2010.Moreover,thefollowingstatisticsareknown(thecandidatemayestimatemissinginformationthatshedeemsrelevant):20socialnetworkingsitesfoundedin10di erentnationsbetween2004and2006areconsidered;theusersbelongtoroughly200di erentnationsandareagedbetween15and65years;unsubscriptionsarenotconsidered,thusausercanstopusingawebsitebutshecannotcancelhersubscription(thetotalnumberofregisteredusersismonotonicallyincreasing);Thefollowingaresomeofthefrequentanalysesthecompanyisinterestedin:(a)foreachsocialnetwork,selectthemonthlytotalnumberofnewlyregisteredusersandthemonthlygrowthpercentageofthesocialnetwork.Themonthlygrowthpercentageisde nedastheratiobetweenthenewusersregisteredintheconsideredmonthandthetotalnumberofsubscribedusersintheconsideredmonth.(b)Foreachsocialnetworkandforeachmonth,selectthedailyaverageofnewusers,separatelyforfemaleandmaleusers.(c)Selectthetotalnumberofnewlyregisteredusersforeachdayoftheweekandthepercentageofnewlyregisteredusersforeachdayoftheweekrespectwiththetotalnumberofnewlyregisteredusers,separatelyforfemaleandmaleusers.Assignadecreasingranktothenumberofnewlyregisteredusersforeachpair(gender,dayoftheweek).Design(a)(7points)Designthedatawarehousetoaddressthedescribedissues.Inparticular,thedesigneddatawarehousemustallowecientexecutionofallthequeriesdescribedinthespeci cations.3 (b)(4points)Writefrequentquery(b)ofthe\problemspeci cations"usingtheextendedSQLlanguage.(c)(Optional:5points)Writefrequentquery(a)ofthe\problemspeci cations"usingtheextendedSQLlanguage.4