For your first assignment, you will be working with the following schema: Employ

Question

For your first assignment, you will be working with the following schema: Employee(SSN, name, salary, DNo) Department(DNo, DeptName, MgrSSN) Project(PNo, location, ProjName) HourLogCsSN, PNo, hours) The Empi oyee relation provides a list of employees with their SSN, name, sal ary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department's manager (Mg rSSN). Each department has only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. Write the following queries in Relational Algebra. You may use assignment of intermediate results for long queries. Find the name and the SSN of everyone who works more than 100 hours on one particular project. Find the name and SSN of everyone who works for department number 1 and also work on project number 2. Find the name and the SSN of everyone who works on at least two projects. Find the SSN of everyone who is not working on any project.

Explanation / Answer

1.

SELECT Employee.name, Employee.SSN
FROM Employee join HourLog
WHERE Employee.SSN = HourLog.SSN
AND hours > 100;

2.

SELECT Employee.name, Employee.SSN
FROM Employee join Project
WHERE Employee.DNo = 1
AND Project.PNo = 2;

3.

SELECT Employee.name, HourLog.SSN
FROM HourLog join Employee
WHERE Employee.SSN = HourLog.SSN
GROUP BY HourLog.SSN
HAVING COUNT(*) > 1;

4.

SELECT Employee.SSN
FROM Employee
LEFT JOIN HourLog on Employee.SSN = HourLog.SSN
where HourLog.SSN is NULL;

Hope this helps! Cheers!

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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