---

Relational Algebra: Relational Database specific operations σ, π, × and ⋈

---

• The selection operation (σ)
  • Syntax:

    σ(Condition)(R)

  • Effect:

    Selects tuples from a relation R that satisfies the condition Condition The Condition expression contains: constants and/or attributes of relation R However: the condition expression cannot be a set !!! The result of σ (Condition) (R) is a subset of tuples of R that satisfies the boolean condition Condition

    ---

  • Example 1:

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

      Answer:

      σ (dno = 4) (employee)

    • The following diagram shows the result graphically (hopefully, it will illustrates the concept unambiguously):

    ---

  • Example 2:
    • Retrieve all employees earning more than $30,000:

      Answer:

      σ (salary > 30000) (employee)

    ---

  • Example 3:
    • Retrieve all employees working for department number 4 and eaning more than $30,000:

      Answer:

      σ (dno = 4 and salary > 30000) (employee)

      OR:

      σ (salary > 30000) ( σ (dno = 4) (employee) )

  ---

  ---

  ---

  ---

• The projection operation (π)
  • Syntax:

    π (attribute-list) (R)

    Effect:

    "Selects out" only the attribute values 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) contains the is a set of tuples

    ---

  • Example 1:
    • Retrieve only the SSN field from the employees

      π ssn (employee)

    • The following diagram shows the result graphically (hopefully, it will illustrates the concept unambiguously):

      ---

    • Example 2:
      • Retrieve the sex information from all employees:

        π sex (employee)

        The following diagram shows the result graphically (hopefully, it will illustrates the concept of projection unambiguously):

      Notes:

      The output of a Relational Algebra query is set. A set in Mathematics does not have duplicate elements Therefore, the result of πsex (employee) is the set {F, M} because duplicates are removed In SQL, the user will have the option to remove duplicates That is because removing duplicates require longer time to process.... (The user has the option to incur the cost or not...)

  ---

  ---

  ---

  ---

• The Cartesian Product ×
  • The Cartesian Product operator × performs the following operation:

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

    ---

  • Example:

    Employee: ssn fname lname dno --------- ------ -------- ----------- 123456789 John Smith 5 333445555 Frankl Wong 5 999887777 Alicia Zelaya 4 987654321 Jennif Wallace 4 Department: dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 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 Example: Employee: ssn fname lname dno --------- ------ -------- ----------- 123456789 John Smith 5 states that John Smith works in department 5 The cartesian product will pair the informtion on John Smith with every department: ssn fname lname dno dname dnumber --------- ------ -------- ----------- --------------- ----------- .... 123456789 John Smith 5 Research 5 123456789 John Smith 5 Administration 4 123456789 John Smith 5 Headquarters 1

  ---

  ---

  ---

  ---

• The join operation ⋈
  • The join operation is a short hand notation for the following:

    A ⋈C B = σC ( A × B )

    ---

  • Example:

    Employee: ssn fname lname dno --------- ------ -------- ----------- 123456789 John Smith 5 333445555 Frankl Wong 5 999887777 Alicia Zelaya 4 987654321 Jennif Wallace 4 Department: dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 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 σdnumber=dno (Employee × Department): ( = Employee ⋈dnumber=dno 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

  ---

  ---

  ---

  ---

• The set division operation
  • We will not use the the set division operation in CS554