The selection operator σ  

  • Syntax: 

         σ(Condition)(R)

  • Effect:

    • Selects all tuples from a relation R that satisfies the condition Condition

  • The Condition contains:

    • Constants and/or
    • Attributes of relation R
    • Relational and boolean operators

  • The result of σ (Condition) (R) is a (sub)set of tuples (of R)

Example of the selection operator σ  

  • Example:

    • Retrieve all employee tuples for employees that work for department number 4

    Answer:

        σ (dno = 4) (employee)

  • The following diagram illustrate the effect of the σ operator:

The projection operation π

  • Syntax: 

       π(attribute-list)(R)

  • Effect:

    • "Selects out" only the attribute values (columns) given in the attribute-list from all tuples in relation R

  • The attribute-list contains the list of attributes in relation R that will be selected.

  • The result of π(attribute-list) (R) is a set of tuples

Example of the projection operation π

  • Example:

    • Retrieve the sex information from all employees

    Answer:

        π sex (employee)

  • The following diagram illustrate the effect of the π operator:

The cartesian product ×  

  • Syntax: 

      A × B

  • Effect:

    • A × B = { (a, b) | a ∈ A ∧ b ∈ B }

  • I.e.:

    • A × B outputs tuple pairs consisting of all combinations of tuples: 

          ( a, b )

      where a is a tuple in relation A   and   b is a tuple in relation B

Example of the cartesian product ×   

 Employee:                           Department:

   ssn       fname  lname    dno        dname           dnumber mgrssn    mgrstartdate 
   --------- ------ -------- -----      --------------- ------- --------- ------------ 
   123456789 John   Smith      5        Research           5    333445555 22-MAY-78 
   333445555 Frankl Wong       5        Administration     4    987654321 01-JAN-85
   999887777 Alicia Zelaya     4 
   987654321 Jennif Wallace    4 



 Employee × Department:

      ssn       fname  lname    dno  dname           dnumber     mgrssn    mgrstartdate 
      --------- ------ -------- ---- --------------- -------- --------- ------------ 
      123456789 John   Smith      5  Research            5    333445555 22-MAY-78    
      123456789 John   Smith      5  Administration      4    987654321 01-JAN-85    
      333445555 Frankl Wong       5  Research            5    333445555 22-MAY-78    
      333445555 Frankl Wong       5  Administration      4    987654321 01-JAN-85    
      999887777 Alicia Zelaya     4  Research            5    333445555 22-MAY-78    
      999887777 Alicia Zelaya     4  Administration      4    987654321 01-JAN-85    
      987654321 Jennif Wallace    4  Research            5    333445555 22-MAY-78    
      987654321 Jennif Wallace    4  Administration      4    987654321 01-JAN-85

Note:   the cartesian product × will produce many meaningless results

The join ⋈  

  • Syntax: 

      A ⋈Cond B          // Cond is a boolean codition

  • Effect:

    • A ⋈Cond B   ≡   σCond ( A × B )

  • I.e.:

    • A ⋈Cond B outputs tuple pairs: 

          ( a, b )   where  a ∈ A and b ∈ B

      and the tuple ( a, b ) satisfies the condition Cond

Example of the join ⋈   

 Employee:                           Department:

   ssn       fname  lname    dno        dname           dnumber mgrssn    mgrstartdate 
   --------- ------ -------- -----      --------------- ------- --------- ------------ 
   123456789 John   Smith      5        Research           5    333445555 22-MAY-78 
   333445555 Frankl Wong       5        Administration     4    987654321 01-JAN-85
   999887777 Alicia Zelaya     4 
   987654321 Jennif Wallace    4 



 Employee ⋈dnumber=dno Department  ≡   σdnumber=dno (Employee × Department):
 

      ssn       fname  lname    dno  dname           dnumber     mgrssn    mgrstartdate 
      --------- ------ -------- ---- --------------- -------- --------- ------------ 
      123456789 John   Smith      5  Research            5    333445555 22-MAY-78    
      333445555 Frankl Wong       5  Research            5    333445555 22-MAY-78     
      999887777 Alicia Zelaya     4  Administration      4    987654321 01-JAN-85    
      987654321 Jennif Wallace    4  Administration      4    987654321 01-JAN-85

Note:   join ⋈ can link corresponding tuples in different relations using a foreign key