Which option is correct a or b and why?


#1

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 ?

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


#2

option c and d are clearly invalid as they use b-city as an attribute of account schema in the query which is not present in account schema.

and for finding correct between a and b

consider the case where all account holders have the negative balance.(worst case)