Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Database Systems INFO20003 A2 S2 2022 Page 1 of 7
INFO20003 Semester 2, 2022
Assignment 2: SQL
Due: 6:00pm Friday 16th September
Weighting: 10% of your total assessment
Melbourne Touch Rugby Database
Description
The Melbourne Touch Rugby competition is a 6-player-a-side competition for men’s, women’s and
mixed teams (a mixed team has a minimum of 3 women and maximum of 3 men on the field at any
time). Players must be registered with a club to play in the competition and players can only be
registered for one club at a time.
Currently there are 8 rugby clubs participating in the competition, which began in 2020. Each club has
three teams – a men’s team, a women’s team and a mixed team. The men’s competition is known as
the Dewar Shield; the women’s competition is known as the Williams Plate; and the mixed teams
competition is known as the Bingham Trophy.
A season is the set of games played in a competition in a calendar year. Each season consists of rounds
in which every team plays a game.
Even though each game is played by 12 players (6 from each team), a team is not a fixed group of 6
players. The team officials choose 6 players from their club, or possibly even from another club, to play
for that team in a game. As such, the composition of a team may vary from game to game.
If for any reason a team is unable to organise enough players to play a game, that team will forfeit the
game and their opponents will score a “walkover”. A walkover awards 28 points to the team who scores
a walkover and no score is recorded for the team who forfeited. If a game is cancelled (e.g. due to
extreme heat, unsuitable playing pitch), no score is recorded against either team. For the purposes of
considering which games a player has ‘played’ in, forfeit and cancelled games are still counted unless
otherwise specified. A walkover counts as a ‘win’ and ‘loss’ for the corresponding teams, whereas
cancelled games are considered a ‘draw’ (neither team won nor lost) for the purposes of analysis.
Database Systems INFO20003 A2 S2 2022 Page 2 of 7
The Data Model
The physical ER model of Melbourne Touch Rugby database.
Implementation Notes
Each season consists of multiple rounds, the ‘round’ that each game is played in is stored as an attribute
of Game.
A forfeit game (walkover) results in the winning team having a score of 28, and the forfeit team having
a score of ‘NULL’. Cancelled games result in both teams having ‘NULL’ scores.
Database Systems INFO20003 A2 S2 2022 Page 3 of 7
Assignment 2 Setup
A dataset is provided which you may use when developing your solutions. To set up the dataset,
download the file rugby_2022.sql from the Assignment link on Canvas and run it in Workbench. This
script creates the database tables and populates them with data. Note that this dataset is provided for
you to experiment with, it is not the same dataset as what your queries will be tested against (the schema
will stay the same, but the data itself may be different). This means when designing your queries you
must consider edge cases even if they are not represented in this particular data set. We encourage
you to modify the dataset and add your own data to test edge cases.
The script is designed to run against your account on the Engineering IT server
(info20003db.eng.unimelb.edu.au). If you want to install the schema on your own MySQL Server
installation, uncomment the lines at the beginning of the script.
Note: Do NOT disable only_full_group_by mode when completing this assignment. This mode is
the default, and is turned on in all default installs of MySQL workbench. You can check whether it is
turned on by running the following in workbench:
SELECT @@sql_mode;
The command should return a string containing “ONLY_FULL_GROUP_BY” or “ANSI”. When marking,
our test server WILL have this mode turned on, and if your query fails due to this, you will lose marks.
Run the below command to add the only_full_group_by mode:
SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'))
The SQL tasks
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question.
Subqueries and nesting are allowed. You may be penalized for overly complicated SQL statements (e.g.
multiple times longer than required, used vague/poorly named variables, formatting makes it difficult to
read, etc). Unless specified in the question, you do NOT need to sort the results of your query. DO NOT
USE VIEWS (or ‘WITH’ statements/common table expressions) to answer questions.
1. Find the names of all players that are currently in the ‘Melbourne Tigers’ club. Your query should
return data in the form (firstName, lastName) (1 mark)
2. Find the team which has given the most walkovers (forfeited the most number of games).
Assume there are no ties. Your query should return one row of the form (teamName). (1 mark)
3. Find the player that has played in the most clubs over time. You may assume there are no ties.
Don’t count the same club multiple times if a player has played for them multiple times, e.g. if a
player joined and quit the ‘Melbourne Tigers’ several times over the years, all these memberships
only count as having played in one club. Return as (firstName, lastName). (1 mark)