Data Integrity
Data integrity means, in part, that you can
correctly and consistently navigate and manipulate the tables in
the database.
There are two basic rules to ensure data integrity:
1. Entity integrity
2. Referential integrity
The entity integrity rule states that
the value of the primary key can never be a null value (a null
value is one that has no value and is not the same as a blank).
Because a primary key is used to identify a unique row in a
relational table, its value must always be specified and should
never be unknown. The integrity rule requires that insert,
update, and delete operations maintain the uniqueness and
existence of all primary keys.
The referential integrity rule states
that if a relational table has a foreign key, then every value
of the foreign key must either be null or match the values in
the relational table in which that foreign key is a primary key.
Relational Data Manipulation
Relational tables are sets. The rows of the tables can be
considered as elements of the set. Operations that can be
performed on sets can be done on relational tables. The eight
relational operations are:
Union
The union operation of two relational tables is formed
by appending rows from one table to those of a second table to
produce a third. Duplicate rows are eliminated. The notation for
the union of Tables A and B is A UNION B.
The relational tables used in the union operation must be
union compatible. Tables that are union compatible must have the
same number of columns and corresponding columns must come from
the same domain. Figure1 shows the union of A and B.
|
K |
X |
Y |
|
1 |
B |
4 |
|
2 |
C |
6 |
|
3 |
D |
8 |
|
4 |
E |
10 |
|
5 |
F |
12 |
Difference
The difference of two relational tables is a third
that contains those rows that occur in the first table but not
in the second. The Difference operation requires that the tables
be union compatible. As with arithmetic, the order of
subtraction matters. That is, A - B is not the same as B - A.
Figure2 shows the different results.
The Difference Operator
Intersection
The intersection of two relational tables is a third table
that contains common rows. Both tables must be union compatible.
The notation for the intersection of A and B is A [intersection]
B = C or A INTERSECT B. Figure3 shows the single row [1, A, 2]
appears in both A and B.
|
A |
|
K |
X |
Y |
|
1 |
A |
2 |
|
2 |
B |
4 |
|
3 |
C |
6 |
|
|
B |
|
K |
X |
Y |
|
1 |
A |
2 |
|
4 |
D |
8 |
|
5 |
E |
10 |
|
|
A INTERSECTION B |
|
K |
X |
Y |
|
1 |
A |
2 |
|
Product
The product of two relational tables, also called the
Cartesian Product, is the concatenation of every row in one
table with every row in the second. The product of table A
(having m rows) and table B (having n rows) is the table C
(having m x n rows). The product is denoted as A X B or A TIMES
B.
|
A |
|
K |
X |
Y |
|
1 |
A |
2 |
|
2 |
B |
4 |
|
3 |
C |
6 |
|
Product of AB |
|
A Times B |
|
ak |
ax |
ay |
bk |
bx |
by |
|
1 |
A |
2 |
1 |
A |
2 |
|
1 |
A |
2 |
4 |
D |
8 |
|
1 |
A |
2 |
5 |
E |
10 |
|
2 |
B |
4 |
1 |
A |
2 |
|
2 |
B |
4 |
4 |
D |
8 |
|
2 |
B |
4 |
5 |
E |
10 |
|
3 |
C |
6 |
1 |
A |
2 |
|
3 |
C |
6 |
4 |
D |
8 |
|
3 |
C |
6 |
5 |
E |
10 |
|
|
B |
|
K |
X |
Y |
|
1 |
A |
2 |
|
4 |
D |
8 |
|
5 |
E |
10 |
|
The project operator retrieves a subset of columns
from a table, removing duplicate rows from the result.
Selection
The select operator, sometimes called restrict to prevent
confusion with the SQL SELECT command, retrieves subsets of rows
from a relational table based on a values in a column or
columns.
Join
A join operation combines the product, selection, and,
possibly, projection. The join operator horizontally combines
(concatenates) data from one row of a table with rows from
another or the same table when certain criteria are met. The
criteria involve a relationship among the columns in the join
relational table. If the join criterion is based on equality of
column value, the result is called an equijoin. A
natural join is an equijoin with redundant columns removed.
Figure 5 illustrates a join operation. Tables D and E are
joined based on the equality of k in both tables. The first
result is an equijoin. Note that there are two columns named k;
the second result is a natural join with the redundant column
removed.
|
D |
|
k |
x |
y |
|
1 |
A |
2 |
|
2 |
B |
4 |
|
3 |
C |
6 |
|
4 |
D |
8 |
|
5 |
E |
10 |
|
|
E |
|
k |
z |
|
1 |
20 |
|
4 |
24 |
|
5 |
28 |
|
7 |
32 |
|
9 |
36 |
|
|
Equijoin |
|
k |
x |
y |
k |
z |
|
1 |
A |
2 |
1 |
20 |
|
4 |
D |
8 |
4 |
24 |
|
5 |
E |
10 |
5 |
28 |
|
|
Natural Join |
|
k |
x |
y |
z |
|
1 |
A |
2 |
20 |
|
4 |
D |
8 |
24 |
|
5 |
E |
10 |
28 |
|
Division
The division operator results in columns values in one table
for which there are other matching column values corresponding
to every row in another table.
|
A |
|
K |
X |
Y |
|
10 |
1101 |
A |
|
10 |
1201 |
B |
|
10 |
1301 |
C |
|
20 |
1201 |
B |
|
30 |
1101 |
A |
|
30 |
1201 |
B |
|
30 |
1301 |
C |
|
| B
(DIVISOR) |
|
X |
Y |
|
1101 |
A |
|
1201 |
B |
|
1301 |
C |
|
|
