Transactions& Locking
1. A. Write a script that includes these statements coded as a transaction:
INSERT Orders
VALUES (3, GETDATE(), '10.00', '0.00', NULL, 4, 'American Express', '378282246310005', '04/2013', 4);
SET @OrderID = @@IDENTITY;
INSERT OrderItems
VALUES (@OrderID, 6, '415.00', '161.85', 1);
INSERT OrderItems
VALUES (@OrderID, 1, '699.00', '209.70', 1);
Here, the @@IDENTITY variable is used to get the order ID value that's automatically generated when the first INSERT statement inserts an order.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.
B. Why is it necessary to put the statements in 1A as a transaction?
C. What does it mean to commit a transaction?
2. A. Write a script to delete the row with a customer ID of 8 from the Customers table. To do this, you must first delete all addresses for that customer from the Addresses table. Place these two SQL statements in a transaction.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.
B. Explain why it is necessary to put statements involving foreign key updates such as in 2A into a transaction?
3. There are four types of concurrency problems. Explain Dirty Read and Lost Update with examples.
4. What are two ways you can prevent a deadlock in a database?
Security
5. Write a script that
A. Creates a user-defined database role named OrderEntry in the MyGuitarShop database
B. Give INSERT and UPDATE permission to the new role for Orders and OrderItems table.
C. Give SELECT permission for all user tables.
6. Write a script that
A. Creates a server login ID named "RobertHalliday" with the password "HelloBob"
B. Sets the default database for the login to the MyGuitarShop database
C. Creates a database user named "RobertHalliday" for the server login
D. Assigns the user to the OrderEntry role you created in exercise#5
7. Write a script that uses a cursor to loop through each row of the Administrators table and prints a login ID for each row in that consists of the administrator's first and last name with no space in between and a random number at the end of the login. You have to look up the function RAND and figure out how to use it.
8. Write a script that removes the user-defined database role named OrderEntry. (Hint: This script should begin by removing all users from this role.
9. In your own words, explain the following. Use specific examples if necessary.
A. Server Role
B. Database Role