• Security, Constraints and Views

Your task:

1. Using the oracle schema HR, create a view called LocalEmployees that would only present the employee data for those who work in Canada. Then, list the first name and last name of each employee working in Canada using the view LocalEmployees.
2. Create a table called Transactions that includes the following columns (data type in the brackets): transactionID (8 digit integer), unitPrice (number with 2 decimal places), units (integer up to 99999), startTime (timestamp), endTime (timestamp).
   The schema of the relation Transactions should include the following constraints:
   • transactionID is the primary key;
   • unitPrice and units should both be positive numbers;
   • endTime should be later than startTime;
   • the total amount (unitPrice*units) should be less than $100,000;
   • none of the attributes should allow null values.
3. Pick a table you created, grant select and update privilege to some of your fellow students. Ask them to test whether they can read, update, insert and delete data from your table.
4. Revoke update privilege from these students and let them test whether they can still read and update your table.
5. Drop the view LocalEmployees and the table Transactions you just created.