[PDF] - But the relation sc hemas m ust b e the same. 2. : Pic PDF Document

SLIDE 1 \Core" Relational Algebra A small set

f

set-based

p

erators that allo w us to manipulate relations in limited but useful w a ys. The

p

erators are: 1. Union, in tersection, and dierence: the usual set

p

erators.

✦

But the relation sc hemas m ust b e the same. 2. Sele ction : Pic king certain ro ws from a relation. 3. Pr

je

ction : Pic king certain columns. 4. Pr

ducts

and joins : Comp

sing

relations in useful w a ys. 5. R enaming

f

relations and their attributes. 1

SLIDE 2 Selection R 1 =

C

(R 2 ) where C is a condition in v

lving

the attributes

f

relation R 2 . Example Relation Sells: bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co

rs

3.00 JoeMenu =

bar

=J

e

s (Sells) bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 2

SLIDE 3 Pro jection R 1 =

L

(R 2 ) where L is a list

f

attributes from the sc hema

f

R 2 . Example

beer

;pr ice (Sells) b eer price Bud 2.50 Miller 2.75 Co

rs

3.00

Notice

elimination

f

duplicate tuples. 3

SLIDE 4 Pro duct R = R 1

R

2 pairs eac h tuple t 1

f

R 1 with eac h tuple t 2

f

R 2 and puts in R a tuple t 1 t 2 . Theta-Join R = R 1 . / C R 2 is equiv alen t to R =

C

(R 1

R

2 ). 4

SLIDE 5 Example Sells = bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co

rs

3.00 Bars = name addr Jo e's Maple St. Sue's Riv er Rd. BarInfo = Sells . / S ells:bar =B ar s:name Bars bar b eer price name addr Jo e's Bud 2.50 Jo e's Maple St. Jo e's Miller 2.75 Jo e's Maple St. Sue's Bud 2.50 Sue's Riv er Rd. Sue's Co

rs

3.00 Sue's Riv er Rd. 5

SLIDE 6 Natural Join R = R 1 . / R 2 calls for the theta-join

f

R 1 and R 2 with the condition that all attributes

f

the same name b e equated. Then,

ne

column for eac h pair

f

equated attributes is pro jected

ut.

Example Supp

se

the attribute name in relation Bars w as c hanged to bar, to matc h the bar name in Sells. BarInfo = Sells . / Bars bar b eer price addr Jo e's Bud 2.50 Maple St. Jo e's Miller 2.75 Maple St. Sue's Bud 2.50 Riv er Rd. Sue's Co

rs

3.00 Riv er Rd. 6

SLIDE 7 Renaming

S

(A 1 ;::: ;A n ) (R ) pro duces a relation iden tical to R but named S and with attributes, in

rder,

named A 1 ; : : : ; A n . Example Bars = name addr Jo e's Maple St. Sue's Riv er Rd.

R(bar

;addr ) (Bars ) = bar addr Jo e's Maple St. Sue's Riv er Rd.

The

name

f

the ab

v

e relation is R . 7

SLIDE 8 Com bining Op erations Algebra = 1. Basis argumen ts + 2. W a ys

f

constructing expressions. F

r

relational algebra: 1. Argumen ts = v ariables standing for relations + nite, constan t relations. 2. Expressions constructed b y applying

ne
f

the

p

erators + paren theses.

Query

= expression

f

relational algebra. 8

SLIDE 9 Op erator Precedence The normal w a y to group

p

erators is: 1. Unary

p

erators

,
,

and

ha

v e highest precedence. 2. Next highest are the \m ultiplicati v e"

p

erators, . /, . / C , and . 3. Lo w est are the \additiv e"

p

erators, [, \, and .

But

there is no univ ersal agreemen t, so w e alw a ys put paren theses ar

und

the argumen t

f

a unary

p

erator, and it is a go

d

idea to group all binary

p

erators with paren theses enclosing their argumen ts. Example Group R [

S

. / T as R [ ( (S ) . / T ). 9

SLIDE 10 Eac h Expression Needs a Sc hema

If

[ , \ ,

applied,

sc hemas are the same, so use this sc hema.

Pro

jection: use the attributes listed in the pro jection.

Selection:

no c hange in sc hema.

Pro

duct R

S

: use attributes

f

R and S .

✦

But if they share an attribute A, prex it with the relation name, as R :A, S:A.

Theta-join:

same as pro duct.

Natural

join: use attributes from eac h relation; common attributes are merged an yw a y .

Renaming:

whatev er it sa ys. 10

SLIDE 11 Example Find the bars that are either

n

Maple Street

r

sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr)

name
addr

=M apleS t: Bars

R(name)
bar
pr

ice<$3 AN D beer =B ud Sells [ 11

SLIDE 12 Example Find the bars that sell t w

dieren

t b eers at the same price. Sells(bar, beer, price) Sells Sells

bar

. /

S

(bar ;beer 1;pr ice)

beer

6=beer 1 12

SLIDE 13 Linear Notation for Expressions

In

v en t new names for in termediate relations, and assign them v alues that are algebraic expressions.

Renaming
f

attributes implicit in sc hema

f

new relation. Example Find the bars that are either

n

Maple Street

r

sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr) R1(name) :=

name

( addr =M aple S t: (Bars)) R2(name) :=

bar

( beer =B ud AN D pr ice<$3 (Sells)) Answer(name) := R1 [ R2 13

SLIDE 14 Bag Seman tics A relation (in SQL, at least) is really a b ag

r

multiset.

It

ma y con tain the same tuple more than

nce,

although there is no sp ecied

rder

(unlik e a list).

Example:

f1; 2; 1; 3g is a bag and not a set.

Select,

pro ject, and join w

rk

for bags as w ell as sets.

✦

Just w

rk
n

a tuple-b y-tuple basis, and don't eliminate duplicates. 14

SLIDE 15 Bag Union Sum the times an elemen t app ears in the t w

bags.
Example:

f1; 2; 1g [ f1; 2; 3g = f1; 1; 1; 2; 2; 3g. Bag In tersection T ak e the minim um

f

the n um b er

f
ccurrences

in eac h bag.

Example:

f1; 2; 1g \ f1; 2; 3; 3g = f1; 2g. Bag Dierence Prop er-subtract the n um b er

f
ccurrences

in the t w

bags.
Example:

f1; 2; 1g

f1;

2; 3; 3g = f1g. 15

SLIDE 16 La ws for Bags Dier F rom La ws for Sets

Some

familiar la ws con tin ue to hold for bags.

✦

Examples: union and in tersection are still comm utativ e and asso ciativ e.

But
ther

la ws that hold for sets do not hold for bags. Example R \ (S [ T )

(R

\ S ) [ (R \ T ) holds for sets.

Let

R , S , and T eac h b e the bag f1g.

Left

side: S [ T = f1; 1g; R \ (S [ T ) = f1g.

Righ

t side: R \ S = R \ T = f1g; (R \ S ) [ (R \ T ) = f1g [ f1g = f1; 1g 6= f1g. 16

SLIDE 17 Extended (\Nonclassical ") Relational Algebra Adds features needed for SQL, bags. 1. Duplicate-eli minati

n
p

erator

.

2. Extended pro jection. 3. Sorting

p

erator

.

4. Grouping-and-aggregation

p

erator

.

5. Outerjoin

p

erator

.

/ . 17

SLIDE 18 Duplicate Elimination

(R

) = relation with

ne

cop y

f

eac h tuple that app ears

ne
r

more times in R . Example R = A B 1 2 3 4 1 2

(R

) = A B 1 2 3 4 18

SLIDE 19 Sorting

L

(R ) = list

f

tuples

f

R ,

rdered

according to attributes

n

list L.

Note

that result t yp e is

utside

the normal t yp es (set

r

bag) for relational algebra.

✦

Consequence:

cannot

b e follo w ed b y

ther

relational

p

erators. example R = A B 1 3 3 4 5 2

B

(R ) = [(5; 2); (1; 3); (3; 4)]. 19

SLIDE 20 Extended Pro jection Allo w the columns in the pro jection to b e functions

f
ne
r

more columns in the argumen t relation. Example R = A B 1 2 3 4

A+B

;A;A (R ) = A + B A1 A2 3 1 1 7 3 3 20

SLIDE 21 Aggregation Op erators

These

are not relational

p

erators; rather they summarize a column in some w a y .

Fiv

e standard

p

erators: Sum, Av erage, Coun t, Min, and Max. Grouping Op erator

L

(R ), where L is a list

f

elemen ts that are either a) Individual (gr

uping)

attributes

r

b) Of the form

(A),

where

is

an aggregation

p

erator and A the attribute to whic h it is applied, is computed b y: 1. Group R according to all the grouping attributes

n

list L. 2. Within eac h group, compute

(A),

for eac h elemen t

(A)
n

list L. 3. Result is the relation whose columns consist

f
ne

tuple for eac h group. The comp

nen

ts

f

that tuple are the v alues asso ciated with eac h elemen t

f

L for that group. 21

SLIDE 22 Example Let R = bar b eer price Jo e's Bud 2.00 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co

rs

3.00 Mel's Miller 3.25 Compute

beer

;AV G(pr ice) (R ). 1. Group b y the grouping attribute(s), beer in this case: bar b eer price Jo e's Bud 2.00 Sue's Bud 2.50 Jo e's Miller 2.75 Mel's Miller 3.25 Sue's Co

rs

3.00 22

SLIDE 23 2. Compute a v erage

f

price within groups: b eer A V G(price) Bud 2.25 Miller 3.00 Co

rs

3.00 23

SLIDE 24 Outerjoin The normal join can \lose" information, b ecause a tuple that do esn't join with an y from the

ther

relation (dangles) has no v estage in the join result.

The

nul l value ? can b e used to \pad" dangling tuples so they app ear in the join.

Giv

es us the

uterjoin
p

erator

.

/ .

V

ariations: theta-outerjoin, left- and righ t-

uterjoin

(pad

nly

dangling tuples from the left (resp., righ t). 24

SLIDE 25 Example R = A B 1 2 3 4 S = B C 4 5 6 7 R

.

/ S = A B C 3 4 5 1 2 ? ? 6 7 25