Relational Algebra and Database Queries

1
Relational Algebra and Database Queries

• RNR-GEOG 417/517
• Lecture 7

2
Two Definitions

• Relational Algebra consists of a collection of
  operators, such as join, union, and intersect,
  that take relations as their operands and return
  relations as their result
• Relational Closure because the product of every
  operation is the same kind of object as the
  input, the output can become input for additional
  operations.

3
Relational Algebra Operations
Relation
Relation
Operator
Union

A
B
4
E.F. Codds 8 Operators

• Traditional
• Union
• Intersection
• Difference
• Product

• Special
• Restrict
• Project
• Join
• Divide

1970, A Relational Model of Data for Large Shared
Data Banks, in Communications of the ACM, Vol.
13, No. 6, June 1970, pp. 377-387
5
Union

• Returns a relation consisting of all tuples
  appearing in either or both of two specified
  relations.
• Relations must be same shape

6
Intersect

• Returns a relation consisting of all tuples
  appearing in both of two specified relations.
• Relations must be same shape.

7
Difference

• Returns a relation consisting of all tuples
  appearing in the first and not the second of two
  specified relations.
• Relations must be same shape

8
Examples
A
B
A union B
A intersect B
A difference B
9
Product

• Returns a relation consisting of all possible
  tuples that are a combination of two tuples, one
  from each of two specified relations. The
  cardinality of the result will be the product of
  the cardinality of the two relations, and the
  degree will be the sum of the degrees of the two
  relations.

x y


10
Restrict
S

• Returns a relation consisting of all tuples from
  a specified relation that meet a specified
  condition.
• Usually expressed as a WHERE clause.

CITY
Rome
London
Paris
London
London
Rome
S WHERE City London
London
Madrid
11
Restrict

• Returns a relation consisting of all tuples from
  a specified relation that meet a specified
  condition.
• Usually expressed as a WHERE clause.

S WHERE City London
12
Project
Parts

• Returns a relation consisting of all tuples that
  remain as (sub)tuples in a specified relation
  after specified attributes have been eliminated.

length
weight
shipper
part_no
manufacturer
Parts color, city
13
Project

• Returns a relation consisting of all tuples that
  remain as (sub)tuples in a specified relation
  after specified attributes have been eliminated.

Parts color, city
Returns a relation of the color and city
attributes of the Parts relation
14
Join

• Returns a relation consisting of all possible
  tuples that are a combination of two tuples, one
  from each of two specified relations, such that
  the two tuples contributing to any given
  combination have a common value for the common
  attribute(s) of the two relations (and that
  common value appears just once, not twice, in the
  resulting tuple).

a1 b1 a2 b1 a3 b2
b1 c1 b2 c2 b3 c3
Join
a1 b1 c1 a2 b1 c1 a3 b2 c2
15
Divide

• Takes two relations, one binary and one unary,
  and returns a relation consisting of all values
  of one attribute of the binary relation that
  match (in the other attribute) all values in the
  unary relation.

x z
a x a y a z b x c y
Divide
a
b
16
Divide an example
Imperial
17
Query Language

• A computer language used in database management
  systems to retrieve, add, modify, or delete data

18
Computer Languages

• First generation -- machine language
• Second generation -- assembly language
• Third generation -- high level languages
• Fourth generation -- 4GL languages

Byte level 1001101
ASCII American Standard Code for Information
Interchange the standard for representing
characters in all computers
Procedural Language Linear programming
FORTRAN, COBOL, BASIC, AML, C must define
objects and actions
Closer to data, more like spoken languages SQL
19
SQL Structured Query Language

• Standard language used with relational databases
  to build complex logical expressions to access
  data
• Developed by IBM in the 1970s, has become an
  industry standard
• Considered a 4GL

20
SQL Statements -- Select Query

• SELECT column(s)
• FROM table(s)
• WHERE row condition(s)
• Returns a table

21
Film Relation
OR directed by Spielberg
AND directed by Spielberg
Cast Relation
Star Relation
22
Film Relation
AND director citizen of UK
Cast Relation
Star Relation
23
RQBE

• Relational Query by Example
• A simpler method of describing a select query
• Facilitated by specific software interfaces

24
Query Design View

• QBE
• Grid

25
Constructed SQL Statement