**1 Answer**

written 7 months ago by |

**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.