DBMS CH 06

Relational Algebra Expressions can be applied by (2 ways)

- by nesting operations -creating intermediate relations

The SELECT operation

-operation (denoted by σ (sigma)) is used to select a subset of the tuples from a relation based on a selection condition. -The selection condition acts as a filter Keeps only those tuples that satisfy the qualifying condition -Tuples satisfying the condition are selected whereas the other tuples are discarded (filtered out)

Relational algebra expression

A sequence of relational algebra operations forms a relational algebra expression

Relational Algebra

Basic set of operations for relational model

Project operation contains tuples(true/false)

False

The number of tuples in the result of PROJECT π<list>(R)

Less or equal to the number of tuples in R

Is PROJECT commutative?

NO

π <list1> (π <list2> (R) ) = π <list1> (R) is always true. (Yes/No)

NO. It is only true when as <list2> contains the attributes in <list1>

Project Operation

PROJECT Operation is denoted by (pi) This operation keeps certain columns (attributes) from a relation and discards the other columns. PROJECT creates a vertical partitioning The list of specified columns (attributes) is kept in each tuple The other attributes in each tuple are discarded

An example of the shorthand notation for rename

RESULT <- σFNAME, LNAME, SALARY (DEP5_EMPS)

Why is relational algebra closed?

The result of an operation is a new relation, which may have been formed from one or more input relations

The select operation on a relation (R) produces a relation S. R and S will have the same schema.(True/False)

True

SELECT and PROJECT are ______________operators

Unary

When is the number of tuples in a result of a PROJECT operation equal to the number of tuples in the relation?

When the list of attributes in the PROJECT includes a key of the relation

Select operation creates a ______________partitioning

horizontal

Number of tuples in the result of a SELECT

less than or equal to the number of tuples in the relation R

Project Operation creates a _____________partitioning

vertical

Project Operation:Symbol

π

PROJECT operation:Notation

π<attribute list>(R)

Retrieve the first name, last name, and salary of all employees who work in department number 5 (with and without intermediate result)

πFNAME, LNAME, SALARY(σ DNO=5(EMPLOYEE)) DEP5_EMPS <- σDNO=5(EMPLOYEE) RESULT <- π FNAME, LNAME, SALARY (DEP5_EMPS)

To list each employee's first and last name and salary

πLNAME, FNAME,SALARY(EMPLOYEE)

RENAME Operation:Notation

ρ

The Different forms of rename operation

ρS (B1, B2, ..., Bn )(R) changes both: the relation name to S, and the column (attribute) names to B1, B1, .....Bn ρS(R) changes: the relation name only to S ρ(B1, B2, ..., Bn )(R) changes: the column (attribute) names only to B1, B1, .....Bn

Select Operation:Symbol

σ (sigma)

σ <cond1>(σ < cond2> (σ <cond3>(R)) Replace this with a single select

σ <cond1> AND < cond2> AND < cond3>(R)))

Commutative property of Select

σ <condition1>(σ < condition2> (R)) = σ <condition2> (σ < condition1> (R))

Select Operation : Notation

σ <selection condition>(R)

Select the EMPLOYEE tuples whose department number is 4

σ DNO = 4 (EMPLOYEE)

Select the employee tuples whose salary is greater than $30,000

σ SALARY > 30,000 (EMPLOYEE)

Select the tuples for all employees who work in department 4 and make over 25000 per year or work in department 5 and make over 30000

σ(DNo=4 AND Salary>25000) )OR (Dno=5 AND Salary>30000)(EMPLOYEE)