Relational Algebra and Calculus: A Comprehensive Guide for Database Management, Lecture notes of Database Management Systems (DBMS)

Download Relational Algebra and Calculus: A Comprehensive Guide for Database Management and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

4– 1 Relational Algebra and Calculus ( 1. 1. 1 )

Relational Algebra and Calculus

Oct 2017 Version: 1. 1. 1

Overview

✥ The mathematical foundation of query languages such as SQL

✥ Relational Algebra and Calculus, and why they are important

✥ Basic algebra operators

Instances used

sid sname rating age

28

31

44

58

yuppy

Lubber

guppy

Rusty

9

8

5

10

35

35

35

sid sname rating age

22

31

58

Dustin

Lubber

Rusty

7

8

10

45

35

(a) Instance S1 of Sailors

(b) Instance S2 of Sailors

sid bid day

22 101 1996 - 10 - 10

58 103 1996 - 11 - 12

(c) Instance R 1 of Re-

serves

bid bname color

101 Interlake blue

102 Interlake red

103 Clipper green

104 Marine red

(d) Instance B 1 of

Boats

Relational Algebra

✥ Relational algebra (RA) is a query language associated with

the relational model

✥ Every operator in RA takes one or two relations as parameters

and return a relation

✥ A relational algebra expression is recursively defined to be a

✦ relation ,

✦ a unary algebra operator applied to a single expression, or

✦ a binary algebra operator applied to two expressions

✥ Basic operators:

✦ Selection, projection, union, cross-product, and difference

✥ Procedural

Projection

✥ Allows us to extract columns from a relation

✥ Examples:

πsname,rating (S 2 )

sname rating

yuppy 9

Lubber 8

guppy 5

Rusty 10

π age

(S 2 )

age

35

Combining Both

π sname,rating

(σ rating> 8

(S 2 ))

sname rating

yuppy 9

Rusty 10

Intersection

✥ R ∩ S returns a relation which contains the tuples that are both in

R and S

✥ R and S should be union compatible

S 1 ∩ S 2

sid sname rating age

31

58

Lubber

Rusty

8

10

35

Set Difference

✥ R − S returns a relation which contains the tuples that are both in

R but not in S

✥ R and S should be union compatible

S 1 − S 2

sid sname rating age

22 Dustin 7 45

Renaming

✥ When operating in more than one table, name conflicts can arise

✥ renaming operator ρ renames the fields of a relation

✥ ρ(R(F

)E) takes an expression E and returns a new

instance relation called R

✥ R contains same columns as E, but some fields are renamed

✥ R contains same columns as E, but some fields are renamed

✥ Example: ρ(C (1 → sid 1 , 5 → sid 2 ), S 1 × R 1 ) returns:

sid 1 sname rating age sid 2 bid day

22

22

31

31

58

58

Dustin

Dustin

Lubber

Lubber

Rusty

Rusty

7

7

8

8

10

10

45

45

35

35

22

58

22

58

22

58

101

103

101

103

101

103

1996 - 10 - 10

1996 - 11 - 12

1996 - 10 - 10

1996 - 11 - 12

1996 - 10 - 10

1996 - 11 - 12

✥ With schema:

C (sid1: integer , sname: string , rating: integer ,

age: read , sid2: integer , bid: integer , day: date )

Joins

✥ The join operation is the most useful operation in relational

algebra

✥ It can be defined with cross product and selection, projection

✥ It is important to do joins without materializing the cross product

✥ There exist several variants of joins:

✦ Condition Join

✦ Equijoin

✦ Natural Join

4– 17 Relational Algebra and Calculus ( 1. 1. 1 )

Condition Joins

✥ Essentially, a select of a cross product:

R r>< c

S = σ c

(R × S)

✥ Example: S 1 r>< S 1 .sid<R 1 .sid

R 1

(sid) sname rating age (sid) bid day

22

31

Dustin

Lubber

7

8

45

58

58

103

103

1996 - 11 - 12

1996 - 11 - 12

Natural Join

✥ (^) A natural join is a equijoin in which equalities are specified on

all fields that have the same name

✥ In this case we simply omit the condition

✥ The result does not have repeated field names

✥ Example: S 1 r>< R 1 = S 1 r>< S 1 .sid=R 1 .sid

R 1

sid sname rating age bid day

22

58

Dustin

Rusty

7

10

45

35

101

103

1996 - 10 - 10

1996 - 11 - 12

✥ If the two relations have no common attributes, then, the result is

the cross product

Division

4– 20 Relational Algebra and Calculus ( 1. 1. 1 )

✥ It is a complex operator

✥ Useful in situations such as: find the sailors who have reserved

all the boats

✥ For relations A and B, A/B is the largest relation such that

(A/B) × B ⊆ A

✥ Definition:

A/B = {(x)|∀y s.t. (y) ∈ B, ∃(x, y) ∈ A}