csci585 midterm exam solutions fall...
TRANSCRIPT
CSCI585Fall’18MidtermExamOctober19th,2018
CLOSEDbookandnotes.Noelectronicdevices.DOYOUROWNWORK.Duration:1hour.Ifyouarediscoveredtohavecheatedinanymanner,youwillgeta0andbereportedtoSJACS.Ifyoucontinueworkingontheexamaftertimeisupyouwillgeta0.Thisdocumentcontains12pagesincludingthisone.
Signature:_________________________________
ProblemSet NumberofPointsQ1 5Q2 5Q3 6Q4 7Q5 7Q6 4Q7 1Total 35
Q1.(5pointstotal)ERMODELINGYouarerequiredtofillinthefiveblanksintheERDiagramofalibrarydatabasesoitmeetsthefollowingrequirements.Forblanks1and2,pleasewritethekeytype.Forblanks3,4and5,pleasedrawanedgetorepresenttherelationshipbetweenitsentities.Feelfreetodrawedgesonthediagram,butpleasecopythemontheblanksaswell(tobegraded).
Thelibraryhastwotypesofitemstocheckout,booksandmedia.Foreachitem,thedatabaseneedstorecorditsuniqueItem_ID,title,contributorandpublisher.EachitemisalsoassignedonecategorylikeScience,Art,Historyandsoonandeachcategoryisassignedtooneormoreitems.Eachitemcanbecheckedoutbyatmostonestudentandthedatabaseshouldrecordwhoborrowedtheitemandduedateforreturn.Forabook,thedatabaseshouldrecorditsnumberofpages.Foramediaitem,thedatabaseshouldrecordnumberofCDscontainedinit.Allitemsshouldbeinthedatabaseregardlesswhethertheyareavailableorhavebeenalreadycheckedout.
Studentscanborrowzero,oneormoreitemsfromthelibrary.EachstudenthasauniqueStudent_ID.Thedatabaseshouldrecordallstudents’Student_IDsandnames.
Solution
①PK
②FK1,FK2(orjustFK)
③
④
⑤
Q2.(5pointstotal)SQL A.(2points)Writeabriefdescriptionofwhatthefollowingquerydoes.Thesemanticsshouldbestraightforward,butyoucanmakeanyreasonableassumptions(ie:ViterbiisaschoolwithinUSC,etc.) B.(3points)SketchthebasicERdiagram/schema,showentities,attributes,andconnectionsbetweenthem(relationships).Tablenamesare:uscstudent,course,coursedescription,uscschool,semester,andstudentsemesterenrollment.SELECT stu.student_id, stu_fname, stu_lname, stu_email, totalunits FROM uscstudent stu JOIN ( SELECT uscstudent.student_id, Sum(course.course_numofunits) AS totalunits FROM ( SELECT * FROM studentsemesterenrollment sse JOIN uscstudent scs ON ( sse.student_id = scs.student_id ) JOIN semester sem ON ( sse.semester_id = sem.course_id ) ) sem JOIN course c ON sem.semester_code = c.semester_code JOIN coursedescription cd ON c.course_id = cd.course_id JOIN uscschool sch ON sch.school_id = cd.school_id WHERE uscschool.school_name = 'VITERBI' AND semester.semester_date BETWEEN '01-JAN-18' AND '31-DEC-18' GROUP BY uscstudent.student_id ) tommy ON stu.student_id = tommy.student_id WHERE totalunits = ( SELECT Max(totalunits) FROM ( SELECT uscstudent.student_id,Sum(course.course_numofunits) AS totalunits FROM ( SELECT * FROM studentsemesterenrollment sse JOIN uscstudent scs ON ( sse.student_id = scs.student_id ) JOIN semester sem ON ( sse.semester_id = sem.course_id ) ) sem JOIN course c ON sem.semester_code = c.semester_code JOIN coursedescription cd ON c.course_id = cd.course_id JOIN uscschool sch ON sch.school_id = cd.school_id WHERE uscschool.school_name = 'VITERBI' AND semester.semester_date BETWEEN '01-JAN-18' AND '31-DEC-18' GROUP BY uscstudent.student_id ) );
Q2.Solution Thisisaquerytodisplaythestudentid,studentfirstname,studentlastname,e-mail,andtotalcourseunitstakenforthestudentwhotookthemostViterbischoolclassesbetweenJanuary1,2018,andDecember31,2018.Thefollowingsubquery:FROM(SELECT*FROMstudentsemesterenrollmentsseJOINuscstudentscsON(sse.student_id=scs.student_id)JOINsemestersemON(sse.semester_id=sem.course_id))semIsabridgetablethatlinkstheuscstudentandsemestertableswithM:Nrelationship.Therestshouldbeclearwiththefollowingdiagram:
Q3.(6pointstotal)NORMALIZATIONShowdependencydiagramandnormalizethefollowingtablein3NF.StudentIDName AgeCourseIDCourseName RegisteredOn12 Alex 19 CSCI511 C++ 08/11/2018
CSCI510 Java 08/12/2018123 Bin 20 CSCI511 C++ 08/05/2018
CSCI670 Algorithms 08/05/201832 Young18 CSCI550 DataStructures 08/15/2018
CSCI511 C++ 08/11/2018CSCI585 DatabaseSystems08/11/2018
133 Tracy 20 CSCI520 Math 08/09/2018CSCI510 Java 08/09/2018
Solution
Q4.(7points)TRANSACTIONMANAGEMENTYouaregiventheexampletablesthatrepresentinformationofafactory,aretailer,andacustomer.Eachtablehasinformationofproductsandtheircounts.Alsoprovidedisatransactionlog(onthenextpage),whichcontains2transactions:onerepresentsproductionof100productsfromfactorytoretailer,theotherrepresentsapurchaseof150productsbyacustomer.
(1) ConsiderthecasethatlockingisnotproperlyimplementedintheDBMS.Discusswhethertheresultsofthetwotransactionsaredeterministic.(Noneedtoconsiderotherexternaltransaction,butfailureorrollbackcanhappen).
(2) ConsiderthattheDBMSinuseisimplementingalockingmechanism.Istwo-phaselockingrequiredtoensurecorrectnessofthetwotransactions?Stateyourreasons.(Noneedtoconsiderotherexternaltransactions,butfailureorrollbackcanhappen).
(3) Considerthatpessimisticlockingisimplementedwithtwo-phaselockingprotocol.Createachronologicallistoflocking,unlocking,anddatamanipulationactivitythatwouldoccurduringthecompletionofthetwogiventransactions.(Nostepfailsandnorollbackhappens).
Exampletables:
FACTORY
PRODUCT_ID PRODUCT_COUNT
42 1000
RETAILER
PRODUCT_ID PRODUCT_COUNT
42 58
CUSTOMER
CUSTOMER_ID PRODUCT_ID PRODUCT_COUNT
1007 42 3
Q4.(Continued)TransactionlogTRL_ID TRX_NUM PREVPTR NEXTPTR OPERATIONDESCRIPTION
214 101 Null ****StartTransaction
216 101 214 225 Update"RETAILER"tableontherowwithPRODUCT_ID=42andadd100toPRODUCT_COUNT
225 101 216 233 Update"FACTORY"tableontherowwithPRODUCT_ID=42andsubtract100fromPRODUCT_COUNT
233 101 225 Null ****EndofTransaction
220 105 Null ****StartTransaction
227 105 220 239 CheckthatPRODUCT_ID=42inRETAILERtablehasPRODUCT_COUNT>150andwaituntiltheconditionismet.
239 105 227 243 Update"RETAILER"tableontherowwithPRODUCT_ID=42andsubtract150toPRODUCT_COUNT
243 105 239 252 Update"CUSTOMER"tableontherowwithPRODUCT_ID=42andCUSTOMER_ID=1007andthenadd100toPRODUCT_COUNT
252 105 243 Null ****EndofTransaction
Solution
(1) Theresultwillbenon-deterministicifnolockingisimplemented.Eveniftransaction105checksthatPRODUCT_ID42shouldhaveatleast150itemsbeforeproceedingwhichseemstosuggestthattransaction105willnotproceedbeforetransaction101isdone,itisstillpossiblethatoneofthetransactionisabortedthatmaycauseinconsistencies,forexample,considerthefollowingevents:*TRX101starts*TRX101updatesRETAILERtable,nowRETAILER.PRODUCT_COUNT=158forRETAILER.PRODUCT_ID=42*TRX105starts*TRX105checksRETAILERtable,findtheRETAILER.PRODUCT_COUNT>150forRETAILER.PRODUCT_ID=42,andproceedtothenextstep*TRX105updatesRETAILERtablebysubtracting150forRETAILER.PRODUCT_ID=42,nowRETAILER.PRODUCT_COUNT=8*TRX101failedtoupdateFACTORYtableinitsnextstep,andthewholeTRX101isreverted,nowRETAILER.PRODUCT_COUNT=58again.*TRX105updatesCUSTOMERtable,nowthatCUSTOMER.PRODUCT_COUNT=153for
CUSTOMER.PRODUCT_ID=42andCUSTOMER.CUSTOMER_ID=1007Theresultofthisexampleshowsthecustomersuccessfullybought150itemswhiletheothertablesarenotupdatedproperly.Hence,aproperlockingmechanismisrequired.
(2) Two-phaselockingprotocolisrequired,becauseinTRX101,updatingofRETAILERtablehappensbeforeupdatingFACTORYtable,whichhasthepossibilitythatthelattermayfailandrollbackthetransaction(liketheexamplegivenintheaboveanswer).Withouttwo-phaselockingprotocol,onlylockingonetablemaynotensurecorrectnessonceerrorsoccur.
(3) Exampleofchronologicalevents
Time TRX_NUM Event
1 101 LocktableRETAILER
2 101 LocktableFACTORY
3 101 UpdatetableRETAILERbyadding100toPRODUCT_COUNTofPRODUCT_ID=42
4 101 UpdatetableFACTORYbysubtracting100toPRODUCT_COUNTofPRODUCT_ID=42
5 101 UnlocktableFACTORY
6 101 UnlocktableRETAILER
7 105 LocktableRETAILER
8 105 LocktableCUSTOMER
9 105 ChecktableRETAILERofPRODUCT_ID=42thatPRODUCT_COUNT>150
10 105 UpdatetableRETAILERbysubtracting150toPRODUCT_COUNTofPRODUCT_ID=42
11 105 UpdatetableCUSTOMERbyadding150toPRODUCT_COUNTwithPRODUCT_ID=42andCUSTOMER_ID=1007
12 105 UnlocktableCUSTOMER
13 105 UnlocktableRETAILER
Q5.(7points)OPTIMIZATION
Considerthethreefollowingtablesforanairportdatabaseandallattributesareneitherindexednorsorted.
• AIRPLANES(aid,brand,size),aidistheprimarykey.• PILOTS(pid,name,age),pidistheprimarykey.• LastFlight(aid,pid,date),aidandpidareacompositeprimarykey.
AndwewanttoexecutethefollowingSQLquery:
SELECTP.nameFROMAIRPLANESA,PILOTSP,LastFlightLWHEREA.aid=L.aidANDP.pid=L.pidANDP.age<35ANDA.brand=‘Boeing737’;
Assuming:• Thereare1,000rowsinAIRPLANES,1,000rowsinPILOTSand1,000,000rowsin
LastFlight.• PILOTS.agerangesfrom[30to49](bothinclusive)equallydistributedinPILOTS.• AIRPLANES.brandhas100distinctvaluesequallydistributedinAIRPLANES.• LastFlighthaseverycombinationofaidandpid.
SupposethecostofrunningaSELECToperationisthenumberofrowsinthesourcetableandthecostofrunningaJOINoperation(Cartesianproduct)isthetotalrowsofthetwosourcetables.Ifweexecutethequerywithfollowingaccessplan,thecostwillbe1,001,001,002,000.Step Operation Cost EstimatedresultrowsA1 Cartesianproduct(A,L) 1,001,000 1,000,000,000A2 Cartesianproduct(A1,P) 1,000,001,000 1,000,000,000,000A3 SelectrowsinA2withallconditions 1,000,000,000,000 2,500**Hereishowthenumberofresultingrowswereestimated:-ThepossibilityofA.aid=L.aidis1/1,000forthereare1,000differentaid.-ThepossibilityofP.pid=L.pidis1/1000forthereare1,000differentpid.-Thepossibilityofanairplanebrand=‘Boeing737’is1/100forthereare100differentbrands.-ThepossibilityofP.age<35is5/20.-Sinceallconditionsareindependent,thenumberofresultingrowsinA3isabout:
1,000,000,000,000*(1/1,000)*(1/1,000)*(1/100)*(5/20)=2,500.Doyouhaveabetteraccessplantoexecutethequerywithalowertotalcost?Pleasefillthefollowingformaboutyouraccessplan.
• Youdon’thavetofillallrowsdependingonhowmanystepsareinyouraccessplan.• Thereshouldbeenoughroomineachcellforyoutoanswerandmakecorrections.
Q6(4points)DISTRIBUTEDDATABASESListandexplaincharacteristicsofdistributeddatabases(provideclearexplanationand/orexamples).SolutionThisquestionwasdesignedtoteststudent’sunderstandingofdistributeddatabasesystems.OnepotentialansweristolistandexplainseveralDDBMSfunctions.Forsampleanswer,pleaserefertochapter12-4onpage559ofclasstextbook.Thealternateanswerwastolistandexplainthedistributeddatabasetransparencyfeatures:distribution,transaction,failure,performance,andheterogeneitytransparencies.Forsampleanswers,pleaserefertochapter12-7onpage564ofclasstextbook.