RelationalAlgebra Chapter4,PartA DatabaseManagementSystems3ed,R. - - PDF document

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 1

RelationalAlgebra

Chapter4,PartA

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 2

RelationalQueryLanguages

• Querylanguages: Allowmanipulationandretrieval
• fdatafromadatabase.
• Relationalmodelsupportssimple,powerful QLs:

Strongformalfoundationbasedonlogic.

Allowsformuchoptimization.

• QueryLanguages!= programminglanguages!

QLs notexpectedtobe“Turingcomplete”.

QLs notintendedtobeusedforcomplexcalculations.

QLs supporteasy,efficientaccesstolargedatasets.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 3

FormalRelationalQueryLanguages

• TwomathematicalQueryLanguagesform

thebasisfor“real”languages(e.g.SQL),and forimplementation:

RelationalAlgebra:Moreoperational,veryuseful forrepresentingexecutionplans.

RelationalCalculus:Letsusersdescribewhatthey want,ratherthanhowtocomputeit.(Non-

• perational,declarative.)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 4

Preliminaries

• Aqueryisappliedtorelationinstances,andthe

resultofaqueryisalsoarelationinstance.

Schemas ofinputrelationsforaqueryarefixed(but querywillrunregardlessofinstance!)

Theschemafortheresult ofagivenqueryisalso fixed! Determinedbydefinitionofquerylanguage constructs.

• Positionalvs.named-fieldnotation:

Positionalnotationeasierforformaldefinitions, named-fieldnotationmorereadable.

BothusedinSQL

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 5

ExampleInstances

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96

R1 S1 S2

• “Sailors”and“Reserves”

relationsforourexamples.

• We’llusepositionalor

namedfieldnotation, assumethatnamesoffields inqueryresultsare `inherited’fromnamesof fieldsinqueryinput relations.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 6

RelationalAlgebra

• Basicoperations:

Selection ()Selectsasubsetofrowsfromrelation.

Projection ()Deletesunwantedcolumnsfromrelation.

Cross-product ()Allowsustocombinetworelations.

Set-difference () Tuples in reln.1,butnotin reln.2.

Union () Tuples in reln.1andin reln.2.

• Additionaloperations:

Intersection,join,division,renaming:Notessential,but (very!)useful.

• Sinceeachoperationreturnsarelation,operations

canbecomposed!(Algebrais“closed”.)

σ

π

−

×

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 7

Projection

sname rating yuppy 9 lubber 8 guppy 5 rusty 10

πsname rating S

, ( ) 2

age 35.0 55.5

πage S

( ) 2

• Deletesattributesthatarenotin

projectionlist.

• Schema ofresultcontainsexactly

thefieldsintheprojectionlist, withthesamenamesthatthey hadinthe(only)inputrelation.

• Projectionoperatorhasto

eliminateduplicates!(Why??)

Note:realsystemstypically don’tdoduplicateelimination unlesstheuserexplicitlyasks forit.(Whynot?)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 8

Selection σrating

S >8 2 ( )

sid sname rating age 28 yuppy 9 35.0 58 rusty 10 35.0 sname rating yuppy 9 rusty 10

π σ

sname rating rating S , ( ( )) >8 2

• Selectsrowsthatsatisfy

selectioncondition.

• Noduplicatesinresult!

(Why?)

• Schema ofresult

identicaltoschemaof (only)inputrelation.

• Resultrelationcanbe

theinputforanother relationalalgebra

• peration!(Operator

composition.)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 9

Union,Intersection,Set-Difference

• Alloftheseoperationstake

twoinputrelations,which mustbeunion-compatible:

Samenumberoffields.

`Corresponding’fields havethesametype.

• Whatistheschema ofresult?

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 44 guppy 5 35.0 28 yuppy 9 35.0 sid sname rating age 31 lubber 8 55.5 58 rusty 10 35.0

S S 1 2 ∪ S S 1 2 ∩

sid sname rating age 22 dustin 7 45.0

S S 1 2 −

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 10

Cross-Product

• EachrowofS1ispairedwitheachrowofR1.
• ResultschemahasonefieldperfieldofS1andR1,

withfieldnames`inherited’ifpossible.

ρ ( ( , ), ) C sid sid S R 1 1 5 2 1 1 → → ×

(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/96 31 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

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 11

Joins

• ConditionJoin:
• Resultschemasameasthatofcross-product.
• Fewer tuples thancross-product,mightbe

abletocomputemoreefficiently

• Sometimescalledatheta-join.

R c S c R S

= × σ ( )

(sid) sname rating age (sid) bid day 22 dustin 7 45.0 58 103 11/12/96 31 lubber 8 55.5 58 103 11/12/96

S R

S sid R sid

1 1

1 1

. . <

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 12

Joins

Equi-Join:Aspecialcaseofconditionjoinwhere theconditionc containsonlyequalities.

Resultschemasimilartocross-product,butonly

• necopyoffieldsforwhichequalityisspecified.

NaturalJoin: Equijoin onall commonfields. sid sname rating age bid day 22 dustin 7 45.0 101 10/10/96 58 rusty 10 35.0 103 11/12/96

S R

sid

1 1

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 13

Division

Notsupportedasaprimitiveoperator,butusefulfor expressingquerieslike: Findsailorswhohavereservedall boats.

LetA have2fields,x andy;B haveonlyfieldy:

i.e.,A/Bcontainsallx tuples (sailors)suchthatforevery y tuple (boat)inB,thereisan xy tuple inA.

Or:Ifthesetofy values(boats)associatedwithanxvalue (sailor)inA containsallyvaluesinB,thexvalueisinA/B.

Ingeneral,x andy canbeanylistsoffields;y isthe listoffieldsinB,and x y isthelistoffieldsofA.

{ }

x x y A y B | , ∃ ∈ ∀ ∈

∪

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 14

ExamplesofDivisionA/B

sno pno s1 p1 s1 p2 s1 p3 s1 p4 s2 p1 s2 p2 s3 p2 s4 p2 s4 p4 pno p2 pno p2 p4 pno p1 p2 p4 sno s1 s2 s3 s4 sno s1 s4 sno s1

A B1 B2 B3 A/B1 A/B2 A/B3

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 15

ExpressingA/BUsingBasicOperators

Divisionisnotessentialop;justausefulshorthand.

(Alsotrueofjoins,butjoinsaresocommonthatsystems implementjoinsspecially.)

Idea:ForA/B,computeallx valuesthatarenot `disqualified’bysomey valueinB.

x valueisdisqualified ifbyattachingyvaluefromB,we

• btainan xy tuple thatisnotinA.

Disqualifiedx values:

A/B:

π π x x A B A (( ( ) ) ) × − π x A ( ) − alldisqualified tuples

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 16

Findnamesofsailorswho’vereservedboat#103

Solution1: π σ

sname bid

serves Sailors (( Re ) )

=103

• ✂

Solution2:

ρ σ ( , Re ) Temp serves

bid

1

103 =

ρ ( , ) Temp Temp Sailors 2 1

π sname Temp ( ) 2

Solution3: π σ sname bid serves Sailors ( (Re )) =103

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 17

Findnamesofsailorswho’vereservedaredboat

Informationaboutboatcoloronlyavailablein Boats;soneedanextrajoin:

π σ sname color red Boats serves Sailors (( ' ' ) Re ) =

Amoreefficientsolution:

π π π σ sname sid bid color red Boats s Sailors ( (( ' ' ) Re ) ) =

Aqueryoptimizercanfindthis,giventhefirstsolution!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 18

Findsailorswho’vereservedaredoragreenboat

Canidentifyallredorgreenboats,thenfind sailorswho’vereservedoneoftheseboats: ρ σ ( , ( ' ' ' ' )) Tempboats color red color green Boats = ∨ =

π sname Tempboats serves Sailors ( Re )

Canalsodefine Tempboats usingunion!(How?)

∨ ∧

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 19

Findsailorswho’vereservedaredand agreenboat

Previousapproachwon’twork!Mustidentify sailorswho’vereservedredboats,sailors who’vereservedgreenboats,thenfindthe intersection(notethat sid isakeyforSailors): ρ π σ ( , (( ' ' ) Re )) Tempred sid color red Boats serves =

• π sname Tempred

Tempgreen Sailors (( ) ) ∩

ρ π σ ( , (( ' ' ) Re )) Tempgreen sid color green Boats serves =

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 20

Findthenamesofsailorswho’vereservedallboats

Usesdivision;schemasoftheinputrelations to/mustbecarefullychosen: ρ π π ( , ( , Re ) / ( )) Tempsids sid bid serves bid Boats

π sname Tempsids Sailors ( )

/ ( ' ' ) π σ bid bname Interlake Boats =

.....

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 21

Summary

Therelationalmodelhasrigorouslydefined querylanguagesthataresimpleand powerful.

Relationalalgebraismoreoperational;useful asinternalrepresentationforquery evaluationplans.

Severalwaysofexpressingagivenquery;a queryoptimizershouldchoosethemost efficientversion.