Information Technology > QUESTIONS & ANSWERS > CIS 450/550: Database and Information Systems Homework 6 - Query Optimization ( 100% COMPLETE AND C (All)

CIS 450/550: Database and Information Systems Homework 6 - Query Optimization ( 100% COMPLETE AND CORRECT SOLUTIONS)

Document Content and Description Below

CIS 450/550: Database and Information Systems Homework 6 - Query Optimization Due by 23:59:59 EDT on April 23, 2019 (NO LATE DAYS!) Submission Instructions: You will be using Gradescope to submit y ... our solutions. Answer each part on a new page and submit your solution as a single PDF file on Gradescope. Problem 1 (15 points) Consider the following SQL query: select SName from Student s, Votes v, Candidate c where s.SName = v.SName and s.Year = "Senior" and v.CName = c.CName and c.Position = "President"; (a) (5 points) What is the immediate translation of this query into the relational algebra? SOLUTION: We will use S, V, and C as shorthand for Student, Votes, and Candidate (respectively). ΠSNameσS.SName=V.SName∧S.Year=”Senior”∧V.CName=C.CName∧C.Position=”President”((S×V )×C) (b) (5 points) What is the result of pushing the projections and selections down as far as possible towards the base relations? SOLUTION: ΠSName(ΠV.SName,V.CName(σS.Year=”Senior” S ⨝ S.SName=V.SName Votes) ⨝ V.CName=C.CName σC.Position=”President” C) (c) (5 points) Which join orders of Student, Votes and Candidate will be considered? SOLUTION: The join orders are: (V ⨝ S) ⨝ C or (S ⨝ V) ⨝ C (V ⨝ C) ⨝ S or (C ⨝ V) ⨝ S (C ⨝ S) ⨝ V or (S ⨝ C) ⨝ V Of these, since S and C have no attributes in common, S ⨝ C and C ⨝ S would be products and therefore not considered [Show More]

Last updated: 3 years ago

Preview 1 out of 6 pages

Buy Now

Instant download

We Accept: