Lecture 4
Relational Algebra
Relational Algebra
• Relational algebra is a procedural query language, which takes instances of relations as
input and yields instances of relations as output. It uses operators to perform queries.
An operator can be either unary or binary. They accept relations as their input and
yield relations as their output. Relational algebra is performed recursively on a relation
and intermediate results are also considered relations.
Relational Algebra
• Procedural language
• Six basic operators
• select
• project
• union
• set difference
• Cartesian product
• rename
• The operators take two or more relations as inputs and give a new relation as a
result.
Select Operation
• This is used to fetch rows(tuples) from table(relation) which satisfies a given condition.
• Notation: p(r)
• Where, σ represents the Select Predicate, r is the name of relation(table name in which
you want to look for data), and p is the prepositional logic, where we specify the
conditions that must be satisfied by the data. In prepositional logic, one can
use unary and binary operators like =, <, > etc, to specify the conditions.Defined as:
p(r) = {t | t r and p(t)}
Where p is a formula in propositional calculus consisting of terms connected by :
(and), (or), (not)
Each term is one of:
<attribute> op <attribute> or <constant>
where op is one of: =, , >, . <.
• Example of selection:
branch-name=“Perryridge”(account)
• Example of selection:
branch-name=“Perryridge”(account)
Answer of given selection:
branch-name=“Perryridge” (account)
here selection means it select single row
Account- Branch-name Balance
number
A-102 Perryridge 400
Select Operation
• Example of selection:
amount>1200 (loan)
Answer of selection Table:
amount>1200 (loan)
Loan- Branch-name Amount
number
L-14 Downtown 1500
L-15 Perryridge 1500
L-16 Perryridge 1300
L-23 Redwood 2000
• Example of selection:
branch-name=“Perryridge”^amount>1200 (loan)
Example of selection:
branch-name=“Perryridge”^amount>1200 (loan)
Answer:
Loan-number Branch-name Amount
L-15 Perryridge 1500
L-16 Perryridge 1300
Project Operation
• Project operation is used to project only a certain set of attributes of a relation. In
simple words, If you want to see only the names all of the students in
the Student table, then you can use Project Operation.
• It will only project or show the columns or attributes asked for, and will also remove
duplicate data from the columns
• Notation:
A1, A2, …, Ak (r)
where A1, A2 are attribute names and r is a relation name.
• The result is defined as the relation of k columns obtained by erasing the columns that
are not listed
• Duplicate rows removed from result, since relations are sets
• E.g. To eliminate the branch-name attribute of account
account-number, balance (account)
Question: account-number ,branch-name (account)
Answer :
Account- Branch-name
number
A-101 Downtown
A-102 Perryridge
A-201 Brighton
A-215 Mainus
A-217 Brighton
A-222 Redwood
A-305 Round hill
Composite of Relational Operation
Π customer-name(σcustomer-city=“Harrison”(customer))
Composite of Relational Operation
Question: Π customer-name(σcustomer-city=“Harrison”(customer))
Answer:
select operation:
Customer- name Customer-street Customer-city
Hayes Main Harrison
Jones Main Harrison
project operation:
Customer- name
Hayes
Jones
Union Operation
• This operation is used to fetch data from two relations(tables) or temporary
relation(result of another operation).
• For this operation to work, the relations(tables) specified should have same number of
attributes(columns) and same attribute domain. Also the duplicate tuples are
automatically eliminated from the result.
• Notation: r s
• Defined as:
r s = {t | t r or t s}
• For r s to be valid.
1. r, s must have the same arity (same number of attributes)
2. The attribute domains must be compatible (e.g., 2nd column
of r deals with the same type of values as does the 2nd
column of s)
• E.g. to find all customers with either an account or a loan
• customer-name (depositor) customer-name (borrower)
Question: customer-name (depositor) customer-name (borrower)
Union Operation
branch-name, loan-number (Loan) branch-name (Account)
Account Relation
Set Difference Operation
This operation is used to find data present in one relation and not present in the second
relation. This operation is also applicable on two relations, just like Union operation
Notation r – s
Defined as:
r – s = {t | t r and t s}
Set differences must be taken between compatible relations.
r and s must have the same arity
attribute domains of r and s must be compatible.
Set Difference Operation
Π branch-name(Account) − Π branch-name(Loan)
Answer: Π branch-name(account) − Π branch-name(loan)
Account Relation
Answer:
Branch-name
Brighton
Set Difference Operation
• Πcustomer-name(Customer)−Πcustomer-name(Account)
• Account Relation
• Customer Relation
• Πbranch-name(Customer)−Πbranch-name(Account)
Cartesian-Product Operation
• This is used to combine data from two different relations(tables) into one and fetch data
from the combined relation.
• Notation r x s
• Defined as:
r x s = {t q | t r and q s}
• Assume that attributes of r(R) and s(S) are disjoint. (That is,
R S = ).
• If attributes of r(R) and s(S) are not disjoint, then renaming must be used.
σbranch-name=“Perryridge”(borrower ×loan)
•
Loan Relation
Answer: I have considered those both tables has 3 elements . Now I multiple 3 by 3 .
• Account
Customer Loan Loan Branch amount
name number(b) number(loan) name
Adams L-16 L-11 Round hill 900
Curry L-93 L-11 Round hill 900
Hayes L-15 L-11 Round hill 900
Adams L-16 L-14 Downtown 1500
Curry L-93 L-14 Downtown 1500
Hayes L-15 L-14 Downtown 1500
Adams L-16 L-15 Perryridge 1500
Curry L-93 L-15 Perryridge 1500
Hayes L-15 L-15 Perryridge 1500
Now doing selection operation :
Customer Loan Loan Branch amount
name number(b) number(loan) name
Adams L-16 L-15 Perryridge 1500
Curry L-93 L-15 Perryridge 1500
Hayes L-15 L-15 Perryridge 1500
Find the names of all customers who have a
loan at the Perryridge branch.
• σborrower.loan-number=loan.loan-number(σbranch-name=“Perryridge”(borrower
×loan))
• Πcustomer-name(σborrower.loan-number=loan.loan-number(σbranch-
name=“Perryridge”(borrower ×loan)))
Banking Example
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Example Queries
• Find all loans of over $1200
σamount > 1200 (loan)
n Find the loan number for each loan of an amount greater than
$1200
Π loan-number
(σamount > 1200
(loan))
• Find the names of all customers who have a loan, an account, or both, from the bank
Π customer-name (borrower) ƯΠ customer-name
(depositor)
n Find the names of all customers who have a loan and an account at bank.
Π customer-name (borrower) Λ Π customer-name
(depositor)
• Account• Find the names of all customers who have a loan at the Perryridge branch.
Relation
Π (σ (σ (borrower x
customer-name branch-name=“Perryridge” borrower.loan-number = loan.loan-number
loan)))
n Find the names of all customers who have a loan at the Perryridge branch but do not
have an account at any branch of the bank.
Π customer-name
(σbranch-name = “Perryridge”
(σborrower.loan-number = loan.loan-number(borrower x loan))) –
Π customer-name
(depositor)
• Find the names of all customers who have a loan at the Perryridge branch.
Query 1
Π (σ (
customer-name branch-name = “Perryridge”
σ (borrower x loan)))
borrower.loan-number = loan.loan-number
Query 2
Π customer-name(σloan.loan-number = borrower.loan-number(
(σbranch-name = “Perryridge”(loan)) x borrower))
Find the largest account balance
• Rename account relation as d
• The query is:
Π (account) - Π
balance account.balance
(σ (account x (account)))
account.balance < d.balance d
