SQL -> Relational Algebra

Question

Suppose I have the following relations:

Branch (branchNo(PK), street, city, postcode)

Staff (staffNo(PK), fName, lName, sex, branchNo(FK))

Not that it matters for this question, but PK = primary key & FK = foreign key

How would I write the relational algebra for the following query:

List the names of all female staff that work in Glasgow.

My attempt:

σStaff.sex=F & Branch.city = Glasgow(πfName, lName, sex, branchNo(Staff) x πcity, branchNo(Branch))

I know that my selection (σ) statement (NOT TO BE CONFUSED WITH SELECT) is syntactically incorrect:

σStaff.sex=F & Branch.city = Glasgow

How do I write two selections on different relations? Or in other words, how do I express an SQL statement with two or more conditions in the WHERE clause in relational algebra? I have used '&' but this cannot be right? Do I have to embed one selection within the other?

NOT HOMEWORK

Answer 1

Formal relational algebra uses logical conjunction and disjunction and (typically) the symbols for same (∧ and ∨, respectively), though authors are free to pick their own syntax. The query could be written as:

πfName, lName(σ(gender=F ∧ city=Glasgow)(Staff ⋈ Branch))

Note that x (rather, ⨯) is the symbol for Cartesian product. For natural joins, you want ⋈ (bowtie).

If you want the Cartesian product rather than natural join, you basically implement a natural join by adding the appropriate condition to the select. You'll also need to deal with the fact that the attribute branchNo is common to both relations, which you can do by using the rename operator (ρ).

πfName, lName(σ(gender=F ∧ city=Glasgow ∧ branchNo=bNum)(Staff ⨯ ρbNum/branchNo(Branch)))

Formally, you can do this because:

R ⋈ S = πα(R),α(S)-α(R)(σα(R)∩α(S)=t1..k(R ⨯ ρ t1..k/α(R)∩α(S)(S))))

where α(T) are the attribute names for relation T (making α(R) ∩ α(S) the common attribute names) and t_1..k ⊈ α(R) ∪ α(S) are new names for the common attributes.