Reference no: EM131473500
Database Systems Assignment -
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)
The relational schema shown in 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 screen snapshots 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.
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.
Attachment:- Assignment Files.rar
|
How should the court rule on the universities claims
: Louisiana State University, Ohio State University, the University of Oklahoma, and the University of Southern California have each adopted a two-color scheme.
|
|
Which of the given is type of business-to-business web site
: Which of the following is a type of Business-to-business (B2B) Web site? Which of the following methods of financial transactions is considered the most secure?
|
|
Compute three different valuation ratios
: Compute three different valuation ratios, three different profitability ratios, and three financial strength ratios for each of the three years. Compare the ratios over time.
|
|
Staffing operations in emerging markets
: Discuss the challenges involved in staffing operations in emerging markets. Discuss the common causes of expatriate failure.
|
|
List the names of debaters won at least one contest
: CSC3400 & CSC8500 - Database Systems Assignment. List the names of debaters who participated the contest but have not won a contest
|
|
How should the panel decide the given case
: The well-known publication, The Economist, brought a UDRP action to gain control of the domain name www.theeconomist.com. The registrant of the domain name.
|
|
Apply elements of employee discipline to this arbitration
: Apply the elements of employee discipline to this arbitration. Determine whether these elements are helpful in making the decision.
|
|
Compute different valuation ratios
: Compute three different valuation ratios, three different profitability ratios, and three financial strength ratios for each of the three years.
|
|
What is your organizations value proposition
: Choose an organization to analyze. This organization can be one you are personally familiar with, or one you have observed to be an effective organization.
|