[SOLVED] Dbs211 lab 06 – transactions and security

Objectives: The purpose of this lab is to introduce the student to both transactions and security. In the real-world, databases tasks often involve multiple steps and if any step in the middle fails, the procedure is a failure.This lap walks the student through a couple transactions and lets them learn how various steps have varying consequences that they need to be aware of. By the end of this lab, the student will be able to: • Describe the steps of a transaction, how a transaction begins and ends and walk through live scenarios of a variety of transactions• Understand and act appropriately on what needs to be done in the case of transaction failure • Grant and revoke permissions to and from other users and public users from the databaseSubmission: Your submission will be a single WORD file with the query and result screenshot from Oracle SQL developer Make sure every SQL statement terminates with a semicolon. • You will use following data to complete the given tasks: • SET TRANSACTION READ WRITE starts a new transaction. • COMMIT commits the current transaction, making its changes permanent. • SAVEPOINT sets a pointer to a location that can be rolled back to. • ROLLBACK rolls back the current transaction, canceling its changes. • SET autocommit disables or enables the default autocommit mode for the current session. Tasks: It is very important that these tasks/questions be performed in the order presented here for maximum learning.PART A – Transactions 1. Execute the following commands. SET AUTCOMMIT OFF; SET TRANSACTION READ WRITE; Using SQL, create an empty table, that is the same as the RETAILCUSTOMERS table, and name it newCustomers.2. Write an INSERT statement to populate the newCustomers table with the rows of the sample data. (Write a single INSERT statement to insert all the rows, combine firstname and last name to get full name during insert) customerNumber contactLastName contactFirstName Phone addressLine1 city country 100 Patel Ralph 2233355555 10 SenecaWay Paris France 101 Denis Betty 3344455555 110 SenecaWay Chicago USA 102 Biri Ben 44555445544 13000 SenecaWay Toronto Canada 103 Newman Chad 66777332233 12 SenecaWay Mexico city Mexico 104 Ropeburn Audrey 7788811212 15000 SenecaWay Havana Cuba 105 Lucy Preston 45555511111 12 SenecaWay Charlotte USA 3. Create a query that shows all the inserted rows from the newCustomers table. How many rows are selected? 4. Execute the rollback command. Display all rows and columns from the newCustomers table. How many rows are selected? 5. RepeatQuestion 2. Make the insertion permanent to the table newCustomers. Display all rows and columns from the newCustomers table. How many rows are selected? 6. Write an update statement to update the value of column addressLine1 to ‘unknown’ for all the customers in the newCustomers table. 7. Make your changes permanent. 8. Execute the rollback command. a. Display all customers from the newCustomers table whose address is ‘unknown’. How many rows are still updated? b. Was the rollback command effective? c. What was the difference between the result of the rollback execution from Question 6 and the result of the rollback execution of this task? 9. Begin a new transaction and then create a statement to delete the customers from the newCustomers table10. Perform a rollback to undo the deletion of the customers a. How many customers are now in the newCustomers table? b. Was the rollback effective and why? 11. Begin a new transaction and rerun the data insertion from Question 2 (copy the code down to Question 11 and run it) 12. Set a Savepoint, called insertion, after inserting the data 13. Rerun the update statement from Question 6 and run a query to view the data (copy the code down and run it again)14. Rollback the transaction to the Savepoint created in Question 12 above and run a query to view the data. What does the data look like (i.e. describe what happened? 15. Use the rollback statement and again view the data. Describe what the results look like and what happened.Part B – Permissions 16. Write a statement that denies all access to the newCustomers table for all public users 17. Write a statement that allows a person ‘RGNANAOLIVU’ read only access to the newCustomers table. 18. Write a statement that allows the same person ‘RGNANAOLIVU’ to modify (insert, update and delete) the data of the newCustomers table. 19. Write a statement the denies all access to the newCustomers table for the same person ‘RGNANAOLIVU’.Part C – Clean up 20. Write statements to permanently remove the view and table created for this lab.