1. Suppose the following relations are part of a project management database of

Question

1. Suppose the following relations are part of a project management database of a company, where keys are underlined: E(ssn, name, salary) stores the information of employees, P(pid, title, budget) stores the information of projects, and M(ssn, pid, start, end) stores the information of management of projects by employees.

1) Write a relational algebra expression to return the pids of projects that are managed by employee “Mary” but not by “Jack”.

2) Write a relational algebra expression to return the names of those employees who manage at least two projects.

3) Write a relational algebra expression to return the pids of those projects that are managed by an employee with a salary great than $500,000.

4) Write an SQL query to return the ssns of employees that manage at least two projects, one is finished (end < today), and the other is still ongoing ( start =< today =< end).

5) Write an SQL query to delete all the projects from table P that are NOT managed by any employee.

6) Define an SQL view PNum (pid, num) that gives the number of employees that manages project pid.

7) Write an SQL query to return the name of employees that manage each project in P.

8) Write an SQL CREATE ASSERTION statement to force the constraint “An employee needs to manage at least one project”

9) Write an SQL CREATE ASSERTION statement to enforce the constraint that “an employee never manages two projects that overlap in time.”

10) Define an SQL view Budget(ssn, totalbudget) that summarizes the total budget of projects that an employee manages.

Explanation / Answer

Screenshots:

SQL Queries:

Screenshots of the tables E, P and M are given above. Also, the SQL queries are given below. In table P, the end date is given NULL for projects which are ongoing assuming that an end date gets updated as and when the project is completed.

--Question 4
--SQL query to return the ssns of employees that manage at least two projects, one is finished (end < today), and the other is still ongoing
--( start =< today =< end).
Select PCompleted.ssn from
(Select ssn from M where [end] < GETDATE()) as Pcompleted JOIN
(Select ssn from M where [end] is NULL) as PInProgress --(Select ssn from M where [end] >= getdate()) as PInProgress
ON Pcompleted.ssn=PInProgress.ssn

--Question 5
--SQL query to delete all the projects from table P that are NOT managed by any employee.
Delete from P where not exists (Select * from M where M.pid=p.pid)

--Question 6
--SQL view PNum (pid, num) that gives the number of employees that manages project pid.
CREATE VIEW PNUM As
Select pid, count(*) as num from M group by pid

--Question 7
--SQL query to return the name of employees that manage each project in P.
Select distinct E.name from E Join M ON E.ssn=M.ssn

--Question 10
--SQL view Budget(ssn, totalbudget) that summarizes the total budget of projects that an employee manages.
CREATE VIEW Budget As
Select ssn, sum(budget) as totalbudget from M JOIN P ON M.pid=P.pid group by ssn

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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