The Relational Languages Calculus and SQL

1
The Relational LanguagesCalculus and SQL
Adapated from Ramez Elmasri and Shamkant
Navathe Fundamentals of Database C J Date
Introduction to Databases Wotsely Drake
Practial PostgreSQL
2
Database for the examples

• All examples discussed here refer to the
  following database

3
Database Definitioncreation in PostgreSQL _at_TCU

• Here is a copy of the output after the commands
  were executed, note that the command CREATE
  DATABASE was executed in a previous session

4
Data ManipulationRow Insertion in PostgreSQL _at_TCU

• Here is part of the output after the commands
  were executed

sanchez insert into sp (SN, PN, QTY ) values
(1, 1, 300) INSERT 17738 1 sanchez insert into
sp values (1, 2, 200) INSERT 17739 1 sanchez
insert into sp values (1, 3, 400) INSERT 17740
1 sanchez insert into sp values (1, 4, 200)
sanchez insert into suppliers values (1,
'Smith', 20,'London') INSERT 17750 1 sanchez
insert into suppliers values (2, 'Jones',
10,'Paris') INSERT 17751 1 sanchez insert into
suppliers values (3, 'Blake', 30,'Paris') INSERT
17752 1 sanchez insert into suppliers values
(4, 'Clark', 20,'London') INSERT 17753
1 sanchez insert into suppliers values (5,
'Adams', 30,'Athens') INSERT 17754 1
sanchez insert into parts values (4, 'Screw',
'red',14,'London') INSERT 17758 1 sanchez
insert into parts values (5, 'Cam',
'blue',12,'Paris') INSERT 17759 1 sanchez
insert into parts values (5, 'Cog',
'red',19,'London') ERROR duplicate key
violates unique constraint "parts_pn_key" sanchez
insert into parts values (6, 'Cog',
'red',19,'London') INSERT 17761 1
5
Relational Calculus

• A relational calculus expression creates a new
  relation, which is specified in terms of
  variables that range over rows of the stored
  database relations (in tuple calculus) or over
  columns of the stored relations (in domain
  calculus).
• In a calculus expression, there is no order of
  operations to specify how to retrieve the query
  resulta calculus expression specifies only what
  information the result should contain. This is
  the main distinguishing feature between
  relational algebra and relational calculus.
• Relational calculus is considered to be a
  nonprocedural language. This differs from
  relational algebra, where we must write a
  sequence of operations to specify a retrieval
  request hence relational algebra can be
  considered as a procedural way of stating a query.

6
Tuple Relational Calculus

• The tuple relational calculus is based on
  specifying a number of tuple variables. Each
  tuple variable usually ranges over a particular
  database relation, meaning that the variable may
  take as its value any individual tuple from that
  relation.
• A simple tuple relational calculus query is of
  the form
• t COND(t) or t where
  COND(t)
• where t is a tuple variable and COND (t) is a
  conditional expression involving t. The result of
  such a query is the set of all tuples t that
  satisfy COND (t).
• .

7
The Existential and Universal Quantifiers

• Two special symbols called quantifiers can appear
  in formulas these are the universal quantifier
  (?) aka forall, and the existential quantifier
  (?) aka exists
• Informally, a tuple variable t is bound if it is
  quantified, meaning that it appears in an (? t)
  or (? t) clause otherwise, it is free.
• If F is a formula, then so is (? t)(F), where t
  is a tuple variable. The formula (?  t)(F) is
  true if the formula F evaluates to true for some
  (at least one) tuple assigned to free occurrences
  of t in F otherwise (? t)(F) is false.
• If F is a formula, then so is (? t)(F), where t
  is a tuple variable. The formula (?  t)(F) is
  true if the formula F evaluates to true for every
  tuple (in the universe) assigned to free
  occurrences of t in F otherwise (? t)(F) is
  false.
• It is called the universal or for all
  quantifier because every tuple in the universe
  of tuples must make F true to make the
  quantified formula true.

8
Example Query Using Existential Quantifier

• Retrieve the number, name, city of all suppliers
• Query
• Range sx over suppliers
• sx.sn, sx.status, sx.city sx.city
  London
• sx.sn, sx.status, sx.city where
  suppliers.city London
• suppliers.sn, suppliers.status,
  suppliers.city
• where suppliers.city
  London
• The only free tuple variables in a relational
  calculus expression should be those that appear
  to the left of the bar ( ). In above query, sx
  is the only free variable it is then bound
  successively to each tuple. If a tuple satisfies
  the conditions specified in the query, the
  attributes sn, status, city are retrieved for
  each such tuple.

9
Example Query Using Universal Quantifier

• Get supplier names for suppliers who supply all
  parts
• Range sx over suppliers, spx over sp, px over
  parts
• sx.sname where forall px (exists spx
• ( sx.sn spx.sn spx.pn px.pn ) )
• Exclude from the universal quantification all
  tuples that we are not interested in by making
  the condition true for all such tuples. The first
  tuples to exclude (by making them evaluate
  automatically to true) are those that are not in
  the relation R of interest.
• In query above, using the expression
  not(PROJECT(x)) inside the universally quantified
  formula evaluates to true all tuples x that are
  not in the PROJECT relation.
• Remember that using De Morgans law
• forall x where p not exists x where not p,
• therefore we can express this query as
• Range sx over suppliers, spx over sp, px over
  parts
• sx.sname where notexists px (
  notexists spx
• ( sx.sn spx.sn spx.pn px.pn ) )

10
Languages Based on Tuple Relational Calculus

• The language SQL is based on tuple calculus. It
  uses the basic
• SELECT ltlist of attributesgt
• FROM ltlist of relationsgt
• WHERE ltconditionsgt
• block structure to express the queries in tuple
  calculus where the SELECT clause mentions the
  attributes being projected, the FROM clause
  mentions the relations needed in the query, and
  the WHERE clause mentions the selection as well
  as the join conditions.
• SQL syntax is expanded further to accommodate
  other operations.
• Another language which is based on tuple calculus
  is QUEL which actually uses the range variables
  as in tuple calculus.
• Its syntax includes
• RANGE OF ltvariable namegt IS ltrelation namegt
• Then it uses
• RETRIEVE ltlist of attributes from range
  variablesgt
• WHERE ltconditionsgt
• This language was proposed in the relational DBMS
  INGRES.

11
Basic Operations on tables
Project
sanchez select pn,sn from sp sanchez- pn
sn -------- 1 1 2 1 3 1 4
1 5 1 6 1 1 2 2 2 2 3
2 4 4 4 5 4 (12 rows)
Relation Calculus
sp.pn,sp.sn
SQL Select from where
12
Basic Operations on tables
Project
sanchez select pn,sn from sp sanchez- pn
sn -------- 1 1 2 1 3 1 4
1 5 1 6 1 1 2 2 2 2 3
2 4 4 4 5 4 (12 rows)
SQL Select from where
13
Basic Operations on tables
Project
sanchez select distinct(sn) from sp sn ----
1 2 3 4 (4 rows)
14
Basic Operations on tables
Restrict
15
Basic Operations on tables
Restrict
16
Basic Operations on tables
Restrict
17
Basic Operations on tables
Restrict
sanchez select pname, weight 2 as twoTimes
from parts where color'red' pname twotimes
----------------- Nut 24 Screw
28 Cog 38 (3 rows)
18
Basic Operations on tables
SQL Select from where
sanchez select sname, qty from suppliers, sp
where suppliers.snsp.sn sname qty
------------ Smith 300 Smith 200 Smith
400 Smith 200 Smith 100 Smith 100 Jones
300 Jones 400 Blake 200 Clark 200
Clark 300 Clark 400 (12 rows)
Join
Relation Calculus
suppliers.sname,sp.qty where exists sp(
sp.snsuppliers.sn)
19
Basic Operations on tables
SQL Select from where
sanchez select suppliers.sname, parts.pname
from suppliers, parts, sp where
suppliers.snsp.sn and parts.pnsp.pn sname
pname -------------- Smith Nut Smith
Bolt Smith Screw Smith Screw Smith Cam
Smith Cog Jones Nut Jones Bolt Blake
Bolt Clark Bolt Clark Screw Clark
Cam (12 rows)
Join
Relation Calculus
parts.pname, suppliers.sname where exists sp (
sp.snsuppliers.sn) and
parts ( sp.pnparts.pn)
20
Basic Operations on tables
SQL Select from where
Join
sanchez select suppliers.sn, suppliers.sname,
suppliers.status, suppliers.city, sp.pn, sp.qty
from suppliers, sp where suppliers.snsp.sn sn
sname status city pn qty
------------------------------------ 1
Smith 20 London 1 300 1 Smith
20 London 2 200 1 Smith 20
London 3 400 1 Smith 20 London
4 200 1 Smith 20 London 5
100 1 Smith 20 London 6 100 2
Jones 10 Paris 1 300 2 Jones
10 Paris 2 400 3 Blake 30
Paris 2 200 4 Clark 20 London
2 200 4 Clark 20 London 4
300 4 Clark 20 London 5 400 (12
rows)
Relation Calculus
suppliers.sn,suppliers.sname,
suppliers.status, suppliers.city,sp.pn,
sp.qty Where exists sp ( sp.snsuppliers.sn)
21
Basic Operations on tables
SQL Select from where
sanchez select suppliers.sname, parts.pname
from suppliers, parts, sp where
suppliers.snsp.sn and parts.pnsp.pn sname
pname -------------- Smith Nut Smith
Bolt Smith Screw Smith Screw Smith Cam
Smith Cog Jones Nut Jones Bolt Blake
Bolt Clark Bolt Clark Screw Clark
Cam (12 rows)
Join
Relation Calculus
parts.pname, supplier.sname Where exists sp (
sp.snsupplier.sn) and
parts ( sp.pnparts.pn)
22
Basic Operations on tables
SQL Select from where
Using Aggregate Attributes count,
sum,max,min,every,any
sanchez select avg(weight) as AverageWeight
from parts averageweight
--------------------- 15.1666666666666667 (1
row)
Relation Calculus
Avg(parts.weight)
23
Operations on tables
Provide the number of different parts provided
by each supplier (not the quantity)
Using Aggregate Attributes count,
sum,max,min,every,any
Using Aggregate Attributes
SQL
sanchez select sn, sname, count(pn) from
suppliers, sp where suppliers.snsp.sn Group by
sn,sname ERROR column reference "sn" is
ambiguous sanchez select suppliers.sn,sname,coun
t(pn) from suppliers, sp where
suppliers.snsp.sn ERROR column "suppliers.sn"
must appear in the GROUP BY clause or be used
in an aggregate function sanchez select
suppliers.sn, sname, count(pn) from suppliers,
sp where suppliers.snsp.sn Group by
suppliers.sn,sname sn sname count
------------------ 4 Clark 3 2
Jones 2 3 Blake 1 1 Smith
6 (4 rows)
Relation Calculus
suppliers.sn,suppliers.sname, count(sp.pn)
where exists sp ( sp.snsupplier.sn) group by
suppliers.sn, suppliers.sname
24
Operations on tables
Get supplier names for suppliers who supply part 1
SQL
sanchez select distinct suppliers.sname from
suppliers where suppliers.sn in (select sp.sn
from sp where sp.pn 1) sname
------- Jones Smith (2 rows)
Relation Calculus
Range sx over supplier, spx over sp sx.sname,
where spx.sn sp.sn
25
Operations on tables
Get supplier names for suppliers who supply at
least one red part
SQL
sanchez select distinct suppliers.sname from
suppliers where suppliers.sn in (select sp.sn
from sp where sp.pn in (select parts.pn from
parts where parts.color'red')) sname
------- Clark Jones Smith (3 rows)
Relation Calculus
Range sx over suppliers, spx over sp, px over
parts sx.sname where exists spx (spx.snsx.sn
exists px (px.pn spx.pn px.color
red ))
26
Operations on tables
Get supplier names for suppliers who supply all
parts
a b c
x y
SQL
a
sanchez select distinct suppliers.sname from
suppliers where not exists (select from parts
where not exists ( select from sp where
sp.sn suppliers.sn and sp.pn
parts.pn)) sname ------- Smith (1 row)
Divide
Relation Calculus
Range sx over suppliers, spx over sp, px over
parts sx.sname where notexists px (
notexists spx ( sx.sn spx.sn spx.pn
px.pn ) )
27
Operations on tables
Get supplier names for suppliers who do not
supply part 2
SQL
Difference
sanchez select distinct suppliers.sname from
suppliers where not exists (select from sp
where sp.sn suppliers.sn and sp.pn2 ) sname
------- Adams (1 row)
Relation Calculus
Range sx over suppliers, spx over sp, px over
parts sx.sname where notexists spx (
notexists spx ( sx.sn spx.sn spx.pn
2 ) )
28
Operations on tables
Intersection
Get supplier details for suppliers with number gt
1 and that are located in London
SQL
sanchez select sn,sname,city from suppliers
where sn gt 1 intersect select sn,sname,city from
suppliers where city 'London' sn sname
city ------------------- 4 Clark
London (1 row) sanchez select sn,sname,city
from suppliers where sn gt 1 and city 'London'
sn sname city ------------------- 4
Clark London // here the idea is that it could
be two different tables
Relation Calculus
Range sx,sy over suppliers sx.sn,
sx.sname,sx.city where exists sx (exists sy
( sx.sn gt 1 sy.city London
sx.snsy.sn ) )
29
Basic Operations on tables
Multiply
a a b b c c
x y x y x y
a b c
x y
Here the idea is that we can generate the
cartesian product (tuples) of the relation
for all values in the domains
30
Basic Operations on tables
Union
We can think of UNION as an INSERT operation
sanchez insert into suppliers values (1,
'Smith', 20,'London') INSERT 17750 1 sanchez
insert into suppliers values (2, 'Jones',
10,'Paris') INSERT 17751 1 sanchez insert into
suppliers values (3, 'Blake', 30,'Paris') INSERT
17752 1 sanchez insert into suppliers values
(4, 'Clark', 20,'London') INSERT 17753
1 sanchez insert into suppliers values (5,
'Adams', 30,'Athens') INSERT 17754 1