TREASURE HUNTER’S RELATIONAL MODEL Player (username, firstName, lastName, gender
ID: 3592346 • Letter: T
Question
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.
Task 4 [3 marks]
Create Index (1 mark)
Currently the database only contains a small number of records, however the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.
Write a command to create an index on webpage of the treasure table.
Create view – 2 marks
Write a command to create a view to list the firstname, lastname and account creation date of any players that haven’t completed any quests.
Task 5 [2 marks]
Treasure Hunters Company have two employees, Nikki and Phil, to work with the MySQL database. Working as MySQL database administrator, provide the commands required to grant or revoke access so the following security requirements are met:
A. User Nikki must be able to add records to the PLAYER table (0.5 Marks)
B. User Nikki must be able to remove records from the PLAYER table (0.5 Marks)
C. User Phil is no longer allowed to add data to the PLAYER table (0.5 Marks)
D. User Phil is no longer allowed to delete records from the PLAYER table (0.5 Marks)
Assume usernames of employees Nikki and Phil are nikki and phil respectively.
Explanation / Answer
Task-4 Solution:
SELECT firstname, lastname, creationDateTime FROM player WHERE username NOT IN(
select username from PlayerPogress WHERE NOT pogress = 'complete'
)
Task-5 Solution:
Treasure Hunters Company have two employees, Nikki and Phil, to work with the MySQL database. Working
as MySQL database administrator, provide the commands required to grant or revoke access so the following
security requirements are met:
A. User Nikki must be able to add records to the PLAYER table (0.5 Marks)
GRANT INSERT ON PLAYER TO nikki;
B. User Nikki must be able to remove records from the PLAYER table (0.5 Marks)
GRANT DELETE ON PLAYER TO nikki;
C. User Phil is no longer allowed to add data to the PLAYER table (0.5 Marks)
REVOKE INSERT ON PLAYER TO phil;
D. User Phil is no longer allowed to delete records from the PLAYER table (0.5 Marks)
REVOKE DELETE ON PLAYER TO phil;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.