Practice Exam 2011, Advanced Database Systems, questions and answers PDF

Preview

Summary

Download Practice Exam 2011, Advanced Database Systems, questions and answers PDF

Description

Question1.FundamentalsofDBMS  1.WhatdoesACIDstandfor? Atomicity Consistency Isolation Durability  2.Whatarethetwobasicdatabaseaccessoperationsinatransaction?  Readandwrite.  3.Whatisthepurposeofsystemlogusedinatransactionprocessingsystem? Thesystemlogkeepsarecordofalltransactionoperationsondatabaseitems.  Sooperationsofthetransactioncanbetracedbackandhavingrecordedoldvaluesofwritten itemsyoucandoarollback.Itcanfacilitateinasystemfailure.  4.Nametwotransactionprocessingsystemsyoumayhaveusedinyourdailylife? bankingsystems airlinereservations Ebaypurchase  5.Whatisthepurposeofconcurrencycontrolinatransactionprocessingsystem? Concurrencycontrolensuresthattransactionsrunninginparallelappearasiftheyarerunin isolation.Topreventmultipledifferentusersfromtryingtoupdatethesamedataatthesame timewhilefollowingACIDproperties.     

Question2.DistributedDatabaseDesign  1.GivetwobenefitsofDDBsandbrieflyexplainthem.  ANYOFTHEFOLLOWING:  ○ Transparency ■ Network ● Doesn’tmatterwherethedataisstoredonthenetwork ■ Replication ● cascadeupdates ■ Fragmentation ■ Onlyuseasectionofthetablesneededinsteadoftheentiredatabase ● Abstractionoftheentiresystem ○ Reliability ■ Nosinglepointsoffailure(multiplenodes) ○ Availability ■ Redundancy ○ Performance ■ Doingthingsinparallelorhavingalocalcopyofthedata ○ Expansion ■ Easytoaddanothernodeinsteadofhavingahugemulticomputer ○ DataIntegrity     

2.Thefollowingglobalschemaisgiven:  R1=(ABCD) R2=(EFIG) R3=(HPZ)  Assumetherearetwosites.Site1frequentlyaccessestuplesthatsatisfythefollowing conditionsE>200,A500;andsite2frequentlyaccessestuplesthatsatisfyE=100,H>600.Designacorrecthorizontaldatafragmentationoftheaboveschema.If replicationsoffragmentsareneeded,describethemandjustifyyourdecision.  Site1: R1.1=(A,B,C,D)whereA=300. R2.2=(E,F,I,G)whereE=100. ReplicationofR2.2. ReplicationofR3.2.  Justification: R1 R1hasacompletehorizontalfragmentationoverthetwosites.  R2  Site1containsthefragmentR2.1whereE>=300. Site1alsocontainsthefragmentR2.2whereE200andE300.ItjustneedstobeabletoaccessE>200...butyesthattoo   Site2containsareplicationofthefragmentR2.2whereE=500. Site2containsareplicationofthefragmentR3.2whereH>=500.  ThisleavesthefragmentR3.1,whichcontainstheinfrequentlyaccessedtuplesEpreparetoall, all>voteyettoHobart, Hobart>committoall, all>acktoHobart  Hobartend  

 Question5. 1)Completethefollowingarchitectures(LS:localschema,LES:localexternalschema) 



  

Local/Global externalschema=user’sview:thedefinitionofdataisinthecontextofreportsandscreens designedtoaidindividualsindoingtheirspecificjobs.  internalschema=computerview:dataisdefinedintermsoffilestructuresforstorageand retrieval.    

2)Considerdatamodelsoftwocompanies:  IncompanyA,employeerecordsarestoredinonetableEmp Emp(Emp#,Fname,Lname,Bdate,Dept#,Rank,Salary) IncompanyB,employeerecordsarestoredinmanytablesoneforeachcompanydepartment Deptxxx(Sid,Fname,Sname,Position,Phone#,email,URL) Createaviewtointegrateallemployeerecordsinthesetwocompanies(supposecompanyB hastwodepartments).   CreateviewEMP_DATA(Emp#,Fname,Lname,Bdate,Dept#,Rank,Salary,Position,Phone#, email,URL)AS  SELECTEmp#,Fname,Lname,Bdate,Dept#,Rank,Salary,NULL,NULL,NULL,NULLFROM EMP  UNION  SELECTSid,Fname,Sname,NULL,‘Dept01’,NULL,NULL,Position,Phone#,email,URL FROMDept01  UNION  SELECTSid,Fname,Sname,NULL,‘Dept02’,NULL,NULL,Position,Phone#,email,URL FROMDept02  . Question6.DataQualityControl(5Marks) 1)WhatarethebasicstepsofDataGovernance?(2marks)  1.Recognisetheproblem. 2.Measureitscosts(usingmetrics). 3.Deviseasolutiontotheproblem. 4.Aimtowardsdatagovernancematurity.  2)Thefollowingarethevalues(i.e.strings)infieldBUSINESS_NAMEinatable  BUSINESS_NAME AmazonOnlineBook BordersBookStore

SecondHandBookShop BennettsBookStore  Supposethefieldvaluesaretokenizedinto.words.Foreachofthefollowingtokens,whatis thetermfrequency,inversedocumentfrequencyandtermscore.(3marks) a)Amazon documentfrequency=1 no.docs=4 idf=4/1=4  (AmazonOnlineBook) termscore=tf*idf tf=1 termscore=log(1+tf)*log(4)=log(2)*log(4)=0.181    

b)Book documentfrequency=4 no.docs=4 idf=4/4=1  (AmazonOnlineBook) tf=1 termscore=log(1+tf)*log(1)=log(2)*log(1)=0  (BordersBookStore) tf=1 termscore=log(1+tf)*log(1)=log(2)*log(1)=0  (SecondHandBookShop) tf=1 termscore=log(1+tf)*log(1)=log(2)*log(1)=0  (BennettsBookStore) tf=1 termscore=log(1+tf)*log(1)=log(2)*log(1)=0





  Project Part Supplier Quantity    



2)Astarschemawiththefacttabledesignedin1),andappropriatedimensiontables(2marks) 

    ProjectDimension Project_No Name Budget Manager   PartDimension Part_No Name Weight Dimensions Type  

SupplierDimension Supplier_ID Address Phone Fax Website Contact   3)Supposethereare5supplier,4parts,and3projectsinthisdatacube.Selecttwodimensions andshowthecrosstabulationbypivotingonthesetwodimensions(leavecellsempty) (2marks)  Notcoveredin2012.   

Question8.SpatialDataManagement(5Marks) 1)SpatialRangequery,NearestNeighbourqueryandSpatialJoinqueryaremainquerytypes overspatialdata.Explainthem.(2marks)  Notcoveredin2012.  

  Notcoveredin2012.  

Question9.RFIDandSensorDataManagement(5Marks)  SupposeyourRFIDapplicationisparceltracking.ThedatacollectedbyRFIDtagscontinues flowingin.Usercanquerytheinformationaboutthelocationsofparcelsaswellasarrivaland departuretime.Twoapproachescanbeusedtoanswerqueries.TheyarePushapproachand Pullapproach. 1)Brieflyexplainthesetwoapproaches(2marks)  Notcoveredin2012.  2)UserAisanetworktroubleshooterwhosejobistoidentifyparcelswheretheactualstation differsfromtheexpectedstation.Forthistypeofusers,whichapproach(Push/Pull)willbe usedtoanswerthequery?Andwhy?(3marks)  Notcoveredin2012.    

Question10.DataModels(5Marks), 1)Tickfromthefollowingsthemainreasons(multipleticks)ofthesuccessoftherelational model(2mark)  ☑Logicaldataindependenceeasierwithasimplerdatamodel ☑Setatatimelanguagesaregoodbecausetheyprovidephysicaldataindependence oSchemafirst oBetterathidingmathematics ☑Functionaldependenciesandnormalization ☑Effectivequeryoptimizers  IalsohadwritteninmynotesthattheRelationalmodelusessetatatimewhichprovides physicaldataindependence.  2)XMLisasemistructureddatamodel.WhatisthemainapplicationareaofXMLcurrently andintheexpectedfuture?WhatisthemaindisadvantageofXML?(3marks)  XMLiscurrentlyadatamodelandisalsoadatainterchangeformat.Itisexpectedthatthedata modelwilldieout,whilethedatainterchangeformatwillcontinuetobeused.XML’s disadvantageasadatamodelisthatitisexcessivelycomplex,notclearonhowtoimplementlet alonebeefficient. ...