TREASURE HUNTER’S RELATIONAL MODEL Player ( username , firstName, lastName, gend

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.

Question

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’.

Explanation / Answer

INSERT INTO Badge VALUES(1234567, ‘Fools Gold’, ‘Trickiest trickster in all the seas’)

please note that 1234567 is the BadgeID and must be unique and thus it is dependent on the content of the table.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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