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).

(a)List all the employees (Fname and Lname) whose salary is less than $35,000.

(b)Which department (Dnumber) has locations in Houston and Sugarland?

(c)Who are the employees (Ssn) who is not a supervisor?

(d)List the managers (Mgr_ssn) who is also a supervisor.

(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 Bdate Sex Salary Super ssn Dno Fname MinitLname John Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Alicia JZolaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 3334455555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 ames EBorg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 DEPARTMENT DEPT LOCATIONS Dlocation Mgr ssn 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 Administration Stafford WORKS ON PROJECT Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Pno Hours Pnumber Plocation Dnum 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30 30.0 10 10.0 10 35.0 5.0 30 20.0 20 15.0 Computerization10 20 30 Houston Stafford Houston Stafford Reorganization 10 DEPENDENT 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 123456789 Michael 123456789 123456789 Elizabeth Sex Bdate F 1986-04-05Daughter M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M 1988-01-04 Son F 1988-12-30 Daughter F 1967-05-05Spouse 30 20 NULL

Explanation / Answer

(a)List all the employees (Fname and Lname) whose salary is less than $35,000.
SELECT Fname,Lname
FROM employee
WHERE Salary < 35000

(b)Which department (Dnumber) has locations in Houston and Sugarland?
SELECT Dnumber
FROM Dept_locations
WHERE Dlocation like ( ('Houston', 'Sugarland')

(c)Who are the employees (Ssn) who is not a supervisor?
Select e.ssn
from employee
where e.ssn not in (select super_ssn
                                from employee)
(d)List the managers (Mgr_ssn) who is also a supervisor.
SELECT d.Mgr_ssn
FROM department d, employee e
WHERE d.mgr_ssn=e.super_ssn

(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.
Select e.Fname,e.Lname
from employee e and works_on w
where e.ssn=w.essn and w.hours>=20

(f)List the employee names (Fname and Lname) and their children’s name (Dependent_name, if any).
select e.fname,e.lname,d.dependent_name
from employee e and dependent d
where e.ssn=d.essn

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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