First of all it is a question based on nested query with correlation in which :
a) Outer query uses the result of inner query
b) Inner query uses the attributes defined in the outer query..
So here we need to implement division query as we need to find the names of
"companies which are located in every city plus the additional constraint that in that particular city "Made easy" centre should be located..
So we find M.company_name s.t. |All cities in which "Madeeasy" is located| - |All the cities in which the company referred in the outer query is present|
So this has to be checked for each of the companies using the subquery mentioned above..Since the attribute of outer query is being used , it is nested query with correlation..
Now the subquery which is written above in plain form can be written in SQL using difference operator..So if
. |All cities in which "Madeeasy" is located| = |All the cities in which the company referred in the outer query is present|
for a particular company so the difference operator will yield an empty result for that company , thus indicating that the particular company is present in all cities in which "Madeeasy" is located..
We know ,
In correlated subquery , we use EXISTS and NOT EXISTS clauses..Here we use NOT EXISTS clause as we need those company names whose result of inner query(or subquery) is an empty result..And NOT EXISTS returns true when the result of the inner subquery is empty which is the case here..
Hence option C) is the correct answer..
The same thing we can represent in relational algebra as :
Πcompany_name,city(σcompany_name = "Madeeasy" (Company)) / Πcity(σcompany_name = "Madeeasy" (Company))