ADVANCE DATABASE MANAGEMENT SYSTEM

QUESTION 3 With the help of the information provided in the table below, solve the following questions with SQL statement(s) POSITION RIGHTS /PRIVILEGES Sales Manager All object privileges on SALESREP, INVOICE, and SALES tables Sales clerk Building tables and views Querying, updating, and inserting new data into the SALES table and INVOICE table Sales assistant Querying the BOOKS_AND_PRICS view. a. Suppose a user of a database (with username: elsam) who used to be the sales manager resigns, and is succeeded by Ernest Tolbert (with username: etolbert), who is currently a sales clerk, already a user of the database assigned the rights of sales clerk. i. Ensure that the outgoing sales manager is no more a user of the database (1 mark) ii. Write an SQL commands that will cancel all of Ernest Tolbert's current privileges as a sales clerk. (2 marks) iii.Use the information in the table above to create a sales manager role and assign it to Erest Tolbert name (4 marks) b. i. Create a view called Books_and_Prices which displays title of books and their corresponding prices. (1 mark) ii. Create a role called SA, meaning Sales Assistant (1 mark) iii. Write SQL command(s) that will assign the appropriate right(s) (as specified in the above table) to SA (1 mark) c. i. Create an account for a new sales clerk called Patrick Asamoah using the username pamoah and default password Pa245, and grant him the required permission to be able to connect to the instance and access the database (2 marks) ii. Write SQL commands that will assign the role SA to Mr. Patrick Amoah. (1 marks) d. When a DBA revokes privileges granted to a user, what happens if the privileges were granted WITH ADMIN OPTION, and what happens if the privileges were granted WITH GRANT OPTION