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:
- Basic Operators
- 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 |