Quick Contact

    What is Relational Algebra?

    Relational Algebra is a compilation of applications to manipulate or access relations. It is a procedural (or abstract) language with applications that is executed on additionally current relations to derive outcome (another) relations without modifying the initial relation(s). Furthermore, relational algebra represents the complete schema for each of the outcome relations.

    Relational algebra includes a set of relational operators. Each operator has unnecessary relations as its input and generates a relation as its output. So, both the operands and the outcome are connections, and so the output from one operation can develop into the input to another operation.

    A fundamental expression in relational algebra includes the following:

    • A relation in the database
    • A constant relation

    The expression is composed of small sub-expressions. For instance, let E_1 and E_2be relational-algebra expressions. Then, the following are also expressions:

    1. E1∪E2
    2. E1-E2
    3. E1 X E2
    4. σP (E1)where P is a predicate on the attribute in E1.
    5. S (E1)where S is a series involving some of the attributes in E1.
    6. ρx (E1)where x is the new name for the result of E1.

    A query in relational algebra will include the following operations.

    Types of Operations in Relational Algebra

    Following are the two types of operations in relational algebra

    1. Basic/Fundamental Operations
    2. Derived Operations
    What is Relational Algebra?

    Let us explain these operations one by one with the support of the following examples.

    Basic/Fundamental Operations
    SELECT Operation (σ)

    The SELECT operation is used to choose a subset of the rows from a relationship that satisfies a selected condition.

    Syntax of SELECT Operation (σ)

    σ< Selection Condition >(R)

    Where σ(sigma) is the symbol for SELECT operation.

    < Selection Condition >is a Boolean expression described on the attributes of relation R.

    In the above syntax, R is a relation or name of a table, and the condition is a propositional logic which helps the relational operators like <,>,≤,≥,=,≠.

    Example: Consider the following schema

    Employee (Eno, Ename, ECity, EStreet, Salary, Dno)

    Department (Dno, Dname, DCity, TotalSal)

    Where the Total-Sal is the total salary of all employees in a department.

    Employee
    Eno Ename ECity EStreet Salary Dno
    001 Rohan Faridabad Sec-16 42,000 01
    002 Sumit Noida Sec-15 35,000 02
    003 Neha Delhi Sec-4 28,000 01
    004 Gaurav Gurgaon Sec-11 50,000 03
    005 Raman Faridabad Sec-8 46,000 02
    Department
    Dno DName DCity TotalSal
    01 Marketing Delhi 70,000
    02 inance Noida 81,000

    Query1: Get information on those employees who are drawing a salary of more than 40,000.

    σSalary>40000 (Employee)

    The resultant relation will be:

    Eno Ename ECity EStreet Salary Dno
    001 Rohan Faridabad Sec-16 42,000 01
    004 Gaurav Gurgaon Sec-11 50,000 03
    005 Raman Faridabad Sec-8 46,000 02

    Query2: Get information on those employees who are living in Noida.

    σEcity=”Noida” (Employee)

    The resultant relation will be:

    Eno Ename ECity EStreet Salary Dno
    002 Sumit Noida Sec-15 35,000 02
    PROJECT Operation (∏)

    The PROJECT operation is used to select a subset of the attributes of relation by defining the name of the necessary attributes. Any matching tuples in the result are automatically deleted. It can also be used to modify the order of attributes in a relation.

    Syntax of PROJECT Operation (∏)

    < attribute list >(R)

    Where ∏ is the symbol for project operation, < attribute list > is a list of attributes from the relation R.

    Example

    Consider the STUDENT relation

    SID NAME SUBJECT GRADE AGE
    111 Mohit Maths A 15
    112 Garima History B 16
    113 Aayush Science A+ 14
    114 Sheena English C 12

    Query1: Get a list of all the student names and student subjects.

    NAME,SUBJECT(STUDENT)

    The resultant relation will be:

    NAME SUBJECT
    Mohit Maths
    Garima History
    Aayush Science
    Sheena English
    Query2: Get a list of all the student ID and student age.

    SID,AGE(STUDENT)

    The resultant relation will be:

    SID AGE
    111 15
    112 16
    113 14
    114 12
    Union Operation(∪)

    UNION operator is represented by ∪ symbol, and it is used to choose all the rows (tuples) from two tables (relations). Matching tuples are automatically deleted from the result.

    Earlier taking it union between two relations, provides that both the union must be consistent, which involve that:

    1. Both the relation must be of a similar degree, i.e., they must have the same number of
      attributes.
    2. For all k, the domain of the kth attribute of one relation must be similar to the domain of the kth attribute of another relation.
    Syntax of UNION Operation(∪)

    A1∪A2

    In the above syntax, A1 is the name of the first relation or table, and A2 is the name of the second relation or table.

    Example: Consider the following schema

    DEPOSIT (Cust_Name, Account_No)

    LOAN (Cust_Name, Loan_No)

    DEPOSIT
    Cust_Name Account_No
    Mayank A-101
    Hardik A-103
    Himanshu A-105
    Ashutosh A-106
    LOAN
    Cust_Name Loan_No
    Rahu L-101
    Hardik L-103
    Himanshu L-105

    Query: Get the names of those customers, who have either an account or loan in a bank

    Cust-Name (Deposit)∪∏Cust-Name (Loan)

    Cust_Name
    Mayank
    Hardik
    Himanshu
    Ashutosh
    Rahul
    SET DIFFERENCE Operation(-)

    The SET DIFFERENCE operation is a-b, which is between two relations ‘a’ and ‘b’ that generates a relation with the tuples which are there in ‘a’ but not there in ‘b’.

    Syntax of SET DIFFERENCE Operation(-)

    A-B

    In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

    Example: Consider the following schema

    DEPOSIT (Cust_Name, Account_No)

    LOAN (Cust_Name, Loan_No)

    DEPOSIT
    Cust_Name Account_No
    Mayank A-101
    Hardik A-103
    Himanshu A-105
    Ashutosh A-106
    LOAN
    Cust_Name Loan_No
    Rahu L-101
    Hardik L-103
    Himanshu L-105

    Query: Get the names of those customers, who have an account in a bank, but do not have a loan.

    Cust-Name (Deposit) -∏Cust-Name (Loan)

    The resultant relation will be:

    Cust_Name
    Mayank
    Ashutosh
    Rahul
    CARTESIAN PRODUCT Operation (X)

    CARTESIAN PRODUCT is represented by (X) symbol. The Cartesian product of two tables merges each row in one table with each row in the other table.

    The Cartesian product of two relations a and b is expressed as a x b. The resultant relation will be an on the schema that will be a concatenation of the two schemas A and B, expressed as (A, B). For each tuple ta∈a and each tuple tb∈b,there will be a tuple t in axb, such that t[A]=tA and t[B]=tB.

    Let say, relation a has p tuples, and the relation b has q tuples, then the resultant relation will have p*q tuples.

    Syntax of CARTESIAN PRODUCT (X)

    A x B

    Example: Consider the following schema

    EMPLOYEE (Eno, Ename, Salary, Dno)

    DEPARTMENT (Dno, Dname)

    EMPLOYEE
    Eno Ename Salary Dno
    101 Rahul 15,000 1
    102 Rohit 20,000 4
    103 Rohan 18,000 5
    DEPARTMENT
    Dno Dname
    1 Marketing
    3 Sales
    5 Legal

    Query: Let’s find the Cartesian product of the table EMPLOYEE and DEPARTMENT.

    EMPLOYEE X DEPARTMENT

    The resultant relation will be:

    Eno Ename Salary Dno Dno Dname
    101 Rahul 15,000 1 1 Marketing
    101 Rahul 15,000 1 3 Sales
    101 Rahul 15,000 1 5 Legal
    102 Rohit 20,000 4 1 Marketing
    102 Rohit 20,000 4 3 sales
    102 Rohit 20,000 4 5 Legal
    103 Rohan 18,000 5 1 Marketing
    103 Rohan 18,000 5 3 sales
    103 Rohan 18,000 5 15 Legal
    RENAME Operation (ρ)

    A RENAME is a unary operation written as ρ_(a/b)(R) where the result is exact to R, except that the b field in all tuples is renamed to a field.

    Syntax of RENAME Operation (ρ)

    ρ (new_relation_name, old_relation_name)

    Example

    Consider the STUDENT relation

    SID NAME SUBJECT GRADE AGE
    111 Mohit Maths A 15
    112 Garima History B 16
    113 Aayush Science A+ 14
    114 Sheena English C 12

    Query: Get a list of all the student names.

    ρNAME, ∏NAME(STUDENT)

    The resultant relation will be:

    NAME
    Mohit
    Garima
    Aayush
    Sheena
    Derived Operations
    SET INTERSECTION Operation (∩)

    The intersection operator is denoted by the (∩) symbol, which is used to show all the rows of frequent attributes from two relations.

    Syntax of SET INTERSECTION Operation (∩)

    A∩B

    In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

    Example: Consider the following schema

    DEPOSIT (Cust_Name, Account_No)

    LOAN (Cust_Name, Loan_No)

    DEPOSIT
    Cust_Name Account_No
    Mayank A-101
    Hardik A-103
    Himanshu A-105
    Ashutosh A-106
    LOAN
    Cust_Name Loan_No
    Rahu L-101
    Hardik L-103
    Himanshu L-105

    Query: Get the names of those customers, who have an account as well as loan in a bank

    Cust-Name (Deposit) ∩ ∏Cust-Name (Loan)

    The resultant relation will be:

    Cust_Name
    Hardik
    Himanshu
    DIVISION Operation (/)

    The DIVISION is a binary operation that is composed of A/B. The outcome includes the constraints of rows in A to the attribute names exclusive to A, i.e., in the header of A, but not in the header of B, for which it influences that all their mixtures with rows in B are demonstration in A.

    Syntax of DIVISION Operation (/)

    A/B

    Example: Consider the following Banking Schema

    Branch
    Branch-Name Branch-City
    Sec-18, HDFC Noida
    South Delhi Delhi
    West Delhi Delhi
    Account
    Account-No Amount Branch-Name
    101 20,000 Sec-18, HDFC
    102 25,000 South Delhi
    103 30,000 West Delhi
    Depositor
    Customer-Name Account-No
    Vikram 101
    Rohit 102
    Rohit 103

    Query: Find the names of all customers who have accounts in all branches of Delhi.

    A1←∏ Branch-Name(σ_(Branch-City=”Delhi” (Branch))
    A2←∏Customer-Name,Branch-Name (Depositor C Account)

    Result:A2/A1

    A1
    Branch-Name
    South Delhi
    West Delhi
    A2
    Customer-Name Branch-Name
    Vikram Sec-62, HDFC
    Rohit South Delhi
    Rohit West Delhi

    The result relation will be:

    Rohit
    ASSIGNMENT Operation (←)

    Sometimes, it is useful to write a relational algebra expression by split it into sub-expressions and authorize the intermediate outcomes to temporary relation-variables. The ASSIGNMENT operation is represented by (←) designate the outcome of a relational-algebra sub-expression to a provisional relation-variable. The relation-variable can be used in subsequent expressions, equal to any permanent relation. Assignment to a permanent relation cause affects its modification.

    Joins Operation(⋈)

    A join operation is denoted by ⋈ (Bowtie) Symbol. Joins are the merger of related rows from two different relations or tables into an individual type. It is associated with a cartesian product.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.