Answer the following questions in a word processing document, export the doc- ument 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) both the Tuple Relation- al Calculus and the Domain Relational Calculus, and (C) SQL for Question (a). Use only SQL for Question (b), (c) and (d).
For all the questions in this assignment, please include screen snapshots of the query results you obtain from XAMPP server. This is important for demon- strating 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 will be deducted if the snapshots are missing in your answers. The screen snapshots should capture the screen area covering both the SQL statement and the output result. The screen snapshots should be consistent with the output of the SQL answers provided.
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 if you do so.
Other than XAMPP server, you can also use other relational database systems for completing this assignment.
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 in the assignment.
(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.
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.