Week 5-6: Relational Algebra (Part III) and Relational Calculus
Week 5-6: Relational Algebra (Part III) and Relational Calculus
Week 5-6: Relational Algebra (Part III) and Relational Calculus
2
Deletion
▪ A delete request is expressed similarly to a query, except instead
of displaying tuples to the user, the selected tuples are removed
from the database.
▪ Can delete only whole tuples; cannot delete values on only
particular attributes
▪ A deletion is expressed in relational algebra by:
rr–E
where r is a relation and E is a relational algebra query.
3
Banking Enterprise Schema Diagram
4
4
Deletion Examples
▪ Delete all account records in the Perryridge branch.
account account – branch_name = “Perryridge” (account )
5
Insertion
6
Insertion Examples
▪ Insert information in the database specifying that Smith has $1200 in
account A-973 at the Perryridge branch.
7
Updating
▪ Each Fi is either
▪ the I th attribute of r, if the I th attribute is not updated, or,
▪ if the attribute is to be updated Fi is an expression, involving only
constants and the attributes of r, which gives the new value for the
attribute
8
Update Examples
9
Tuple Relational Calculus
10
Predicate Calculus Formula
11
Banking Enterprise Schema Diagram
12
12
Example Queries
▪ Find the loan number for each loan of an amount greater than $1200
13
Example Queries
▪ Find the names of all customers who have a loan and an account at the
bank
14
Example Queries
▪ Find the names of all customers having a loan at the Perryridge branch
▪ Find the names of all customers who have a loan at the Perryridge branch,
but no account at any branch of the bank
15
Example Queries
▪ Find the names of all customers having a loan from the Perryridge
branch, and the cities in which they live
16
Example Queries
▪ Find the names of all customers who have an account at all branches
located in Brooklyn:
The set of all customers (that is, tuples t) such that, for all tuples u in the
branch relation, if the value of u on attribute branch-city is Brooklyn, then
the customer has an account at the branch whose name appears in the
branch-name attribute of u.
17
Safety of Expressions
18
Domain Relational Calculus
19
Example Queries
▪ Find the loan_number, branch_name, and amount for loans of over $1200
{ l, b, a | l, b, a loan a > 1200}
▪ Find the names of all customers who have a loan of over $1200
{ c | l, b, a ( c, l borrower l, b, a loan a > 1200)}
▪ Find the names of all customers who have a loan from the Perryridge branch
and the loan amount:
{ c, a | l ( c, l borrower b ( l, b, a loan b = “Perryridge”))}
Alternative:
{ c, a | l ( c, l borrower l, “ Perryridge”, a loan)}
20
Example Queries
▪ Find the names of all customers who have an account at all branches
located in Brooklyn:
{ c | s, n ( c, s, n customer)
x, y, z ( x, y, z branch y = “Brooklyn”)
a, x ( a, x, m account c, a depositor)}
21
Safety of Expressions
The expression:
{ x1, x2, …, xn | P (x1, x2, …, xn )}
22