3

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.

Argon
  • 133
  • 3

2 Answers2

5

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.

Doug Deden
  • 2,171
  • 10
  • 14
-1

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

Samanuai A
  • 1