Download free books at BookBooN.com
An Introduction to Relational Database Theory
166
Constraints and Updating
Example 6.12: Enrolling a student on a course using INSERT
INSERT IS_ENROLLED_ON RELATION { TUPLE { StudentId SID('S3'),
CourseId('C2') } } ;
As you can see, “IS_ENROLLED_ON := IS_ENROLLED_ON UNION” in Example 6.11 has been
replaced by “INSERT IS_ENROLLED_ON”. We avoid the repeated mention of IS_ENROLLED_ON
because, as I have already stated, INSERT implicitly “retains all the existing tuples”, those being the ones
contained in the first operand of the UNION invocation in Example 6.11.
In general, INSERT rv r, where rv is a relvar name and r denotes a relation, is equivalent to
rv := rv UNION r, implying that rv and r, now referred to as the target and source, respectively, of the
INSERT statement, must have identical headings. It is normal practice to require r and the current value
of rv to have no tuples in common (i.e., their bodies to be disjoint), such that the operation fails on an
attempt, loosely speaking, to insert a tuple that already exists in the target. The reason for this normal
practice lies in the checking of key constraints. Every relvar is subject to at least one key constraint, even
when the key in question is the entire heading, as is the case with IS_ENROLLED_ON. When processing
an INSERT statement, the DBMS knows that the current value of the target relvar satisfies all the key
constraints, so only the tuples of the source need their key values to be checked for uniqueness. If it
encounters a tuple whose key value matches that of an existing tuple or another tuple in the source, the
INSERT fails and the value of rv does not change. Having discovered a clash on key values, most systems
do not bother to check to see if in fact the clashing tuples are identical, even when the key value is in fact
the entire tuple!
It is easy to imagine, therefore, that Example 6.11, though equivalent to Example 6.12 in its effect, will
take very much longer to execute when the current value of IS_ENROLLED_ON is of high cardinality.
Internally, the DBMS is very likely to execute a relvar assignment by first deleting all the existing tuples,
then inserting into the now empty target the tuples of the relation denoted by the expression on the right-
hand side of the assignment.
Remarks similar to those on key constraints apply also to other constraints that can be expressed as a
condition to be satisfied by all the tuples of a relvar (i.e., by AND(rv,c) where rv is a relvar name),
including tuple constraints in particular. (Recall that not all such constraints are tuple constraints under the
usual definition of that term.) The tuple constraint of Example 6.10 is an obvious case, but consider also
foreign key constraints. Suppose we have the constraint condition IS_EMPTY ( IS_ENROLLED_ON
NOT MATCHING IS_CALLED ) AND IS_EMPTY (IS_ENROLLED_ON NOT MATCHING
COURSE ), effectively defining { StudentId } to be a foreign key in IS_ENROLLED_ON,
referencing IS_CALLED and { CourseId } in the same relvar to be a foreign key referencing
COURSE. Then the DBMS, executing Example 6.12, need only check that student identifier S3 appears in
some tuple of the current value of IS_CALLED and course identifier C2 appears in some tuple of the
current value of
COURSE. The existing tuples of IS_ENROLLED_ON
are all guaranteed to satisfy that
constraint and therefore do not need to be reexamined. A DBMS faced with the Tutorial D method of
expressing foreign key constraints might find it quite a challenge to determine that it is only necessary to