I need some help converting an SQL query into relational algebra.
Here is the SQL query:
SELECT * FROM Customer, Appointment
WHERE Appointment.CustomerCode = Customer.CustomerCode
AND Appointment.ServerCode IN
(
SELECT ServerCode FROM Appointment WHERE CustomerCode = '102'
)
;
I'm stuck because of the IN
subquery in the above example.
Can anyone demonstrate for me how to express this SQL query in relational algebra?
Many thanks.
EDIT: Here is my proposed solution in relational algebra. Is this correct? Does it reproduce the SQL query?
Scodes ← ΠServerCode(σCustomerCode='102'(Appointment))
Ccodes ← ΠCustomerCode(Appointment ⋉ Scodes)
Result ← (Customer ⋉ Ccodes)
Your SQL code will result in duplicate columns for CustomerCode and the use of
SELECT [ALL]
is likely to result in duplicate rows. Because the result is not a relation, it cannot be expressed in relational algebra.These problems are easily fixed in SQL:
You didn't specify which relational algebra you are intereted in. Date and Darwen proposed an algebra named A, specified an A language named D, and designed a D language named Tutorial D.
Tutorial D uses operators
JOIN
for natural join,WHERE
for restriction andMATCHING
for semijoin, The slight complication is the comparison in SQL:CustomerCode = '102'
The comparison of a
CustomerCode
value to aCHAR
value in SQL is possible because of implicit coercion. Tutorial D is stricter -- type safe, if you will -- requiring you to overload the equality operator or, more practically, define a selector operator forCHAR
, which would typically have the same name as the type.Therefore, the above (revised) SQL may be written in Tutorial D as: