Question: Problem 4. [25pts) Consider a database with the following schema: Employee(SSN, name, salary, DNo) Department (DNo, DeptName, MgrSSN) Project (PNo, location, ProjName) HourLog(SSN, PNo, hours)

Problem 4. [25pts) Consider a database with the following schema: Employee(SSN, name, salary, DNo) Department (DNo, DeptName, MgrSSN) Project (PNo, location, ProjName) HourLog(SSN, PNo, hours) The Employee relation provides a list of employees with their SSN, name, salary, and department number (DN). 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 (MgrSSN). 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 Hour Log 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 using the rename operator. 1. Find the name and the SSN of everyone who works more than 100 hours on a project located in Boston. 2. Find the name and SSN of everyone who works for department number 1 and also works on project number 2. 3. Find the name and the SSN of everyone who works on at least two projects. 4. Find the name and the SSN of everyone who works on all projects
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
