Relational_Database-Relational_Database

Document Sample
Relational_Database-Relational_Database Powered By Docstoc
					            G64DBS                                                         Manipulating Data
        Database Systems                                         •   The Relational Data Model is designed so that
                                                                     data may be processed with mathematical
                                                                     operations.


      Lecture 3                                                  •   In maths an operation is a procedure that produces a
                                                                     new value from one or more input values.

Relational Operators                                                 •    For example: Projection



                     Tim Brailsford




                                                                     Relational Database
                                                                         Operations
                                                                 •       There are EIGHT relational database operations
                                                                     •     Four relational operations
                                                                          •   Projection

                                                                          •   Selection

                                                                          •   Join

                                                                          •   Division

                                                                     •     Four set operations
                                                                          •   Union

                                                                          •   Difference

                                                                          •   Intersection

                                                                          •   Cartesian Product




                     Algebra                                              Relational Algebra
•   Algebra
    •   “The branch of mathematics concerning the study of the
        rules of operations and relations.”

•   Operations and Operators                                     •       Relational operators take one or two relations
                                                                         as inputs and return relations as the result.
    •   “An action or procedure which produces a new value
        from one or more input values.”

    •   For example: +   -!÷                                     •       Set operators take one or two sets as inputs and
                                                                         return sets as the result.
    •   Take numbers as input and return numbers as results

•   Some operations have restrictions
    •   For example you can’t divide by zero
                           Projection                                      PROJECT Operator
       •    Extracts data vertically (i.e. as a column)
                                                                       •   PROJECT (relational operator)
                                                                       •   A unary operator
                                                                           •   i.e. it operates on a single relation

                                                                       •   Chooses some of the attributes of the relation
                                                                           •   i.e. columns of the table
                                                                           •   PROJECT Product name

                                                                               Melon
                                                                               Strawberry
                                                                               Apple
                                                                               Melon




                             Selection                                         SELECT Operator
       •    Extracts data horizontally (i.e. as a row)                 •   SELECT (relational operator)
                                                                       •   A unary operator
                                                                           •   i.e. it operates on a single relation

                                                                       •   Chooses the tuples in a relation which satisfy
                                                                           certain conditions.
                                                                           •   i.e. it selects rows of the table
                                                                           •   For example:
                                                                               SELECT Product name
                                                                               where Unit price > 500G

                                                                               Melon




                               Division                                        Division Operator
 •   Extracts rows whose column values match those                     •   / (relational operator)
     in the second table, but only returns columns that
     don’t exist in the second table.
                                                                       •   A binary operator

Sales Table                                                            •   Note:
                                            Export Destination Table       •   For A / B to be defined the attributes of B must be a
                                                                               subset of the attributes of A
                                                                           •   The tuples of A / B are all tuples d, such that for
                                                                               EVERY b in B, the combination of d and b is a tuple in
                                                                               A
                                                                               A
 The result of the division will be the
                                                                                                           B               D
 date on which there are exports to                                                                                    b
 both The Kingdom of Minanmi and                                                                                       b       d
 the Alpha Empire.
           Cartesian Product                                                   Product Operator
      •   Combines all rows in the two tables.
                                                                       •   Cartesian product operator - X (set operator)

                           X                                  3 rows   •   A binary operator

                                                                       •   The tuples of the product A B are all tuples which
                                                                           can be formed by coalescing a tuple from A and a
                                                                           tuple from B

                                                                       •   Coalescing:
                                                             3x3=
                                                             9 rows        •   (a,b,c) and (x,y,z) become (a,b,c,x,y,z)




                             Join                                                                 Join
•   Joins two tables together using a shared key
    (i.e. primary/foreign)
Product Table                           Sales Table




                   Natural Join
                                                                                        Outer Join
                   (Inner Join)
      •   Usually called JOIN                                          •    Natural joins can “lose” some tuples from one
                                                                           relation, if they do not have a match in the other
      •   JOIN is a binary operator that combines the                      relation
          PRODUCT and SELECT operators into a single
          operation.                                                   •   Outer join may retain the tuples which would be
                                                                           lost by natural join
      •   The result of the natural join of A and B is the
                                                                           •   Left outer join of A and B retains all of A
          combinations of tuples a from A and b from B
          where certain conditions are satisfied
                                                                           •   Right outer join of A and B retains all of B
                Outer Join (example)                                                                                                             Union
  Product Table                                                     Sales Table
   Product code      Product name      Unit price                        Date    Product code   Quantity
                                                                                                                             •   Combine all rows in two tables
          101           Melon            800G                            11/1        102         1,100
          102        Strawberry          150G                            11/1        101          300
          103
          104
                        Apple
                        Lemon
                                         120G
                                         200G
                                                                         11/5
                                                                         11/8
                                                                         11/9
                                                                                     103
                                                                                     101
                                                                                     105
                                                                                                 1,700
                                                                                                  500
                                                                                                  400
                                                                                                                                                     !
Product code Product name Unit price       Date     Quantity   Product code Product name Unit price        Date   Quantity
    101             Melon       800G       11/1       300          101            Melon         800G       11/1     300
    101             Melon       800G       11/8       500          101            Melon         800G       11/8     500
    102         Strawberry      150G       11/1      1,100         102          Strawberry      150G       11/1    1,100
    103             Apple       120G       11/5      1,700         103            Apple         120G       11/5    1,700
    104             Lemon       200G       NULL      NULL          105            NULL          NULL       11/9     400


Product left outer join Sales                                  Product right outer join Sales




                                                    Union                                                                           Union Operator

                                                                                                                             •   Union - ! (set operator)

                                                                                                                             •   Binary operator - it takes two relations, A and B,
                                                                                                                                 both of which must have the same set of attributes

                                                                                                                             •   The union, A ! B, consists of all the tuples in A and
                                                                                                                                 all the tuples in B




                                    Intersection                                                                                          Intersection
                •    Extract items that are included in both tables.



                                                        "
Intersection Operator                                                      Difference
                                                         •   Extracts rows from one of the tables
                                                             •   For example - all of the products in the first table that
                                                                 don’t appear in the second.
•   Intersection - " (set operator)

•   Binary operator - it takes two relations, A and B,
    both of which must have the same set of attributes

•   The intersection, A " B, consists of those tuples
    that are in both A and B




               Difference                                             Difference (2)
                                                         •   The results depend upon which table contains
                                                             rows to extract, and which has rows to exclude.
                                                             •   For example - all of the products in the second table that
                                                                 don’t appear in the first.




          Difference (2)                                     Difference Operator

                                                         •   Difference - (set operator)

                                                         •   Binary operator - it takes two relations, A and B,
                                                             both of which must have the same set of attributes

                                                         •   The difference, A - B, consists of those tuples in A
                                                             which are not in B

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:6/26/2012
language:English
pages:5