Relational Algebra and Domain Relational Calculus for SQL Query

Question

Tables:

Contest(starter, ender, pointsStarter, pointsEnder, debatedate)
Debater(name, club, age)

Query:

Give all contest details of contests where the starting debater is a member of the "Plato" club and prevented his opponent from scoring any points.

SQL I have written:

SELECT starter, pointsStarter, ender, pointsEnder, debatedate
FROM contest
WHERE pointsEnder = 0
AND starter IN (SELECT name FROM debater WHERE club = 'Plato')

Relational Algebra I have written:

Π starter,ender,pointsStarter,pointsEnder,debatedate (
    Ϭ Contest.pointsEnder=0 (
        Ϭ club=”Plato” (Contest Debater)))

Domain Relational Calculus I have written:

{< starter,ender,pointsStarter,pointsEnder,debatedate> | 
    Ǝ < starter,ender,pointsStarter,pointsEnder,debatedate > ϵ Contest (
        Ǝ < name,club,age > ϵ Debater (
            ^ pointsEnder=0 ^ club=”Plato”))
}

Answer 1

Apparent typos: There is an errant ^ before pointsEnder in your calculus. You presumably intended a join between Contest & Debater in your algebra.

You haven't given a reference to the versions of the algebra & calculus that you are to use. So we can only guess the likely nature of corrections. It's unlikely you need Contest..

Note your algebra & calculus haven't equated starter & name. If your join is natural, you need to rename name to starter around Debater. Your innermost calculus needs an added conjunct that equates them.

You don't want to drop the result columns via:

Π starter,ender,pointsStarter,pointsEnder,debatedate (...)

You want to drop the non-result columns. If your join is natural that's:

Π club, age (...)

Similarly the result columns of a calculus query have to appear free (not quantified by Ǝ) in the right hand side expression. So instead of:

Ǝ < ... > ϵ Contest (...)

you need something like:

Ǝ < s,e,ps,pe,dd > ϵ Contest (
        s=starter ^ e=ender ^ ps=pointsStarter ^ pe=pointsEnder ^ dd=debatedate
    ^   ...)