Schema Diagram: enter image description here Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

The given solution is :

enter image description here

However, I believe that simply using the following expression should be enough:

enter image description here

Now, the reason why I believe this is mainly because of the fact that the EMPLOYEE relation holds the tuples which correspond to workers as well as managers. Now, an employee can be either a worker or a manager. So, going by that logic the above expression should be enough. I wholeheartedly agree with the fact that a given query can be represented through more than one relational algebraic expressions, however doing what the given solution (the bigger one) suggests above is highly redundant.

So to sum it up, is the direction of my thought correct?

Source : Fundamentals of Database Systems by Ramez Elmasri & Shamkant B. Navathe, 6th Edition, Page No. 172 Query 4.


2 Answers 2


Trying to find a simpler solution is admirable, but your expression is subtly wrong.

The important part of the question is:

...or as a manager of the department...

Notice, that's not the same as "...or as a manager of the employee...".

Let's throw some names out there.

  • Ed White works for Brian Green. Ed is working on Project 123.
  • Project 123 is controlled by the Finance Department. Kathy Smith is the manager of the Finance Department.

So the requested result should include Project 123 in the output, because Project 123 is controlled by a department whose manager has a last name of Smith.

Your proposed solution will not include Project 123, because neither Ed nor his manager have a last name of Smith.

  • Thank you for the reply. However I'm still not completely convinced by the answer. For instance, let's consider your example of Kathy Smith : according to me, as long as Kathy Smith is an employee, her record will be present in the EMPLOYEE relation. Secondly, since she is a part of Project 123, therefore the WORKS_ON relation will also contain her record. So, according to my solution, the join operation should be able to pick this up. And also, I didn't get how Ed or his manager fit into the entire picture. That's my whole thought process when it comes to your answer. Where am I wrong?
    – Argon
    Commented Jul 4, 2019 at 0:33
  • 1
    Kathy is not working on Project 123. There is nothing in the Works_On table that connects Kathy to Project 123. So your solution won't list Project 123, while the requirements indicate that Project 123 should be listed. Kathy is connected to Project 123 because she is the manager of the department that controls the project.
    – Doug Deden
    Commented Jul 4, 2019 at 21:31
  • Okay. Point accepted. Now it makes everything clear. Thank you for the answer once again!
    – Argon
    Commented Jul 5, 2019 at 0:16

if we change the join condition in 3rd step from ssn==mgrssn to ssn=mgrssn AND lname="smith" we can again simply this by taking only the Dnumber and join that relation with projects with dnumber=dnum to retrieve the pnos of smith hence reducing steps 3,4 and 5 to 2 steps. I hope im making sense here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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