CS4750 - Database Systems

POTD 5: Query cost estimation

Due 31-Mar-2026 2-April-2026, 1pm 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 exam 3

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 2-3 other students in this course.

Consider the following relations:

Actor (pid, fname, lname, gender)
Movie (mid, name, year, revenue)
Directors (did, fname, lname)
Casts (pid, mid, role)
Movie_Directors (did, mid)

with the following properties:

T(Directors)            = 480 = number of tuples in relation Directors
V(Directors, fname)	= 360 = number of distinct values of attribute fname in Directors
V(Directors, lname)	= 440 = number of distinct values of attribute lname in Directors

T(Movie)                = 450 = number of tuples in relation Directors
V(Movie, year)          = 120 = number of distinct values of attribute year in Movie

T(Movie_Directors)      = 600 = number of tuples in relation Movie_Directors
V(Movie_Directors, did)	= 450 = number of distinct values of attribute did in Movie_Directors
V(Movie_Directors, mid)	= 440 = number of distinct values of attribute mid in Movie_Directors 

-- Some movies are directed by multiple directors. 
-- Some directors direct multiple movies.

Write an RA expression that is equivalent to the following SQL query

SELECT   D.fname, D.lname, COUNT(*) AS number_movies
FROM     Directors D, Movie_Directors MD
WHERE    D.did = MD.did
GROUP BY D.did, D.fname, D.lname;

Draw an RA tree representing a logical plan of the given SQL query and then estimate the number of tuples produced by the query.
To help you practice, you should estimate the cardinality of each RA operator
Based on the above query what is the range of number_movies.

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 31-Mar-2026 1pm EST, by 1-Apr-2026 1pm EST)
(-5 points) for 48 hours late (submitted after 1-Apr-2026 1pm EST, by 2-Apr-2026 1pm EST)

Per the class discussion on 03/30, we decided to move POTD5 down by one meeting.

(-2.5 points) for 24 hours late (submitted after 2-Apr-2026 1pm EST, by 3-Apr-2026 1pm EST)
(-5 points) for 48 hours late (submitted after 3-Apr-2026 1pm EST, by 4-Apr-2026 1pm 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 5 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.

CC-BY-NC-SA 4.0 license.
Last updated 2026-03-23 20:37