Five basic relational operations can be categorized into:-

- Unary: Select, Project

- Binary: Union, Cartesian Product, Difference

SELECT Operation

It selects certain rows of a table using a predicate or condition

It works on the table horizontally

Resulting new table has all attributes of input table but only rows that match specified criteria

This means new and old table has same degree

Written as:

Small Sigma subscript (condition) (Relation)

Condition operators could be: <,>,<=,>=.<>. Larger predicates can be composed using ^ and v

It is commutative operation i.e. Two conditions to select rows from a table can be applied in any order

E.g. Small sigma(current sem>3)(STUDENT)

PROJECT Operation

It extracts specified attributes of a table, eliminating duplicates being a set

It works on the table vertically i.e. Produces vertical subset of the input relation

Resulting table has some attributes but all rows of the input relation

Written as:

Big Pie subscript (fields reqd)(Relation)

E.g. Big Pie subscript( facId, salary)(FACULTY)

Composition of Relational Operators

Select and Project can be applied in nested form to produce new relation that contains selected attributes of rows matching the specified condition from a table

E.g. facName, deptt of all assistant prof from FACULTY table

Sequence of applying operators is important. Applying project first in above case will not have input for select operation

UNION Operation

It produces new relation from two relations R and S such that resulting table has tuples which are either in R or S or both

Since it is set operations, so no duplicate rows

R and S should be Union compatible:-

Both should have same number of attributes i.e. R and S should have same degree

Corresponding attributes should have same domain

Corresponding attributes should have same name

Union operation is commutative i.e. R U S is same as S U R

E.g. All Courses run at both campuses of a university

INTERSECTION Operation

It produces new relation from two relations R and S that has rows common to both relations

The two input relations should be union compatible

Intersection operation is also commutative

E.g. Courses common to both campuses of a university

SET DIFFERENCE Operation

It produces new relation from R and S such that it contains all tuples that appear in R and do not appear in S

The two input relations should be union compatible

Difference operation is not commutative

E.g. Courses run exclusively at campus R

CARTESIAN PRODUCT Operation

The two input relations need not be union compatible

Cartesian product of two relations R and S with m columns and C rows & n columns and D rows will contain all attributes of R and S and all rows of two tables will be merged.

Degree of new table will be n+m

New table will contain C*D rows

This operation is both commutative and associative

E.g. Cartesian product of COURSE and STUDENT relations

- Unary: Select, Project

- Binary: Union, Cartesian Product, Difference

SELECT Operation

It selects certain rows of a table using a predicate or condition

It works on the table horizontally

Resulting new table has all attributes of input table but only rows that match specified criteria

This means new and old table has same degree

Written as:

Small Sigma subscript (condition) (Relation)

Condition operators could be: <,>,<=,>=.<>. Larger predicates can be composed using ^ and v

It is commutative operation i.e. Two conditions to select rows from a table can be applied in any order

E.g. Small sigma(current sem>3)(STUDENT)

PROJECT Operation

It extracts specified attributes of a table, eliminating duplicates being a set

It works on the table vertically i.e. Produces vertical subset of the input relation

Resulting table has some attributes but all rows of the input relation

Written as:

Big Pie subscript (fields reqd)(Relation)

E.g. Big Pie subscript( facId, salary)(FACULTY)

Composition of Relational Operators

Select and Project can be applied in nested form to produce new relation that contains selected attributes of rows matching the specified condition from a table

E.g. facName, deptt of all assistant prof from FACULTY table

Sequence of applying operators is important. Applying project first in above case will not have input for select operation

UNION Operation

It produces new relation from two relations R and S such that resulting table has tuples which are either in R or S or both

Since it is set operations, so no duplicate rows

R and S should be Union compatible:-

Both should have same number of attributes i.e. R and S should have same degree

Corresponding attributes should have same domain

Corresponding attributes should have same name

Union operation is commutative i.e. R U S is same as S U R

E.g. All Courses run at both campuses of a university

INTERSECTION Operation

It produces new relation from two relations R and S that has rows common to both relations

The two input relations should be union compatible

Intersection operation is also commutative

E.g. Courses common to both campuses of a university

SET DIFFERENCE Operation

It produces new relation from R and S such that it contains all tuples that appear in R and do not appear in S

The two input relations should be union compatible

Difference operation is not commutative

E.g. Courses run exclusively at campus R

CARTESIAN PRODUCT Operation

The two input relations need not be union compatible

Cartesian product of two relations R and S with m columns and C rows & n columns and D rows will contain all attributes of R and S and all rows of two tables will be merged.

Degree of new table will be n+m

New table will contain C*D rows

This operation is both commutative and associative

E.g. Cartesian product of COURSE and STUDENT relations