ere is the database design for the personnel data: employee(id, name, address) employeeMap(id, confidentialId) empSalary(confidentialId, salary) The role 'Clerk' would be granted SELECT, UPDATE, DELETE to employee table and granted SELECT to empSalary table. While the clerk can see salary information for any statistical analysis (e.g. average salary computation), he has no idea which employee the salary is for. The role 'Administrator' is granted rights on all three tables; using the confidentialId in the employeeMap table, she has access to salary data for individual employees too. A SQL query that the administrator can use to access all employee fields is: select e.id, e.name, e.address, es.salary from employee e inner join employeeMap m on e.id = m.id inner join empSalary s on m.confidentialId = s.confidentialId; Can you draw a database schema?
Here is the
employee(id, name, address)
employeeMap(id, confidentialId)
empSalary(confidentialId, salary)
The role 'Clerk' would be granted SELECT, UPDATE, DELETE to employee table and granted SELECT to empSalary table. While the clerk can see salary information for any statistical analysis (e.g. average salary computation), he has no idea which employee the salary is for.
The role 'Administrator' is granted rights on all three tables; using the confidentialId in the employeeMap table, she has access to salary data for individual employees too. A SQL query that the administrator can use to access all employee fields is:
select e.id, e.name, e.address, es.salary from employee e inner join employeeMap m on e.id = m.id
inner join empSalary s on m.confidentialId = s.confidentialId;
Can you draw a database schema?
Step by step
Solved in 2 steps
