# Relational Algebra

Relational algebra is a procedural language. It consists of set of operations which takes one or two relations as input and gives out a result which is a new relation.

The examples for the different operations will be explained by using the company database,

**Employee (employee_name, emp_id, DOfB, address,gender,dep_number,salary)**

**Department (Dep_name, D_number, mngr_id, mngr_st_date)**

**Project (proj_num, proj_name, p_location, d_num)**

**Works_on (empl_id, p_num, hours)**

**Dependent (emp_id, depen_name, gender, bdate, relationship)**

The SELECT Operation

The SELECT operation selects the tuples from the relation which satisfies the selection condition, it is denoted by symbol ‘σ’ (sigma). The selection acts as a filter to the tuples to be selected.

The comparisons operators like <,>, =, are used in selection predicate. The connectives like and (), or (V), not(-) are used to combine several predicates.

The PROJECT operation

This operation is used to select certain columns from a relation and discards the other columns. It is denoted by ∠ (Pi).

UNION Example

** **

Intersection Example

** **

Difference Example

** **

Cross Product Operation

The cross product combines the tuples in A and B in a combinatorial manner, i.e. every tuple in A will be combined with every in relation B.

**Example,**

σ _{branch_name=’bangalore’ }(Depositor × Borrower)

Renaming Operation

The rename operation is denoted by lowercase Greek letter rho(ρ).

The rename operation can be expressed by the following, ρA ( R) changes the name of the relation to A ρA(c1,c2,c3…..cn)( R ) changes the name of the relation to A and the column names to c1,c2,c3,…cn.

JOIN Operator

JOIN is used to combine related tuples from two relations:

- In its simplest form the JOIN operator is just the cross product of the two relations.
- As the join becomes more complex, tuples are removed within the cross product to make the result of the join more meaningful.

The notation used is

R JOIN_{join condition} S

Equi join

A special case of join condition where the join condition is only between the equalities, like employee.dept_no = department.dept_num. There is equality between 2 fields of employee and department.

Equi join is represented by R S where R & S are two relations.

Natural Join

Invariably the JOIN involves an equality test, and thus is often described as an equi-join. Such joins result in two attributes in the resulting relation having exactly the same value.

OUTER JOINs

An outer join retains the information that would have been lost from the tables, replacing missing data with nulls.

There are three forms of the outer join, depending on which data is to be kept.

- LEFT OUTER JOIN - keep data from the left-hand table
- RIGHT OUTER JOIN - keep data from the right-hand table
- FULL OUTER JOIN - keep data from both tables

# Relational Calculus

Relational calculus is an alternative form of relational algebra which is non-procedural or declarative language where it is not required to be concerned about the procedure to obtain the result rather the output is obtained without knowing the method of retrieval.

Tuple relational Calculus

A tuple variable is a variable which ranges over particular database relation. That is, every value assigned to tuple variable has same number and type of fields. For example, a simple calculus query can be written as {T | p(T)}.

Here T is a tuple variable and p(T) is a conditional expression for it. The query will return the result set which satisfies the selection condition.

Atoms

For the construction of the formulae we will assume an infinite set *V* of tuple variables. The formulas are defined given a database schema *S* = (*D*, *R*, *h*) and a partial function *type* : *V* -> 2* ^{C}* that defines a

*type assignment*that assigns headers to some tuple variables. We then define the

*set of*atomic formulas

*A*[

*S*,

*type*] with the following rules:

- if
*v*and*w*in*V*,*a*in*type*(*v*) and*b*in*type*(*w*) then the formula*“v*.*a*=*w*.*b”*is in*A*[*S*,*type*], - if
*v*in*V*,*a*in*type*(*v*) and*k*denotes a value in*D*then the formula “*v*.*a*=*k*“ is in*A*[*S*,*type*], and - if
*v*in*V*,*r*in*R*and*type*(*v*) =*h*(*r*) then the formula “*r*(*v*)” is in*A*[*S*,*type*].

Examples of atoms are:

- (
*t*.age =*s*.age) —*t*has an age attribute and*s*has an age attribute with the same value - (
*t*.name = “Codd”) — tuple*t*has a name attribute and its value is “Codd” - Book(
*t*) — tuple*t*is present in relation Book.

The formal semantics of such atoms is defined given a database *db* over *S* and a tuple variable binding *val* : *V* -> *T _{D}* that maps tuple variables to tuples over the domain in

*S*:

- “
*v*.*a*=*w*.*b”*is true if and only if*val*(*v*)(*a*) =*val*(*w*)(*b*) - “
*v*.*a*=*k*” is true if and only if*val*(*v*)(*a*) =*k* - “
*r*(*v*)” is true if and only if*val*(*v*) is in*db*(*r*)

**Formulas**

The atoms can be combined into formulas, as is usual in first-order logic, with the logical operators (and), (or) and ¬ (not), and we can use the existential quantifier (∃) and the universal quantifier () to bind the variables. We define the *set of formulas* *F*[*S*,*type*] inductively with the following rules:

- every atom in
*A*[*S*,*type*] is also in*F*[*S*,*type*] - if
*f*_{1}and*f*_{2}are in*F*[*S*,*type*] then the formula “*f*_{1}*f*_{2}” is also in*F*[*S*,*type*] - if
*f*_{1}and*f*_{2}are in*F*[*S*,*type*] then the formula “*f*_{1}*f*_{2}” is also in*F*[*S*,*type*] - if
*f*is in*F*[*S*,*type*] then the formula “ ¬*f*” is also in*F*[*S*,*type*] - if
*v*in*V*,*H*a header and*f*a formula in*F*[*S*,*type*_{[v->H]}] then the formula - “
*v*:*H*(*f*)” is also in*F*[*S*,*type*], where*type*_{[v->H]}denotes the function that is equal to*type*except that it maps*v*to*H*, - if
*v*in*V*,*H*a header and*f*a formula in*F*[*S*,*type*_{[v->H]}] then the formula - “
*v*:*H*(*f*)” is also in*F*[*S*,*type*]