'Tuple Relational Calculus query
I am revising for a Database course and I have the following question from a past assignment:
The database schema is as follows:
LIKES(drinker,beer); FREQUENTS(drinker,bar); SERVES(bar,beer,cost);
Bars can serve many beers, drinkers can frequent many bars, and they can like many beers.
However, a bar cannot serve the same beer at different costs.
Write the following query in relational calculus: Find bars that serve at least one beer liked by Joe, but do not serve any beer liked by Michael.
Here is what I have:
{P | ∃L1 ∈ LIKES ∃L2 ∈ LIKES ∃S ∈ SERVES
( L1.drinker = 'Joe' ^ S.beer = L1.beer
^ L2.drinker = 'Michael' ^ L1.beer != L2.beer
^ P.bar = S.bar
)
}
Does this seem ok?
Solution 1:[1]
Mechanical issues: P seems to refer to a tuple from SERVES, but P is never defined. Add in a P is an element of SERVES Also, you're returning a tuple from SERVES, not bars. You'll want to return P.bar, and surround this all with something to the effect of all P such that this inner condition.
Now the issue with the logic itself: you're finding bars that serve at least one drink liked by Joe, but serve at least one drink Michael doesn't like. That's not the same as no drinks liked by Michael.
There are lots of ways to do this, but modifying what you have to say that there does not exist a LIKES such that drinker is Michael and beer =beer from a SERVES where bar is the bar that serves what Joe likes.
Solution 2:[2]
There is a slight problem. P
should be replaced by p.bar
because only the names of the bar is asked and also define P
like the other variables. That's it.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Cannoliopsida |
Solution 2 | Math |