Relational Algebra: A Foundation for Database Query Languages, Lecture notes of Algebra

Download Relational Algebra: A Foundation for Database Query Languages and more Lecture notes Algebra in PDF only on Docsity!

Relational Query Languages v Query languages:

Allow manipulation and retrieval of data from a database. v Relational model supports simple, powerful QLs:–^ Strong formal foundation based on logic.–^ Allows for much optimization. v Query Languages

!=^ programming languages!

-^ QLs not expected to be “Turing complete”.–^ QLs not intended to be used for complex calculations.–^ QLs support easy, efficient access to large data sets.

Preliminaries v A query is applied to

relation instances

, and the

result of a query is also a relation instance.^ – Schemas^ of input relations for a query are fixed (butquery will run regardless of instance!)–^ The schema for the

result^ of a given query is also fixed! Determined by definition of query languageconstructs. v Positional vs. named-field notation:– Positional notation easier for formal definitions,named-field notation more readable.– Both used in SQL

Database Management Systems, R. Ramakrishnan

Example Instances

sid^ sname

rating^ age 22 dustin

31 lubber

58 rusty^

sid^ sname

rating^ age 28 yuppy

31 lubber

44 guppy

58 rusty^ sid^ bid^ day 22 101 10/10/96 58 103 11/12/96^10 35. R1 S1 S

v^ “Sailors” and “Reserves”relations for our examples. v^ We’ll use positional ornamed field notation,assume that names of fieldsin query results are`inherited’ from names offields in query inputrelations.

Projection

sname^ ratingyuppy^9 lubber^8 guppy^5 rusty^10 π^ sname rating

S ( )^2 , age35.055.

π S (^ )^2 age

v^ Deletes attributes that are not in^ projection list

. v^ Schema^ of result contains exactlythe fields in the projection list,with the same names that theyhad in the (only) input relation. v^ Projection operator has toeliminate

duplicates! (Why??)– Note: real systems typicallydon’t do duplicate eliminationunless the user explicitly asksfor it. (Why not?)

Selection

σ^ rating^

S^2 ( ) > 8

sid^ sname

rating^

age

28 yuppy

58 rusty

sname^ ratingyuppy^9 rusty^10 π σ sname rating^

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

v^ Selects rows that satisfy^ selection condition

. v^ No duplicates in result!(Why?) v^ Schema^ of resultidentical to schema of(only) input relation. v^ Result^ relation can bethe^ input^

for anotherrelational algebraoperation! ( Operatorcomposition. )

Cross-Product v Each row of S1 is paired with each row of R1. v Result schema

has one field per field of S1 and R1, with field names `inherited’ if possible.^ – Conflict : Both S1 and R1 have a field called

sid.

ρ^ (^ (^

,^ ),

C^ sid^

sid^ S^ R

1 1 5^

→^ →

×

(sid)^ sname^

rating^ age^ (sid)

bid^ day 22 dustin^

7 45.0^22

101 10/10/ 22 dustin^

7 45.0^58

103 11/12/ 31 lubber^

8 55.5^22

101 10/10/ 31 lubber^

8 55.5^58

103 11/12/ 58 rusty^

10 35.0^22

101 10/10/ 58 rusty^

10 35.0^58

103 11/12/ *^ Renaming operator

:

Joins v^ Condition Join

v^ Result schema

same as that of cross-product. v^ Fewer tuples than cross-product, might beable to compute more efficiently v^ Sometimes called a

R^ S^ c^ theta-join.

R^ Sc

><^ =^

×σ ( )

(sid)^ sname

rating^

age^ (sid)

bid^ day

22 dustin

7

45.0^58

103 11/12/

31 lubber

8

55.5^58

103 11/12/

S^

R

1 S sid^ R sid

><^1 1.^ .<

Database Management Systems, R. Ramakrishnan

Division v Not supported as a primitive operator, but useful forexpressing queries like:^ Find sailors who have reserved

all^ boats.

v^ Let^ A^ have 2 fields,

x^ and^ y ;^ B

have only field

y :

- A/B^ = –^ i.e.,^ A/B^ contains all

x^ tuples (sailors) such that for

every^ y

tuple (boat) in

B , there is an

xy^ tuple in^

A****.

- Or : If the set of

y^ values (boats) associated with an

x^ value

(sailor) in^ A

contains all

y^ values in^

B , the^ x^ value is in

A/B.

v^ In general,

x^ and^ y^ can be any lists of fields;

y^ is the

list of fields in

B , and^ x^

y^ is the list of fields of

A.

{^

} x^ x y^

A^ y^

B |^ ,∃^ ∈

∀^ ∈ ∪

A

• Relational Algebra Module 3, Lecture
• Examples of Division A/B sno pnos1 p1s1 p2s1 p3s1 p4s2 p1s2 p2s3 p2s4 p2s4 p
• pnop
• pnop2p
• pnop1p2p
• snos1 s2 s3 s
• snos1 s
• snos
• B
• B
• B
• A/B
• A/B
• A/B

Find names of sailors who’ve reserved boat #103 v Solution 1:^

π^ σ sname^ bid

serves Sailors ((^

Re^ )^

) =^103

<

v^ Solution 2

:^ ρ^

Re^ )

Temp^

serves

1 bid^103 =

ρ^ (^

,^

Temp^ Temp

Sailors 2 1 ><

π^ Temp (^ sname^

v^ Solution 3

π^ :

σ sname bid^

serves^ Sailors

(^

(Re^

=^103

Find names of sailors who’ve reserved a red boat^ v^ Information about boat color only available inBoats; so need an extra join:^ π^

σ sname color^ red

Boats^

serves^ Sailors ((^ '^

)^ Re'

)

=^

<^

<

v^ A more efficient solution:^ π^ π^

π^ σ sname^ sid^

bid^ color^

Boats^ red

s^ Sailors

(^ ((^

)^ ' ' Re )^

) =^

<^ ><

*^ A query optimizer can find this given the first solution!

Find sailors who’ve reserved a red and a green boat^ v^ Previous approach won’t work! Must identifysailors who’ve reserved red boats, sailorswho’ve reserved green boats, then find theintersection (note that

sid^ is a key for Sailors):

ρ^

π^ σ

(^ ,^

((^ '^

)^ '

Re^ ))

Tempred^

sid^ color

Boats red

serves

=^

π^ sname

Tempred^

Tempgreen

Sailors

((^

)^

∩^

ρ^

π^ σ (^

,^ ((^

'^ '^

)^ Re^

))

Tempgreen

sid^ color

Boats green serves =^

<

Database Management Systems, R. Ramakrishnan

Find the names of sailors who’ve reserved all boats v Uses division; schemas of the input relationsto / must be carefully chosen:^ ρ^

π^

π

(^ , (

Re,^

) / (^

))

Tempsids^

servessid bid Boatsbid

π^ sname

Tempsids^

Sailors

(^

v^ To find sailors who’ve reserved all ‘Interlake’ boats:^ /

(^

'^

π^ σ^ bid^ bname

Interlake

Boats

.....