Relational Algebra Introduction

Relational Algebra Fundamentals

• Relational algebra is a fundamental concept in database management systems (DBMS).
• It facilitates the manipulation and retrieval of data from relational databases.
• Relational databases store data in tables, enabling the manipulation and retrieval of this data.
• Relational algebra consists of operations applied to relations (tables).
• It provides a theoretical foundation for relational databases, being primarily a theoretical concept.
• It is purely mathematical, lacking English keywords.
• It offers a set of mathematical operations for data manipulation and retrieval.

Relational Algebra Example

• The example of getting the complete record of a student whose roll number is 21 is demonstrated.
• Before writing an SQL query (e.g., SELECT * FROM student WHERE roll_number = 21), a mathematical operation is written.
• The mathematical operation involves using the Sigma operator with the condition roll_number = 21 and the table name "student."
• The mathematical operation is written on paper before the SQL query to facilitate the process.
• Relational algebra is a theoretical concept and is not executed in MySQL software.

Development and Classification

• Edgar F. Codd developed the relational model and relational algebra.
• Relational algebra is a formula-based procedural query language.
• It requires a step-by-step process to obtain results.
• Relational operations are classified into unary and binary types.
• Unary operations operate on a single table, while binary operations work on multiple tables.

Unary Relational Operations

• Operations performed on a single table.
• Select operation: filters rows based on a condition.
  • The Sigma operator (σ) is used for selection.
  • Syntax: σcondition(relation).
  • Acts similarly to the WHERE clause in SQL queries.
  • The operation will display the entire record.
  • Selection operation will support duplicate values.
• Project operation: selects specific columns, discarding others.
  • The Pi symbol (π) is used for projection.
  • Syntax: πcolumn_names(relation).
  • It can display a single column as output (unlike selection operation).
  • Project operation will not allow duplicate values.
• Rename operation: renames columns or tables.
  • The Row symbol (ρ) is used for renaming.
  • Syntax: ρnew_table_name(old_table_name) or ρnew_column_name(old_column_name).
  • Used to change table names or column names within a table.

Binary Relational Operations: Cartesian Product

• Cartesian product combines all possible pairs of rows from two tables.
• Also known as cross product.
• Denoted by the X symbol.
• Syntax: relation1 X relation2.
• involves combining each record from the first table with every record from the second table.
• The number of records in the result is the product of the number of records in each table.
• It is not mandatory to have similar columns to perform Cartesian products.

Set Operations

• Used to manipulate and combine sets of tuples from tables.
• Classified into Union, Intersection, and Set Difference.
• Performed on multiple tables, making them binary relational operations.
• Union operation: combines rows from two tables into a single table, removing duplicates.
  • Denoted by the U symbol.
  • Syntax: table1 U table2.
  • It will select all rows and will order duplicate values so any particular record repeated it will display only once
• Intersection operation: retrieves only the common rows between two tables.
  • Syntax: table1 ∩ table2.
• Set difference operation: retrieves rows from one table that are not present in another table.
  • Denoted by the - symbol.
  • Syntax: A - B.

Join Operations

• Used to combine rows from multiple tables based on common attributes or conditions.
• Classified into inner joins and outer joins.
• Inner joins include natural join, theta join, and equi join.
• Outer joins include left outer join, right outer join, and full outer join.
• Inner Join: Returns only the rows that have matching values in both tables.
  • Theta Join
    • Syntax: relation1 θ relation2

Theta Join

• Theta join uses operators like =, ≠, <, >, <=, and >= in its condition.
• Syntax: Relation1 join Relation2, with a theta condition (e.g., Relation1 join Relation2 where column1 < column2).
• Tables are converted into a Cartesian product before applying the theta operation.
• The Cartesian product combines each row from the first table with every row from the second table.
• Example: if Table 1 has 2 records and Table 2 has 2 records, the Cartesian product results in 4 records.
• The theta condition is then applied to the Cartesian product to filter the result.
• Only records that satisfy the theta condition are included in the final output.
• Example: Student join Department where Department Number = DID.
• The output includes records from the Cartesian product where the values in the 'Department Number' column of the Student relation are equal to the values in the 'DID' column of the Department relation.
• Example: Student join Department where Department Number < DID.
• The output includes records from the Cartesian product where the 'Department Number' is less than the 'DID'.

EQUI Join

• Equi Join specifically uses the equality (=) operator for the join condition.
• Syntax: Table1 join Table2 where Column_in = Column_m.
• Example: Student join Department where Department Number = DID.
• Only records where the 'Department Number' and 'DID' columns have matching values are included in the output.

Natural Join

• Natural Join is denoted by a star (*) symbol.
• Syntax: Table1 * Table2.
• Requires common columns between the two tables.
• Common columns must have the same name and shared values.
• Combines the common columns into a single column in the result.
• Example: Joining Student and Department tables, both having a 'Department Number' column.
• The resulting table combines 'Department Number' from both tables into one and includes columns for student name (SName), department name (DName), and the combined 'Department Number'.

Outer Joins

• Outer joins return all rows from one table and matching rows from another table.
• If there's no match, the result contains NULL values.
• Types: Left Outer Join, Right Outer Join, and Full Outer Join.

Left Outer Join

• Returns all rows from the left table and matching rows from the right table.
• If there's no match in the right table, NULL values are included.
• Syntax: Relation1 left join Relation2 on condition.
• Example: Student left join Department on Department Number = DID.
• The output includes all rows from the Student table, and only matching rows from the Department table.
• If a student's department number doesn't exist in the Department table's DID column, the DName and DID for that student will be NULL.

Right Outer Join

• Returns all rows from the right table and matching rows from the left table.
• If there's no match in the left table, NULL values are included.
• Example: Student right join Department on Department Number = DID.
• The output includes all rows from the Department table and only matching rows from the Student table.
• If a department number in the Department table doesn't exist in the Student table's Department Number column, the SName and Department Number for that department will be NULL.

Full Outer Join

• Returns all rows from both tables.
• Includes matching rows where the join condition is met.
• If there's no match in either table, NULL values are inserted for the missing columns.
• Example: Student full outer join Department on Department Number = DID.
• Contains all rows from both Student and Department
• If there are non matching rows NULL values are included for both.