Relational Algebra and Database Queries

Document Sample
Relational Algebra and Database Queries Powered By Docstoc
					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
    additional operations.


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

   Traditional                                           Special
       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

                                                      Madrid


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
        Imperial   Burn After Reading
                                             Divide
        Imperial     My Little Pony
        Imperial    Reservoir Dogs
          Royal      My Little Pony
          Royal    Burn After Reading                          Imperial




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


A computer language used in database
 management systems to retrieve, add,
 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