Consider the COMPANY database as specified in Figure 3.6 and the FK ? PK relatio

Question

Consider the COMPANY database as specified in Figure 3.6 and the FK ? PK relationships as specified in Figure 3.7, write relational algebra expressions to get answers from the following queries and show the answers (all answers are in the form of a relation/table).

e) List the name of the employees (Fname and Lname) and the name of the project(s) (Pname) that the employee had worked at least 20.0 hours on the project.

f) List the employee names (Fname and Lname) and their children’s name (Dependent_name, if any).

Figure 3.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname John FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Alicia JenniferS Wallace 9876543211941-06-20 291 Berry, Bellaire, TX Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 3334455555 Joyce AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TXM 25000 987654321 4 James EBorg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Ssn Bdate Address Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 JZelaya999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 F 43000 888665555 4 A English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 333445555 5 DEPARTMENT DEPT LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston Dnumber Mgr start date 1988-05-22 1995-01-01 1981-06-19 Dname Research Administration Headquarters Mgr _ssn 333445555 987654321 888665555 4 4

Explanation / Answer

e)

select fname, lname from employee where ssn in (select essn from works_on where pno < 15 and hours >= 20);

OR
select fname, lname from employee where exists (select * from works_on where ssn = essn and pno < 15 and hours >= 20);

f)

((dependent where relationship = 'son' or relationship = 'daughter')join employee) [fname, lname]

(If OR is not allowed:)
(((dependent where relationship = 'son') union (dependent where relationship = 'daughter'))join employee)   [fname, lname]

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.