Transcript Thomas Connolly and Carolyn Begg’s

Thomas Connolly
and Carolyn Begg’s
Database Systems:
A Practical Approach to Design,
Implementation, and Management
Chapter 4 Part Two:
Relational Calculus
Of course, we are still using Kroenke’s slide format!
1-1
Today’s Objectives
• To practice relational algebra queries
• To learn the syntax of tuple relational
calculus
• To form queries in the relational calculus
• To contrast the two DML discussed to date
1-2
Relational Algebra Practice
1. List the names of staff who work at a London branch.
2. List the client numbers of those who have viewed all
London property.
3. List the client numbers of those who have viewed all
types of property.
4. List names of all people in the Dream House database.
5. List the names of staff who do not have the same first
name as some client.
1-3
Tuple Relational Calculus
• The relational algebra tells “how” to get
information.
• Tuple Calculus  (Codd 1972) Nonprocedural language for expressing WHAT
is to be retrieved, not how to retrieve it
• Tuple  record from any part of the
database
1-4
Tuple Calculus Notation
• Assume x is a tuple and R is a relation, to
specify the relation which holds a particular
tuple, aka the range of the tuple: x ∈ R
• E.g. c ∈ CLIENT
• Find all tuples, x, such that some condition(s),
F(x), is true: {x | F (x)}
• E.g. List all clients in the database:
{c | c ∈ CLIENT}
1-5
Notation cont’d
• To specify a particular attribute simply list the
tuple followed by a dot and the attribute’s name.
• E.g. List the names of all clients.
• {c.fname, c.lname | c ∈ CLIENT}
• As all formulas in the calculus evaluate to true or
false, multiple formulas, or predicates, are joined
using ∧ (and) and ∨ (or). Also the operator ∼
(not) may be used.
1-6
Notation cont’d
• List the names of clients willing to pay more than $500 in
rent.
{c.fname, c.lname | c ∈ Client ∧ c.maxRent > 500}
• List private owners numbers who own a property in
Aberdeen.
{p.ownerNo | p ∈ PropertyForRent ∧ p.city = ‘Aberdeen’}
• List all supervisors and managers.
{s | s ∈ Staff ^ (s.position = ‘Manager’ ∨ s.position =
‘Supervisor’)}
• List all male assistants.
1-7
Quantifiers
• Existential quantifier: ∃ there exists
• Used in formula that must be true for at least
one instance
• List names of all clients who have viewed some
property
{c.fname, c.lname | c ∈ Client ^ ∃(v) (v ∈ Viewing ^
c.clientNo = v.clientNo)}
• List telephone numbers of Aberdeen property
owners.
{o.telNo | o ∈ PrivateOwner ^ ∃(p) (p ∈ PropertyForRent
^ p.city = ‘Aberdeen’ ^ o.ownerNo = p.ownerNo)}
1-8
Quantifiers cont’d
• Universal quantifier: ∀ for all
• For all branches not in London:
∀(b) (b ∈ Branch ^ b.city ≠ ‘London’}
• ∀ needs an  when present in the relational
calculus
• List numbers of clients who have viewed all
properties.
{v.clientNo | v ∈ viewing ^ ∀ (p)(p ∈
PropertyForRent)  v.propertyNo =
p.propertyNo}
1-9
Quantifiers cont’d
• List numbers of clients who have viewed all
properties with 3 rooms.
{v.clientNo | v ∈ viewing ^ ∀ (p)(p ∈
PropertyForRent ^ p.rooms = 3)  v.propertyNo
= p.propertyNo}
• List names of clients who have viewed all
properties with 3 rooms.
{c.fname, c.lname | c ∈ Client ^ ∃(v) ( v ∈ viewing ^
v.clientNo = c.clientNo ^ ∀ (p)(p ∈
PropertyForRent ^ p.rooms = 3)  v.propertyNo
= p.propertyNo))}
1-10
Logical Equivalence
• p  q ≡ ~p ∨ q
{c.fname, c.lname | c ∈ Client ^ ∃(v) ( v ∈ viewing ^
v.clientNo = c.clientNo ^ ∀ (p)(p ∈
PropertyForRent ^ p.rooms = 3)  v.propertyNo
= p.propertyNo))} ≡
{c.fname, c.lname | c ∈ Client ^ ∃(v) ( v ∈ viewing ^
v.clientNo = c.clientNo ^ ∀ (p)(~ (p ∈
PropertyForRent ^ p.rooms = 3)) ∨ v.propertyNo
= p.propertyNo))}
1-11
Conversion from ∀ to ∃
• Why convert?
• Rules:
• ∀(p) (F(p)) ≡ ~ ~(∀(p) (F(p)) ≡ ~ ∃(p)
(~F(p))
{c.fname, c.lname | c ∈ Client ^ ∃(v) ( v ∈ viewing ^
v.clientNo = c.clientNo ^ ∀ (p)(~ (p ∈ PropertyForRent ^
p.rooms = 3)) ∨ v.propertyNo = p.propertyNo))} ≡
{c.fname, c.lname | c ∈ Client ^ ∃(v) ( v ∈ viewing ^
v.clientNo = c.clientNo ^ ~(∃ (p)(p ∈ PropertyForRent ^
p.rooms = 3) ∨ v.propertyNo = p.propertyNo))}
1-12
Relational Calculus Practice
1.
Get the names of all owners who have property for rent in
Glasgow.
2.
Get the names of all clients who have viewed all of the property for
rent in Aberdeen.
3.
Get the names of all of the owners.
4.
Get the names of all of the staff who work at a London branch.
5.
Get the client number of each client who viewed a house.
6.
Get the addresses for all of the property for rent in Glasgow.
7.
Get the names of any clients who are also private owners.
8.
Get the names of clients who have never viewed any property.
1-13
Thomas Connolly
and Carolyn Begg’s
Database Systems:
A Practical Approach to Design,
Implementation, and Management
End of Presentation:
Chapter Four: Part Two
Of course, we are still using Kroenke’s slide format!
1-14