Example Database Application (COMPANY) Relational Algebra Unary Relational Operations Relational Algebra Operations From Set Theory

Database Systems

Relational Algebra

Syed Sarmad Ali Database Management Systems

1

Today’s agenda

Example Database Application (COMPANY)

Relational Algebra

Unary Relational Operations

Relational Algebra Operations From Set Theory

Syed Sarmad Ali

2

DEFINITION SUMMARY

Informal Terms

Formal Terms

Table

Relation

Column

Attribute/Domain

Row

Tuple

Values in a column

Domain

Table Definition

Schema of a Relation

Populated Table

Extension

The attributes and tuples of a relation

STUDENT

4

Relational Algebra Overview

£ Unary Relational Operations

• SELECT (symbol: s (sigma))

• PROJECT (symbol: p (pi))

• RENAME (symbol: r (rho))

£ Relational Algebra Operations from Set Theory

• UNION ( È ), INTERSECTION ( Ç ), DIFFERENCE (or

MINUS, – )

• CARTESIAN PRODUCT ( x )

£ Binary Relational Operations

• JOIN (several variations of JOIN exist)

• DIVISION

£ Additional Relational Operations

• OUTER JOINS, OUTER UNION

Relational Algebra

The basic set of operations for the relational model is known as the

relational algebra. These operations enable a user to specify basic

retrieval requests.

The result of a retrieval is a new relation, which may have been formed

from one or more relations. The

algebra operations

thus produce new

relations, which can be further manipulated using operations of the same

algebra.

A sequence of relational algebra operations forms a

relational algebra

expression

, whose result will also be a relation that represents the result

of a database query (or retrieval request).

Syed Sarmad Ali

6

Relational Algebra: 5 Basic Operations

Selection

( ) Selects a subset of

rows

from relation (horizontal).

Projection

( ) Retains only wanted

columns

from relation (vertical).

Cross-product

(

x

) Allows us to combine two relations.

Set-difference

(–) Tuples in r1, but not in r2.

Union

(

∪

) Tuples in r1 and/or in r2.

Unary Relational Operations

SELECT Operation

SELECT operation is used to select a subset of the tuples from a relation that satisfy a

selection condition. It is a filter that keeps only those tuples that satisfy a qualifying condition – those satisfying the condition are selected while others are discarded.

Example: To select the EMPLOYEE tuples whose department number is four or those whose salary is greater than $30,000 the following notation is used:

σ

DNO = 4 (EMPLOYEE)

σ

SALARY > 30,000(EMPLOYEE)

In general, the select operation is denoted by

σ <selection condition>(R) where the

symbol

σ

(sigma) is used to denote the select operator, and the selection condition is a Boolean expression specified on the attributes of relation R

8

Unary Relational Operations

SELECT Operation Properties

The SELECT operation σ _{<selection condition>}(R) produces a relation S that has the same schema as R

The SELECT operation σ is commutative; i.e.,

● σ_<condition1>(σ _{< condition2>} (R)) = σ_<condition2> (σ _{< condition1>} ( R))

A cascaded SELECT operation may be applied in any order; i.e., ● σ_<condition1>(σ _{< condition2>} (σ_<condition3> ( R))

● = σ _<condition2> (σ_{< condition3>} (σ _{< condition1>} ( R)))

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

● σ_<condition1>(σ _{< condition2>} (σ_<condition3> ( R))

● = σ _{<condition1> AND < condition2> AND < condition3>} ( R)))

Syed Sarmad Ali

9

[image:10.960.145.789.199.436.2]

FIGURE 6.1

Results of SELECT and PROJECT operations. (a) σ_{(DNO=4 AND SALARY>25000) OR (DNO=5 AND}

SLARY>30000)(EMPLOYEE). (b) πSEX, SALARY(EMPLOYEE).

Syed Sarmad Ali

10

Unary Relational Operations (cont.)

PROJECT Operation

This operation selects certain columns from the table and discards the other columns. The PROJECT creates a vertical partitioning – one with the needed columns (attributes)

containing results of the operation and other containing the discarded Columns.

Example: To list each employee’s first and last name and salary, the following is used:

π

_{LNAME, FNAME,SALARY}(EMPLOYEE)

The general form of the project operation is

π

<attribute list>(R) where

π

(pi) is the symbol used to represent the project operation and <attribute list> is the desired list of attributes from the attributes of relation R.

The project operation removes any duplicate tuples, so the result of the project operation is a set of tuples and hence a valid relation.

Syed Sarmad Ali

13

Projection

Unary Relational Operations (cont.)

PROJECT Operation Properties

The number of tuples in the result of projection

π

<list>

(R)

is always

less or equal to the number of tuples in R.

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

π

_<list1>

(

π

_<list2>

(R)

)

=

π

_<list1>

(R)

as long as

<list2>

contains

the

attributes in

<list2>

Syed Sarmad Ali

15

[image:16.960.145.789.199.436.2]

FIGURE 6.1

Results of SELECT and PROJECT operations. (a) σ_{(DNO=4 AND SALARY>25000) OR (DNO=5 AND}

SLARY>30000)(EMPLOYEE). (b) πSEX, SALARY(EMPLOYEE).

Syed Sarmad Ali

16

(a) Project sname,rating

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 create intermediate result

relations. In the latter case, we must give names to the relations that hold the intermediate results.

Example: To retrieve the first name, last name, and salary of all employees who work in department number 5, we must apply a select and a project operation. 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 each intermediate relation:

DEP5_EMPS ←

σ

_DNO=5(EMPLOYEE)

RESULT ←

π

_{FNAME, LNAME, SALARY} (DEP5_EMPS) Syed Sarmad Ali

19

Unary Relational Operations (cont.)

The RENAME operator is used to give a name to results or output of queries, returns of selection statements, and views of queries that we would like to view at some other point in time: [3] [4] [5] [6]

The RENAME operator is symbolized by ρ (rho).

The general syntax for RENAME operator is: ρ

s(B1, B2, B3,….Bn)(R )

ρ is the RENAME operation.

S is the new relation name.

B₁, B₂, B₃, …B_n are the new renamed attributes (columns).

Syed Sarmad Ali

20

RENAME OPERATOR

The RENAME Operator

R is the relation or table from which the attributes are chosen.

To implement the RENAME statement in SQL, we take a look at an example in which we would like to choose the Date of Birth and Employee Number attributes and RENAME them as ‘Birth_Date’ and ‘Employee_Number’ from the EMPLOYEE relation…

ρ

_{(Birth_Date, Employee_Number)}

(EMPLOYEE ) ←

π

_{dob, empno}

(EMPLOYEE )

[image:22.960.27.799.66.493.2]

FIGURE 6.2

Results of a sequence of operations. (a) π

_{FNAME, LNAME, SALARY}

(σ

_DNO=5

(EMPLOYEE)). (b) Using intermediate relations and

renaming of attributes.

Syed Sarmad Ali

22

Union and Set-Difference

All of these operations take two input relations, which

must be

union-compatible

:

Same number of fields.

`Corresponding’ fields have the same type.

Union

S1

Relational Algebra Operations From

Set Theory

UNION Operation

The result of this operation, denoted by R

∪

S, is a relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated.

Example: To retrieve the social security numbers of all employees who either work in

department 5 or directly supervise an employee who works in department 5, we can use the union operation as follows:

DEP5_EMPS ←

σ

_DNO=5 (EMPLOYEE)

RESULT1 ←

π

_SSN(DEP5_EMPS)

RESULT2(SSN) ←

π

_SUPERSSN(DEP5_EMPS)

RESULT ← RESULT1 ∪ RESULT2

The union operation produces the tuples that are in either RESULT1 or RESULT2 or both. The two operands must be “type compatible”.

Syed Sarmad Ali

25

Relational Algebra Operations From

Set Theory

Type Compatibility

The operand relations R

₁

(A

₁

, A

₂

, ..., A

_n

) and R

₂

(B

₁

, B

₂

, ..., B

_n

) must have

the same number of attributes, and the domains of corresponding attributes

must be compatible; that is, dom(A

_i

)=dom(B

_i

) for i=1, 2, ..., n.

The resulting relation for R

₁

∪

R

₂

,R

₁

∩

R

₂

, or R

₁

-R

₂

has the same attribute

names as the

first

operand relation R1 (by convention).

Syed Sarmad Ali

26

Relational Algebra Operations From

Set Theory

UNION Example

STUDENT∪INSTRUCTOR Syed Sarmad Ali

27

Relational Algebra Operations From Set Theory (cont.)

INTERSECTION OPERATION

The result of this operation, denoted by R

∩

S, is a relation that includes all tuples that are in both R and S. The two operands must be "type compatible"

Example: The result of the intersection operation (figure below) includes only those who are both students and instructors.

STUDENT ∩ INSTRUCTOR

Syed Sarmad Ali

28

Intersection

S1

Relational Algebra Operations From Set Theory (cont.)

Set Difference (or MINUS) Operation

The result of this operation, denoted by R - S, is a relation that includes all tuples that are in R but not in S. The two operands must be "type compatible”.

Example: The figure shows the names of students who are not instructors, and the names of instructors who are not students.

STUDENT-INSTRUCTOR

INSTRUCTOR-STUDENT

Syed Sarmad Ali

30

Set Difference

S1

S2

Relational Algebra Operations From Set Theory (cont.)

Notice that both union and intersection are

commutative operations;

that is

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

Syed Sarmad Ali

32

Relational Algebra Operations From Set Theory (cont.)

CARTESIAN (or cross product) Operation

This operation is used to combine tuples from two relations in a combinatorial fashion. In general, the result of R(A

1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a relation Q with degree n + m

attributes Q(A

1, A2, . . ., An, B1, B2, . . ., Bm), in that order. The resulting relation Q has one

tuple for each combination of tuples—one from R and one from S. Hence, if R has n

R tuples (denoted as |R| = nR ), and S has nS tuples, then

| R x S | will have n_R

*

n_S tuples.

The two operands do NOT have to be "type compatible”

Example:

FEMALE_EMPS ←

σ

GENDER=’F’(EMPLOYEE)

EMPNAMES ←

π

FNAME, LNAME, SSN (FEMALE_EMPS)

EMP_DEPENDENTS ← EMPNAMES x DEPENDENT

Syed Sarmad Ali

33

FIGURE≈6.5a

The CARTESIAN PRODUCT (CROSS PRODUCT) operation.

Syed Sarmad Ali

34

FIGURE≈6.5b

The CARTESIAN PRODUCT (CROSS PRODUCT) operation.

Syed Sarmad Ali

35

Cross Product Example

R1

S1

Compound Operator: Join

Joins are compound operators involving cross product, selection, and (sometimes) projection.

Most common type of join is a “natural join” (often just called “join”). R S conceptually is:

Compute R X S

Select rows where attributes that appear in both relations have equal values

Project all unique atttributes and one copy of each of the common ones.

Note: Usually done much more efficiently than this.

Natural Join Example

R1

S1

Other Types of Joins

Condition Join (or “theta-join”):

Result schema

same as that of cross-product.

May have fewer tuples than cross-product.

“Theta” Join Example

R1

S1