Relational Algebra

 Topic Outline

Relational Algebra

• Union
• Difference
• Intersection
• Product
• Projection
• Join
• Division

Relational Algebra

Relational tables are sets. Relational tables consist of rows and columns. Table rows are considered elements of the set. Operations can be performed on sets and relational tables. Operations are as follows:

(1) Union

It combines the rows of both tables to produce a result. If any table contains the same rows, the duplicate rows are eliminated. In union operation, operation relational tables must have the same column numbers. The union of tables A and B is written as AUB. If A set is A={a, b, c, d} and B set is B={a, d, e, f} then result of C= AUB will return C={a, b, c, d, e, f}. 

Example

We have two relational tables A and B which are together by the ing Union operator.

Table (A)

X	Y	Z
1	A	5
2	B	10
3	C	15
4	D	20

Table (B)

X	Y	Z
4	D	20
5	E	25
6	F	30
7	G	35

 

AUB

X	Y	Z
1	A	5
2	B	10
3	C	15
4	D	20
5	E	25
6	F	30
7	G	35

(2) Difference

In the Difference operation, the third table consists of those rows that are present in the first table but not in the second table. Difference operations of A and B are written as A-B. Order of subtraction is important because A-B is not the same as B-A. So A-B and B-A are not the same or equal.

Example

We have two relational tables A and B. So the result of A-B and B-A are the not same.

A-B

X	Y	Z
1	A	5
2	B	10
3	C	15

B-A

X	Y	Z
5	E	25
6	F	30
7	G	35

(3) Intersection

In intersection operation, the third table consists of common (same) rows from two relational tables. Intersection operations of A and B are written as an.

Example

We have two relations A and B. So only one common row from A and B relational tables.

AnB

X	Y	Z
4	D	20

(4) Product

The product of tables is the concatenation of each row in one table with each row in the second table. This is also a catcalled cartesian product. Product of A table with m rows and B table with n rows is the C table with mxn rows. Product operation is written as AXB. 

AXL

X1	Y1	Z1	X2	Y2	Z2
1	A	5	4	D	20
1	A	5	5	E	25
1	A	5	6	F	30
1	A	5	7	G	35
2	B	10	4	D	20
2	B	10	5	E	25
2	B	10	6	F	30
2	B	10	7	G	35
3	C	15	4	D	20
3	C	15	5	E	25
3	C	15	6	F	30
3	C	15	7	G	35
4	D	20	4	D	20
4	D	20	5	E	25
4	D	20	6	F	30
4	D	20	7	G	35

(5) Projection

Projection operation retrieves a subset of the column from the table. It removes duplicated rows from the table. It displays a specific column from the relation.

(6) Join

It combines the selection, product, possibly, and projection. It combines data from one row of the table with rows from another table.

Table (A)

X	Y	Z
1	A	5
2	B	10
3	C	15
4	D	20

Table (B)

M	N
1	100
4	200
5	300
6	400

• Equijoin

If the join criteria are based on equality of column value is called equijoin.

X	Y	Z	M	N
1	A	5	1	100
4	D	20	4	200

• Natural Join

It is an equijoin with duplicate columns removed.

X	Y	Z	N
1	A	5	100
4	D	20	200

(7) Division

In division operation results in column values in one table for which there are matching column values corresponding to row obtain another table.

Table (A)

X	Y	Z
101	A	5
201	B	5
301	C	5
201	B	10
101	A	15
301	C	15

Table (B)

X	Y
101	A
201	B
301	C

 Result

Z

5

15