Transcript S2

Session 3
Welcome: To session 3-the second learning sequence
“ Relational algebra “
Recap : In the previous learning sequences, we
discussed the relational model.
Present learning: We shall explore the following topics:
- Relational algebra.
- Some operators.
1
Relational Algebra
2
Query Languages
 Language in which user requests information from
the database.
 Categories of languages


procedural
non-procedural
 “Pure” languages:



Relational Algebra
Tuple Relational Calculus
Domain Relational Calculus
 Pure languages form underlying basis of query
languages that people use.
3
Relational Algebra
Procedural language
Four basic operators




select
project
union
Intersection
The operators take one or more
relations as inputs and give a new
relation as a result.
Select Operation
 Notation:  p(r)
 p is called the selection predicate
 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)
Select Operation – Example
Account
Select Operation – Example
The result is the relation:
Accountnumber
A-102
Branchname
Perryridge
balance
400
7
Select Operation – Example
 Balance >“700” (account)
Accountnumber
A-201
Branchname
Brighton
balance
A-217
Brighton
750
900
8
Project Operation
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
Project Operation
E.g. To eliminate the branch-name
attribute of account
account-number, balance (account)
The result relation is:
Project Operation
Account-number
A-101
A102
A-201
A-215
A-217
A-222
A-305
Balance
500
400
900
700
750
750
350
11
Union Operation
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)
Union Operation
E.g. to find all customers with either an
account or a loan
customer-name (depositor)  customer-name (borrower)
Accou
nt-no.
A-101
A-201
A-217
A-222
A-305
borrower
depositor
Customer
-name
Ali
Mahmood
Ahmid
Linda
Rana
Customer- Loanname
no.
Ali
L-11
Kasim
L-11
Ahmid
L-25
Linda
L-11
Rana
L-34
Union Operation
 customer-name (depositor)
Customername
Ali
Mahmood
Ahmid
Linda
Rana
….
customer-name (borrower)
Customer
-name
Ali
Kasim
Ahmid
Linda
Rana
…..
Union Operation
 The result relation is:
Customername
Ali
Mahmood
Ahmid
Linda
Rana
Kasim
Intersection Operation
Notation: r  s
Defined as:
r  s ={ t | t  r and t  s }
Assume:
 r, s have the same arity
 attributes of r and s are compatible
Note: r  s = r - (r - s)
Intersection Operation
 E.g. to find all customers with an account and a
loan
customer-name (depositor)  customer-name (borrower)
Accou
nt-no.
A-101
A-201
A-217
A-222
A-305
borrower
depositor
Customer
-name
Ali
Mahmood
Ahmid
Linda
Rana
Customer- Loanname
no.
Ali
L-11
Kasim
L-11
Ahmid
L-25
Linda
L-11
Rana
L-34
Intersection Operation
 customer-name (depositor)
Customername
Ali
Mahmood
Ahmid
Linda
Rana
….
customer-name (borrower)
Customer
-name
Ali
Kasim
Ahmid
Linda
Rana
…..
Intersection Operation
The result relation is:
Customer-name
Ali
Ahmid
Linda
Rana
Relational Algebra
Summary: In this learning sequence, we discussed Four
basic operators of the topic relational algebra.
20
END
21