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

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.

Explanation / Answer

Index are of two types:

Syntax for both index are:

1.Normal Index :

CREATE INDEX index_name

ON table_name (column1, column2, ...);

2. UNIQUE INDEX :

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

Using above we will create index on webpage of the treasure table. As webpage is not a primary or foreign key. So it can have duplicate values. Therefore normal index will be:

CREATE INDEX webpage_treasure_index

ON Treasure (webpage);

Please upvote, if you found it useful.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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