Special relational operations | Relational Model

SPECIAL RELATIONAL OPERATIONS

These includes operations like
 

1. Selection
2. Projection
3. Join
4. Division

1. Selection-To identify a set of tuples which is a part of a relation and to extract only these tuples out. The select operation selects tuples that satisfy a given predicate or condition. It is also known as restriction operation. 

It is a unary operation defined on a single relation.

·   It is denoted as s.

REPRESENTATION-  s  (CONDITION) (RELATION)

EXAMPLE- Consider a relation "EMP"

ID	NAME	SALARY
1	Rahul	5000
2	Arjun	10000
3	Abay	4000
4	Avinash	8000
5	Anu	10000

Ex-1  Select all employees  whose salary>=8000

s  (salary>8000) (EMP)

OUTPUT- 

ID	NAME	SALARY
2	Arjun	10000
4	Avinash	8000
5	Anu	10000

2. Projection- It results in a new relation that contains a subset of the columns of a relation and eliminate any duplicate node.

·         It is a unary operation defined on a single relation

 It is denoted as ?.

REPRESENTATION-  ?   (COLUMN) (RELATION)

EX-2  select all employees name and their salary from the relation "EMP"

 ?   (NAME,SALARY) (EMP)

OUTPUT-

NAME	SALARY
Rahul	5000
Arjun	10000
Abay	4000
Avinash	8000
Anu	10000

3. Join- To restrict the no of rows obtained from the Cartesian product we used joins.Join is performed on 2 relations having one or more attributes in common and they should have some datatype.It is a binary operation and a combination of certain selections and a Cartesian product into one operation.

·         It is denoted as |X| .

·         It is associative.

REPRESENTATION- (P)| X|  (JOIN CONDITION) (Q)

where P,Q are the names of the relation and where condition is of the form  pi  Q  qi

where pi is the attributes of the relation P and qi is the attribute of the relation Q.

EXAMPLE- Consider 2 relations

1 relation "EMP"

EMP_ID	ENAME	DEPTID	SAL
123	Balwinder	1	5000
122	Simar	2	10000
134	Anmol	3	20000

2 relation "DEPT"

DEPTNO	DNAME
1	CSE
2	ECE
3	CSE

Ex- Display the employee information with department name

EMP | X|  (DEPID=DEPTNO) DEPT

OUTPUT-

EMP_ID	ENAME	DEPTID	SAL	DNAME
123	Balwinder	1	5000	CSE
122	Simar	2	10000	ECE
134	Anmol	3	20000	CSE

4. Division- The division operator divides a dividend relation "r" of degree (m+n) by a divisor relation "s" of degree (m) and produces a resultant relation of degree "n".

REPRESENTATION-  Let r(R) and s(S) be relations

r ÷ s: -

 The result consists of the restrictions of tuples in r to the attribute names unique to R, i.e. in the Header of r but not in the Header of s, for which it holds that all their combinations with tuples in s are present in r.

EXAMPLE-     Consider 2 relations "r" and "s"

Relation"r"

S.NO	P.NO
S1	P1
S1	P2
S3	P1
S4	P2
S2	P3
S2	P1

Relation "s"

P.NO

P1

Now   r ÷ s   

OUTPUT-

S.NO

S1

S3

S2