Relational Algebra Operations

Relational Algebra

• The relational algebra consists of operations that are basic for the relational model
• These operations facilitates users in specifying retrieval requests
• The result of a request is a new relation, from one or more relations
• Algebra operations produce new relations, which can be manipulated using similar operations
• Relational algebra operations form a relational algebra expression, whose result represents the result of a database query

Relational Algebra Operations

• Relational Algebra consists of several groups of operations

Unary Relational Operations

• Operate on single relations
• SELECT denoted by symbol sigma
• PROJECT denoted by symbol pi

Relational Algebra Operations from Mathematical Set Theory

• UNION denoted by symbol U
• INTERSECTION denoted by symbol ∩
• DIFFERENCE denoted by symbol –

Binary Relational Operations

• Operate on two relations
• JOIN denoted by symbol ⋈ (Natural Join)

The SELECT Operation

• Used to select a subset of the tuples from a relation that satisfies a selection condition
• It is a filter that keeps tuples that satisfy a condition
• Those tuples that satisfy the condition are selected, others are discarded
• Denoted by σ (R), where sigma denotes the select operator
• Produces a relation S that has the same schema as R

SELECT Operation Example

• To select employee tuples with department number 4, or salary greater than $30,000
• The notation DNO = 4 (EMPLOYEE) can be used
• The notation SALARY > 30,000 (EMPLOYEE) can also be used
• Relation result is the result of (Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000) (EMPLOYEE)

The PROJECT Operation

• Selects certain columns from a table and discards others
• A vertical partitioning is created
• One partition contains the needed columns/attributes
• The other partition contains discarded columns

PROJECT Operation Example

• To list each employee's first/last name and salary, FNAME, LNAME, SALARY(EMPLOYEE) is used
• The general form of the project operation is (R)
• Pi (π) is the symbol used to represent the project operation
• "" specifies the desired attribute list from the attributes of relation R
• The project operation removes any duplicate tuples, making the result a set of tuples and a valid relation
• The number of tuples is always less than or equal to the number of tuples in R
• If the list of attributes includes a key of R, then the number of tuples equals the number of tuples in R

Relational Algebra Operations From Set Theory

• The next group of operations are the standard mathematical operations on sets
• To retrieve the SSN of all employees who work in department 5 or supervise someone who works in department 5:
  • First: DEP5_EMPS ← σDno=5(EMPLOYEE)
  • Second: RESULT1 ← πSsn (DEP5_EMPS)
  • Third: RESULT2(Ssn) ← πSuper_ssn(DEP5_EMPS)
  • Finally: RESULT ← RESULT1 ∪ RESULT2

Union Operation

• Denoted by R ∪ S
• Includes tuples that are in R or S or both
• Duplicate tuples are eliminated

Intersection Operation

• Denoted by R ∩ S
• Includes tuples that are in both R and S

Set Difference Operation

• Denoted by R - S
• Includes tuples that are in R but not in S
• Union, Intersection, and Set Difference are binary operations, requiring type-compatible operands

Type Compatibility

• Operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes
• Domains of corresponding attributes must be compatible (dom(Ai) = dom(Bi) for i=1, 2, ..., n)
• Resulting relation (R1 ∪ R2, R1 ∩ R2, or R1-R2) has the same attribute names as the first operand relation R1

The JOIN Operation

• Denoted by ⋈
• Combines related tuples from two relations into single tuples
• General form: R ⋈ S

EQUIJOIN

• Joining is built on the basis of shared attributes, mark (=) identifies common attributes, deleting redundancy

JOIN Operation Example

• To retrieve the name of each department's manager
• Department tuple must be combined with the employee tuple whose SSN value matches the manager's SSN value in the department tuple
  • First: DEPT_MGR ← DEPARTMENT ⋈Mgr_ssn=Ssn EMPLOYEE
  • Second: RESULT ← πDname, Lname, Fname (DEPT_MGR)