Contest(starter, ender, pointsStarter, pointsEnder, debatedate) Debater(name, cl

Question

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

The relational schema shown models a very simple database for
a Debating club. In a debating contest, one debater starts and is followed
by his opponent who ends the contest. Debaters are both given points by an
independent jury. The debater with the most points wins; draws are possible. It
is assumed that not all the debaters in Debater relation participate the contest.

The following two Foreign Keys exist in the schema:
FK Contest(starter) references Debater(name)
FK Contest(ender) references Debater(name)

For the relational schema shown, answer the following question.
Please use the Domain Relational Calculus

(a) 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.

Explanation / Answer

SQL Statement:

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

Relational Algebra:

starter, ender, pointsStarter, pointsEnder,
debatedate(pointsStarter>=pointsEnder(Contest))) |>
Contest.starter=Debater.name (club=’Plato’(Debater))

Relational Calculus:

{st, en, ps, pe, dd | (nm, cl, ag) (Contest(st, en, ps, pe, dd) / Debater(nm, cl, ag) / (st=nm) / (ps>=pe) / (cl=’Plato’)}

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.