relationa agebral-41slides.ppt

7/27/2019 relationa agebral-41slides.ppt

1/41

2

Relational Algebra

The basic set of operations for the relationalmodel is the relational algebra. enable the specification of basic retrievals

The result of a retrieval is a new relation, whichmay have been formed from one or morerelations. algebra operations thus produce new relations,

which can be further manipulated the same algebra.

A sequence of relational algebra operationsforms a relational algebra expression, the result will also be a relation that represents the

result of a database query (or retrieval request).


2/41

3

What is an Algebra?

A language based on operators and adomain of values

Operators map values taken from thedomain into other domain values

Hence, an expression involving operatorsand arguments produces a value in the

domain When the domain is a set of all relations we

get the relational algebra


3/41

4

Relational Algebra Definitions

Domain: set of relations

Basic operators: select, project, union, set

difference, Cartesian (cross) product

Derived operators: set intersection, division,

join

Procedural: Relational expression specifies

query by describing an algorithm (the sequencein which operators are applied) for determining

the result of an expression


4/41

5

Unary Relational Operations

SELECT Operation: used to select asubsetofthe tuples from a relation that satisfy a selectioncondition. It is a filter that keeps only those tuplesthat satisfy a qualifying condition.

Examples:

DNO = 4(EMPLOYEE)SALARY > 30,000(EMPLOYEE)

denoted by (R) where the symbol (sigma) is used to denote the select operator, and theselection condition is aBoolean expression specified onthe attributes of relation R


5/41

6

SELECT Operation Properties

The SELECT operation (R) produces arelation S that has the same schema as R

The SELECT operation is commutative; i.e.,(< condition2> (R)) = (< condition1> ( R))

A cascaded SELECT operation may be applied in anyorder; i.e.,

(< condition2> ( (R))

= (< condition3> (< condition1> ( R)))

A cascaded SELECT operation may be replaced by a singleselection with a conjunction of all the conditions; i.e.,

(< condition2> ( (R))= AND < condition2> AND < condition3> ( R)))


6/41

7

Selection Condition

Operators: , =,

Simple selection condition:

operator operator

AND

OR NOT


7/41

8

Select Examples

Person

1123 John 123 Main stamps

1123 John 123 Main coins

5556 Mary 7 Lake Dr hiking

9876 Bart 5 Pine St stamps

Id Name Address Hobby

Id>3000 OR Hobby=hiking (Person)

Id>3000 AND Id


8/41

9

Unary Relational Operations (cont.)

PROJECT Operation: selects certain columnsfrom the table and discards the others.

Example:

LNAME, FNAME,SALARY(EMPLOYEE)The general form of the project operation is:

(R) where is the symbol usedto represent the project operation and is the desired list of attributes.

PROJECT removes duplicate tuples, so the resultis a set of tuples and hence a valid relation.


9/41

10

The number of tuples in the result of R isalways less or equal to the number of tuples in R.

If attribute list includes a key of R, then the number oftuples is equal to the number of tuples in R.

R ) R as longascontains theattributes in

PROJECT Operation Properties


10/41

SELECT and PROJECT Operations

(a)(DNO=4 AND SALARY>25000) OR (DNO=5 ANDSALARY>30000)(EMPLOYEE)

(b) LNAME, FNAME, SALARY(EMPLOYEE)

(c) SEX, SALARY(EMPLOYEE)


11/41

Unary Relational Operations (cont.)

Rename Operation

We may want to apply several relational algebra operations one after the other.Either we can write the operations as a single relational algebra expression

by nesting the operations, or we can apply one operation at a time and createintermediate result relations. In the latter case, we must give names to therelations that hold the intermediate results.

Example: To retrieve the first name, last name, and salary of all employeeswho work in department number 5, we must apply a select and a projectoperation. We can write a single relational algebra expression as follows:

FNAME, LNAME, SALARY(DNO=5(EMPLOYEE))

OR We can explicitly show the sequence of operations, giving a name to eachintermediate relation:

DEP5_EMPSDNO=5(EMPLOYEE)RESULTFNAME, LNAME, SALARY (DEP5_EMPS)


12/41

Relational Algebra Operations

from Set Theory

The UNION, INTERSECTION, and

MINUS Operations

The CARTESIAN PRODUCT (orCROSS PRODUCT) Operation


13/41

Two relations R1 and R2 are said to be union compatible

if they have the same degree and all their attributes

(correspondingly) have the same domain.

The UNION, INTERSECTION, and SET

DIFFERENCE operations are applicable on union

compatible relations

The resulting relation has the same attribute names as

the first relation

Relational Algebra Operations


14/41

The result of UNION operation on two relations, R1

and R2, is a relation, R3, that includes all tuples that areeither in R1, or in R2, or in both R1 and R2.

The UNION operation is denoted by:

R3 = R1 R2

The UNION operation eliminates duplicate tuples

The UNION operation


15/41

Results of the UNION operation

RESULT =RESULT1 RESULT2.

FIGURE 6.3


16/41

17

STUDENT INSTRUCTOR:

UNION Example

What would STUDENT INSTRUCTOR be?


17/41

The result of INTERSECTION operation on two

relations, R1 and R2, is a relation, R3, that includes alltuples that are in both R1 and R2.

The INTERSECTION operation is denoted by:

R3 = R1 R2

The both UNION and INTERSECTION operations are

commutative and associative operations

The INTERSECTIONoperation


18/41

The result of SET DIFFERENCE operation on two

relations, R1 and R2, is a relation, R3, that includes alltuples that are in R1 but not in R2.

The SET DIFFERENCE operation is denoted by:

R3 = R1R2

The SET DIFFERENCE (or MINUS) operation is not

commutative

The SET DIFFERENCEOperation


19/41

(a) Two union-compatible relations. (b) STUDENT INSTRUCTOR.

(c) STUDENT INSTRUCTOR. (d) STUDENTINSTRUCTOR.

(e) INSTRUCTOR STUDENT


20/41

21

Relational Algebra Operations From

Set Theory (cont.)

Union and intersection are commutative operations:

R S = S R, and R S = S R

Both union and intersection can be treated as n-ary

operations applicable to any number of relations as both

are associative operations; that is

R (S T) = (R S) T, and

(R

S)

T = R

(S

T)

The minus operation is not commutative; that is, in general

R - S S R


21/41

22

Cartesian (Cross) Product

IfRand Sare two relations,R Sis the set of allconcatenated tuples , wherex is a tuple inR andyis a tuple in S

R and Sneed not be union compatible

R S is expensive to compute: Factor of two in the size of each row; Quadratic in the number

of rows

A B C D A B C D

x1 x2 y1 y2 x1 x2 y1 y2x3 x4 y3 y4 x1 x2 y3 y4

x3 x4 y1 y2

R S x3 x4 y3 y4

R S


22/41

A B C D A B C D

1 2 x 6 7 = 1 2 6 7

3 4 8 9 1 2 8 9 3 4 6 7

3 4 8 9


23/41

The DIVISION operation is useful for some queries.

For example, Retrieve the name of employees whowork on allthe projects that John Smith works on.

The Division operation is denoted by:

R3

= R1 R2

In general, attributes in R2 are a subset of attributes

in R1

The DIVISIONOperation


24/41

Examples of Division A/B

sno pno

s1 p1

s1 p2

s1 p3s1 p4

s2 p1

s2 p2

s3 p2s4 p2

s4 p4

pno

p2

pno

p2

p4

pno

p1

p2

p4

sno

s1

s2s3

s4

snos1

s4

sno

s1

A

B1

B2B3

A/B1 A/B2 A/B3


25/41

The DIVISION Operation

(a) Dividing SSN_PNOS by SMITH_PNOS.

(b) TR S.


26/41

Cartesian Product Example

We want a list of COMPANYs female

employees dependents.


27/41


28/41

An important operationfor any relational database is

the JOIN operation, because it enables us to combinerelated tuples from two relations into single tuple

The JOIN operation is denoted by:

R3 = R1 R2

The degree of resulting relation is

degree(R1) + degree(R2)

Binary Relational Operations


29/41

The difference between CARTESIAN PRODUCT and

JOIN is that the resulting relation from JOIN consistsonly those tuples that satisfy thejoin condition

The JOIN operation is equivalent to CARTESIAN

PRODUCT and then SELECT operation on the resultof CARTESIAN PRODUCT operation, if the select-

condition is the same as thejoin condition

The JOINOperation


30/41

Database State for COMPANY

All examples discussed below refer to the COMPANY database shown here.


31/41

If the JOIN operation has equality comparison only

(that is, = operation), then it is called an EQUIJOINoperation

In the resulting relation on an EQUIJOIN operation,we always have one or more pairs of attributes that

have identical values in everytuples

The EQUIJOINOperation


32/41

Result of the JOIN operation DEPT_MGR

DEPARTMENT JOIN MGRSSN=SSN EMPLOYEE .

FIGURE 6.6


33/41

In EQUIJOIN operation, if the two attributes in the join

condition have the same name, then in the resultingrelation we will have two identical columns. In order to

avoid this problem, we define the NATURAL JOIN

operation

The NATURAL JOIN operation is denoted by:

R3 = R1 * R2

In R3 only one of the duplicate attributes from the listare kept

The NATURAL JOINOperation


34/41

(a) PROJ_DEPT PROJECT * DEPT.

(b) DEPT_LOCS DEPARTMENT *DEPT_LOCATIONS.

FIGURE 6.7


35/41

36

JOIN Operation

Cartesian productfollowed byselectis commonly

used to identify and select related tuples from two

relations => called JOIN. It is denoted by a

This operation is important for any relational databasewith more than a single relation, because it allows us to

process relationships among relations.

The general form of a join operation on two relations

R(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is:R S

where R and S can be any relations that result from

general relational algebra expressions.


36/41

Additional RelationalOperations

Aggregate Functions and Grouping

Recursive Closure Operations

The OUTER JOIN Operation


37/41

Aggregate Functions

E.g. SUM, AVERAGE, MAX, MIN, COUNT


38/41

Recursive Closure Example


39/41

40

OUTER JOINs

In NATURAL JOIN tuples without a matching(orrelated) tupleare eliminated from the join result. Tuples with null in the joinattributes are also eliminated. This loses information.

Outer joins, can be used when we want to keep all the tuples in R,all those in S, or all those in both relations

regardless of whether they have matching tuples in the other relation.

The left outer join operation keeps every tuple in thefirstorleftrelation R in R S; if no matching tuple is found in S, thenthe attributes of S in the join result are padded with null values.

A similar operation, right outer join, keeps every tuple in thesecondorrightrelation S in the result of R S.

A third operation,full outer join, denoted by keeps alltuples in both the left and the right relations when no matching

tuples are found, padding them with null values as needed.


40/41

Outer Join


41/41

Full outer Join

relationa agebral-41slides.ppt

Documents