Relational Algebra
Relational algebra is a procedural query language that accepts instances of relations and returns instances of relations as output. It utilizes operators to execute queries. An operator may be either unary or binary. They receive relations as input and generate relations as output. Relational algebra is applied recursively to a relation, and the intermediate solutions are also considered relations.
The fundamental operations of relational algebra are as follows:
- Select
- Project
- Union
- Set difference
- Cartesian product
- Rename
1. Select Operation ( σ )
It selects tuples from a relation that satisfy a particular condition.
Notation - σp(r)
Where σ is the selection predicate and r represents the relation. P is a prepositional logic formula that may include connectors such as and, or, and not. These terms may utilize relational operators such as − =, ≠, ≥, <, >, and ≤.
For example
σ subject = "database" (Books)
Output - Chooses tuples from books whose subject is 'database'.
σ subject = "database" and price = "450" (Books)
Output - Chooses tuples from books where the subject is 'database' and the price is 450.
σ subject = "database" and price = "450" or year > "2010" (Books)
Output - Chooses tuples from books with the subject 'database' and 'price' of 450, or books published after 2010.
2. Project Operation ( ∏ )
It generates column(s) that satisfy a specific criteria.
Notation - ∏A1, A2, An (r)
Where A1, A2, An are attribute names of relation r .
Duplicate rows are immediately deleted because the relation is a set.
Example ∏subject , author (Books)
Selects and projects columns called subject and author from the Books relationship.
3. Union Operation (∪)
The binary union of two specified relations is defined as −
r ∪ s = { t | t ∈ r or t ∈ s }
Notation - r U s
Where r and s represent either database relations or relation result sets (temporary relations).
To be valid, a union operation needs to meet the following conditions:
- R and S must have the same number of properties.
- Attribute domains should be compatible.
- Duplicate tuples are automatically removed.
∏ author (Books ) U ∏ author ( Articles )
4. Set Difference (-)
The set difference operation produces tuples that are present in one relation but not the other.
Notation - r - s
Finds all tuples that exist in r but not in s.
∏ author ( Books ) - ∏ author ( Articles )
Output Lists the names of authors who have written books but not articles.
5. Cartesian Product (X)
Combine information of two different relations into one.
Notation - r X s
r and s are relations, and their output is defined as −
r X s = {q t | q ∈ r and t ∈ s }
σ author = 'Geeks Help' (Books X Articles)
Output - Returns a connection containing all of Geeks Help's books and articles.
6. Rename Operation (ρ)
Relational algebra produces relations, but without a name. The rename operation lets us rename the output relation. A 'rename' action is symbolized by the tiny Greek letter rho (ρ).
Notation - ρ x (E)
Where the result of expression E is saved with name of x.
Additional Operations are-
- Set intersection
- Assignment
- Natural join
