SQL & # 8594; Relational algebra

Question

Suppose I have the following relationships:

Branch (branchNo(PK), street, city, postcode) Staff (staffNo(PK), fName, lName, sex, branchNo(FK)) 

Not that this is important for this question, but PK = primary key and FK = foreign key

How to write relational algebra for the following query:

List the names of all female employees who 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 (SHOULD NOT BE ELIMINATED WITH SELECT) is syntactically incorrect:

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

How to write two choices for a different relationship? Or, in other words, how to express an SQL statement with two or more conditions in a WHERE clause in relational algebra? I used '&' but it can't be right? Should I embed one choice in another?

NOT HOMEWORK

Answer 1

Formal relational algebra uses logical conjunction and disjunction and (usually) symbols for them ( & and; and & or;, respectively), although authors can choose their own syntax. The request can 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 a Cartesian product, not a natural join, you basically implement a natural join by adding the appropriate condition to the selection. You also need to deal with the fact that the branchNo attribute is common to both relationships, which you can do using