CSE-4/562 Spring 2019

Declarative	Imperative
Say what you want	Say how you want to get it
"Get me the TPS Reports"	`Look at every T report For each week: Sum up the sprocket count Find that week's S report ...`
SQL, RA, R, …	C, Scala, Java, Python, …

Relational Algebra

Operation	Sym	Meaning
Selection	$\sigma$	Select a subset of the input rows
Projection	$\pi$	Delete unwanted columns
Cross-product	$\times$	Combine two relations
Set-difference	$-$	Tuples in Rel 1, but not Rel 2
Union	$\cup$	Tuples either in Rel 1 or in Rel 2

Also: Intersection, Join, Division, Renaming

(Not essential, but can be useful)

Input		Query Language		Output
Sets of Tuples	$\rightarrow$	Set Relational Algebra	$\rightarrow$	Set of Tuples
Bags of Tuples	$\rightarrow$	Bag Relational Algebra	$\rightarrow$	Bag of Tuples
Lists of Tuples	$\rightarrow$	Extended Relational Algebra	$\rightarrow$	List of Tuples

First we focus on sets and bags.

Selection ( $\sigma_{c}$ )

Delete rows that fail the condition $c$ .

$\sigma_{(\textbf{BORONAME} = \texttt{'Brooklyn'})} \textbf{Trees}$

TREE_ID	SPC_COMMON	BORONAME	...
204026	'honeylocust'	'Brooklyn'	...
204337	'honeylocust'	'Brooklyn'	...
189565	'American linden'	'Brooklyn'	...
192755	'London planetree'	'Brooklyn'	...
189465	'London planetree'	'Brooklyn'	...
... and 177287 more


          SELECT * FROM Trees [[ WHERE BORONAME = 'Brooklyn' ]]

Projection ( $\pi_{A}$ )

Delete attributes not in the projection list $A$ .

$\pi_{\textbf{BORONAME}}(\textbf{Trees})$

BORONAME
Queens
Brooklyn
Manhattan
Bronx
Staten Island

Only 5 results... not 683788?

Set and Bag Projection are different


              SELECT [[ DISTINCT BORONAME ]] FROM Trees;

Reminder: Queries are Relations

What are these queries schemas?

$\pi_{\textit{TREEID},\ \textit{SPC_COMMON},\ \textit{BORONAME}} \textbf{Trees}$

$\sigma_{(\textit{BORONAME} = \texttt{'Brooklyn'})} \textbf{Trees}$

$\sigma_{(\textit{BORONAME} = \texttt{'Brooklyn'})}(\pi_{\textit{TREEID},\ \textit{SPC_COMMON},\ \textit{BORONAME}} \textbf{Trees})$

Union ( $\cup$ )

Takes two relations that are union-compatible...

(Both relations have the same number of fields with the same types)

... and returns all tuples appearing in either relation

$(\sigma_{(BORONAME=\texttt{'Brooklyn'})} \textbf{Trees}) \cup (\sigma_{(BORONAME=\texttt{'Manhattan'})} \textbf{Trees})$

We use $\uplus$ if we explicitly mean bag union

Intersection ( $\cap$ )

Return all tuples appearing in both
of two union-compatible relations

$(\sigma_{(BORONAME=\texttt{'Brooklyn'})} (\pi_{SPC\_COMMON} \textbf{Trees})) \\ ~~~~~~~~~\cap (\sigma_{(BORONAME=\texttt{'Manhattan'})} (\pi_{SPC\_COMMON} \textbf{Trees}))$

What is this query asking?

Set Difference

Return all tuples appearing in the first, but not the second
of two union-compatible relations

$(\sigma_{(BORONAME=\texttt{'Brooklyn'})} (\pi_{SPC\_COMMON} \textbf{Trees})) \\ ~~~~~~~~~- (\sigma_{(BORONAME=\texttt{'Manhattan'})} (\pi_{SPC\_COMMON} \textbf{Trees}))$

What is this query asking?

Union, Intersection, Set Difference

What is the schema of the result of any of these operators?

Cross (Cartesian) Product ( $\times$ )

Create all pairs of tuples.

$\pi_{SPC\_COMMON, AVG\_HEIGHT} (\textbf{TreeInfo}) \times \pi_{SPC\_COMMON, BORONAME} (\textbf{Trees})$

TreeInfo

SPC_COMMON	AVG_HEIGHT
cedar elm	60
lacebark elm	45
... and more

SPC_COMMON	BORONAME	SPC_COMMON	AVG_HEIGHT
honeylocust	Brooklyn	cedar elm	60
honeylocust	Brooklyn	cedar elm	60
American linden	Brooklyn	cedar elm	60
London planetree	Manhattan	cedar elm	60
London planetree	Manhattan	cedar elm	60
...
honeylocust	Brooklyn	lacebark elm	45
honeylocust	Brooklyn	lacebark elm	45
American linden	Brooklyn	lacebark elm	45
London planetree	Manhattan	lacebark elm	45
London planetree	Manhattan	lacebark elm	45
... and more

Cross (Cartesian) Product ( $\times$ )

$\pi_{SPC\_COMMON,\ BORONAME} (\textbf{Trees}) \times \pi_{SPC\_COMMON,\ AVG\_HEIGHT} (\textbf{TreeInfo})$

What is the schema of the resulting relation?

The relation has a naming conflict
(two attributes with the same name)

Renaming ( $\rho$ )

$\rho_{TNAME,\ BORO,\ INAME,\ HEIGHT}\left( \pi_{SPC\_COMMON,\ BORONAME} (\textbf{Trees}) \times \pi_{SPC\_COMMON,\ AVG\_HEIGHT} (\textbf{TreeInfo})\right)$

What is the schema of the resulting relation?

When writing cross-products on the board,
I will use implicit renaming

Join ( $\bowtie_c$ )

Pair tuples according to a condition c.

$\pi_{SPC\_COMMON,\ BORONAME} (\textbf{Trees}) \bowtie_{T.SPC\_COMMON = TI.SPC\_COMMON} \pi_{SPC\_COMMON,\ AVG\_HEIGHT} (\textbf{TreeInfo})$

Identical to...

$\sigma_{T.SPC\_COMMON = TI.SPC\_COMMON}\left(\pi_{SPC\_COMMON,\ BORONAME} (\textbf{Trees}) \times \pi_{SPC\_COMMON,\ AVG\_HEIGHT} (\textbf{TreeInfo})\right)$

$R \bowtie_c S \equiv \sigma_c(R \times S)$

Join Shorthands

Equi-joins are joins with only equality tests in the condition.

Join on attribute(s): $R \bowtie_{A} S \equiv R \bowtie_{R.A = S.A} S$; Same values on the listed attributes
Natural Join: $R \bowtie S \equiv R \bowtie_{attrs(R) \cap attrs(S)} S$; Same values on all shared attributes

Which operators can create duplicates?

(Which operators behave differently in Set- and Bag-RA?)

Operator	Symbol	Creates Duplicates?
Selection	$\sigma$	No
Projection	$\pi$	Yes
Cross-product	$\times$	No
Set-difference	$-$	No
Union	$\cup$	Yes
Join	$\bowtie$	No

Relational Algebra

Textbook: Ch. 2.4, 5.1

The running theme...

Relational Data

Relational Data

Set

Bag

List

Declarative Languages

SQL

Python

Declarative Queries

Relational Algebra

Relational Calculus

Declarative Queries

Preliminaries

Relational Algebra

Selection ( $\sigma_{c}$ )

Projection ( $\pi_{A}$ )

Reminder: Queries are Relations

Union ( $\cup$ )

Intersection ( $\cap$ )

Set Difference

Union, Intersection, Set Difference

Cross (Cartesian) Product ( $\times$ )

Cross (Cartesian) Product ( $\times$ )

Renaming ( $\rho$ )

Join ( $\bowtie_c$ )

Join Shorthands

Which operators can create duplicates?

Group Work

Division ( $/$ )

Division ( $/$ )

Group Work

Relational Algebra