0

I have the following tables:

  • Employee(eid,ename,salary,did,classification)
  • Project(pid,pname,did,budget,ddate)
  • Department(did,dname,floor,head)
  • dbudget(did,byear,budget)
  • Project(pid,pname,did,budget,ddate)
  • Onproject(pid,eid,fdate)

did department identifier, so we need something that is in employee.did but non on project.did

and I want to retrieve all of the projects whose duedate is not passed, and that all of its workers belong to a department that is not responsible for the project.

now, I don't know how to check if the ddate is over(ddate is due date, and I don't know if it's passed or not), and how to represent it in a query.

I'll show the query I constructed so far:

SELECT   Project.pname 
from         Project 
FULL OUTER JOIN Employee 
on   Project.did <>  Employee.did 
AND .... 

(don't know how to check if ddate is passed) is there a function to show date in SQL to compare (so the difference will be positive) or any trick to work with the given info?

1
  • 1
    postgresql, but if possible, need some general sql because i later have to translate it into relation calculus Dec 6 '17 at 12:17
6

The problem is not simple as it involves 2 entities/tables (Employee and Project) that have 2 relationships between them. One is through the Department (a project belongs to a department, an employee belongs to a department) and the other through Onproject (which shows that an employee has started working on a project).

Moreover, the answer needs a relational division query ("all is workers belong ..."). There are many ways to do relational division - and all are in the range of not simple to very complicated.

One way to express the problem is with a (double) negative. Instead of finding projects where all workers do not do something, find projects where none of the workers do something. This can be expressed with a NOT EXISTS subquery:

SELECT p.* 
FROM Project AS p
WHERE p.ddate > CURRENT_DATE 
  AND NOT EXISTS
      ( SELECT * 
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
        WHERE w.pid = p.pid
          AND e.did = p.did
      ) ;

Or with EXCEPT:

SELECT p.* 
FROM Project AS p
    JOIN
      ( SELECT p.pid, p.did
        FROM Project AS p
        EXCEPT
        SELECT w.pid, e.did
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
      ) AS pw
      ON pw.pid = p.pid
WHERE p.ddate > CURRENT_DATE ;

Given the tables definitions, the latter can be (simplified?) to use NATURAL JOIN:

SELECT * 
FROM Project
    NATURAL JOIN
      ( SELECT pid, did FROM Project 
        EXCEPT
        SELECT pid, did FROM Employee NATURAL JOIN Onproject
      ) AS pw
WHERE ddate > CURRENT_DATE ;
2
  • thank you for your detailed answer. i was wondering, since i need to turn it later into a relation algebra expression, is there anyway to bypass CURRENT_DATE? or do you know if CURRENT_DATE can be used in relation algebra? thank you very much again for your effort! Dec 6 '17 at 15:27
  • 1
    Perhaps you ask your teacher/tutor about that. I don't see why you can't use it. The same you would translate a WHERE year = 2000 condition to relational algebra. CURRENT_DATE is a (run time) constant. Not much different than a constant. Dec 6 '17 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.