制作一款增强现实的手机游戏，可以让玩家在Melbourne捕获Phonemon的虚拟生物的游戏，通过将Phonemon放置在地图上的各个点并对其进行可视化，将它们叠加到物理世界上。类似Pokémon Go.
Phonemon Case Study
You are producing an augmented reality phone game which allows players to move around Melbourne, capturing virtual creatures called Phonemon (short for Phone Monster). The Phonemon are superimposed onto the physical world by locating them at points on the map and visualizing them via the app. (This fictitious game is based on Nintendo’s popular app Pokemon Go.)
We record about each player the date and time when they joined the game, their unique id, username and which team they chose to join (if they are part of the team). We regularly update their current location and level. Players begin on level 1 and level up according to their achievements. There are three teams to choose from: each has a colour, a (fictitious) leader, and a Phonemon mascot.
Each species has a title, description and can be of 1 or maximally 2 different “types”.
Individual Phonemons spawn regularly. We keep track of each one, recording when and where it spawns. A Phonemon starts as “wild” (the player column is null), and if it is captured, the player id is recorded. Each Phonemon has the species if belongs to and a “power” score (which may change as the game proceeds).
Our game allows in-app purchases of a range of items whose properties we record. Some items have extra properties that are recorded in a separate entity. We record the details of each purchase, including the item and quantity bought, and date and time of the purchase. Items can be of type ‘F’ (if they are food) or ‘M’ (if they are medicine), or none if they don’t belong to either of the two groups. Items have a title and price. Both food and medicine are worth some points stored in tables Food and Medicine respectively.
All locations in Phonemon are expressed as a pair of decimal numbers representing latitude and longitude. Calculating the distance between two points P1 and P2 requires a complex formula which you can read about at https://en.wikipedia.org/wiki/Haversine_formula. For the purposes of this assignment, you can use the following simplified formula based on the Euclidean distance, which works well enough in Melbourne:
distance in km = sqrt( (P1.latitude - P2.latitude)^2 + (P1.longitude - P2.longitude)^2 ) * 100
Assignment 2 Setup
A dataset is provided against which you can test your solutions to the assignment. To set up the dataset, download the file Phonemon_2021.sql from the Assignment on Canvas and run it in Workbench. This script creates the database tables and populates them with data.
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 three lines at the beginning of the script that create the schema on your local server.
Note: Do NOT disable 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 using the command “SELECT @@sql_mode;”. It should return a string containing “full_group_by”.
When testing, our test server WILL have this mode turned on, and if your query fails due to this, you will lose marks. You can run the command:
SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
to ensure that this mode is configured properly.
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 within a single SQL statement. However, you may be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS (or ‘WITH’ statements/common table expressions) to answer questions.
- How many species have a description which contains the word “this”? Your query should return results of the form: (speciesCount).
- Player ‘Cook’ is about to battle player ‘Hughes’. For both players, show the player’s username and the total summed power of all the Phonemons they own. Your query should return results of the form: (username, totalPhonemonPower).
- How many players does each team have? List the team names with their player counts, in descending order. Return results as: (title, numberOfPlayers).
- Which species have a type of “grass”? Return results as: (idSpecies, title)
- List the players who never purchased any food item. Your query should return results of the form: (idPlayer, username).
- Each player is at a particular level in the game. What is the total amount that has been spent on purchases by all players of a given level? Your query should return results of the form: (level, totalAmountSpentByAllPlayersAtLevel) in the descending order of the amount spent.
- Which item was purchased the most? In case of a tie, find all such items. Your query should return results of the form: (item, title, numTimesPurchased)
- Find the number of (distinct) food items available, and any players who have purchased all types of food items at least once. Your query should return results of the form: (playerID, username, numberDistinctFoodItemsPurchased).
- We’ll refer to the Euclidean distance, rounded to 2 decimal places, between the closest two Phonemon as ‘X’. We wish to count the number of Phonemon PAIRS which are at distance (to 2 decimals places) X from each other. Return as (numberOfPhonemonPairs, distanceX). HINT1: use the ROUND() function. HINT2: Ensure you are not double counting the pairs: eg phonemon1 and phonemon2 are distance 0.11 from each other. If this is the minimum distance and no other phonemon are also at distance 0.11 from another phonemon, then the return value should be (1, 0.11), since there is a single PAIR which are at distance 0.11 from each other.
- Some players are really into a certain type of Phonemon… List the usernames of players that have captured at least one of every species of a given type, and the title of that type. Return a separate row for each type that a player has caught all species of. Your query should return results of the form: (username, title). An example: if there are 3 Phonemon species with a type of ‘bug’, and player ‘Greg’ has caught at least 1 Phonemon of each of these species, then Greg will appear in the list as (Greg, bug). If additionally, Greg had caught one of every species with type ‘fairy’, then a second row of the output would be (Greg, fairy).
SQL Response Formatting
To help us mark your assignment queries as quickly/accurately as possible, please ensure that:
- Your query returns the projected attributes in the same order as given in the question, and do not include additional columns. E.g., if the question asks for (userId, name), please write “SELECT userId, name “ instead of “SELECT name, userId, phone “ (you can name the columns using ‘AS’ however you’d like, only the order matters)
- Please do NOT use “databaseName.tableName” format. E.g., please write “SELECT userId FROM users” instead of “SELECT userId FROM coltonc.users “
- Ensure that you are using single quotes( ‘ ) for strings (e.g. WHERE name = ‘bob’)and double quotes ( “ ) only for table names (e.g. SELECT name FROM “some table name with spaces”)
- Ensure that you match the capitalisation of table/attribute names: some OS’s are case insensitive but others are case sensitive. E.g. for an attribute “name” in table “user”, please write “SELECT name, FROM user “ instead of “SELECT Name FROM User”
Submission Instructions
Your submission will be in the form of an SQL script. There is a template file on the LMS, into which you will paste your solutions and fill in your student details (more information below).
This .sql file should be submitted on Canvas by 6pm on the due date of Friday 30 April. Name your submission as 987654.sql, where 987654 corresponds to YOUR student id.
Filling in the template file:
The template file on the LMS has spaces for you to fill in your student details and your answers to the questions. There is also an example prefilled script also available on the LMS. Below are screenshots from those two documents explaining the steps you need to take to submit your solutions.