# Relational Algebra and Database Queries

Document Sample

```					Relational Algebra and Database
Queries

RNR-GEOG 417/517
Lecture 7
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

RNR 417/517          Lecture 7: Relational Agebra   2
Relational Algebra Operations

Relation   Operator         Relation                 Relation

A     Union                 B               =   AB

AB        Union                 C               =   ABC

RNR 417/517                Lecture 7: Relational Agebra                  3
E.F. Codd’s 8 Operators

   Union                                                      Restrict
Most Common
   Intersection                                               Project    Operators

   Difference                                                 Join
   Product                                                    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

RNR 417/517                          Lecture 7: Relational Agebra                      4
Union

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

RNR 417/517    Lecture 7: Relational Agebra   5
Intersect

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

RNR 417/517         Lecture 7: Relational Agebra   6
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

RNR 417/517        Lecture 7: Relational Agebra   7
Examples                  Are these tables the same shape?

A                                               B
S#         SNAME   STATUS CITY                S#            SNAME   STATUS CITY
S1         Smith        20 London             S1            Smith        20 London
S4         Clark        20 London             S2            Jones        20 Paris

S#            SNAME   STATUS    CITY
A union B                              S1
S4
Smith
Clark
20
20
London
London
S2            Jones        20   Paris

A intersect B                          S#
S1
SNAME
Smith
STATUS CITY
20 London

S#            SNAME   STATUS CITY
A difference B                         S4            Clark        20 London

RNR 417/517                 Lecture 7: Relational Agebra                            8
Product
1       2
Returns a relation consisting of
all possible tuples that are a
combination of two tuples,
one from each of two                              a   x            a   x
*      2   =
specified relations. The                  3       b   y            a   y
cardinality of the result will                    c                b   x
be the product of the                                                          6
cardinality of the two                                             b   y
relations, and the degree                                          c   x
will be the sum of the
degrees of the two                                                 c   y
relations.

RNR 417/517               Lecture 7: Relational Agebra                       9
Restrict
S
Returns a relation
CITY
consisting of all tuples
from a specified                                    Rome
relation that meet a                                London
specified condition.                                Paris
Usually expressed as a
London
WHERE clause.
London

Rome
S    WHERE    City = London                           London

RNR 417/517            Lecture 7: Relational Agebra            10
Restrict
S
Returns a relation
CITY
consisting of all tuples
from a specified                                    London
relation that meet a                                London
specified condition.                                London
Usually expressed as a
London
WHERE clause.

S     WHERE   City = London

RNR 417/517            Lecture 7: Relational Agebra            11
Project

Returns a relation consisting of                                                    Parts
all tuples that remain as

city
color
part_no

length

shipper
manufacturer
weight
(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

RNR 417/517               Lecture 7: Relational Agebra                                                               12
Project

Returns a relation consisting of                         Parts
all tuples that remain as

city
color
(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

RNR 417/517               Lecture 7: Relational Agebra                  13
Join                                                     Common Attributes

Returns a relation consisting of
all possible tuples that are a               a1         b1
combination of two tuples,
b1        c1
one from each of two                         a2         b1           b2        c2
specified relations, such                    a3         b2           b3        c3
that the two tuples                          a2          b2
contributing to any given                                     Join
a1          b3
combination have a
common value for the
common attribute(s) of the                             a1      b1         c1
two relations (and that                                a2      b1         c1
common value appears just                              a3      b2         c2
once, not twice, in the
resulting tuple).                                      a2      b2         c2
a1      b3         c3

RNR 417/517               Lecture 7: Relational Agebra                                14
Divide

Takes two relations, one
Binary          Unary
binary and one unary,
and returns a relation                    a             x        x
consisting of all values
of one attribute of the                   a             y        z
binary relation that                      a             z Divide
match (in the other                       b             x
attribute) all values in                  c             y
the unary relation.                        b            y
b            z              a
b

RNR 417/517           Lecture 7: Relational Agebra                           15
Divide – an example

Theatre         Movie                              Movies I want to see
Regal      Reservoir Dogs                               Reservoir Dogs
Regal     Burn After Reading                            My Little Pony
Divide
Imperial     My Little Pony
Imperial    Reservoir Dogs
Royal      My Little Pony

RNR 417/517                     Lecture 7: Relational Agebra                      16
Query Language

A computer language used in database
modify, or delete data

RNR 417/517    Lecture 7: Relational Agebra   17
Computer Languages
machine

   First generation -- machine language
Byte level – 1001101

   Second generation -- assembly language
ASCII: American Standard Code for Information Interchange –
the standard for representing characters in all computers
   Third generation -- “high level” languages
Procedural Language – Linear programming – FORTRAN,
COBOL, BASIC, AML, C++ – must define objects and actions
   Fourth generation -- “4GL” languages
Closer to data, more like spoken languages – SQL

data

RNR 417/517                      Lecture 7: Relational Agebra          18
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”

RNR 417/517          Lecture 7: Relational Agebra   19
SQL Statements -- Select Query
Project
   SELECT column(s)
Join
   FROM table(s)
   WHERE row condition(s)
Restrict

   Returns a table

Relational Closure

RNR 417/517           Lecture 7: Relational Agebra                        20
Film Relation
TITLE             DIRECTOR CNTRY YEAR LENGTH
Jaws                      Spielberg    USA 1977 125
Star Wars                 Lucas        USA 1977 121
American Graffiti         Lucas        USA 1973 110
Raiders of the Lost Ark   Spielberg    USA 1981 115
A Bridge Too Far          Attenborough UK  1977 175
Manhattan                 Allen        USA 1979 96
Kramer v Kramer           Benton       USA 1979 105               Name of all films made after 1978
The Deer Hunter           Cimino       UK  1978 182
The Great Gatsby          Clayton      UK  1974 140                     OR directed by Spielberg
ANDdirected by Spielberg
The Godfather             Coppola      USA 1972 175
French Lieut’s Woman      Riesz        UK  1981 123
All The President’s       Pakula       USA 1976 129                           Cast Relation
Men                                                    FILM_STAR          FILM_TITLE                 ROLE
Last Tango in Paris                Or 1972 129
IT/FR statements expand the possible
Bertolucci
Marlon Brando  The Godfather             Don Vito Corleone
Superman                  Donner UK    1978  143
Marlon Brando  Last Tango in Paris       Paul
Marathon Man                       number of126
USA
Schlessinger 1976   returned tuples
Marlon Brando  Superman                  Superman’s Father
Harrison Ford  Star Wars                 Han Solo
the Raiders of
And statements restrictFord possible the Lost Ark
Harrison                                 Indiana Jones
Star Relation                 Harrison Ford
number of returned tuples. American Graffiti Men           Bob Flafa
Dustin Hoffman All the President’s       Carl Bernstein
NAME             BIRTH_YEAR GENDER CNTY          Dustin Hoffman Marathon Man              Babe Levy
Marlon Brando            1924         M     USA      Dustin Hoffman Kramer v Kramer           Ted Kramer
Harrison Ford            1942         M     USA      Robert Redford All the President’s Men   Bob Woodward
Dustin Hoffman           1937         M     USA      Robert Redford A Bridge Too Far
Robert Redford Great Gatsby              Gatsby
Robert Redford           1937         M     USA      Meryl Streep   Manhattan                 Jill
Meryl Streep             1949         F     USA      Meryl Streep   Kramer v Kramer           Joanna Kramer
Meryl Streep   The Deer Hunter           Linda
Meryl Streep   French Lt’s Woman         Sarah/Anna

RNR 417/517                                 Lecture 7: Relational Agebra                                21
Film Relation
TITLE             DIRECTOR CNTRY YEAR LENGTH
Jaws                      Spielberg    USA 1977 125
Star Wars                 Lucas        USA 1977 121
American Graffiti         Lucas        USA 1973 110
Raiders of the Lost Ark   Spielberg    USA 1981 115                       Name of all films whose star was
A Bridge Too Far          Attenborough UK  1977 175
Manhattan                 Allen        USA 1979 96                              born before 1940
Kramer v Kramer           Benton       USA 1979 105
The Deer Hunter
The Great Gatsby
Cimino
Clayton
UK
UK
1978
1974
182
140
AND director citizen of UK
The Godfather             Coppola      USA 1972 175
French Lieut’s Woman      Riesz        UK  1981 123
All The President’s       Pakula       USA 1976 129                                     Cast Relation
Men                                                                      FILM_STAR            FILM_TITLE                 ROLE
Last Tango in Paris       Bertolucci     IT/FR   1972     129
Marlon Brando    The Godfather             Don Vito Corleone
Superman                  Donner          UK     1978     143
Marlon Brando    Last Tango in Paris       Paul
Marathon Man              Schlessinger    USA    1976     126
Marlon Brando    Superman                  Superman’s Father
Harrison Ford    Star Wars                 Han Solo
Harrison Ford    Raiders of the Lost Ark   Indiana Jones
Star Relation                                Harrison Ford    American Graffiti         Bob Flafa
Dustin Hoffman   All the President’s Men   Carl Bernstein
NAME       BIRTH_YEAR GENDER CNTY                                  Dustin Hoffman   Marathon Man              Babe Levy
Marlon Brando      1924     M     USA                                  Dustin Hoffman   Kramer v Kramer           Ted Kramer
Harrison Ford      1942     M     USA                                  Robert Redford   All the President’s Men   Bob Woodward
Dustin Hoffman     1937     M     USA                                  Robert Redford   A Bridge Too Far
Robert Redford   Great Gatsby              Gatsby
Robert Redford     1937     M     USA                                  Meryl Streep     Manhattan                 Jill
Meryl Streep       1949      F    USA                                  Meryl Streep     Kramer v Kramer           Joanna Kramer
Meryl Streep     The Deer Hunter           Linda
Meryl Streep     French Lt’s Woman         Sarah/Anna

RNR 417/517                                          Lecture 7: Relational Agebra                                           22
RQBE
   Relational Query by Example

   A “simpler” method of describing a select
query

   Facilitated by specific software interfaces

RNR 417/517          Lecture 7: Relational Agebra   23
Query Design View

QBE
Grid

RNR 417/517   Lecture 7: Relational Agebra   24
Constructed SQL Statement

Project

Join

Restrict
RNR 417/517              Lecture 7: Relational Agebra             25

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 4 posted: 8/18/2011 language: English pages: 25
How are you planning on using Docstoc?