POTD 7: Query cost estimation

Due 4-Apr-2024 6-Apr-2024, 12pm/noon EST

---

Purpose:

• Practice writing logical plans (RA trees) and explore equivalent logical plans
• Estimate the cost of the queries using Cardinality estimation
• Get ready to work on homework assignment and course project
• Prepare for the final exam

You may make a copy of a worksheet and complete this activity, type your answers in any text editor, or simply write on paper(s).

You may work alone or with 3-4 other students in this course.

---

1. Consider the relations Q(a,b), R(a,c,d), S(b,d) with the following statistics:

   T(Q)	= 2500     = number of tuples in relation Q
   V(Q,a) 	= 30       = number of distinct values of attribute a in relation Q
   V(Q,b)	= 500	   = number of distinct values of attribute b in relation Q
   T(R)	= 6000	   = number of tuples in relation R
   V(R,a)	= 60       = number of distinct values of attribute a in relation R
   V(R,c)	= 20       = number of distinct values of attribute c in relation R
   V(R,d)	= 40       = number of distinct values of attribute d in relation R
   T(S)	= 1000     = number of tuples in relation S
   V(S,b)	= 250      = number of distinct values of attribute b in relation S
   V(S,d)	= 100      = number of distinct values of attribute d in relation S

   Estimate the size (number of tuples) of the result of the following query

   SELECT * 
   FROM R NATURAL JOIN S
   WHERE R.a = 22 AND S.b = 90

   [sample solution]
   
   
   
2. Consider the relations instructor and department and the following statistics:

   instructor(ID, name, dept_name, salary)
       T(instructor)   = 13     #of tuples
       V(dept_name)    = 4      #of distinct values
   
   department(dept_name, building, budget)
       T(department)   = 5      #of tuples
       V(dept_name)    = 5      #of distinct values 

   Find the buildings of departments where more than two instructors are working

   1. Write a SQL query
      [sample solution]

      SELECT D.dept_name, D.building 
      ROM instructor I NATURAL JOIN department D 
      GROUP BY D.dept_name, D.building
      HAVING COUNT(*) > 2  
      
      Assume: each department is located in exactly one building 

      
      
   2. Draw an RA plan
      [sample solution]
      
      
      
   3. Estimate the cardinality of each RA operator
      [sample solution]
      

---

Grading rubric

[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)

• (5 points) — Providing evidence of your attempt, minimal effort

(-2.5 points) for 24 hours late (submitted after 4-Apr-2024 12pm/noon EST, by 5-Apr-2024 12pm/noon EST)
(-5 points) for 48 hours late (submitted after 5-Apr-2024 12pm/noon EST, by 6-Apr-2024 12pm/noon EST)

(-2.5 points) for 24 hours late (submitted after 6-Apr-2024 12pm/noon EST, by 7-Apr-2024 12pm/noon EST)
(-5 points) for 48 hours late (submitted after 7-Apr-2024 12pm/noon EST, by 8-Apr-2024 12pm/noon EST)

---

Submission

• [optional] Take a selfie (or picture) of your team and submit it with your POTD
• Include all team member names and computingIDs in the report.
• You may do one of the following:
  • Write on the paper and take picture(s) of your POTD, or
  • Type and save your POTD as a .pdf file — No Word document.
• Each team submits only one copy.
• Upload your report to POTD 7 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
• Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.

---

Copyright © 2024 Upsorn Praphamontripong
Released under the CC-BY-NC-SA 4.0 license.
Last updated 2024-03-31 20:24