Search code examples
joindatabase-designrelational-algebrarelational

Relational Algebra Join - necessary to rename?

Let's assume i have some 2 simple tables:

IMPORTANT: This is about relational algebra, not SQL.

Band table:

 band_name founded
 Gambo     1975
 John.     1342

Album table:

album_name band_name
Celsius.   Gambo
Trambo     Gambo

Now, since the Band and the Album table share the same column name "band_name", would it be necessary to rename it when i would join them?

As far as i know, the join eliminates the duplicate entry that is shared amongst the join. This example, where i simply pick all Bands that are existing in the Album table (obviously just 'Gambo' in this giving example)

Πfounded, band_name(Band ⋈ Album)

should therefore work fine, right? Can somebody confirm?

Solution

  • (Have to enter a caveat that there are many variants of Relational Algebra; that they differ in semantics; and they differ in syntax. Assuming you intend a variant similar to that in wikipedia ...)

    Yes that expression should work fine. The natural join operator ⋈ matches same-named attributes between its two operands. So the subexpression Band ⋈ Album produces a result with attributes {band_name, founded, album_name}. Your expression projects two of those.

    Note the attributes for a relation value are a set not a sequence; therefore any operation over relation operands with same-named attributes must match attributes.

    In contrast, Cartesian Product × requires its operands to have disjoint attribute names. Then Band × Album is ill-formed and would be rejected. (So you'd need to Rename band_name in one of them, to get relations that could be operands.)

    I'm not all that happy with your way of putting it "the join eliminates the duplicate entry that is shared amongst the join." Because only in SQL do you get a duplicate (from SELECT * FROM Band, Album ... -- which results in a table with four columns, of which two are named band_name). SQL FROM list of tables is a botch-up: neither join nor Cartesian Product, but something trying to be both, and succeeding only in being neither. RA's ⋈ never produces a "duplicate" so never does it "eliminate" anything.

    Particularly if there's Keys declared and a Foreign Key constraint (from Album's band_name to Band's) I see those as identifying the same band, then the natural operation is to bring together that which has been taken apart, so the name 'Natural Join'.