06 algebra

7/18/2019 06 Algebra

http://slidepdf.com/reader/full/06-algebra 1/26

By relieving the brain of all unnecessary

work, a good notation sets it free to

concentrate on more advanced problems,

and, in effect, increases the mental power of

the race.

-- Alfred North Whitehead (1861 - 1947)

Relational Algebra

R & G, Chapter 4

π

7/18/2019 06 Algebra


Relational Query Languages

• Query languages: Allow manipulation andretrieval of data from a database.

• Relational model supports simple, powerful QLs:

– Strong formal foundation based on logic.– Allows for much optimization.

• Quer Languages ! programming languages!

– QLs not epected to be "#uring complete$.

– QLs not intended to be used for complecalculations.

– QLs support easy% efficient access to largedata sets.

7/18/2019 06 Algebra


&ormal Relational Query

Languages"wo mathemati#al Quer Languages form thebasis for $real% languages e.g. 'QL(,and for implementation:

Relational Algebra: )ore operational, veruseful for representing e*e#ution plans.

Relational Calculus: Lets users des#ribewhat the want, rather than how to#ompute it. +onpro#edural,declarative.(

Understanding Algebra & Calculus is

understanding SQL, query processi

7/18/2019 06 Algebra


'reliminaries

• A -uer is applied to relation instances, and theresult of a -uer is also a relation instan#e.

– Schemas of input relations for a (uery arefied )but (uery will run o*er any legalinstance+

– #he schema for the result of a gi*en (uery isalso fied. ,t is determined by thedefinitions of the (uery language constructs.

• ositional vs. namedfield notation:

– 'ositional notation easier for formal

definitions% named-field notation morereadable.

– oth used in SQL• #hough positional notation is not encouraged

7/18/2019 06 Algebra


Relational Algebra/ 0 asic1perations

• Selection ) σ + Selects a subset of rows from relation )horizontal+.

• Proection ) π + Retains only wanted columns from relation )*ertical+.

• Crossproduct ) × + Allows us to combine tworelations.

• Setdi""erence ) 2 + #uples in r3% but not inr4.

• Union ) ∪ + #uples in r3 or in r4.

Since each operation returns a relation%operations can be composed! )Algebra is"closed$.+

7/18/2019 06 Algebra


sid sname rating age

22 dustin 7 45.

!" lubber # 55.55# rusty " !5.


2# yuppy $ !5.!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

sid bid day

22 "" "%"%$&

5# "! ""%"2%$&

R1

S1

S2

bid bname color

"" 'nterlake blue

"2 'nterlake red

"! (lipper green

"4 )arine red

Boats

5ample ,nstances

7/18/2019 06 Algebra


'ro6ection )π+

π age S * +2• 5amples/ 7

• Retains only attributes that are in the" proection list#.

• Schema of result/– eactly the fields in the pro6ection list% with thesame names that they had in the input relation.

• 'ro6ection operator has to eliminateduplicates )8ow do they arise9 :hy remo*e

them9+– ;ote/ real systems typically don<t do duplicateelimination unless the user eplicitly as=s for it.):hy not9+

π

sname rating

S

,

* +2

7/18/2019 06 Algebra


'ro6ection )π+

age

35.055.5


2# yuppy $ !5.

!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

'/

sname rating

yuppy >

lubber ?guppy 0rusty 3@

+2*,

S rating snameπ

π age

S * +2

7/18/2019 06 Algebra


Selection )σ+

σ rating S # 2* +

sname ratingyuppy >

rusty 3@

π σ sname rating rating S , * * ++# 2

• Selects rows that satisfy selection condition.• Result is a relation.

Schema of result is same as that of the input relation.

• o we need to do duplicate elimination9


2# yuppy $ !5.!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

7/18/2019 06 Algebra


Bnion and Set-ifference

• oth of these operations ta=e two input relations%which must be union-compatible:

– Same number of fields.

– CDorresponding< fields ha*e thesame type.

• &or which% if any% is duplicate elimination

re(uired9

7/18/2019 06 Algebra


Bnion

sid sname rating age22 dustin 7 45.031 lubber 8 55.5

58 rusty 10 35.0

44 guppy 5 35.028 yuppy 9 35.0

sid sname rating age22 dustin 7 45.

!" lubber # 55.5

5# rusty " !5.


2# yuppy $ !5.

!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

'0

'/

S S " 2∪

7/18/2019 06 Algebra


Setifference


!" lubber # 55.5

5# rusty " !5.


2# yuppy $ !5.

!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

'0

'/

sid sname rating age22 dustin 7 45.0

S2 – S1


2# yuppy $ !5.44 guppy 5 !5.

S S " 2−

7/18/2019 06 Algebra


Dross-'roduct

• S3 × R3/ 5ach row of S3 paired with eachrow of R3.

• Q/ 8ow many rows in the result9

• $esult schema has one field per field of

S3 and R3% with field names Cinherited<if possible.– %ay hae a naming con"lict/ oth S3 and R3ha*e a field with the same name.

– ,n this case% can use the renaming operator/ ρ * * , +, +C sid sid S R" " 5 2 " "- -

7/18/2019 06 Algebra


Dross 'roduct 5ample

(sid) sname rating age (sid) bid day

22 dustin 7 45.0 22 101 10/10/96

22 dustin 7 45.0 58 103 11/12/9631 lubber 8 55.5 22 101 10/10/96

31 lubber 8 55.5 58 103 11/12/96

58 rusty 10 35.0 22 101 10/10/96

58 rusty 10 35.0 58 103 11/12/96


22 dustin 7 45.

!" lubber # 55.5

5# rusty " !5.

sid bid day

22 "" "%"%$&

5# "! ""%"2%$&

R0 '0

R0 1 '0 2

7/18/2019 06 Algebra


Dompound 1perator/,ntersection

• ,n addition to the 0 basic operators% there arese*eral additional "Dompound 1perators$

– #hese add no computational powerto the language% but are useful

shorthands.– Dan be epressed solely with thebasic ops.

• ,ntersection ta=es two input relations% which mustbe union-compatible.

• Q/ 8ow to epress it using basic operators9

R ∩ S R − )R − S+

7/18/2019 06 Algebra


,ntersection


!" lubber # 55.5

5# rusty " !5.


2# yuppy $ !5.

!" lubber # 55.5

44 guppy 5 !5.

5# rusty " !5.

'0

'/


31 lubber 8 55.5

58 rusty 10 35.0

S 1/S 2

7/18/2019 06 Algebra


Dompound 1perator/ Eoin

• Eoins are compound operators in*ol*ing cross

product% selection% and )sometimes+ pro6ection.• Fost common type of 6oin is a "natural oin$)often 6ust called "6oin$+. R Sconceptually is/– Dompute R × S

– Select rows where attributes that appear in bothrelations ha*e e(ual *alues

– 'ro6ect all uni(ue atttributes and one copy of eachof the common ones.

• ;ote/ Bsually done much more efficiently than

this.

7/18/2019 06 Algebra


;atural Eoin 5amplesid sname rating age

22 dustin 7 45.

!" lubber # 55.5

5# rusty " !5.

sid bid day

22 "" "%"%$&

5# "! ""%"2%$&

R0 '0

'0 R0 2

sid sname rating age bid day

22 dustin 7 45.0 101 10/10/96

58 rusty 10 35.0 103 11/12/96

7/18/2019 06 Algebra


1ther #ypes of Eoins

• Condition oin !or "theta-#oin$%:

• $esult schema same as that of cross-product.

• Fay ha*e fewer tuples than cross-product.• &'ui-oin: 'pe#ial #ase: #ondition c #ontains onl #on3un#tionof equalities.

R c S c R S >< = ×σ ( )

)sid+ sname ratingage )sid+ bid day

44 dustin G H0.@ 0? 3@I 33J34J>KI3 lubber ? 00.0 0? 3@I 33J34J>K

""."."

RS sid R sid S <

<>

7/18/2019 06 Algebra


5amples


22 dustin 7 45.

!" lubber # 55.55# rusty " !5.

bid bname color

"" 'nterlake Blue"2 'nterlake 0ed

"! (lipper 1reen"4 )arine 0ed

sid bid day

22 "" "%"%$&

5# "! ""%"2%$&

Reserves

Sailors

Boats

7/18/2019 06 Algebra


&ind names of sailors who<*e reser*edboat 3@I

• 'olution 0: π σ sname bid serves Sailors** 0e + +

"! ><

•

'olution /:π σ

sname bid serves Sailors* *0e ++

"! ><

7/18/2019 06 Algebra


&ind names of sailors who<*e reser*eda red boat

• nformation about boat #olor onlavailable in 5oats6 so need an e*tra3oin:

π σ sname color red Boats serves Sailors** 3 3 + 0e + >< ><

A more efficient solution/

π π π σ sname sid bid color red Boats s Sailors* **

3 3+ 0e + +

>< ><

A query optimi'er can "ind this gien the

7/18/2019 06 Algebra


&ind sailors who<*e reser*ed a red ora green boat

• Can identif all red or green boats,then find sailors who7ve reserved oneof these boats:

ρ σ * , *

3 3 3 3

++Tempboats

color red color green

Boats

∨

π sname Tempboats serves Sailors* 0e +>< ><

7/18/2019 06 Algebra


&ind sailors who<*e reser*ed a red anda green boat

• Cutandpaste previous slide8

ρ (Tempboats,(σcolor ='red '∧color ='green'

Boats))

π sname Tempboats serves Sailors* 0e +>< ><

7/18/2019 06 Algebra


&ind sailors who<*e reser*ed a red anda green boat

• revious approa#h won7t wor9! )ustidentif sailors who7ve reserved red

boats, sailors who7ve reserved greenboats, then find the interse#tion notethat sid is a 9e for 'ailors(: ρ π σ * , **

3 3+ 0e ++Tempred

sid color red Boats serves

><

π sname Tempred Tempgreen Sailors** + +/ ><

ρ π σ * , ** 3 3 + 0e ++Tempgreen sid color green Boats serves ><

7/18/2019 06 Algebra


Summary

• Relational Algebra: a small set ofoperators mapping relations torelations

– 1perational% in the sense that youspecify the eplicit order ofoperations

– A closed set of operators! Dan mi

and match.• 5asi# ops in#lude: , , ×, ∪,

• mportant #ompound ops: ∩,

06 algebra

Documents