Answerwithadequatedetailsandclearlyciteyourreferenceswheneverapplicable. Allworkmustbedoneindividually.
Question1(10pts) Letourdatabasebe:
Employee{EmpId,DeptId,Salary} Department{DeptId,ManagerId,Budget}
AssumethatalloftableEmployeeisstoredatsite_1andallofDepartmentisstored insite_2.Thequery:
SELECT *
FROM EmployeeE,DepartmentD WHERE E.EmpId=D.ManagerId
isissuedatsite_3.Weknowthatonly1%oftheemployeesaremanagers.
A. Whataretheoptionsthatyou haveintermsofprocessingthequery?Whatother informationdoyou needinordertoevaluatethecostofeachoptioninorderto
makeadecision?Inordertosolvethisproblem,makesomerealistic
assumptionsforthisinformationthatyou needandthenselectwhatyouconsider
tobethebest(intermsofcost)optionforexecutingthequery.Youranswermust includeallthenecessarydetails
B. Afteryouanswerthepreviousquestion,giveyouranalysis,opinion,and evaluationofusingtheprevailingcostmodelsforqueryprocessingina
distributeddatabaseenvironment.
Question2(10pts)
Assumethatyou areassignedthetaskofleadingtheprocessofdesigningdistributed databaseforalargeglobalsearchengine.Howwillyouhandlethistask?
Explainyour approach,thedesignprocess,and theimportantfactorsthatwillimpactthedesignand implementationprocess.
Rememberthatcompaniesthatprovidesearchenginesalso incorporatetheuseofadvertisementsandsponsorshipofproductsandservicesaswell as
manyotherbusinesspracticesaspartofitsbusinessmodel.
Onemethodtoensureprivacyofdataistodistributethedataintofragmentssothatin thecaseofillegalaccesstothedatain
onesite,thedamageisverylimited.For example,ifsomeonewasable to accessdataaboutnamesandsocialsecuritynumbers together,thenthedamagecanbe
significant.However,ifsomeonecanaccessthe socialsecuritynumberswithoutthenames,then thereisverylittleconcernfromsecurity
perspective.Anotherapproachisto use encryption,however,thisapproachshouldbe avoidedasmuchaspossibleandshouldbeusedasalastresource.
Acertaincompanyisveryconcernedabouttheprivacyandsecurityof itsinformation
thatisstoredintableProject.Weneedtoverticallyfragmenttherelation:
Project{project_name,mngr_id,duration,budget,priority}
into2 fragment.Therelationhasthefollowingprivacyconstraints(meaningwecannot storethesameattributesinthesamefragment):
o {budget,mngr_id}
o {budget,start_date}
o {budget,priority}
Whataregoodverticalfragments,in ordertominimizetheuseofencryption?The
fragmentationofdataisperformedinsuchafashionastoensurethattheexposureof
thecontentsofanyonedatabasedoesnotresultinaviolationofprivacy.Explainyour answer.
Question4(5pts)
Mostofthetime,thetopicofDistributedDatabasesisdiscussedinconjunctionwith ParallelDatabases.
Whatarethemostimportantsimilaritiesanddifferencesbetween them?Whatarethefactorsthatwillimpactyourdecisionofselectingoneversusthe other?
Question5(10pts)
CompanyAacquiredCompanyB.You areassignedthetaskofleadingtheprocessof mergingbothdatabasessothatallapplicationcanaccessthedataasasingle
database.Howwillyouhandlethistask?Explainyourapproach,thedesignprocess,
andtheimportantfactorsthatwillimpactthedesignandimplementationprocess.
Letourdatabasebe:
Employee{EmpId,DeptId,Salary,Sex,DOB}
ThefollowingstatisticsarestoredintheDBMS’ssystemtables:
o Thereare10000rowsin thetable.
o Thereare20departmentsinthecompany.
o Sexiseither’M’or’F’.
o Thelowestsalaryis20,000andthehighestis100,000.
o Flatdistributionsofdatavaluesincolumns
Whatistheselectivityofthefollowingminterms:
o SALARY<35000
o ((SEX=’M’ANDDEPT=’HR’)OR
(SEX=’F’ANDDEPT=’Software’))AND
(SALARY>50000ORSALARY<25000)
Question7(5pts)
Considerthefollowingthreerelations(underlinednamesindicatekeys):
o E(eid,ename,salary,location),whichcontainstheID,name,salary, andlocationofemployeesofsomecompany.Only thevalues”NY”and”LA”
appearinthelocationattribute.
o P(pname,mid,start,budget,priority),containingthename, managerID,startdate,budget,andpriorityofcurrentprojectsatthecompany.
o A(pname,eid),containingtheproject-employeeallocations.
WhenEisaccessed,thefollowingpredicatesappearinamajorityofthequeries:
salary<100;salary 200 location=LA;location=NY
Whatarethefragmentsthatresultfromperformingprimaryhorizontalfragmentationon
E,usingthepredicateslistedabove.