GATE | Gate IT 2007 | Question 66

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-nameb-city = “Agra” ⋀ bal < 0 (branch ⋈ (account ⋈ depositor)

Which one of the following queries is the most efficient version of the above query ?
(A) Пc-namebal < 0b-city = “Agra” branch ⋈ account) ⋈ depositor)
(B) Пc-nameb-city = “Agra”branch ⋈ (σbal < 0 account ⋈ depositor))
(C) Пc-nameb-city = “Agra” branch ⋈ σb-city = “Agra” ⋀ bal < 0 account) ⋈ depositor)
(D) Пc-nameb-city = “Agra” ⋀ bal < 0 account ⋈ depositor))


Answer: (A)

Explanation:  

For better record processing, We should always do select before join to avoid unnecessary tuples being considered for join.(For an SQL query this is not strictly required as DBMS will rearrange the query to make it efficient) and processing a smaller table gives more efficiency than the larger one.

option (A): П c-name (σ bal < 0 (σ b-city = “Agra” branch ⋈ account) ⋈ depositor) here we are doing join between relatively smaller table(branch) and larger one(account) and the output that this inner table gives (which is smaller in comparison to joins that we are doing in B) is used for join with depositor table with the selection condition.(same as given query).
option (B): П c-name (σ b-city = “Agra” branch ⋈ (σ bal < 0 account ⋈ depositor)) here we are doing a join between two massive table account and depositor with selection condition(for balance less than zero) and the output that this inner table gives is used for join with branch table(relatively smaller table) with the selection condition (for city Agra). (same as given query) so option A and option B both are same as given query but there is difference between record processing as the record processing of option A will get reduced by some rate. (as filtered conditions are applied after the join between one smaller and one larger table which will give relatively smaller table than the join of two larger tables). So, overall option A is much better than option B.
option (C): П c-name ((σ b-city = “Agra” branch ⋈ σ b-city = “Agra” depositor) Incorrect as there is no b-city column in a-Schema.
option (D): П c-name (σ b-city = “Agra” branch ⋈ (σ b-city = “Agra” depositor))
Incorrect as there is no b-city column in a-Schema.
⋀ bal < 0 account) ⋈
⋀ bal < 0 account ⋈
Note: don’t miss this sentence “account and depositor relations are much bigger than the branch relation” because That makes A the best answer.
This solution is contributed by Nitika Bansal.

Quiz of this Question

My Personal Notes arrow_drop_up
Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.