MadeEasy Subject Test: Databases - Relational Algebra

Question

Can anyone tell why 1st is not correct?

My approach:-When we will join drinker and bar,we will get tuples of the drinker and the bar corresponding to his/her city.One tuple per drinker will come.Then we will join this with frequent ,the key will be (DrinkerName,BarName),As from previous query we have one tuple,so that will be matched at max. one tuple only.And we project drinker's name.

What did i miss?

Answer 1

1st one is incorrect as the 1st query will give output as drinkers who frequently visit atleast one bar in there city.

Let's take an eg.

Drinker

drinker name	age	city
Aaa	19	1
Bbb	20	2

Bar

Bar name	owner	city
X	rr	1
Y	mm	1
Z	pp	2

 

Frequent

Drinker name	Bar name
Aaa	X
Aaa	Z
Bbb	Z

Now when you will join drinker and bar then

drinker join bar

Drinker name	age	city	bar name	owner	city
Aaa	19	1	X	rr	1
Aaa	19	1	Y	mm	1
Bbb	20	2	Z	pp	2

frequent join drinker join bar

drinker name	bar name
Aaa	X
Bbb	Z

Now when you will project Aaa,Bbb both will be projected but according to the question only Bbb should be the answer.

CORRECT ME IF I AM WRONG.

Comments

Why only Bbb be the answer both Aaa and Bbb should?

Because Aaa is frequent drinker and goes to bar X(in city 1) also he lives in city 1

Please clear.

---

Aaa frequents in bars that is situated in it's own city and some other city.Whereas Bbb only frequents to the bar which is situated in it's city.

If you are still not getting it then please read the question 4 to 5 times and you will get it.

---

Yes got it , there is " only "..