---
title: "Optimize query with year 2009 condition"
subject: "Database Management System"
chapter: 5
key: "semester_6/database_management_system:optimize-query-music-2009"
source: https://www.bctengineer.com/q/semester_6/database_management_system/optimize-query-music-2009
site: BCT Engineering
---

# Optimize query with year 2009 condition

Database Management System · Chapter 5: Query Processing & Optimization

- Asked in: year not given
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/optimize-query-music-2009
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-5

## Question

Optimize: Π name, title (σ dept-name = music ∧ year = 2009 (instructor ⋈ (teaches ⋈ Π c_id, title (course))))

## Short answer

Split selection: σ dept='music' on instructor, σ year=2009 on teaches. Early projections keep only ID,name; ID,c_id; c_id,title. Join filtered instructor with filtered teaches first, then with course.

## Answer

**Query:** Π name, title (σ dept-name = music ∧ year = 2009 (instructor ⋈ (teaches ⋈ Π c_id, title (course))))

Assumed schema: instructor(ID, name, dept_name, salary), teaches(ID, c_id, sec_id, semester, year), course(c_id, title, dept_name, credits). The attribute dept_name is in instructor and year is in teaches.

#### Original tree

```text
Π name,title
  |
σ dept=music ∧ year=2009
  |
  ⋈
 /  \
instructor   ⋈
            /  \
       teaches  Π c_id,title
                    |
                  course
```

#### Rules applied
1. **Cascade of selection:** σθ1∧θ2(E) = σθ1(σθ2(E)). The conjunction is split into dept_name = music and year = 2009.
2. **Push selection through join:** σθ(E1 ⋈ E2) = σθ(E1) ⋈ E2 when θ uses only attributes of E1. Here dept_name = music goes to instructor and year = 2009 goes to teaches.
3. **Early projection** and a good **join order** (join the two reduced relations first).

#### Step by step
1. Split the selection:

   σ dept=music ( σ year=2009 ( instructor ⋈ (teaches ⋈ Π c_id,title(course)) ) )

2. Push each part down to its own relation:

   Π name,title ( σ dept=music(instructor) ⋈ ( σ year=2009(teaches) ⋈ Π c_id,title(course) ) )

3. Reorder so that the two filtered relations are joined first, and drop unneeded columns:

   Π name,title ( ( Π ID,name(σ dept=music(instructor)) ⋈ Π ID,c_id(σ year=2009(teaches)) ) ⋈ Π c_id,title(course) )

#### Optimized tree

```text
            Π name,title
                 |
              ⋈ (c_id)
           /            \
       ⋈ (ID)         Π c_id,title
      /      \             |
Π ID,name   Π ID,c_id    course
    |           |
σ dept=music  σ year=2009
    |           |
instructor   teaches
```

Both large tables, instructor and teaches, are filtered before any join, so the joins work on small inputs. The result is the same as that of the original query, but the cost (tuples and disk blocks processed) is much lower.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. What does the cascade rule do with dept_name = music AND year = 2009?
   A. Splits it into two separate selections that can be moved independently
   B. Merges it into one join
   C. Removes it
   D. Converts it to a projection
   Answer: A. σθ1∧θ2(E) = σθ1(σθ2(E)), so each condition can be pushed to its own relation. (Key point: Cascade of selection)

2. Where are the two conditions pushed?
   A. Both to instructor
   B. dept_name = music to instructor and year = 2009 to teaches
   C. dept_name to course and year to instructor
   D. Both to course
   Answer: B. dept_name is an attribute of instructor and year is an attribute of teaches. (Key point: Where each selection goes)

3. After pushing selections, what is joined first?
   A. The two filtered, projected relations instructor and teaches
   B. course with instructor
   C. All relations unfiltered
   D. course with itself
   Answer: A. Both big tables are reduced first, so the joins work on small inputs. (Key point: Join order)

## Close variants set in other exams

- [Optimize relational algebra query: music](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Aoptimize-query-music-instructor) — Database Management System, chapter 5