RELATIONAL ALGEBRA

RELATIONAL ALGEBRA

Chapter 6

1

“Relational” Mathematics

⚫ A mathematical basis is a great way to formally express the myriad of requests we may want to make

⚫ Operations from Algebra and Set Theory

2

The Relational Algebra and Relational Calculus

⚫ Relational algebra

◦ Basic set of operations for the relational model

⚫ Relational algebra expression

◦ Sequence of relational algebra operations

⚫ Relational calculus

◦ Higher-level declarative language for

specifying relational queries

Relational Algebra

⚫ Unary Relational Operations

◦ SELECT (symbol:

σ

◦ PROJECT (symbol: π (pi))

◦ RENAME (symbol: ρ (rho))

• Relational Algebra Operations From Set Theory

◦ UNION ( ∪ ), INTERSECTION ( ∩ ), DIFFERENCE (or MINUS, – )

◦ CARTESIAN PRODUCT ( x )

4

Operations

⚫

Operations: have relations as inputs and outputs (i.e., produce new relations)

◦ The result of an operation is a new relation, which may have been formed from one or more input relations

◦ The algebra is “closed” (all objects in relational algebra are relations)

⚫

Expressions: A sequence of relational algebra operations

◦ The result of a relational algebra expression is also a relation that represents the result of a database query (or retrieval request)

5

Unary Relational Operations

⚫ SELECT operation - σ (sigma)

◦ Unary -- Applied to a single relation

• <selection condition> applied independently to each individual tuple t in R

• If condition evaluates to TRUE, tuple selected

• <attribute > <op> [ <constant > | <attribute name>]

• Boolean conditions AND, OR, and NOT

SELECT Operation

⚫

Examples:

◦ Select the EMPLOYEE tuples whose department number is 4:

σ

(EMPLOYEE)

◦ Select the EMPLOYEE tuples whose salary is greater than $30,000:

σ

(EMPLOYEE)

Also,

σ (Dno = 4 AND Salary > 25000)

OR (Dno = 5 AND Salary > 30000) (EMPLOYEE)

SELECT operation

⚫

Nested application of SELECT operations o σ

(σ

(R)) ≡

⚫

Is SELECT Commutative?

o Yes! ☺

o σ <condition1>(σ < condition2> (R)) = σ <condition2> (σ < condition1> (R))

⚫

Cascading of the SELECT operation may be applied in any order:

o σ

(σ

(σ

(R))) = σ

(σ

(σ

( R)))

9

PROJECT Operation - π _(pi)

⚫

Selects columns from table and discards the other columns:

⚫

Eliminates duplicates

◦ Result of PROJECT operation is a set of distinct tuples

⚫ Project each employee’s first, last name, and salary:

π

(EMPLOYEE)

π Lname, Fname, Salary (EMPLOYEE)

PROJECT operation

⚫ Nested application of PROJECT operations

o π

(π

(R))

o Attributes in <list2> contains the attributes in

<list1>

⚫ Is PROJECT Commutative?

o No ☹

o π

(π

(R)) ≠ π

(π

(R))

12

Nesting Different Operators

⚫

List birthdates of employees in departments 3, 5, 7.

σ

(EMPLOYEE) π

(EMPLOYEE)

13

π

(σ

(EMPLOYEE))

RENAME Operation - ρ (rho)

⚫ Rename attributes in intermediate results

⚫ In-line expression:

⚫ Sequence of operations

≈ ρ

RENAME Operation

⚫

ρ

(R) only changes:

◦ The relation name to S

⚫

ρ

(R ) only changes:

◦ The column (attribute) names to B1, B1, …..Bn

⚫

ρ

(R) changes both:

◦ The relation name to S, and

◦ The column (attribute) names to B1, B1, …..Bn

ρ RESULT (F,M,L,S,B,A,SX,SAL,SU, DNO) (EMPLOYEE)

RELATIONAL ALGEBRA

Set Theory

16

UNION, INTERSECTION, and MINUS

⚫ Merge the elements of two sets in various ways

⚫ Binary operations

⚫ Relations must have the same type of tuples

17

R S

Relational Algebra Operations from Set Theory

⚫

UNION

◦ R ∪ _S

◦ Includes all tuples in either R, or S, or in both R and S

⚫

INTERSECTION

◦ R ∩ S

◦ Includes all tuples that are in both R and S

⚫

SET DIFFERENCE (or MINUS)

◦ R – S

◦ Includes all tuples in R but not in S

R S

R S

R S

UNION operation - ∪

⚫ The result of R ∪ S, is a relation that includes all tuples that are either in R or in S or in both R and S

⚫ Duplicate tuples are eliminated

⚫ R and S must be “type compatible” (or UNION compatible)

19

R S

Type Compatibility

⚫ R (A

, A

, ..., A

) and S(B

, B

, ..., B

) are type compatible if:

◦ They have the same number of attributes

◦ The domains of corresponding attributes are

type compatible i.e., dom(A

) = dom(B

) for i =

1, 2, ..., n.

UNION Operation Example

⚫

List the social security numbers of all employees who either work in Dept. 5 or directly supervise an employee who works in Dept. 5.

DEP5_EMPS ← σ_Dno=5 (EMPLOYEE) RESULT1 ← π _Ssn(DEP5_EMPS)

RESULT2(SSN) ← π_{Super_ssn}(DEP5_EMPS) RESULT ← RESULT1 ∪ RESULT2

INTERSECTION operation- ∩

⚫ R ∩ S includes all tuples that are in both R and S

⚫ The attribute names in the result will be the same as the attribute names in R

⚫ The two operand relations R and S must be “type compatible”

22

R S

DIFFERENCE operator –

⚫ Also called MINUS or EXCEPT

⚫ The result of R – S, is a relation that

includes all tuples that are in R but not in S

⚫ The attribute names in the result will be the same as the attribute names in R

⚫ The two operand relations R and S must be “type compatible”

23

R S

UNION, INTERSECT, and DIFFERENCE

STUDENT ∪ INSTRUCTOR

STUDENT ∩ INSTRUCTOR

STUDENT −

INSTRUCTOR INSTRUCTOR - STUDENT

UNION, INTERSECT, and DIFFERENCE

⚫

Both union and intersection are commutative operations:

◦ R ∪ S = S ∪ R, and R ∩ S = S ∩ R

⚫

Both union and intersection can be treated as n-ary operations

◦ as both are associative operations

◦ R ∪ (S ∪ T) = (R ∪ S) ∪ T

◦ (R ∩ S) ∩ T = R ∩ (S ∩ T)

⚫

The minus operation is not commutative

◦ R – S ≠ S – R

Joins

⚫ Join information between 2 or more tables

◦ Binary operators

◦ Cross Product - X

◦ Theta - θ

◦ Equi-join

◦ Natural

26

The CARTESIAN operation - X

⚫

CROSS PRODUCT or CROSS JOIN

⚫

Combine tuples from two relations in a combinatorial fashion, i.e. an exhaustive pairing of tuples

⚫

Q = R(A

, A

, . . ., A

) x S(B

, B

, . . ., B

)

◦ Relation Q has degree n + m attributes:

● Q(A₁, A₂, . . ., A_n, B₁, B₂, . . ., B_m), in that order.

⚫

The two operands do NOT have to be "type compatible”

⚫

When is this operation useful?

27

Database COMPAN Y Ex., Find all

dependants of female employes

Not Join

Compatible

Find all dependants of female employes

FEMALE_EMPS ← σ _SEX=’F’(EMPLOYEE)

EMPNAMES ← π FNAME, LNAME, SSN (FEMALE_EMPS)

EMP_DEPENDENTS ← EMPNAMES x DEPENDENT

contains every combination

ACTUAL_DEPS ←

σ _SSN=ESSN(EMP_DEPENDENTS) RESULT ←

π FNAME, LNAME, DEPENDENT_NAME

(ACTUAL_DEPS)

Follow with σ / π

The JOIN Operation

⚫ Denoted by

⚫ Combine related tuples from two relations

⚫ General join condition of the form

<condition> AND <condition>

AND...AND <condition>

⚫ Example:

32

THETA JOIN Operation

⚫ R

S

◦ θ can be any general boolean expression on the attributes of R and S

◦ Each term of θ relates a tuple from R with a tuple from S using any {<, ≤, >, ≥, =, ≠}

⚫

In practice, θ is constructed with just one or more equality conditions “AND”ed together:

◦ θ ← R.A_i = S.B_j AND R.A_k = S.B_l AND R.A_p = S.B_q

◦ Each attribute pair is called a join attribute

33

Variations of JOIN

⚫

EQUIJOIN

◦ Only = comparison operator used

◦ Always have one or more pairs of attributes that have identical values in every tuple

⚫

NATURAL JOIN

◦ Denoted by *

◦ Removes second (superfluous) attribute in an

EQUIJOIN condition

35

Retrieve the name of the manager of each department

Combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple.

Equijoin

DEPT_MGR ← DEPARTMENT

EMPLOYEE

NATURAL JOIN Operator

⚫

A simplification of an Equijoin joins join attributes with the same name

⚫ Q ← R(A,B,C,D) * S(C,D,E)

◦ The implicit join condition is

● R.C = S.C AND R.D = S.D

◦ Resulting relation Q(A, B, C, D, E)

36

List information about each department including its location

Combine DEPARTMENT with DEPT_LOCATIONS

DEPT_LOCS ← DEPARTMENT * DEPT_LOCATIONS

37

38

⚫

Use EQUIJOIN

◦ π

(EMPLOYEE

(σ

Dlocation = ‘Ann Arbor’

(DEPT_LOCATIONS)))

⚫

Use NATURAL Join

◦ π

(EMPLOYEE * ρ

(σ

‘Ann Arbor’

(DEPT_LOCATIONS)))

Find the full names of employees who

work in Ann Arbor

Joins – Binary Operations

39

Name Notation Notes

Cross-Product ^{R X S} Combines all tuples and attributes of R, S Useful when followed by selection

Join ^R <joincondition> S Only match related tuples

Theta-Join ^R _θ ^S θ can be any general boolean expression on the attributes of R and S

Each term of θ relates a tuple from R with a tuple from S using any {<, ≤, >, ≥, =, ≠}

Equi-Join ^R _θ ^S

θ ←

R.A_i = S.B_j AND R.A_k = S.B_l AND R.A_p = S.B_q

Combines all attributes of R, S

Constructed with = as the only comparison operator

More equality conditions may be AND’d together

Natural Join ^{R * S} A simplification of an Equijoin Combines all attributes of R, S

Joins attributes with the same name

Matching attributes are implicitly matched

Relational Algebra e-Book

40