Using the following schema, write the following queries in relational algebra (2
ID: 3739144 • Letter: U
Question
Using the following schema, write the following queries in relational algebra (20 points):
Emp(eid, ename, salary) E
Works(eid, did, pct_time) W
Dept(did, dname, budget, manager_eid) D
1. List the employee names of employees who did not work in the ‘R&D’ department
2. List the employee names of employee who have worked in all departments
3. List the names of managers who manage a department with a budget over $1,000,000
4. List the names of managers who managed more than one department
Explanation / Answer
1. PROJECT ename (SELECT did != ("R","D") (EMP') JOIN EMP.eid=Works.eid (Works)).
it displays the employee names who didn't work in department R and D. PROJECT will display employee names. SELECT will select the employees who didn't work in R and D. JOIN is required here beacuse we need to get information from Works table also. so Emp and Works tables should be joined for checking department id's where employees work..
3. PROJECT ename (SELECTbudget>1000000 (Dept) JOIN manager_eid = eid (Emp)).
PROJECT will display employee names. SELECT will select those employees who manages department with a budget over 1000000. JOIN is required here beacuse we need to get information from Dept table also. so Emp and Dept tables should be joined for getting budget details based on a common attribute (foriegn key).
4. did Fcount(manager_eid)>1 (PROJECTename(Emp) JOIN eid = manager_eid (Dept))
did Fcount(manager_eid)>1 will count no.of managers who works in more than one department by grouping department ids..PROJECT will display employee names.JOIN is required here beacuse we need to get information from Dept table also. so Emp and Dept tables should be joined for getting employee name based on a common attribute (foriegn key) employee id.
**********************THANKS****PLS GIVE ME GOOD RATING************************************
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.