Given the schema Students(id:integer,grade:integer)
, you can solve the problem in tuple relational calculus by using the negation operator (¬
).
{T1.id | ∃T1 ∈ Students ¬(∃T2 ∈ Students (T2.grade > T1.grade))}
This would return the id of all students in T1 where there is no student in T2 with a higher grade.
Because T1 and T2 are from the same relation, this effectively returns the set of students with the top grade.
If there are no ties for the top grade, it will return just one id.
It looks like you were thinking in terms of relational algebra rather than tuple relational calculus.
Tuple relational calculus does not have a set difference operator, so you can't find the maximum by subtracting all non-maximums.
Reference
The Solutions Manual for the third edition of Database Management Systems by Ragu Ramakrishnan and Johannes Gerke helped me solve this.
Question 4.3 asks you to solve some problems using this schema:
Suppliers(sid:integer,sname:string,address:string)
Parts(pid:integer,pname:string,color:string)
Catalog(sid:integer,pid:integer,cost:real)
Question 4.3.11 is similar to yours.
Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
The solution looks like this:
{T | ∃T1 ∈ Catalog(∃X ∈ Suppliers
(X.sname = 'Yosemite Sham' ∧ X.sid = T1.sid) ∧ ¬(∃S ∈ Suppliers
(S.sname = 'Yosemite Sham' ∧ ∃Z ∈ Catalog
(Z.sid = S.sid ∧ Z.cost > T1.cost))) ∧ T.pid=T1.pid)}
The solution here is more complicated because of the joins between Suppliers and Catalog, but the essence of the solution is the same.
Elide the joins to get this:
T1 ∈ Catalog ...
(... ¬(... ∃Z ∈ Catalog
(... Z.cost > T1.cost)))
In the reference solution, free variable T has the same pid as bound variable T1.
Taking the pid of T1 directly is a simpler way to get the same result.
I had to look up wikipedia because I did not know Tuple Relational Calculus. I did only skim over that article. There are differnces in notation as far as I can see.
But after activating my knowledge about formal logic I think you are right because
∀r ∈ Rainfall (...)
is true
if Rainfall
does not contain any tuples. In this case your query reduces to
{ t | ∃s ∈ Station (t[station_id] = s[station_id]}
and this describes all tuples of Station
What I am missing in your notation is that is unclear which attributes the tuple t
of the solution contains. So if you want to select station_id
then the query should start with something like
{ t: {station_id} | ...}
if one uses the notataion of the wiki article.
Maybe you can supply some reference to your notation.
Best Answer
A tuple relational calculus expression should be written with the usual language of first order logic.
In this case, for instance, you could write:
(a part from minor variations on the notation used, like “:” instead of “.”).
The idea is that you obtain the result of the query (a well formed formula of a first order logic), by finding the elements of the relations that, substituted to the free variables of the formula, make the formula true.
In this case, we have two variables,
d
ande
, that range onDepartments
andEmployees
respectively, a condition that must be true (e.Dept_no = d.No
), and from every pair of tuples of the relations that satisfy this condition, you add to the resulting set a tuple formed by theName
ofe
and theName
ofd
(in other words you perform a simple join).Note that the structure of the expression can be easily mapped to the SQL query (and actually this language is the origin of the SQL language, together with other additions). This can be a suggestion on how to write other queries similar to this one.