I am struggling with these two last questions, please help. Task 4 [3 marks Crea

Question

I am struggling with these two last questions, please help.

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

Hi,
Task4:
a. creating an index using the create index statement,
CREATE INDEX webpage_index ON TREASURE(webpage);
--please not you might need to add data type of webpage like varchar(10), since table structure not given, i didnt add, here webpage_index is the index name

Task5:

use the grant statement of SQL to give privileges to users on tables,
A. GRANT SELECT,INSERT ON PLAYER TO nikki;
this or you can try GRANT SELECT,INSERT TO 'nikki'@'localhost'; --since a local database

this will grant select and update statements privilege on player table to nikki user
B.GRANT DELETE ON PLAYER TO nikki;
this or you can try GRANT DELETE ON PLAYER TO 'nikki'@'localhost'; --since a local database
this will grant delete privelege to user nikki
Now, to revoke also mysql provides the revoke command
C.REVOKE INSERT ON PLAYER FROM phil;
or you can try .REVOKE INSERT ON PLAYER FROM 'phil'@'localhost'; --since a local database
this will revoke insert privilege from phil
D.REVOKE DELETE ON PLAYER FROM phil;
or you can try .REVOKE DELETE ON PLAYER FROM 'phil'@'localhost'; --since a local database
this will revoke delete privilege from phil
Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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