Relational Query Languages

Contest(starter, ender, pointsStarter, pointsEnder, debatedate) Debater(name, club, age) Figure 1: Relational Schema.

The relational schema shown in Figure 1 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 in Figure 1, answer the following four questions. Please use (A) the Relational Algebra, (B) both the Tuple Relational Calculus and the Domain Relational Calculus, and (C) SQL for Question (a). Use only SQL for Question (b), (c) and (d).

It is not allowed to have hardcoded SQL answers, meaning that your answers should be general enough to deal with other possible data in the test dataset used. It is not allowed either to use SQL with the flow of control feature to answer the questions.

(a) Give the names of the starting and the end debaters of the contests where the starting debater is a member of the Plato or Storm club and prevented his opponent from scoring any points. (It is required that the selection symbol appear only once in the relational algebra expression for this query) (b) List the names of debaters won at least one contest. (c) List the names of clubs as well as the number of members in each club. (d) Give the name, club and age of all debaters who have lost one or more debates as a starter.