02 - Relational Operators

The relational model is based on the principles of the relational algebra. The relational algebra is a collection of operators that operate on relations. Each operator takes one or two relations as input and produces the new relation as its output.

The lists of operators that have been defined as relational operators with detailed explanation are mentioned in this chapter.

2.1 Restrict Operator

The restrict operator extracts the specified tuples or rows from the given relation based on a condition. The following diagram shows how restrict operator can extract the specified tuples or rows from a given relation.

In the above diagram, the black colored rows represent the extracted rows from the relation by using the operator.

Consider an example of STUDENT table. It has attributes as ROLLNO, STUDNAME, AGE and GENDER.

The following table describes the columns of the STUDENT table.

ROLL NO

STUDNAME

AGE

GENDER

101

Jerry

21

Male

103

Mark

24

Male

105

Sara

27

Female

106

Anthony

24

Male

108

Nancy

25

Female

109

Peter

26

Male

The condition is to extract the tuples whose age is more than 25. The following table describes the relation.

ROLL NO

STUDNAME

AGE

GENDER

105

Sara

27

Female

109

Peter

26

Male

2.2 Project Operator

The project operator is used to extract the specified attributes or columns from a given relation. The following diagram represents how project operator can extract specified attributes or columns from a given relation.

In the diagram, the black colored represent the extracted columns by using the project operator.

User can use the PROJECT operator it select some Age and STUDNAME from the STUDENT table. Apply the operator on the above mentioned STUDENT table. The output is as shown below:

STUDNAME

AGE

Jerry

21

Mark

24

Sara

27

Anthony

24

Nancy

25

Peter

26

2.3 Product Operator

The product operator joins the two relations such that every tuple of the first relation is matched with every tuple of the second relation. To be product compatible, the two relations must have common attribute. The following diagram shows the example of the product operator.

The product operator creates the Cartesian product between the two tables. Consider a TEACHER table which contains two attributes as T_CODE and NAME. Table BATCH contains BATCH_CODE and T_CODE. The T_CODE is the common attribute between the two tables.

TEACHER

T_CODE

NAME

11001

Catherine

11002

Mac

11003

Olive

BATCH

T_CODE

BATCH_CODE

11001

B001

11002

B002

11003

B003

The following table lists the product of the TEACHER and BATCH tables with all the possible combinations of their tuples.

T_CODE

NAME

BATCH_CODE

T_CODE

11001

Catherine

B001

11001

11001

Catherine

B002

11002

11001

Catherine

B003

11003

11002

Mac

B001

11001

11002

Mac

B002

11002

11002

Mac

B003

11003

11003

Olive

B001

11001

11003

Olive

B002

11002

11003

Olive

B003

11003

2.4 Union Operator

The union operator builds a relation from tuples appearing in either or both of the specified relations. The following diagram represents the union operator.

To be union compatible, the two tables must have same type of attributes. Consider the two tables as A and B.

A

ROLLNO

NAME

101

Anthony

102

Nancy

B

ROLLNO

NAME

101

Anthony

104

Susan

The table A contains roll no and names of the students whose principle subject is computer science. Table B contains roll no and names of the students whose principle subject is mathematics. These tables are union compatible because they have the same type of attribute.

The following table shows the union operation on table A and B.

ROLLNO

NAME

101

Anthony

102

Nancy

104

Susan

2.5 Intersect Operator

The intersect operator builds the relation containing tuples that appear in both the relations. The following diagram represents the intersect operator.

Consider table P and Q.

P

ROLLNO

NAME

101

Anthony

102

Nancy

Q

ROLLNO

NAME

101

Anthony

103

Peter

Anthony is doing double major and hence his name appears in both the tables. An intersect operation on both the tables P and Q extracts the common rows to both the relation.

The following table shows the intersect result.

A INTERSECT B

ROLLNO

NAME

101

Anthony

2.6 Divide Operator

The divide operator takes two relations and builds another relation consisting of values of an attribute of one relation that match all the values in the other relation. The following diagram shows the working of the operator.

Consider an example of STUDENT. It contains the attributes as NAME and COURSECD. There is another table as COURSE, containing COURSECD attribute. It is common attribute in both the tables. Applying the divide operator on both the tables, the result will be the matching values of the relation, COURSE.

STUDENT

NAME

COURSECD

Anthony

A21

Anthony

D21

Anthony

C60

Nancy

H10

COURSE

COURSECD

A21

D21

C60

RESULT OF DIVIDE

NAME

Anthony

2.7 Difference Operator

The difference operator builds a relation of tuples appearing in the first but not in the second of the two specified relations. The following diagram represents the difference operator.

The following set of tables shows the difference operation on tables X and Y.

X

ROLLNO

NAME

101

Anthony

103

Nancy

Y

ROLLNO

NAME

104

Nick

103

Nancy

X-Y

ROLLNO

NAME

103

Nancy

2.8 Join Operator

The join operator builds a relation from two specified relations. The relation consists of all possible combinations of tuples, one from each relation that satisfies the specified condition. The operation requires a common attribute. The following diagram represents the join operation.

Consider an example of two tables as X and Y. Table X contains roll no and course codes. Table Y contains ID of teachers and the course code they teach. The join operation on these tables is as shown below:

X

ROLL NO

COURSECD

101

A21

102

D21

103

C67

104

D21

105

C67

Y

T_ID

COURSECD

109

A21

108

D21

107

C67

106

A21

X JOIN Y

ROLL NO

T_ID

COURSECD

101

109

A21

102

108

D21

103

107

C67

104

108

D21

105

107

C67

Like us on Facebook