Introduction of Relational Algebra in DBMS

Relational Algebra is a formal language used to query and manipulate relational databases, consisting of a set of operations like selection, projection, union, and join. It provides a mathematical framework for querying databases, ensuring efficient data retrieval and manipulation. Relational algebra serves as the mathematical foundation for query SQL.

• It provides a clear, structured approach for formulating database queries.
• Helps in understanding and optimizing query execution plans for better performance.
• SQL queries are based on relational algebra operations, making it essential for learning SQL.
• Enables complex queries, like joins and nested queries, that are critical for working with large datasets.

Key Concepts in Relational Algebra:

• Relations: In relational algebra, a relation is a table that consists of rows and columns, representing data in a structured format. Each relation has a unique name and is made up of tuples.
• Tuples: A tuple is a single row in a relation, which contains a set of values for each attribute. It represents a single data entry or record in a relational table.
• Attributes: Attributes are the columns in a relation, each representing a specific characteristic or property of the data. For example, in a “Students” relation, attributes could be “Name”, “Age”, and “Grade”.
• Domains: A domain is the set of possible values that an attribute can have. It defines the type of data that can be stored in each column of a relation, such as integers, strings, or dates.

Operators in Relational Algebra

Relational algebra consists of various operators that help us fetch and manipulate data from relational tables in the database to perform certain operations on relational data. The fundamental operators in relational algebra, such as selection, projection, and join, are essential for querying and transforming data efficiently within a relational database.

Operators in Relational Algebra

Basic Operators

Basic operators are fundamental operations that include selection (σ), projection (π), union (U), set difference (−), Cartesian product (×), and rename (ρ). These operators are used to manipulate and retrieve data from relational databases.

1. Selection(σ): Selection Operation is basically used to filter out rows from a given table based on certain given condition. It basically allows you to retrieve only those rows that match the condition as per condition passed during SQL Query.

Example:

Consider the relation R as follows:

For the above relation,

σ_(c>3)(R) will select the tuples which have c more than 3.

Output Table:

Note: The selection operator only selects the required tuples but does not display them. For display, the data projection operator is used. 

2. Projection(π): While Selection operation works on rows, similarly projection operation of relational algebra works on columns. It basically allows you to pick specific columns from a given relational table based on the given condition and ignoring all the other remaining columns.

Example: Suppose we want columns B and C from Relation R.

π_(B,C)(R) will show following columns.

Note: By Default, projection removes duplicate data.   

3. Union(U): Union Operator is basically used to combine the results of two queries into a single result. The only condition is that both queries must return same number of columns with same data types. Union operation in relational algebra is the same as union operation in set theory.

Example: Consider the following table of Students having different optional subjects in their course.

FRENCH

GERMAN

For the above relations, the query:

π_{(Student_Name)}(FRENCH) U π_{(Student_Name)}(GERMAN)

will give us output table:

Note: The only constraint in the union of two relations is that both relations must have the same set of Attributes.

4. Set Difference(-): Set difference basically provides the rows that are present in one table, but not in another tables. Set Difference in relational algebra is the same set difference operation as in set theory.

Example: From the above table of FRENCH and GERMAN, Set Difference is used as follows:

π(_{Student_Name)}(FRENCH) – π_{(Student_Name)}(GERMAN)

Note: The only constraint in the Set Difference between two relations is that both relations must have the same set of Attributes.   

5. Rename(ρ): Rename operator basically allows you to give a temporary name to a specific relational table or to its columns. It is very useful when we want to avoid ambiguity, especially in complex Queries. Rename is a unary operation used for renaming attributes of a relation.

Table R:

ρ_(D/B)R will rename the attribute ‘B’ of the relation by ‘D”.

Output Table:

6. Cross Product(X): Cartesian product Operator combines every row of one table with every row of another table, producing all the possible combination. It’s mostly used as a precursor to more complex operation like joins.

Example: Cross-product between two relations. Let’s say A and B, so the cross product between A X B will result in all the attributes of A followed by each attribute of B. Each record of A will pair with every record of B.

Relation A:

Relation B:

Relation A X B:

Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘n*m’ tuples.   

Derived Operators

Derived operators are built using basic operators and include operations like join, intersection, and division. These operators help perform more complex queries by combining basic operations to meet specific data retrieval needs.

1. Join Operators: Join operations in relational algebra combine data from two or more relations based on a related attribute, allowing for more complex queries and data retrieval.

Types of Joins in Relational Algebra:

(a) Inner Join
An inner join combines rows from two relations based on a matching condition and only returns rows where there is a match in both relations. If a record in one relation doesn’t have a corresponding match in the other, it is excluded from the result. This is the most common type of join.

• Conditional Join: A conditional join is an inner join where the matching condition can involve any comparison operator like equals (=), greater than (>), etc.
  Example:
  Joining Employees and Departments on DepartmentID where Salary > 50000 will return employees in departments with a salary greater than 50,000.

• Equi Join: An equi join is a type of conditional join where the condition is specifically equality (=) between columns from both relations.
  Example:
  Joining Customers and Orders on CustomerID where both relations have this column, returning only matching records.

• Natural Join: A natural join automatically combines relations based on columns with the same name and type, removing duplicate columns in the result. It’s a more efficient way of joining.
  Example:
  Joining Students and Enrollments where StudentID is common in both, and the result contains only unique columns.

(b) Outer Join
An outer join returns all rows from one relation, and the matching rows from the other relation. If there is no match, the result will still include all rows from the outer relation with NULL values in the columns from the unmatched relation.

• Left Outer Join: A left outer join returns all rows from the left relation and the matching rows from the right relation. If there is no match, the result will include NULL values for the right relation’s attributes.
  Example:
  Joining Employees with Departments using a left outer join ensures all employees are listed, even those who aren’t assigned to any department, with NULL values for the department columns.

• Right Outer Join: A right outer join returns all rows from the right relation and the matching rows from the left relation. If no match exists, the left relation’s columns will contain NULL values.
  Example:
  Joining Departments with Employees using a right outer join includes all departments, even those with no employees assigned, filling unmatched employee columns with NULL.

• Full Outer Join: A full outer join returns all rows when there is a match in either the left or right relation. If a row from one relation does not have a match in the other, NULL values are included for the missing side.
  Example:
  Joining Customers and Orders using a full outer join will return all customers and orders, even if there’s no corresponding order for a customer or no customer for an order.

Learn more about Joins here.

2. Set Intersection(∩) : Set Intersection basically allows to fetches only those rows of data that are common between two sets of relational tables. Set Intersection in relational algebra is the same set intersection operation in set theory.

Example: Consider the following table of Students having different optional subjects in their course.

Relation FRENCH:

Relation GERMAN:

From the above table of FRENCH and GERMAN, the Set Intersection is used as follows:

π_{(Student_Name)}(FRENCH ∩ π_{(Student_Name)}(GERMAN)

Output Table:

Note: The only constraint in the Set Difference between two relations is that both relations must have the same set of Attributes.  

3. Division (÷): Division is used to find tuples in one relation that are related to all tuples in another relation. It’s typically used for “for all” queries.

Example: We have two tables

Student_Course (Dividend Table):

Course (Divisor Table):

Query is to find students who are enrolled in all courses listed in the Course table. In this case, students must be enrolled in both C1 and C2.

Student_Course_{(Student_ID, Course_ID)}÷ Course_{(Course_ID)}

Output Table:

Relational Calculus

Relational calculus is a non-procedural query language used in the context of relational algebra. It focuses on what data to retrieve, rather than how to retrieve it, making it different from relational algebra, which is procedural. In relational calculus, queries are expressed using logical formulas that describe the desired result, without specifying the exact steps to get there.

There are two types of Relational Calculus

1. Tuple Relational Calculus(TRC)
2. Domain Relational Calculus(DRC)

Following are the Previous Year’s Gate Questions 

GATE | GATE CS 2012 | Question 50
GATE | GATE CS 2012 | Question 41

Introduction of Relational Algebra in DBMS – FAQs

What is a relational database ?

A relational database is basically a type of database where data is stored in the form of tables, which look similar to spreadsheets. Each table has rows and columns: rows represent individual records ( like a person or an ordered data ) & columns represent attributes ( like name, and address). Since they are connected with each other through shared data, it makes it allowing for more organized storage and easier retrieval of information

What is the relational model ?

Relational model is the theory behind how relational database are organized. In this model, data is organized into tables, with each table representing a specific type of information ( like customer or order ). The model also defines how these tables relate to one another, typically through shared keys ( based on primary key or candidate keys) or identifier.

What is the main difference between Selection Operation and Projection Operation ?

Selection Operation is basically used to filter out rows based on certain condition, While on other hand the projection operations of Relational algebra selects the specific columns from a table.

How is cartesian product different from a Join Operations ?

Cartesian product is used to form all the possible combinations of rows from two tables , while Join typically used to combine rows based on a related column (like foreign key)

Why is Relational Algebra important ?

Relational algebra helps in designing efficient database queries. It provides a clear framework for understanding how data is retrieved and manipulated in relational databases.

kartik

Follow

Improve

Article Tags :

• DBMS
• DBMS-Relational Algebra