# DBMS – RELATIONAL ALGEBRA

**DBMS – RELATIONAL ALGEBRA** : Algebra – As we know is a formal structure that contains sets and operations, with operations being performed on those sets. Relational algebra can be defined as procedural query language which is the core of any relational query languages available for the database. It provides a framework for query implementation and optimization. When a query is made internally the relation algebra is being executed among the relations. To perform queries, it uses both unary and binary operators.

**DBMS – RELATIONAL ALGEBRA**

Let us first study the basic fundamental operations and then the other additional operations.

Fundamental operations are-

**Select****Project****Union****Set difference****Cartesian product****Rename**

__Select operation__

__Select operation__

- It performs the operation of selecting particular tuple or a row which satisfies a specific predicate from a relation.
- It is a unary operation.
- Represented by σ
_{p}(r),where σ is selection predicate, r is relation, p is prepositional logic formula that might use connectors like and, or, not. - Examples

- σ
_{name = }_{“}_{Nicholas_sparks}_{”}(Novels), selects tuples from the Novels where the name of the author is Nicholas sparks. - σ
_{name = }_{“}_{Nicholas_sparks}_{”}_{ and price = }_{“}_{300}_{”}(Novels), selects tuples from the Novels where the name of the author is Nicholas sparks and price is 300. - σ
_{name = }_{“}_{Nicholas_sparks}_{”}_{ and price = }_{“}_{300}_{”}_{ or }_{“}_{year}_{”}(Novels), selects tuples from the Novels where the name of the author is Nicholas sparks and price is 300 or those Novels published in a particular year.

__Project Operation__

__Project Operation__

- It projects column(s) which satisfy a particular predicate (given predicate).
- It is a unary operation.
- Represented by Π
_{A1, A2, An ( r ), }where A_{1, }A_{ 2, }A_{n }are the attributes of relation r. - As the relation is set, duplicate rows are automatically eliminated.
- Example – Π
_{name, author }(Novels), selects and projects columns named as ‘name’ and ‘author’ from the relation Novels.

__Union Operation__

__Union Operation__

- It performs the operation of binary union between two relations.
- It is a set operation.
- Represented by r s, where r and s are relations in database.
- The following criteria have to be satisfied for a union operation to be valid, called as union compatibility.

- R and s should have the same degree (same number of attributes in the relation).
- Duplicate tuples are eliminated automatically.
- Domains of the attribute must be compatible. Say if r and s are two relations, then the ith attribute of r should have the same domain as ith attribute of s.

- Example – Π
_{author }(Novels) Π_{author }(Articles), projects the names of the authors who have written either a novel or an article or both.

__Set Difference Operation__

__Set Difference Operation__

- It gives the result as tuples which are present in one relation but not in the other relation.
- It is a binary operation.
- Represented by Π
_{author }(Novels) – Π_{author }(Articles), results in names of the authors who have written Novels but not the Articles.

__Cartesian Product Operation__

__Cartesian Product Operation__

- It performs the function of combining information from two or more relations into one.
- Represented by r Χ s, where r and s are relations.
- Example –
_{‘}_{Nicholas_sparks}_{’}(Novels Χ Articles), provides a relation that shows all books and Articles by Nicholas sparks.

__Rename Operation__

__Rename Operation__

- Results in relational algebra are just the relations without any name, the rename operation allows to rename the output relation.
- Represented by ρ
_{X }(E), where E is a resultant expression with the name given as x. - Consider the example of a relation
_{‘}_{Nicholas_sparks}_{’}(Novels Χ Articles), which outputs all books and Articles by Nicholas sparks, this does not have any name. If required, it can be named as ρ_{newname }[_{Nicholas_sparks}_{’}(Novels Χ Articles)] where newname is the name given to it.

Other operations are

**Intersection**

**Natural join**

**Division**

**Assignment**

__Intersection Operation__

__Intersection Operation__

- It is a set operation, which selects only the common elements from two given relations.
- Represented by r s, where r and s are relations in database.
- Example – Π
_{author }(Novels) Π_{author }(Articles), provides the names of authors who have written both Novels and Articles.

__Natural join operation__

__Natural join operation__

- It is a binary operation, combination of some selections and forms cartesian product of its two arguments.
- Forms cartesian product, then performs selection forcing equality on the attributes appearing in both relations and ultimately removes duplicate attributes.
- Represented by r |Χ| s, where r and s are relations.

__Division operation__

__Division operation__

- This outputs the result as restriction of tuples in one relation to the name of attributes unique to it. In other words, restriction of tuples in the header of r but not in the header of s, for which it also indicates all combinations of tuples in r are present in s.
- Represented by r / s, where r and s are relations.

__Assignment operation__

__Assignment operation__

- It is similar to assignment operator in programming languages.
- Denoted by ←
- It is useful in the situation where it is required to write relational algebra expressions by using temporary relation variables.
- The database might be modified if assignment to a permanent relation is made.

So these were the different types of operations in relational algebra.

## Leave a Reply