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)