Reference no: EM132360174

Question

You are a DBA for the accounting firm AFS. The firm is a merging of two previous companies so they need to implement a new human resource system. You have created a relation about employees as follows:

Employee(ename, dept, salary, address, telephone, job).

For authorization reasons, the HR director asked you to create two views for her:

• EmployeeNames that displays only the employee name (ename).
• DeptInfo that lists the average salary for each department (dept, avgsalary).

a. Show the view definition statements for EmployeeNames and DeptInfo.

b. What privileges should be granted to a user who needs to know only average department salaries for the HR and CS departments?

c. The HR director is taking an extended vacation, and to make sure that emergencies can be handled, she want to authorize her boss Joe to read and modify the Employees relation and the EmployeeNames relation (and Joe must be able to delegate authority, because he is too far up the management hierarchy to do any actual work). Show the appropriate SQL statements. Can Joe read the DeptInfo view?

d. After returning from her (wonderful) vacation, she found a note from Joe, indicating that he authorized his secretary Mike to read the Employees relation. She asked you to revoke Mike's SELECT privilege on Employees, but she does not want to revoke the rights she gave to Joe, even temporarily. Can you do this in SQL?