RECENT ASSIGNMENT

CSC3400&CSC8500 – Database Systems Assignment 2 Page 1

Relational Query Languages
Answer the following questions in a word processing document, export the document as a PDF file, and submit via the course web site. For mathematical symbols you can normally use the “Symbols” font that is available in your word processor. If you are unable to create a join (??) symbol, you may simulate it as | |.
It is recommended (but not required) that you test your SQL answers using a relational database. Such tests may help you find the correct answers.
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) the Domain Relational Calculus, and (C) SQL for Question (a) and use only SQL for Question (b), (c) and ( d ).
For all the questions in this assignment, please include screensnapshots of the query results you obtain from XAMPP MySQL. This is important for demonstrating your ability in running your queries on a real-life database platform. We accept query results from other relational database platforms as well. Note that a large portion of marks (ranging from 33% to 50%) will be deducted if the snapshots are missing in your answers.
A test dataset is provided which can be downloaded under the instruction of the assignment on the Studydesk. Please kindly note that all the students should only use this test dataset for testing their SQL answers and preparing the screen snapshots. Other test dataset is NOT allowed and you may lose some significant marks ranging from 33% to 50% if you do so.
(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.
(b) List the names of debaters won at least one contest.
(c) List the names of debaters who participated the contest but have not won a contest.
(d) Give the name and age of all debaters who have lost exactly one debate as a starter.
.../2
CSC3400&CSC8500 – Database Systems Assignment 2 Page 2

For Question(c), please try to avoid the use of EXCEPT when calculating the difference between 2 sets as it is not supported by XAMPP MySQL. You can consider using other alternative ways such as NOT IN to answer this question.
.../3