 |
CSCI A114 / INFO I111
|
A relation is a subset of a cartesian product. A relation is a set.
Union
The union of relations R and S is the set of tuples that are in R and S or both. We only apply the union operator to relations of the same arity, so all tuples in the result have the same number of components.
Example: consider the following two relations R and S
|
|
| a | b | c |
| d | a | f |
| c | b | d |
| b | g | a |
Set Difference
The difference of two relations R and S is the set of tuples in R but not in S.
For example the difference of R and S is:
| a | b | c |
| c | b | d |
Cartesian Product
If R and S are two relations of arities k1 and k2 their cartesian product is the set of (k1 + k2) -tuples whose first k1 components form a tuple in R and whose last k2 components form a tuple in S.
Example: for the relations above this is the cartesian product.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| a | b | c | b | g | a |
| a | b | c | d | a | f |
| d | a | f | b | g | a |
| d | a | f | d | a | f |
| c | b | d | b | g | a |
| c | b | d | d | a | f |
Projection
The idea behind projection is that we take a relation R, remove some components (columns, attributes) and/or rearrangesome of the remaining components. We denote the operation by P followed by a sequence of indices, that identify the components to be kept.
Example: here's PA,C(R)
| A | C |
|---|---|
| a | c |
| d | f |
| c | d |
Selection
If we have a formula F that involves column names, constants, arithmetic comparison
operators (<, <=, etc.), and logical operators (such
as and, not, or) then we denote selection of
tuples according to F as
SF(R)and it represents the set of all tuples that satisfy that condition.
Example: here's SB=b(R)
| A | B | C |
|---|---|---|
| a | b | c |
| c | b | d |
Intersection
This is the set of all tuples that appear in both relations.
Note that R and S must have the same arity.
Example: the intersection of R and S above is
| d | a | f |
Quotient
Let R and S be relations of arity r and s, respectively, where r > s and S is not empty. Then the quotient of R by S is the set of (r-s)-tuples t such that for all s-tuples u in S the tuple tu is in R.
Example: with R and S as below
|
|
The quotient of the first by the second is:
| a | b |
| e | d |
Join
A join involves two columns: i in R and j in S and a formula F.
If R has arity r then JF(i, j)(R, S) is
SF(i, r+j)(R x S)where by R x S we denote the cartesian product between R and S.
Example:
|
|
Then JB<D(R, S) is
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 2 | 3 | 3 | 1 |
| 1 | 2 | 3 | 6 | 2 |
| 4 | 5 | 6 | 6 | 2 |
Natural Join
The natural join is applicable when both R and S have columns that are named by attributes. To compute the natural join first compute the cartesian product and then for each attribute A that names both a column in R and a column in S select from the cartesian product those tuples whose values agree in the columns for R.A and S.A.
Example:
|
|
Then NatJoin(R, S) is
| A | B | C | D |
|---|---|---|---|
| a | b | c | d |
| a | b | c | e |
| d | b | c | d |
| d | b | c | e |
| c | a | d | b |
A114/I111