Avatar of J2015
J2015
 asked on

Problem converting relational algebra to SQL

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
Microsoft SQL Server

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck