Relational Querying II

1
Relational Querying II
2
Objectives

• Understanding of relational algebra
• Basic understanding of relational calculus
• Relationship between SQL, algebra and calculus

3
Relational Algebra

• Relational algebra defines a set of operators
  that may work on relations.
• Relations are just sets of data. As such,
  relational algebra deals with set theory.
• The operators in relational algebra are very
  similar to traditional algebra except that they
  apply to sets.

4
Relational Algebra

• Relational algebra provides several main
  operators
• Union
• Difference
• Intersection
• Product
• Projection
• Selection (Restriction)
• Join

5
Union Operator

• The union operator adds tuples from one relation
  to another relation to form a third relation
• A union operation will result in a combined
  relation
• The Union of relations A and B is denoted as A
  B or Ap_code Bp_code

A
B
A UNION B A ? B
6
Union Example

• returns set of all rows from two tables. The
  tables have to have the same characteristics,
  i.e. be union compatible
• A B

7
Difference Operator

• The difference operator produces a third relation
  that contains the tuples that appear in the first
  relation, but not the second
• This is similar to a subtraction
• Just like maths the order matters
• Also union compatible
• This is denoted A B or
• Af_name - Bf_name

A
B
A - B
8
Difference Example

• returns set of all rows in one table (A) that are
  not found in the other (B)
• A B

9
Intersection Operator

• An intersection operation will produce a third
  relation that contains the tuples (rows) that are
  common to the relations involved.
• This is similar to the logical operator AND
• Also union compatible
• Example
• (A ? B)

A
B
A INTERSECT B
10
Intersection Example

• returns set of all rows belonging to A and B
  relations, i.e. rows that appear in BOTH tables
• A B

11
Product Operation

• A product operator is a concatenation of every
  tuple in one relation with every tuple in a
  second relation (Cartesian product)
• The resulting relation will have x times y
  tuples, where
• x the number of tuples in the first relation
  and
• y the number of tuples in the second relation
• This is similar to multiplication A ? B

12
Product Example
A
B
13
Project Operator

• A projection operation produces a second relation
  that is a subset of the first.
• The subset is in terms of attributes, not tuples
• The resulting relation will contain a limited
  number of columns. However, every tuple will be
  listed.
• Af_name, s_name

A
Project x,y, z(A)
14
Project Example

• returns selected attributes for all rows in
  relation A
• A

15
Select (Restrict) Operator

• The selection operator (sometimes called
  restrict) is similar to the projection operator.
  It produces a second relation that is a subset of
  the first. Uses a condition
• However, the selection operator produces a subset
  of tuples (rows) , not attributes (columns).
• The resulting relation contains all columns, but
  only contains a portion of the tuples.
• A where store gt 24

A
16
Select (Restrict) Example

• Return rows from a relation where qualifier is
  true.
• A A where store gt 24

17
Join Operator

• The join operator is a combination of the
  product, selection, and projection operators.
• There are several variations of the join
• operator
• Equi-join
• Natural join

18
Join Operator (Cont.)

• A join works along these lines
• (a) Form the product of A and B
• (b) Perform a selection to eliminate some tuples
• Criteria for the selection are specified as
  part of
• the join
• (c) Then remove some attributes through
  projection (optional)
• A join (A.TutorId B.StaffNo) B

19
Join Example (Data)
A
B
20
Join Example (Equi-join)
21
Join Example (Natural Join)
22
Relational Algebra and SQL

• Relational algebra gives a useful mathematical
  basis for relational databases
• Was used to demonstrate that complex processing
  could be performed on relational databases
• Implemented as a friendly language via SQL
• has direct support of all algebra operators
• SQL queries formulated as table expressions
• e.g. SELECT x, y
• FROM A
• WHERE A.city Paris

23
Example Queries 1

• Retrieve branch numbers and the city for all
  branches
• SQL
• select branchNo, city
• from branch
• Relational Algebra
• BRANCH branch_no, city

24
Example Queries 2

• Retrieve all information about branch B003
• SQL
• select
• from branch
• where branchNo B003
• Relational Algebra
• BRANCH WHERE branchNo B003
• The project operation is not strictly necessary
  here, why?

25
Example Queries 3

• Get the branch number and staff names at each
  branch
• SQL
• select branch.branchNo, branch.street, staff.name
• from branch, staff
• where branch.branchNo staff.branchNo
• Relational Algebra
• (BRANCH JOIN (branch.branchNo staff.branchNo)
  STAFF) branch.branchNo, branch.street,
  staff.name
• This is the same as the ordered pairs operation
  shown earlier

26
Relational Completeness

• No system is fully relational unless it supports
  either the relational algebra or the relational
  calculus
• A language is said to be relationally complete
  if it is at least as powerful as the algebra
  (Date, 2000)
• i.e. can produce every query that the (original)
  relational algebra can
• Relational calculus is the basis of SQL
• ISO standard relational data manipulation
  language found in Oracle, Ingres, DB2, SQLServer,
  mySQL, etc.
• Even Access!

27
Relational Calculus

• Alternative to relational algebra
• algebra provides a collection of explicit
  operations to tell system how to construct some
  desired relation from other sets
• calculus is a notation for stating definition of
  the desired relation in terms of given relations
• descriptive rather than prescriptive
• there is a one-to-one mapping between the algebra
  and calculus

28
Relational Calculus

• Based on predicate calculus
• i.e. write propositions which evaluate to true or
  false
• Interested in finding tuples (rows) for which a
  predicate is true
• Based on the use of tuple (or range) variables
• a variable that ranges over a named relation
• e.g. RANGE OF S IS Staff
• S P(S)
• where P is the Predicate applied to S, e.g.
• S S.Salary gt 10000

29
Relational Calculus Quantifiers

• There are two quantifiers
• existential quantifier ? (there exists)
• universal quantifier ? (for all)
• e.g. The following 3 queries are equivalent
• RANGE OF B IS Branch
• (1) B ? B (B.Bno S.Bno ? B.City
  London)
• (2) B ? B (B.City Paris)
• (3) B ? B (B.City Paris)
• SQL has direct support of EXISTS and range
  variables

30
SQL

• SQL is a mix of relational calculus and
  relational algebra
• includes direct support of join and union from
  relational algebra
• uses range variables and existential quantifier
  of the calculus
• SQL queries formulated as table expressions
• e.g. SELECT PX.COLOUR, PX.CITY
• FROM P AS PX
• WHERE PX.CITY ltgt Paris AND PX.WEIGHT gt 10.0

31
Summary

• Relational data model based on set theory and
  included a number of rules to allow consistency
  in the data model and manipulation
• Relational algebra is the basis of relational
  query languages, based on set manipulation
  operations
• SQL is the most successful relational query
  language
• Structured Query Language
• Based on relational calculus
• Similarities to relational algebra operators

32
Further Reading

• Relational Algebra
• Section 10 of SQL book
• Connolly and Begg, chapter 4
• Next session
• Data modelling