Basic operators:

1) Select (6)

• Used to select the rows from relation based on condition.

• Consider a relation as:

emp(Id, Name, email, age)

• eg

$age\gt_{20}^{(emp)}$

• Generalized syntax is

$condition^{(R)}$

• Above example displays all rows satisfying the condition.

2) Project (2)

• Used to display only required attributes from a relation.

• Generalized syntax.

$\pi_{a1, a2...an}^{(R)}$

• eg $\pi_{name, age}^{(emp)}$

• Both select and project are u nary operators.

3) Union (U)

• It is used to join tuples from two relations with condition that both relations must have same number of attributes and should have same corresponding domain.

• Generalized syntax:

(R.A. Query 1) U (R.A. Query 2)

For example:

$\pi_{custname}^{(borrower)} \ U \pi_{cusname}^{(depositor)}$

• Above query displays all customers who have taken loan and has an account in bank.

4) Set difference (-)

• It is used to select tuples from one relation which are not there in another relation.

• Syntax:

(R.A.Query 1) - (RA Query 2)

Example:

$\pi_{custname}^{(depositor)} - \pi_{custname}^{(borrower)}$

• The query displays name of all customers who has an count but not taken loan.

5) Cartesian product (x)

• Used to join relations.

• Every tuple of one relation are joined with every tuple of another relation.

• syntax R1 x R2

6) Rename

• Used to temporary rename or refer to a relation by more than one name.

• Syntax.

$\zeta_x \ (E)$

where x is new name for expression E.

Additional operators:

1] set interaction (n)

• used to find out common tuples.

between the two relations.

• syntax:

R1 n R2

Example:

$\pi_{empname}^{(depositor)} \ n \ \pi_{empname}^{(borrower)}$

• It displays name of all customers who has an account as well who has taken loan.

2] Nateral join ( )

• used to join two relations based on one or more common attributes.

For example.

$\pi_{custname,amt}^{(depositor \ account)}$

• It will display name of all customers and balance amount.

3] Division ( $\div$ )

• It is used for queries that include phases 'for all'

For example.

$\pi_{custname,bcname}^{(depositor \ account)}$

$\div$ $\pi_{bcname(\zeta branch \ city = 'Brooklyn'}^{(branch))}$

• It will display all customers who have an account at all branches located in Brooklyn city.

4) Assignment ( $\leftarrow$ )

• Provides a convenient way to express complex queries.

• syntax

temp $\leftarrow$ R.A.Query

Extended relational algebra operations:

1] Generalized projection.

• It is used to extend projection operation by allowing arthrimatic functions.

• syntax

$\pi_{f1,f2....fn}^{(E)}$

• For eg.

$\pi_{empname,sal*101}^{(emp)}$

• It will display employee with 10% more salary.

2] Aggregate functions:

• Takes a collection of values and returns a single value.

• Aggregate functions are: avg, min, max, sum and count.

• syntax.

• for eg

• Will display average salary of all employs.

• will display average salary in every company.

3] Outer join.

• extension of join operation that avoids loss of information.

• computes the join and adds tuples from one relation that does not match tuples in the other relation to the result of join.

• There are 3 types

1] Left outer join.

2] Right outer join.

3] Full outer join.