Relational Algebra in DBMS

Relational Algebra is a widely used procedural query language, which takes instances of one or more relation as an input and generates a new relation as an output. It uses a different set of operators (like unary or binary operators) and operands to perform queries. EF Codd created the relational algebra for relational database. 
The relational algebra provides a query language in which all tuples or rows of data are accessed in one statement without the use of loops, etc.

The operators that are used to perform relational algebra are:

1. Basic Operators
2. Derived Operators

Basic Operators

• SELECT (?)
• PROJECT (?)
• RENAME ( ? )
• UNION (?)
• DIFFERENCE (-)
• CARTESIAN PRODUCT ( x )

Project Operator(?)

The project operator is represented by the pie(?) symbol, which is used to access the values of specified columns or attributes from a table or a relation.

Syntax: ? (R)

In the above syntax, R is the name of a relation or table and C1, C2 C3…. CN are the attributes or columns name of the relation.

Example:

Table Student:

Rollno	Name	Age
101	A	20
102	B	21
103	C	20
104	D	20

Query: Retrieve the Rollno from the above table Student

• ?_Rollno(Student)

Output:

Rollno

101

102

103

104

Select Operator(?)

The select operator is represented by the sigma(?) symbol, which is used to fetch the tuples (rows) from the relation that satisfies the selection condition. We cannot fetch the attributes of a relation using this command.

Syntax: ?(R)

In the above syntax, R is a relation or name of a table, and the condition is a propositional logic which uses the relational operators like ?, <,=,>, ?.

Example:

Table Student:

Rollno	Name	Age
101	A	20
102	B	21
103	C	20
104	D	20

Query: Retrieve the name of Rollno 102 from the above table Student

• ?_Name(? _Rollno=102(Student))

Output:

Name

B

Rename Operator( ? )

The rename operator is denoted by the rho() symbol. It is used to rename a relation or an attribute of the relation.

Syntax:  ? (new_Relation_name,old_Relation_name)

Example:

Table Student:

Rollno	Name	Age
101	A	20
102	B	21
103	C	20
104	D	20

Query: Rename the name of Student relation to student1.

• (student1,Student)

Union Operator (?)

The union operator is represented by the (?)symbol, which is used to display all the tuples (rows) from two relations. It is a binary operation because to apply this operation user must require a minimum of two relations.

For a valid union operation, both the table should have same number of attributes to combine them. Duplicate data are eliminated during the union operation.

Syntax: R1 U R2

In the above syntax, R1 is the name of the first relation or table, and R2 is the name of the second relation or table.

Example:

Table Student:                                                                    

Rollno	Name
101	A
102	B
103	C
104	D

Table Employee:

Employee_id	Name
201	P
202	Q
203	R
204	S

Query: Combine the table Student with another table Employee

• (Student)U(Employee)

Output:

Rollno	Name
101	A
102	B
103	C
104	D
201	P
202	Q
203	R
204	S

Set Difference (-)

The set difference operation is represented by the minus (-) sign, which is used to display all the tuples (rows) that are in the first relation but not in the second relation.

Syntax: R1 – R2

In the above syntax, R1 is the name of the first relation or table, and R2 is the name of the second relation or table.

Example:

Table Student:                                                                    

Rollno	Name
101	A
102	B
103	C
104	D

Table Employee:

Employee_id	Name
101	A
202	Q
103	C
204	S

• (Student)-(Employee)

Output:

Rollno	Name
102	B
104	D

Cartesian Product (x)

It is represented by the cross (x) symbol, which is used to combine each tuple in one relation with each tuple in another relation. This operation is also known as a cross product.

Syntax: R1 x R2

In the above syntax, R1 is the name of the first relation or table, and R2 is the name of the second relation or table. If the relation R1 contains x columns and m rows, and relation R2 contains y columns and n rows, the resultant table will contain x+y columns and m*n rows.

Example:

Table T1:                                                                    

Column1	Column2	Column3
101	A	12
102	B	21
103	C	20
104	D	31

Table T2:

columnA	ColumnB
101	A
202	Q

• (T1)X(T2)

Output:

Column1	Column2	Column3	columnA	ColumnB
101	A	12	101	A
101	A	12	202	Q
102	B	21	101	A
102	B	21	202	Q
103	C	20	101	A
103	C	20	202	Q
104	D	31	101	A
104	D	31	202	Q

Derived Operators

Derived operators are the operators which can be obtained from the basic operators and consists of following three types:

• INTERSECTION ( ? )
• JOINS (?)
• DIVISION(/)

Intersection Operator (?)

The intersection operator is represented by the (?) symbol, which is used to display all the tuples or rows of common attributes from two relations. It is also a binary operator.

Syntax: R1 ?  R2

In the above syntax, R1 is the name of the fist relation or table, and R2 is the name of the second relation or table.

Example:

Table Student:                                                                    

Rollno	Name
101	A
102	B
103	C
104	D

Table Employee:

Employee_id	Emp_Name
101	A
202	Q
103	C
204	S

• ? Name (Student) ? ? Emp_Name (Employee) 

Output

Name

A

C

Joins

A join operation is represented by ?(Bowtie) Symbol. Joins are the combination of related tuples from two different relations or tables into a single type. It is similar to a Cartesian product. In Cartesian product operation, a user gets all the possible combinations of relations. But in join operation, a user gets only those combinations that satisfy some matching conditions between two relations. A relation can also join to itself, which is called as a self-join.

Division Operator ( / )

The division operator is represented by the division (/) symbol, which can be applied if and only if:

• Attributes of a relation B is a proper subset of attributes of a relation A.
• The resultant relation will have the attributes = (All attributes of relation A – All attributes of relation B).
• The relation or table returned by the division operator will display those tuples from relation A, which are associated with every B’s relation tuple.

Example:
Table: Enrolled

Sid	Cid
S1	C1
S2	C1
S1	C2
S3	C2

Table: Course

Cid

C1

C2

Query: Retrieve the student_id of students who enrolled in every course.

• ?_Sid (Enrolled) – (?_Sid ( (?_Sid(Enrolled)) x ( ?_Cid (Course) ) ) – (Enrolled) )

Output:

Sid

S1