(Solved) : Exercise 1 Following Queries Query List Department Name Project Name Manager S Ssn Contro Q36876661 . . .
Exercise 1: For the following queries: Query A. List the department name, project name and the manager’s ssn of the controlling department of projects controlled by the Finance department and located in Dubai. Query B. List the names of dependents of employees whose salary is greater than 15000 Dirhams and work in the Research department. Develop the following: 1) Write the SQL query 2) Translate the SQL query into Relational Algebra query 3) Create an initial (canonical) Query Tree 4) Move SELECT operations down the query tree, if applicable. 5) Apply the more restrictive SELECT operations first, if applicable. 6) Replace CARTESIAN PRODUCT and SELECT with JOIN operations, if applicable. 7) Move PROJECT operations down the query tree, if applicable. Figure 3.6 ne possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit LnameS ohn B Smth 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Frankln T Wong Aliciaa 999887777 1969-01-19 3321 Caste, Spring, TX F 25000 98765431 4 Jenniler S Wallace 987654321 1941-06-20 291 Berry, Belliaire, TX F 43000 3886655554 Ramesh KNarayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 loyce A Engish453453453 1972-07-31 5631 Rice, Houston, TXF 25000 3334455555 AhmadVJabbar 987987987 1969 03-20 980 Dalas, Houston, TX M 25000 987654321 4 lames E Borg898665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Adddress Sex Salary Super san Dno Bdale 333445555 1955 12-08 638 Vos8, Houston, TX M 40000 899665555 DEPARTMENT DEPT LOCATIONS Dname Dnumber Mgr ssn Mgr start date Doumber Dlocathon 333445555 1988-05-22 Administralion4987654321 1995-01-01 999665555 1981-06-19 Belaire Sugarland WORKS ON PROJECT E 881, Houws 39.5 75 666884444 3400 200 2200 100 100 23456789 123456YE9 Product ProductY Productz Computerition1 Staflord Roorganivation Nowbonetes 53453453 453453453 2Housion 30 Stafford 333445555 33344555510 10.0 DEPENDENT 333445555 20 100 909887777 30 999887T77 1 1010.0 10 350 5.0 30 20.0 20 15.0 333445555 Alice 333445555 Thoodore 33344555 Joy 08765432 Abner 12345678 Michaol 12345679 Alice 12345678zabeth F 198804-( M 1983-1025Son F1958-05-03Spouse M 1942-02-28 Spouse M 1908-01-04 Son F1908-12-30Daughter F 1067 06-06Spouse 987987987 30 987654321 88066556620 NUL Show transcribed image text
Expert Answer
Answer to Exercise 1 Following Queries Query List Department Name Project Name Manager S Ssn Contro Q36876661 . . .
OR