In this chapter, you will learn about the various relational algebras that are used in maintaining a database. In particular, we concentrate on the relational algebra as defined by Codd in the year 1971 as the basis for relational languages. Informally, here you will understand the relational algebra as a (high-level) procedural language: which can be used to tell the DBMS how to build a new relation from one or more relations in the database.
What is Relational Algebra?
The relational algebra is a theoretical procedural query language which takes an instance of relations and does operations that work on one or more relations to describe another relation without altering the original relation(s). Thus, both the operands and the outputs are relations. So the output from one operation can turn into the input to another operation, which allows expressions to be nested in the relational algebra, just as you nest arithmetic operations. This property is called closure: relations are closed under the algebra, just as numbers are closed under arithmetic operations.
The relational algebra is a relation-at-a-time (or set) language where all tuples are controlled in one statement without the use of a loop. There are several variations of syntax for relational algebra commands, and you use a common symbolic notation for the commands and present it informally.
The primary operations of relational algebra are as follows:
- Select
- Project
- Union
- Set different
- Cartesian product
- Rename
Select Operation (σ)
It selects tuples that satisfy the given predicate from a relation.
Notation − σp(r)
Here σ stands for selection predicate, and r stands for relation, and p is a propositional logic formula which may use connectors like and, or, and not.
σ predicate(R): This selection operation functions on a single relation R and describes a relation that contains only those tuples of R that satisfy the specified condition (predicate).
Example:
σteacher = "database"(Names)
Output - It selects tuples from names where the teacher is 'database.'
Project Operation (∏)
The Projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.
Produce a list of salaries for all staff, showing only the staffNo, fName, lName, and
salary details.
ΠstaffNo, fName, lName, salary(Staff)
In the below-mentioned example, the Projection operation defines a relation that contains only the designated Staff attributes staffNo, fName, lName, and salary, in the specified order. The result of this operation is shown in the figure below
Union Operation
For R ∪ S, The union of two relations, R and S, defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.
For a union operation to be applied, the following rules must hold −
- r and s must have the same quantity of attributes.
- Attribute domains must be compatible.
- Duplicate tuples get automatically eliminated.
Set difference
For R − S The Set difference operation defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible.
Example:
∏ writer (Nobels) − ∏ writer (papers)
Cartesian product
For R × S, the Cartesian product operation defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.
Example:
σwriter = 'gauravray'(Articles Χ Notes)
Join Operations
Typically, you want only combinations of the Cartesian product which satisfy certain situations, and so you can normally use a Join operation instead of the Cartesian product operation. The Join operation, which combines two relations to form a new relation, is one of the essential operations in the relational algebra. There are various types of Join operation, each with subtle differences, some more useful than others:
- Theta join
- Equijoin (a particular type of Theta join)
- Natural join
- Outer join
- Semijoin
Rename Operation (ρ)
The results of relational algebra are also relations but without any name. The rename operation provides database designers to rename the output relation. The rename-operation is denoted using a small Greek letter rho (ρ).
It is written as:
ρ x (E)
