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”))
}

I would like to know if the relational algebra and domain calculus are right answers to the query — Kumar Debjit, Apr 26 '17 at 05:37
Please edit your question, comments are not for clarifications, and are ephemeral. Also please give a reference to what algebra & calculus variants you are supposed to use, because there are many. Also if you want more than a yes or no you should justify your work. — philipxy, Apr 26 '17 at 05:45

philipxy · Answer 1 · 2017-04-27T04:02:51.250

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
    ^   ...)

Relational Algebra and Domain Relational Calculus for SQL Query

1 Answers1