F23AF1: DATABASE Relational Algebra

1
F23AF1 DATABASE Relational Algebra

• Miss Jenny Coady
• Heriot-Watt University
• Jenny_at_macs.hw.ac.uk
• Room EMG37, Ext 4178

2
Motivation

• SQL is a declarative language that allows the
  user to express what information they require
  from the database.
• It does not allow the user to specify how that
  information will be extracted. This has the
  benefit from the user's point of view of making
  SQL much more usable because s/he does not need
  to get involved in the detailed mechanisms
  involved in satisfying the query.

3
2.

• Because of this, the responsibility for
  developing procedures for satisfying queries
  devolves to the Query Optimiser, which forms part
  of the DBMS. Relational algebra is a mathematical
  formalism that is used to express queries. It
  forms the basis of the procedural language of the
  execution plans, which are generated by the Query
  Optimiser. Execution plans specify how to satisfy
  the query. They list a sequence of procedures
  which can be applied to the database in order to
  satisfy the query.

4
3.

• The Relational Algebra consists of a set of
  fundamental operators that take relations as
  their operands and return a relation as their
  result. This means that operators can be
  composed, ie the output of one operation may be
  used as input to another operation.
• The relational algebra is a language of
  expressions that can be evaluated to yield
  relations. In a similar way, normal algebraic
  expressions, for example x5y, are evaluated to
  yield numbers. In normal algebra, one expression
  can be equivalent to another expression, for
  example, x5y y5x.

5
4.

• The operators defined in the Relational Algebra
  enable the Query Processor of the DBMS to analyse
  the structure of queries, to generate alternative
  strategies for solving them, to evaluate the
  efficiency of competing strategies, and to
  manipulate the data in the tables in order to
  generate query results.

6
Relational Algebra Operations

• Basic Operations
• SELECTION, s - Selects the rows, or tuples, from
  a relation, which satisfy the specified
  constraints or restrictions
• PROJECTION, p - Selects the specified columns,
  or attributes, from a relation
• CROSS PRODUCT, X - Combines the tuples from two
  relations to create new tuples containing
  attributes from both original relations. Every
  tuple from one relation is combined with every
  tuple from the other relation.

7
Operations 2.

• UNION, U - creates a new relation containing all
  the tuples from two relations which have the same
  structure.
• SET DIFFERENCE, \ - creates a new relation
  containing tuples from two relations which have
  the same structure, and where the tuples exist in
  the first relation but not in the second
  relation.

8
Operations 3.

• Some additional operations are
• Intersection n
• Join
• Renaming ?
• Operations rake relations as input, and output
  relations
• operations can be composed!

9
Example Relation

• Select is used to select specific tuples, or
  rows, from a single relation. We use the Greek
  symbol for s, sigma or s to represent the SELECT
  operator.
• In SQL, we specify the tuples to be selected in
  the WHERE clause. For example
• SELECT
• FROM customer c
• WHERE c.customer_no
  2
• This SQL statement selects only those tuples from
  the relation, Customer, where the customer number
  is less than 6 and greater than or equal to 2.

10
Example SELECT

• If the original relation, Customer, contained the
  following data

11
EXAMPLE SELECT
Then the result of the SELECT is as below
Four rows have been selected from the relation,
Customer. The equivalent relational algebra
expression follows sc.customer_no2 and
c.customer_no 12
Example Projection

• Deletes attributes not in the projection list
• Schema of result contains the attributes in the
  projection list
• Project is used to select specific attributes,
  fields, or columns, from a single relation. We
  use the Greek symbol for p, pi or ? to represent
  the PROJECT operator.

13
Example PROJECTION

• In SQL, we specify the attributes to be selected
  in the SELECT clause. For example
• SELECT c.name, c.tel_no
• FROM customer c
• Then the result would be
• The equivalent relational algebra expression
  follows
• pc.name, c.tel_noCustomer

14
EXAMPLE JOIN

• Join is used to combine two or more relations in
  order to form, as the result, a new relation,
  consisting of new tuples containing attributes
  from all the original relations which satisfy the
  specified conditions.
• SELECT
• FROM supplier S, purchase_order PO
• WHERE S.supplier_no PO.supplier_no
• This SQL statement selects only those tuples from
  the relations, Supplier and Purchase_Order, where
  the supplier_no in the Supplier relation matches
  the supplier_no in the Purchase_Order relation,
  and creates a new relation containing tuples
  consising of attributes from both relations.

15
EXAMPLE JOIN

• For example, if the original relations, Supplier
  and Purchase_Order, contained the following data
• Supplier

16
EXAMPLE JOIN
And Purchase
17
EXAMPLE JOIN
Then the result of the join would be a new
relation with the structure shown below and
containing the tuples listed below
18
EXAMPLE JOIN

• This is an example of an equijoin, where the
  condition for matching tuples from both relations
  depends on the values of the matching field, or
  fields, in both relations being equal.
• A natural join is a special case of an equijoin,
  where all attributes that have the same name in
  the relations to be joined form the basis of the
  join.

19
EXAMPLE JOIN

• The equivalent relational algebra expression
  follows
• Supplier S.supplier_noPO.supplier_no
  Purchase_Order
• This shows the name of the first relation,
  Supplier, followed by JOIN operator, followed by
  the condition controlling the join, which
  specifies which attributes, fields, or columns
  are to be matched, followed by the name of the
  second relation, Purchase_Order, to which the
  join will be applied.

20
EXAMPLE CROSS PRODUCT

• This SQL statement combines the tuples from two
  relations to create new tuples containing
  attributes from both original relations. Every
  tuple from one relation is combined with every
  tuple from the other relation.
• SELECT
• FROM supplier S, purchase_order PO
• What happens if we apply this given the original
  data?
• The equivalent relational algebra expression
  follows
• Supplier Purchase_Order
• This shows the name of the first relation,
  followed by the CROSS PRODUCT operator, followed
  by the name of the second relation, to which the
  cross product will be applied.

21
CONDITION JOIN

• Contains those tuples of the cross product that
  satisfy some condition
• The result schema is the same as that of the
  cross product
• However there are fewer tuples than cross product
  might be computationally more efficient
• E.g. S S1.supplier_no

22
EQUI-JOIN

• A special case of condition join where the
  condition cond contains only equalities
• E.g. S S1.supplier_no P.supplier_no P
• Result schema similar to cross product, but only
  one copy of fields for which equality is
  specified

23
Work through Examples

• Taking into account the sailors database
  previously
• used in class then write the possible relational
• algebra statements for
• Find the names of the sailors whove reserved
  boat 103
• Find the names of the sailors whove reserved a
  red boat
• Find all sailors who have reserved a red or green
  boat