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