RELATIONAL ALGEBRA

(1)

RELATIONAL ALGEBRA

(CONTINUED)

Chapter 6

1

(2)

“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

(3)

Summary

⚫ Complete Set: The set of operations including

◦ SELECT σ,

◦ PROJECT π ,

◦ RENAME ρ,

◦ UNION ∪,

◦ INTERSECTION ∩

◦ DIFFERENCE − ,

◦ CARTESIAN PRODUCT X

⚫ Any other relational algebra expression can be expressed by a combination of these five

operations.

◦ R ∩ S = (R ∪ S ) – ((R − S) ∪ (S − R))

◦ R

S = σ

(R X S)

3

(4)

SELECT

⚫ Nested application of SELECT operations o σ

<cond1>

(σ

< cond2>

(R)) ≡ ^σ

(R)

⚫ Is SELECT Commutative?

o Yes! ☺

o σ

(σ

< condition2>

(R)) = σ

(σ

< condition1>

(R))

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

o σ

<cond1>

(σ

<cond2>

(σ

<cond3>

(R))) = σ

<cond2>

(σ

<cond3>

(σ

<cond1>

( R)))

(5)

The 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:

π LNAME, FNAME,SALARY (EMPLOYEE)

(6)

RENAME Operation - ρ (rho)

⚫ In-line expression:

⚫ Sequence of operations:

⚫ Rename attributes in intermediate results

◦ RENAME operation

(7)

UNION, INTERSECTION, and MINUS

⚫ Merge the elements of two sets in various ways

⚫ Binary operations

⚫ Relations must have the same type of tuples

7

R S

(8)

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

R S

(9)

UNION, INTERSECT, and SET DIFFERENCE

STUDENT ∪ INSTRUCTOR

STUDENT ∩ INSTRUCTOR

STUDENT −

INSTRUCTOR INSTRUCTOR - STUDENT

(10)

UNION, INTERSECT, and SET 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

(11)

Joins

⚫ Join information between 2 or more tables

◦ Binary operators

◦ Cross Product - X

◦ Theta - θ

◦ Equi-join

◦ Natural

11

(12)

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

_n

) x S(B

₁

, B

₂

, . . ., B

_m

)

◦ 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?

(13)

FEMALE_EMPS ← σ _SEX=’F’(EMPLOYEE)

EMPNAMES ← π FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS ← EMPNAMES x DEPENDENT

ACTUAL_DEPS ← σ _SSN=ESSN(EMP_DEPENDENTS) RESULT ← π FNAME, LNAME, DEPENDENT_NAME

(ACTUAL_DEPS)

contains every combination

(14)

The JOIN Operation

⚫ Denoted by

⚫ Combine related tuples from two relations

⚫ General join condition of the form

<condition> AND <condition>

AND...AND <condition>

⚫ Example:

(15)

Variations of JOIN

⚫

THETA JOIN

◦ Any comparison operator used

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

⚫

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

⚫

Inner joins

◦ Type of match and combine operation

◦ Defined formally as a combination of CARTESIAN PRODUCT and

SELECTION

(16)

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

(17)

17

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

MGRSSN = SSN

EMPLOYEE

(18)

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)

(19)

List information about each department including its location

Combine DEPARTMENT with DEPT_LOCATIONS

**DEPT_LOCS ← DEPARTMENT * DEPT_LOCATIONS**

19

(20)

⚫ Use EQUIJOIN

◦ π

Fname, Lname

(EMPLOYEE

Dno = DNnumber

(σ

Dlocation = ‘Ann Arbor’

(DEPT_LOCATIONS)))

⚫ Use NATURAL Join

◦ π

Fname, Lname

(EMPLOYEE * ρ

(Dno, Dlocation)

(σ

Dlocation =

‘Ann Arbor’

(DEPT_LOCATIONS)))

Find the full names of employees who

work in Ann Arbor

(21)

Joins – Binary Operations

21

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

(22)

Relational Algebra e-Book

(23)

OUTER JOIN Operations

⚫ Outer joins

◦ Keep all tuples in R , or all those in S , or all

those in both relations regardless of whether or not they have matching tuples in the other

relation

◦ Pads with nulls for unmatched values

◦ Example:

(24)

OUTER JOIN Operations

⚫ Previous JOINS (THETA JOIN, EQUIJOIN, NATURAL JOIN) all inner joins

◦ Match elements across tables

◦ Rows with no corresponding elements are thrown away

⚫ Sometimes we don’t want to throw away tuples that don’t have matching values

◦ For that we use an outer join

◦ Three types of outer joins:

● Left outer join

(25)

OUTER JOIN Operations

⚫ Outer joins

◦ Keep all tuples in R , or all those in S , or all those in both relations regardless of whether or not they have matching tuples in the other relation

⚫ Types

• LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

◦ Example:

(26)

Relational Algebra Operations

∪ UNION

∩ INTERSECTION

R-S SET DIFFERENCE

INNER JOIN

R S

(27)

OUTER JOIN Operators

⚫ The LEFT OUTER join – R S

◦ Keeps every tuple in the first or left relation R

◦ If no matching tuple is found in S, then the attributes of S in the join result are filled or “padded” with null values.

⚫ The RIGHT OUTER join – R S

◦ Keeps every tuple in the second or right relation S

⚫ The FULL OUTER join – R S

◦ Keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed.

R S

(28)

Left Outer Join

•R ⟕ <join condition> S

◦Still matches on join condition

◦But in cases where there are no matches on the join condition, keeps the unmatched tuple in R

◦Attributes from S padded out as NULL values

(29)

Left Outer Join

EMPNAMES ← π FNAME, LNAME, SSN (FEMALE_EMPS)

ACTUAL_DEPENDENTS ← EMPNAMES ⟕_ssn=Essn (DEPENDENT))

Fname Lname Ssn Essn Dependent_name Sex Bdate Relationship

Alicia Zelaya 999887777 NULL NULL NULL NULL NULL

Jennifer Wallace 987654321 987654321 Abner M 1942-02-28 Spouse

Joyce English 453453453 NULL NULL NULL NULL NULL

(30)

Right Outer Join

• R ⟖ <join condition> S

◦Again matches on join condition

◦But in cases where there are no matches on the join condition, keeps the unmatched tuple in S

◦Attributes from R padded out as NULL values

(31)

Right Outer Join

ACTUAL_DEPENDENTS ← EMPNAMES ⟖_Ssn=EssnDEPENDENT

NULL NULL NULL 333445555 Alice F 1986-04-05 NULL

NULL NULL NULL 333445555 Theodore M 1983-10-25 NULL

NULL NULL NULL 333445555 Joy F 1958-05-03 NULL

NULL NULL NULL 123456789 Michael M 1988-01-04 NULL

NULL NULL NULL 123456789 Alice F 1988-12-30 NULL

NULL NULL NULL 123456789 Elizabeth F 1967-05-05 NULL

(32)

Full Outer Join

• R ⟗ <join condition> S

◦Again matches on join condition

◦But in cases where there are no matches on the join condition, keeps all unmatched tuples

◦Attributes from other relation padded out as

NULL values

(33)

Full Outer Join

ACTUAL_DEPENDENTS ← EMPNAMES ⟗ _Ssn=EssnDEPENDENT

Alicia Zelaya 99988777 NULL NULL NULL NULL NULL

NULL NULL NULL 333445555 Alice F 1986-04-05 Daughter

NULL NULL NULL 333445555 Theodore M 1983-10-25 Son

NULL NULL NULL 333445555 Joy F 1958-05-03 Spouse

Joyce English 453453453 NULL NULL NULL NULL NULL

NULL NULL NULL 123456789 Michael M 1988-01-04 Son

NULL NULL NULL 123456789 Alice F 1988-12-30 Daughter

NULL NULL NULL 123456789 Elizabeth F 1967-05-05 Spouse

(34)

Aggregate Functions

⚫ Group tuples by the value of some of their attributes

◦ Allows functions of attributes to be included in the projection list

◦ Apply aggregate function independently to each group

● Ex., SUM, AVERAGE, MAXIMUM, and

MINIMUM

(35)

Relational algebra operators

35

(36)

Aggregate Function Operation without grouping

⚫ ℱ

MAX Salary

(EMPLOYEE)

◦ retrieves the maximum salary value from the EMPLOYEE relation

⚫ ℱ

MIN Salary

(EMPLOYEE)

◦ retrieves the minimum Salary value from the EMPLOYEE relation

⚫ ℱ

SUM Salary

(EMPLOYEE)

◦ retrieves the sum of the Salary from the EMPLOYEE relation

⚫ ℱ

COUNT SSN, AVERAGE Salary

(EMPLOYEE)

◦ computes the count (number) of employees and their average salary

◦ Note: count just counts the number of rows, without removing

(37)

37

Aggregate Functions with Groups

List the departments (DNO) and the average salary of each

DNO ℱ AVERAGE SAL ( EMP)

EMP( EMPNO, DNO, SAL,...)

100 D3 66,000 200 D3 55,000 300 D3 66,000 400 D1 66,000 500 D1 55,000 600 D1 60,000 700 D2 66,000 800 D2 60,000 900 D2 66,000

DNO AVG SAL

D3 62,333

D1 60,333

D2 64,000

(38)

Aggregate Functions

(39)

Query Trees

39

• A query tree is a tree data structure that corresponds to a relational algebra expression.

• Input relations are leaf nodes

• Relational algebra operations are internal nodes.

• Query trees are evaluated from leaf nodes up to the root node.

• The root node represents final result of entire relational algebra expression

• Used most often in query optimization

(40)

Query Trees

input relations of query as leaf nodes of the tree relational algebra operations as

internal nodes

(41)

Example Queries

41

(42)

Example Queries

Query 5:

List the names of all employees with two or more dependents

T1←ρ

(Essn,NoDeps)

(

^Essn

ℑ

Count(Dep_name)

(DEPENDENT) T2←σ

NoDeps>=2

(T1)

Result←π

Lname,Fname

(EMPLOYEE ⋈

^Ssn=Essn

T2)

(43)

Operations of Relational Algebra

(44)

RELATIONAL ALGEBRA

RELATIONAL ALGEBRA

Chapter 6

“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

Summary

⚫ Complete Set: The set of operations including

◦ SELECT σ,

◦ PROJECT π ,

◦ RENAME ρ,

◦ UNION ∪,

◦ INTERSECTION ∩

◦ DIFFERENCE − ,

◦ CARTESIAN PRODUCT X

⚫ Any other relational algebra expression can be expressed by a combination of these five

operations.

◦ R ∩ S = (R ∪ S ) – ((R − S) ∪ (S − R))

◦ R

S = σ

(R X S)

SELECT

⚫ Nested application of SELECT operations o σ

(σ

(R)) ≡ σ

(R)

⚫ Is SELECT Commutative?

o Yes! ☺

o σ

(σ

(R)) = σ

(σ

(R))

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

o σ

(σ

(σ

(R))) = σ

(σ

(σ

( R)))

The 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:

π LNAME, FNAME,SALARY (EMPLOYEE)

RENAME Operation - ρ (rho)

⚫ In-line expression:

⚫ Sequence of operations:

⚫ Rename attributes in intermediate results

◦ RENAME operation

UNION, INTERSECTION, and MINUS

⚫ Merge the elements of two sets in various ways

⚫ Binary operations

⚫ Relations must have the same type of tuples

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

R S

R S

UNION, INTERSECT, and SET DIFFERENCE

STUDENT ∪ INSTRUCTOR

STUDENT ∩ INSTRUCTOR

STUDENT −

INSTRUCTOR INSTRUCTOR - STUDENT

UNION, INTERSECT, and SET 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)

(R)) ≡ ^σ

The PROJECT Operation - π _(pi)

◦ R ∪ _S

⚫ R _θ S