Understanding Relational Algebra for Database Queries, Slides of Database Management Systems (DBMS)

Download Understanding Relational Algebra for Database Queries and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

1

Lecture 4:

Relational algebra

www.cl.cam.ac.uk/Teaching/current/Databases/

Today’s lecture

• What‟s the (core) relational algebra?

• How can we write queries using the

relational algebra?

• How powerful is the relational algebra?

Formal relational query

languages

• Two formal query languages

1. Relational Algebra

• Simple „operational‟ model, useful for expressing execution

plans

2. Relational Calculus

• Logical model („declarative‟), useful for theoretical results
• Both languages were introduced by Codd in a series of

papers

• They have equivalent expressive power

They are the key to understanding SQL query processing!

Preliminaries

• A query is applied to relation instances,

and the result of a query is also a relation

instance

– Schema of relations are fixed (cf. types)

– The query will then execute over any valid

instance

– The schema of the result can also be

determined

Core relational algebra

• Five basic operator classes:

1. Selection

• Selects a subset of rows

2. Projection

• Picking certain columns

3. Renaming

• Renaming attributes

4. Set theoretic operations

• The familiar operations: union, intersection, difference, …

5. Products and joins

• Combining relations in useful ways

8

Selection

• Selects rows that satisfy a condition, written

R

c

(R

• where c is a condition involving the attributes of

R 2 , e.g.

rating>

(S2)

returns the relation instance

sid sname ratin g ag e 99 Julia 100 20 88 Gavin 100 21

10

Projection

Deletes fields that are not in the

projection list

R

A

(R

where A is a list of attributes from the

schema of R

, e.g.

sname,rating

(S2)

returns the relation instance

sname ratin g Myleene 6 Tim 8 Julia 100 Gavin 100

Projection cont.

• Note:

1. Projection operator has to eliminate

duplicates (why?)

2. Aside: Real systems don‟t normally perform

duplicate elimination unless the user

explicitly asks for it (why not?)

Familiar set operations

• We have the familiar set-theoretic

operators, e.g. , , -

• There is a restriction on their input relation

instances: they must be union

compatible

– Same number of fields

– Same field names and domains

• E.g. S1 S2 is valid, but S1 R1 is not

Cartesian products

A B

• Concatenate every row of A with every

row of B

• What do we do if A and B have some field

names in common?

– Several choices, but we‟ll simply assume that

the resulting duplicate field names will have

the suffix 1 and 2

Theta join

• Theoretically, it is a derived operator

R

1

V

c

R

2

c

(R

1

R

2

• E.g., S1 V

sid.1<=sid.

R

sid.1 sname rating age sid.2 bid day 11 Sue 7 26 22 101 101001 11 Sue 7 26 99 103 111201 22 Tim 8 26 22 101 101001 22 Tim 8 26 99 103 111201 33 Bob 9 28 99 103 111201 55 Kim 10 28 99 103 111201

Theta join cont.

1. The result schema is the same as for a

cross-product

2. Sometimes this operator is called a

conditional join

3. Most commonly the condition is an

equality on field names, e.g.

S1 V

sid.1=sid.

R

Natural join cont.

• Note that the common fields appear only

once in the resulting relation instance

• This operator appears very frequently in

real-life queries

• It is always implemented directly by the

query engine (why?)

Division

• Not a primitive operator, but useful to express

queries such as

Find sailors who have reserved all the boats

• Consider the simple case, where relation A has

fields x and y, and relation B has field y

• A/B is the set of xs (sailors) such that for every y

(boat) in B, there is a row (x,y) in A