5

1
Schedule

• Today
• Relational Algebra.
• Read Chapter 5 to page 199.
• Next
• SQL Queries.
• Read Sections 6.1-6.2.
• And then
• Subqueries, Grouping and Aggregation.
• Read Sections 6.3-6.4.

2
Relational Algebra

• The relational algebra is a precise mathematical
  notation and set of rules for manipulating
  relations.
• SQL is basically a more human readable form of
  the relational algebra.

3
Core Relational Algebra

• A small set of operators that allow us to
  manipulate relations in limited but useful ways.
  The operators are
• 1. Union, intersection, and difference the usual
  set operators.
• But the relation schemas must be the same.
• 2. Selection Picking certain rows from a
  relation.
• 3. Projection Picking certain columns.
• 4. Products and joins Composing relations in
  useful ways.
• 5. Renaming of relations and their attributes.

4
Relational Algebra

• ? SELECT
• p PROJECT
• X CARTESIAN PRODUCT
• NATURAL JOIN

5
Selection

• R1 ?C(R2)
• where C is a condition involving the attributes
  of relation R2.
• Example
• Relation Sells
• JoeMenu ?barJoe's(Sells)

6
Product

• R R1 ? R2
• pairs each tuple t1 of R1 with each tuple t2 of
  R2 and puts in R a tuple t1t2.

7
Natural-Join

• R R1 R2is equivalent to R ?C(R1 ? R2)
  where c is the condition that the the values of
  the attributes that R1 and R2 have in common must
  match.

8
Example

• Sells Bars
• BarInfo Sells Bars

9
Combining Operations

• Algebra
• Universe or domain of objects
• Operators for constructing expressions.
• For relational algebra
• Domain variables standing for relations
  finite, constant relations.
• Expressions constructed by applying one of the
  operators parentheses.
• Query expression of relational algebra.

10
Bag Semantics

• A relation (in SQL, at least) is really a bag or
  multiset.
• It may contain the same tuple more than once,
  although there is no specified order (unlike a
  list).
• Example 1,2,1,3 is a bag and not a set.
• Select, project, and join work for bags as well
  as sets.
• Just work on a tuple-by-tuple basis, and don't
  eliminate duplicates.

11
Bag Union

• Sum the times an element appears in the two bags.
• Example 1,2,1 ? 1,2,3,3 1,1,1,2,2,3,3.
• Bag Intersection
• Take the minimum of the number of occurrences in
  each bag.
• Example 1,2,1 ? 1,2,3,3 1,2.
• Bag Difference
• Proper-subtract the number of occurrences in the
  two bags.
• Example 1,2,1 1,2,3,3 1.

12
Duplicate Elimination

• ?(R) relation with one copy of each tuple that
  appears one or more times in R.
• Example
• R
• A B
• 1 2
• 3 4
• 1 2
• ?(R)
• A B
• 1 2
• 3 4

13
Bank Database Schema

• Branch (branch-name, branch-city, assets)
• Customer (customer-name, customer-street,
  customer-city)
• Account (branch-name, account, balance)
• Depositor (customer-name, account)
• Loan (branch-name, loan, amount)
• Borrower (customer-name, loan)

14
The Customer Table
Customer-name C-Street C-city
Bob 123 Third St San Jose
Carol 456 Main St Santa Clara
Ted 89 Blossom Ave Los Gatos
Alice 64 Longwalk Dr Oakland
15
The Account Table
Branch-name Account Balance
Oakland 101 2000
SJ-Main 205 7500
SJ-Main 207 4500
Santa Clara 311 3100
SJ-West 251 850
16
The Loan Table
Branch-name loan Amount
Oakland 2301 5000
SJ-Main 5155 700
SJ-Main 5709 9000
Santa Clara 1541 1800
SJ-West 4321 250
17
The Depositor Table
Customer-name Account
Bob 207
Carol 311
Ted 205
Alice 101
Bob 251
18
Queries on the Loan Table
Branch-name
Oakland
SJ-Main
SJ-Main
Santa Clara
SJ-West

• Loan (branch-name, loan, amount)
• Find the names of all the branches in the Loan
  relation
• select branch-name
• from Loan
• ?(branch-name) (Loan)

19
More Queries on the Loan Table

• select
• from Loan
• where amount gt 3000
•  
• ?(amountgt3000) (Loan)

20

• Find the loan numbers for all loans made at the
  Oakland branch with loan amounts greater than
  1200. 
• select loan
• from Loan
• where branch-name"Oakland" and amountgt1200
• ?(loan) ?(branch-nameOaklandamountgt1200)
  (Loan)

21
Cross Product

• Select A1, A2 from R1, R2
• Result R R1 ? R2
• pairs each tuple t1 of R1 with each tuple t2 of
  R2 and puts in R a tuple t1t2.
• ?(A1, A2) (R1 ? R2)

22
Natural Join

• Find the name of customers with an account at the
  Oakland branch.
• select customer-name
• from Depositor, Account
• where
• Depositor.account Account.account
• and branch-name "Oakland"
• ?(cust-name) ?(b-nameOakland) (Depositor
  Account)