Consider the following relation schemas : b-Schema = (b-name, b-city, assets)…
2007
Consider the following relation schemas :
b-Schema = (b-name, b-city, assets)
a-Schema = (a-num, b-name, bal)
d-Schema = (c-name, a-number)
Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation. Consider the following query: П
c-name
(σ
b-city = "Agra" ⋀ bal < 0
(branch ⋈ (account ⋈ depositor) Which one of the following queries is the most efficient version of the above query ?
Answer: A. Пc-name (σbal < 0 (σb-city = "Agra" branch ⋈ account) ⋈ depositor) — To optimize the query, selection conditions should be applied as early as possible to reduce intermediate result sizes. Since the branch relation is much…
- A.
Пc-name (σbal < 0 (σb-city = "Agra" branch ⋈ account) ⋈ depositor)
- B.
Пc-name (σb-city = "Agra"branch ⋈ (σbal < 0 account ⋈ depositor))
- C.
Пc-name (σb-city = "Agra" branch ⋈ σb-city = "Agra" ⋀ bal < 0 account) ⋈ depositor)
- D.
Пc-name (σb-city = "Agra" ⋀ bal < 0 account ⋈ depositor))
Attempted by 33 students.
Show answer & explanation
Correct answer: A
To optimize the query, selection conditions should be applied as early as possible to reduce intermediate result sizes.
Since the branch relation is much smaller than account and depositor, filtering branch by b-city = "Agra" first significantly reduces the data before joining.
Joining the two large relations (account and depositor) before filtering creates a much larger intermediate result, which is inefficient.
Therefore, the most efficient version filters the small branch relation first and then performs the joins.