The Relational Algebra and Calculus

SELECT Operation () sigma

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)
SQL> SELECT * FROM Employee WHERE DeptNo=4;
SALARY > 30,000 (EMPLOYEE)
SQL> SELECT * FROM Employee WHERE Salary>=30000;
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

Note: The WHERE clause of an SQL command corresponds to  ().

More Example:

Relational Algebra:
σempno=7(EMPLOYEE)
SQL:
SELECT empno FROM EMPLOYEE WHERE empno=7

Relational Algebra:
σdob<’01-Jan-1980′(EMPLOYEE)
SQL:
SELECT dob FROM EMPLOYEE WHERE DOB < ’01-Jan-1980′
 PROJECT Operation  (pi)
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)

SQL> select Lname,Fname,Salary from 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 number of tuples in the result of projection  <list> (R)is always less
or equal to the number of tuples in R.
More Example:

∏dob, empno(EMPLOYEE )

In SQL :
SELECT dob, empno FROM EMPLOYEE
Type Compatibility
– The operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn)
must have the same number of attributes, and the domains of
corresponding attributes must be compatible; that is,
dom(Ai)=dom(Bi) for i=1, 2, ..., n.
 UNION Example

STUDENTINSTRUCTOR

In SQL:
SELECT * From Student UNION SELECT * From Instructor
 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

In SQL:
SELECT * From Student Intersect SELECT * From Instructor
 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.

SQL> (SELECT * FROM R) EXCEPT (SELECT * FROM S);

STUDENT-INSTRUCTOR

INSTRUCTOR-STUDENT
The set operations UNION, INTERSECTION, and MINUS.
(a) Two union-compatible relations.
(b) STUDENT  INSTRUCTOR.
(c) STUDENT  INSTRUCTOR.
(d) STUDENT – INSTRUCTOR.
(e) INSTRUCTOR – STUDENT
 Rename Operation (ρ) Rho
The results of relational algebra are also relations but without any name. The
rename operation allows us to rename the output relation. 'rename' operation is
denoted with small Greek letter rho ρ.
Another Example:
RA> ρCustomerID,CustomerName -> ID,CustName(Customers)

SQL> SELECT CustomerID as ID, CustomerName AS CustName

FROM Customers;
Here, Branch->Location and Salary->Pay in Employee Relation and
Factory->Location and Wages->Pay in Staff Relation
 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(A1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a relation Q with degree n + m attributes Q(A1, 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 nR tuples (denoted as |R| = nR ), and S has nS tuples, then
| R x S | will have nR * nS tuples.
– The two operands do NOT have to be "type compatible”
SQL> select * from Employee, Dependents
 SQL joins are used to combine rows from two or more tables.
JOIN Operation
– The sequence of Cartesian product followed by select is used
quite commonly to identify and select related tuples from two
relations, a special operation, called JOIN. It is denoted by a
– This operation is very important for any relational database with
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 <join condition> S
where R and S can be any relations that result from general
relational algebra expressions.
Example:
 NATURAL JOIN Operation
The standard definition of natural join requires that the two join attributes, or each pair
of corresponding join attributes, have the same name in both relations. If this is not the
case, a renaming operation is applied first.
Natural join ( ⋈ ) is a binary operator that is written as (R ⋈ S ) where R and S are
relations. The result of the natural join is the set of all combinations of tuples in R and
S that are equal on their common attribute names. For an example consider the tables
Employee and Dept and their natural join:
 θ-join equijoin (INNER JOIN)
The most common use of join involves join conditions with equality comparisons only.
Such a join, where the only comparison operator used is =, is called an EQUIJOIN. In
the result of an EQUIJOIN we always have one or more pairs of attributes (whose
names need not be identical) that have identical values in every tuple.
The JOIN seen in the previous example was EQUIJOIN.
Consider tables Car and Boat which list models of cars and boats and their respective
prices. Suppose a customer wants to buy a car and a boat, but she does not want to spend
more money for the boat than for the car. The θ-join (⋈θ) on the relation CarPrice ≥
BoatPrice produces a table with all the possible options. When using a condition where
the attributes are equal, for example Price, then the condition may be specified as
Price=Price or alternatively (Price) itself.
The semi join relation Employee ⋉ Dept only contains attributes
from the Employee relation, not from the Dept relation. “Semi”
means that we don’t really join the right hand side, we only check if
a join would yield results for any given tuple.
In SQL, we would write the same relation using IN or EXISTS Operator
-- IN
SELECT * FROM Employee WHERE DeptName IN (  SELECT DeptName
  FROM Dept )
 
-- EXISTS
SELECT * FROM Employee WHERE EXISTS (   SELECT 1   FROM Dept
  WHERE Employee.DeptName = Dept.DeptName )
The anti join relaion Employee ▷ Dept only contains attributes from the Employee
relation, not from the Dept relation. “Anti” means that we don’t really join the right
hand side, we only check if a join would NOT yield results for any given tuple.
In SQL, we would write the same relation using ”NOT IN” or ”NOT EXISTS”
Using NOT IN:
SQL> SELECT * FROM Employee WHERE DeptName NOT IN (   SELECT DeptName
  FROM Dept )
 Using NOT EXISTS:
SQL> SELECT * FROM Employee WHERE NOT EXISTS (   SELECT 1   FROM Dept
  WHERE Employee.DeptName = Dept.DeptName )
Here, Eugene task is only Database1! So, eliminated from output
Outer joins
Whereas the result of a join (or inner join) consists of tuples formed by combining matching tuples in
the two operands, an outer join contains those tuples and additionally some tuples formed by
extending an unmatched tuple in one of the operands by "fill" values for each of the attributes of the
other operand. Three outer join operators are defined: left outer join, right outer join, and full outer
join.
Th
an
k
yo
u!