Theta Join in Relational Algebra Correctness

问题:

Considering these two tables:

Band (band_id, band_name)

Band_Member (band_id, member_name, dob, country, sex)

The task: List the names of band members and the names of the bands they are in using the theta Join.

Well, my doubt is in the step to show only the band_name and member_name after the join.

This is how I've done the theta-join: σ Band_Member.band_id = Band.band_id (Band_member x Band)

And this is how I think it might be correct to show only the band_name and member_name:

Π band_name, member_name (σ Band_Member.band_id = Band.band_id (Band_member x Band))

EDIT: I also wonder if it is correct to write the Cartesian Product of the same task in these ways:

Π band_name, member_name (Band_Member x Band)

OR

Π member_name (Band_Member) x Π band_name (Band_Member)

回答1:

Π band_name, member_name (σ Band_Member.band_id = Band.band_id (Band_member × Band))

shows all band members of each band.

Π band_name, member_name (Band_Member x Band)

This lists every member of any band for every band, whether they are members of that band or not. That is not the same as your earlier expression.

Π member_name (Band_Member) x Π band_name (Band_Member)

This is a relation containing every member of any band for every band that has a member (which is a subset of all bands). This is not the same thing as either of your earlier expressions.

The more idiomatic way of doing your first expression is a natural join:

Π band_name, member_name (Band_Member ⋈ Band)

which is the same thing as your first expression.