Databases Reference
In-Depth Information
Appendix B
R e l a t i o n a l O p e r a t o r s
Civilization advances by extending the number of important operations
which we can perform without thinking about them
—Alfred North Whitehead:
Introduction to Mathematics
(1911)
In this appendix, I give for purposes of reference definitions of all of the relational operators
discussed in the body of the topic. The definitions are given in alphabetical order by name (i.e.,
the name by which the operator in question is known in
Tutorial D
); they're based on definitions
in my topic
The Relational Database Dictionary
,
Extended Edition
(Apress, 2008), but I've
deliberately simplified them slightly here and there for present purposes. Observe in particular
that the operators all return a result with a defined heading and therefore a defined relation type,
which is thereby the type of any expression that represents an invocation of the operator in
question. For further discussion, please refer to
SQL and Relational Theory
.
EXTEND:
1. (
New attribute
) Let relation
r
not have an attribute called
A
. Then the
expression
EXTEND
r
: {
A
:=
exp
} returns a relation with heading the heading of
r
extended with attribute
A
and body the set of all tuples
t
such that
t
is a tuple of
r
extended
with a value for
A
that's computed by evaluating
exp
on that tuple of
r
. 2. (
Existing
attribute
) Let relation
r
have an attribute called
A
. Then the expression
EXTEND
r
:
{
A
:=
exp
} returns a relation with heading the same as that of
r
and body the set of all
tuples
t
such that
t
is derived from a tuple of
r
by replacing the value of
A
by a value that's
computed by evaluating the expression
exp
on that tuple of
r
.
GROUP:
Let the heading of relation
r
be partitioned into subsets
X
= {
X1
,
X2
,...,
Xm
} and
Y
= {
Y1
,
Y2
...,
Yn
}; also, let
YR
be an attribute name not appearing in
X
. Then the
expression
r
GROUP ({
Y1
,
Y2
,
...
,
Yn
} AS
YR
) returns a relation
s
. The heading of
s
is
{
X1
,
X2
,...,
Xm
,
YR
}, where
YR
is of type RELATION {
Y1
,
Y2
,
...
,
Yn
}. The body of
s
is
defined as follows. Let
z
be the result of
r
WRAP ({
Y1
,
Y2
,
...
,
Yn
} AS
YT
). For each
distinct
X
value
x
in
z
, let
yr
be the relation whose tuples are all and only those
YT
values
from tuples in
z
in which the
X
value is
x
; let
t
be a tuple of type TUPLE {
X
,
YR
} with
X
value
x
and
YR
value
yr
; then, and only then,
t
is a tuple of
s
.
INTERSECT:
1. (
Dyadic case
) Let relations
r1
and
r2
have the same heading
H.
Then
the expression
r1
INTERSECT
r2
returns a relation with heading
H
and body the set of all
