Unit 9 Assignment 1

Unit 9 Assignment

Kaplan University

IT234 Database Concepts

9-1 Create four new roles in the Northwind database :

SalesPerson SalesManager HRperson HR Manager

9-2 Use Data Control Language (DCL) statements that manage database user permissions.

Grant permissions for Sales Related tables to SalesPerson role to select, insert, and update. Grant permissions for SalesManager to also delete on the Sales Related Tables. Grant permissions for select, insert, and update for HR Related tables to HRperson role. Grant permissions to the HRManager to also delete on HR Related tables.

9-3 Create two new users named Joe and Jane.

Give Jane the role of SalesPerson. Give Joan the role of SalesManager  Give Joe the role of HRPerson. Give James the role of HRManager Once you enter the query with the commands to create the roles, grants, and users, you will simply see the following if the commands execute successfully:

After you have completed all of the changes, execute the following command and check if you have the expected output. Open up the Database Roles in Object Explorer to see the new roles:

9-4 Utilize the Transaction Control Language (TCL) statements to manage changes made by Data Manipulation Language (DML) statements.

1. Use this SQL statement:

Create a query of the Last OrderID and the OrderDate for last order that was shipped to Cork.

Wrap an SQl statement in to a transaction statement. Execute and commit the transaction. UPDATE and Commit You have a customer whose ID is ALFKI who has moved. Write an update query wrapped in a transaction that will change the city to “Bonn” and the address to “Frankfurt Str 44.”

Execute and commit the transaction.

Update and Rollback You may have an order that you think needs to be assigned to a shipper. Select order number 10560 and assign the ShipName AlfredsFutterkiste to this order unless this order already has a ShipName assigned. In this case, rollback the transaction.

Powered by TCPDF (www.tcpdf.org )