Task 2 [11 marks] using the Treasure Hunter’s database For task 2, we have provi

Question

Task 2 [11 marks] using the Treasure Hunter’s database For task 2, we have provided you with the creation script for the Treasure Hunter’s database. You should run this script in MySQL Workbench and use this database to extract the necessary information. The script is based on the following schematic: TREASURE HUNTER’S RELATIONAL MODEL Player (username, firstName, lastName, gender, DOB, email, streetNo, streetName, suburb, state, postcode, creationDateTime, totalPoints) PhoneNumber (phoneNumber, username) Treasure (treasureID, description, points, webpage, type, questID) Quest (questID, questName, story, beacon, advancedQuestID) Store (storeID, storeName, openTime, closeTime) Badge (badgeID, badgeName, badgeDescription) PlayerProgress (questID, username, progress) PlayerTreasure (username, treasureID) Purchase (purchaseID, storeID, username, badgeID, purchaseDateTime, cost) FOREIGN KEYS • PhoneNumber (username) is dependent on Player(username) • Quest (advancedQuestID) is dependent on Quest(questID) • Treasure (questID) is dependent on Quest (questID) • PlayerProgress (questID) is dependent on Quest (questID) • PlayerProgress (username) is dependent on Player (username) • PlayerTreasure (username) is dependent on Player (username) • PlayerTreasure (treasureID) is dependent on Treasure (treasureID) • Purchase (storeID) is dependent on Store (storeID) • Purchase (username) is dependent on Player (username) • Purchase (badgeID) is dependent on Badge (badgeID) OTHER CONSTRAINTS • Player (gender) must be female, male, other or prefer not to disclose. • Player (state) domain is [QLD, SA, TAS, NSW, WA, NT or ACT]. • Treasure (type) domain is [common, uncommon, rare, ultra-rare or elite]. • Players may enter up to three phone numbers. • Players must enter at least one phone number. • PlayerProgress (progress) domain is [active, inactive or complete]. • Player (email) is mandatory. Query 1 (1 mark) Write a query to list the name (first and last), date of birth, gender and email of players who live in Sunnybank or Sunnybank Hills. Note that you can assume these are the only suburbs starting with ‘Sunnybank’. Query 2 (2 mark) Write a query to find out how much each player has spent at the stores. Your output should be sorted by username in descending order. Query 3 (2 marks) Write a query that lists the username and phone number of the oldest player. Note that you must use subqueries for this query. Query 4 (3 marks) Write a query that lists all of the badges. If the badge has been purchased include the first name, last name and email address of the player(s). Sort the list based on the badge name followed by first name then last name in ascending order. Query 5 (3 marks) List the number of quests embarked upon by each player with progress status ‘complete’ for the treasures that are common. Task 3 [3 marks] Insert (1 mark) Write an INSERT command to insert a row into badge table. The badge is called ‘Fools Gold’ and the description should be ‘Trickiest trickster in all the seas’. Delete (1 marks) Write a DELETE command to remove all the rows from the player progress table for which progress is inactive. Update (1 mark) Write an UPDATE comment to change the address of all players with the last name ‘Smith’ who live at ‘180 Zelda Street, Linkburb’ to ’72 Evergreen Terrace, Springfield’.

Explanation / Answer

We have following relations given:

Player (username, firstName, lastName, gender, DOB, email, streetNo, streetName, suburb, state, postcode,creationDateTime, totalPoints)

PhoneNumber (phoneNumber, username)

Treasure (treasureID, description, points, webpage, type, questID)

Quest (questID, questName, story, beacon, advancedQuestID)

Store (storeID, storeName, openTime, closeTime)

Badge (badgeID, badgeName, badgeDescription)

PlayerProgress (questID, username, progress)

PlayerTreasure (username, treasureID)

Purchase (purchaseID, storeID, username, badgeID, purchaseDateTime, cost)

Query 1 :

SELECT firstName, lastName, DOB, gender, email

FROM Player

WHERE suburb = 'Sunnybank' OR suburb = 'Sunnybank Hills';

Query 2 :

SELECT userneme, SUM(cost)

FROM Player as p, Purchase as q

WHERE p.username = q.username

GROUP BY username

ORDERED BY username DESC;

Query 3 :

SELECT username, phoneNumber

FROM Player as p, PhoneNumber as q

WHERE p.username = q.username AND DOB = ( SELECT MIN(DOB) FROM Player );

Query 4 :

SELECT badgeName, firstName, lastName, email

FROM (Player as p JOIN Purchase as q ON p.username = q.username) RIGHT JOIN Badge as b ON q.badgeID = b.badgeID

ORDERED BY badgeName, firstName, lastName ASC;

Query 5 :

SELECT username, COUNT(questID)

FROM PlayerProgress as p JOIN Treasure as t ON p.questID = t.questID

WHERE t.type = 'common' AND p.progress = 'complete'

GROUP BY username;

TASK 2 :

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.