# Relational_Database-Relational_Database by yvtong

VIEWS: 13 PAGES: 5

• pg 1
```									            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 deﬁned 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 satisﬁed
•   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 ﬁrst 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 ﬁrst.

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

```
To top