Transcript Lecture 6 - Relational Algebra I

ICOM 5016 – Introduction to
Database Systems
Lecture 6
Dr. Manuel Rodriguez
Department of Electrical and Computer Engineering
University of Puerto Rico, Mayagüez
Chapter 3: Relational Model
 Structure of Relational Databases
 Relational Algebra
 Tuple Relational Calculus
 Domain Relational Calculus
 Extended Relational-Algebra-Operations
 Modification of the Database
 Views
Database System Concepts
3.2
©Silberschatz, Korth and Sudarshan
Projection Operation
 Given a relation R, the projection operation is used to create a
new relation S, such that each tuple ts is formed by taking a tuple
tR and removing one or more columns.
 Formally, the projection of R over columns A1, A2, …,An is defined
as:
S   A1 , A2 ,..., An ( R)
 {ts  ( A1 , A2 ,..., An ) | t R  R, t R  ( B1 , B2 ,..., Bk ),
and { A1 , A2 ,..., An }  {B1 , B2 ,..., Bk }}
Database System Concepts
3.3
©Silberschatz, Korth and Sudarshan
Project Operation – Example
 Relation r:
 A,C (r)
Database System Concepts
A
B
C

10
1

20
1

30
1

40
2
A
C
A
C

1

1

1

1

1

2

2
=
3.4
©Silberschatz, Korth and Sudarshan
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
 E.g. To eliminate the branch-name attribute of account
account-number, balance (account)
Database System Concepts
3.5
©Silberschatz, Korth and Sudarshan
Union Operation – Example
 Relations r, s:
A
B
A
B

1

2

2

3

1
s
r
r  s:
Database System Concepts
A
B

1

2

1

3
3.6
©Silberschatz, Korth and Sudarshan
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)
 E.g. to find all customers with either an account or a loan
customer-name (depositor)  customer-name (borrower)
Database System Concepts
3.7
©Silberschatz, Korth and Sudarshan
Set Difference Operation – Example
 Relations r, s:
A
B
A
B

1

2

2

3

1
s
r
r – s:
Database System Concepts
A
B

1

1
3.8
©Silberschatz, Korth and Sudarshan
Set Difference 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
Database System Concepts
3.9
©Silberschatz, Korth and Sudarshan
Cartesian-Product Operation-Example
Relations r, s:
A
B
C
D
E

1

2




10
10
20
10
a
a
b
b
r
s
r x s:
Database System Concepts
A
B
C
D
E








1
1
1
1
2
2
2
2








10
10
20
10
10
10
20
10
a
a
b
b
a
a
b
b
3.10
©Silberschatz, Korth and Sudarshan
Cartesian-Product Operation
 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.
 A tuple is r x s is made by concatenating the columns from the
first tuple, with the those of the second tuple.
Database System Concepts
3.11
©Silberschatz, Korth and Sudarshan
Composition of Operations
 Can build expressions using multiple operations
 Example: A=C(r x s)
 rxs
 A=C(r x s)
Database System Concepts
A
B
C
D
E








1
1
1
1
2
2
2
2








10
10
20
10
10
10
20
10
a
a
b
b
a
a
b
b
A
B
C
D
E



1
2
2
 10
 20
 20
a
a
b
3.12
©Silberschatz, Korth and Sudarshan
Rename Operation
 Allows us to name, and therefore to refer to, the results of
relational-algebra expressions.
 Allows us to refer to a relation by more than one name.
Example:
 x (E)
returns the expression E under the name X
If a relational-algebra expression E has arity n, then
x (A1, A2, …, An) (E)
returns the result of expression E under the name X, and with the
attributes renamed to A1, A2, …., An.
Database System Concepts
3.13
©Silberschatz, Korth and Sudarshan
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)
Database System Concepts
3.14
©Silberschatz, Korth and Sudarshan
Example Queries
 Find all loans of over $1200
amount > 1200 (loan)
Find the loan number for each loan of an amount greater than
$1200
loan-number (amount > 1200 (loan))
Database System Concepts
3.15
©Silberschatz, Korth and Sudarshan
Example Queries
 Find the names of all customers who have a loan, an account, or
both, from the bank
customer-name (borrower)  customer-name (depositor)
Find the names of all customers who have a loan and an
account at bank.
customer-name (borrower)  customer-name (depositor)
Database System Concepts
3.16
©Silberschatz, Korth and Sudarshan
Example Queries
 Find the names of all customers who have a loan at the Perryridge
branch.
customer-name (branch-name=“Perryridge”
(borrower.loan-number = loan.loan-number(borrower x loan)))
 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)
Database System Concepts
3.17
©Silberschatz, Korth and Sudarshan
Example Queries
 Find the names of all customers who have a loan at the Perryridge
branch. Two possible solutions follow:
Query 1
customer-name(branch-name = “Perryridge” (
borrower.loan-number = loan.loan-number(borrower x loan)))
 Query 2
customer-name(loan.loan-number = borrower.loan-number(
(branch-name = “Perryridge”(loan)) x borrower))
Database System Concepts
3.18
©Silberschatz, Korth and Sudarshan
Example Queries
Find the largest account balance
 Rename account relation as d
 The query is:
balance(account) - account.balance
(account.balance < d.balance (account x d (account)))
Database System Concepts
3.19
©Silberschatz, Korth and Sudarshan
Formal Definition
 A basic expression in the relational algebra consists of either one
of the following:
 A relation in the database
 A constant relation
 Let E1 and E2 be relational-algebra expressions; the following are
all relational-algebra expressions:
 E1  E2
 E1 - E2
 E1 x E2
 p (E1), P is a predicate on attributes in E1
 s(E1), S is a list consisting of some of the attributes in E1
  x (E1), x is the new name for the result of E1
Database System Concepts
3.20
©Silberschatz, Korth and Sudarshan
Additional Operations
We define additional operations that do not add any power to the
relational algebra, but that simplify common queries.
 Set intersection
 Natural join
 Division
 Assignment
Database System Concepts
3.21
©Silberschatz, Korth and Sudarshan
Set-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)
Database System Concepts
3.22
©Silberschatz, Korth and Sudarshan
Set-Intersection Operation - Example
 Relation r, s:
A
B



1
2
1
A


r
 rs
Database System Concepts
A
B

2
B
2
3
s
3.23
©Silberschatz, Korth and Sudarshan
Natural-Join Operation

Notation: r
s
 Let r and s be relations on schemas R and S respectively.
Then, r
s is a relation on schema R  S obtained as follows:
 Consider each pair of tuples tr from r and ts from s.
 If tr and ts have the same value on each of the attributes in R  S, add
a tuple t to the result, where
 t has the same value as t on r
r
 t has the same value as t
s on s
 Example:
R = (A, B, C, D)
S = (E, B, D)
 Result schema = (A, B, C, D, E), and R S = (B,D)
 r
s is defined as:
r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))
Database System Concepts
3.24
©Silberschatz, Korth and Sudarshan
Natural Join Operation – Example
 Relations r, s:
A
B
C
D
B
D
E





1
2
4
1
2





a
a
b
a
b
1
3
1
2
3
a
a
a
b
b





r
r
s
Database System Concepts
s
A
B
C
D
E





1
1
1
1
2





a
a
a
a
b





3.25
©Silberschatz, Korth and Sudarshan