Search code examples
relational-algebra

Relational algebra for one-to-many relations

Suppose I have the following relations:

Academic(academicID (PK), forename, surname, room)
Contact (contactID (PK), forename, surname, phone, academicNO (FK))

I am using Java & I want to understand the use of the notation.

Π( relation, attr1, ... attrn ) means project the n attributes out of the relation.
σ( relation, condition) means select the rows which match the condition.
⊗(relation1,attr1,relation2,attr2) means join the two relations on the named attributes.
relation1 – relation2 is the difference between two relations.
relation1 ÷ relation2 divides one relation by another.

Examples I have seen use three tables. I want to know the logic when only two tables are involved (academic and contact) as opposed to three (academic, contact, owns).

I am using this structure:

LessNumVac = Π( σ( job, vacancies < 2 ), type )
AllTypes = Π( job, type )
AllTypes – LessNumVac

How do I construct the algebra for:

List the names of all contacts owned by academic "John"

Solution

  • List the names of all contacts who is owned by academic "John".

    For that, you would join the Academic and Conctact relations, filter for John, and project the name attributes. For efficiency, select John before joining:

    πforename, surename (Contact ⋈academicNO = academicID (πacademicID (σforename = "John" Academic))))