We help IT Professionals succeed at work.
Get Started

Problem converting relational algebra to SQL

J2015
J2015 asked
on
101 Views
Last Modified: 2015-10-10
I need to translate this into relational algebra, then translate the relational algebra in SQL for 1) SQL Server and 2) Access:  "List the names of departments in which ALL jobs, except PRESIDENT, are held by at least one employee."

We have two tables:
DEPARTMENT (dptname, dptID)
EMPLOYEE (empID, job, dptID)

Here is the relational algebra:
R1 = Π job (∑ job <> ‘PRESIDENT’ (EMPLOYEE))
R2 (e2, dptID) =Π job, dptID (R1 X DEPARTMENT)
R3 = Π job, dptID (EMPLOYEE)
R4 (e2, d2, e, d) = Π e2, R2.dptID, R3.job, R3.dptID (R2 =* R3)
R5 = Π dptname (∑ e <> NULL (R4 X DEPARTMENT))

Here is my R1 statement converted to SQL. It works well:
SELECT job FROM (SELECT DISTINCT job FROM EMPLOYEE WHERE job <> 'PRESIDENT') AS R1

However, my converted R2 statement fails. Here is the preliminary version, where the embedded R1 is still temporarily represented by 'R1' showing its embedded position inside the logic:
SELECT e2, nodpt FROM (SELECT DISTINCT job AS e2, dptID FROM DEPARTMENT, (R1)) AS R2

And here is the final version, where 'R1' has been replaced with its actual SQL text:
SELECT e2, dptID FROM (SELECT DISTINCT job AS e2, dptID FROM DEPARTMENT, (
SELECT job FROM (SELECT DISTINCT job FROM EMPLOYEE WHERE job <> 'PRESIDENT') AS R1
)) AS R2

My SQL fails in SQL Server with error message "Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'.".

What am I doing wrong? I guess if you can help determine why it fails, then I should be able to convert statements R3, R4, and R5 without any problems.

Thanks
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE