Relational Algebra

It is the procedural query language in which user requests information from the database.

It can be categorized into two

• Procedural
• Nonprocedural

Procedural-: In a procedural language the user instructs the system to try and do a sequence of operations on information to figure the required result.

Nonprocedural-: In nonprocedural language the user describes the desired information without giving a specific procedure for obtaining that information.

Fundamental Operations

• SELECT
• PROJECT
• UNION
• SET DIFFERENCE
• CARTESIAN PRODUCT
• RENAME

   Other Operations

• SET INTERSECTION
• NATURAL JOIN
• DIVISION
• ASSIGNMENT

Select operation-: to spot a group of tuples that may be a part of a relation and to extract only these tuples out. The select operation selects tuples that satisfy a given condition.

• It is a unary operation defined on a single relation.
• It is denoted as σ.

Where-

σ is used for selection prediction
r is used for relation
p is used as a symbolic logic formula which can use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

• Example1:-Select from the relation “Bank” all the banks whose date of birth is 1998.
  
  
• Code:

[B][SIZE=3]σ[/SIZE] [SIZE=1]Date of birth [/SIZE][/B][SIZE=1][B]=[/B][B]1998[/B][/SIZE][B](Bank)

[/B]

• Example2:- Select from the relation “Bank” all the banks whose Account-no is greater than equal to 734214.
  
  

Code:

[B][SIZE=3]σ[/SIZE][SIZE=1]Account- no[/SIZE][SIZE=1]>=734214[/SIZE][/B][B](Bank)[/B]

The project operation: - It returns argument relation with certain attributes left out.

• It is a unary operation defined on a single relation
• It is denoted as Π.

Example1: - List all the Title and Account-no of the “Bank” relation.

Code:

   [B]Π [SIZE=1] Account-no, Name [/SIZE] [/B] [B] (Bank)

         [/B]

The union operation: - It is used when we need some attributes that appear in either or both of the two relations.
It is denoted as U

For a union operation r U s two conditions must hold:

The relation r and s must be of the same attribute i.e. they must have the same number of attributes

.

The domains of the i attribute of r and the i attribute of s must be the same for all i

Example-:
Borrower (customer-name, loan-number)
Depositor (customer-name, account-number)
Customer (customer-name, street-number, customer-city)


List all the customers who have either an account or a loan or both

Code:-

[B]Π [SIZE=1]customer-name [/SIZE][/B][B](Borrower)[/B][B] U Π [SIZE=1]customer-name[/SIZE] [/B][B](Depositor)[/B]

 Set Difference-:

• Suppose there are two tuples X and Y. The set intersection operation contains all tuples that are in X but not in Y.
• It is denoted by intersection minus (-).
• Notation: X - Y 

Example: Find out the names of all the customers those have an account but not a loan.

Code:

  [B]Π [SIZE=1]customer-name [/SIZE][/B][B](Depositor)[/B][B] - Π [SIZE=1]customer-name [/SIZE][/B][B](Borrower)

[/B]

Cartesian product-:

• The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product.
• It is denoted by X.

Notation: T X J 

Example:-
Borrower (Name, loan-number)
Loan (loan-number, branch-name, city, amount)

List the names of all customers who have a loan in “SBI” branch

Code:

  [B]Π [SIZE=1] Name [/SIZE] (σ [SIZE=1] Borrower. Loan-number=Loan. Loan-number [/SIZE] (σ [SIZE=1] branch-name=”SBI” [/SIZE] (Borrower X Loan)))

[/B]

Rename operation-: The rename operation is used to rename the output relation.

It is represented by (ρ).

Example: We can use the rename operator to rename.

ρ(STUDENT1, STUDENT)