s
Section 5.3 Relations and Databases
375
Surely this describes Bruno. However, remember that the result of any comparison involving NULL is NULL, so
PetType = “Dog” AND Breed = NULL
“Dog = “Dog” AND NULL = NULL
True AND NULL
NULL
which then is set to False. Contrary to intuition, Bruno does not satisfy this query
either. The only true fact about Bruno is that he is a dog.
The SQL query
seLeCtPetName
FrOM Pet
Where PetType = “Dog”
aND Breed Is NULL;
is a completely different query from the preceding one. The WHERE clause is
asking whether the Breed attribute for any tuple has the value NULL. This query
would produce the following result because Bruno is the only tuple in the Pet table
with a NULL value for Breed.
Isnull
petName
Bruno
database integrity
New information must be added to a database from time to time, obsolete information deleted, and changes or updates made to existing information. In other
words, the database will be subjected to add, delete, and modify operations. An
add operation can be carried out by creating a second relation table with the new
information and performing a set union of the existing table and the new table.
Delete can be accomplished by creating a second relation table with the tuples to
be deleted and performing a set difference that subtracts the new table from the
existing table. Modify can be achieved by a delete (of the old tuple) followed by an
add (of the modified tuple).
These operations must be carried out so that the information in the database
remains in a correct and consistent state that agrees with the business rules. Enforcing three “integrity rules” will help. Data integrity requires that the values
for an attribute do indeed come from that attribute’s domain. In our example, for
instance, values for the State attribute of Person must be legitimate two-letter
state abbreviations (or the NULL value). entity integrity, as we discussed earlier,
requires that no component of a primary key value be NULL. These integrity
constraints clearly affect the tuples that can be added to a relation.
referential integrity requires that any values for foreign keys of child relations into parent relations either be NULL or have values that match values in the
corresponding primary keys of the parent relations. The referential integrity constraint affects both add and delete operations (and therefore modify operations).
Section 5.3 Relations and Databases
375
Surely this describes Bruno. However, remember that the result of any comparison involving NULL is NULL, so
PetType = “Dog” AND Breed = NULL
“Dog = “Dog” AND NULL = NULL
True AND NULL
NULL
which then is set to False. Contrary to intuition, Bruno does not satisfy this query
either. The only true fact about Bruno is that he is a dog.
The SQL query
seLeCtPetName
FrOM Pet
Where PetType = “Dog”
aND Breed Is NULL;
is a completely different query from the preceding one. The WHERE clause is
asking whether the Breed attribute for any tuple has the value NULL. This query
would produce the following result because Bruno is the only tuple in the Pet table
with a NULL value for Breed.
Isnull
petName
Bruno
database integrity
New information must be added to a database from time to time, obsolete information deleted, and changes or updates made to existing information. In other
words, the database will be subjected to add, delete, and modify operations. An
add operation can be carried out by creating a second relation table with the new
information and performing a set union of the existing table and the new table.
Delete can be accomplished by creating a second relation table with the tuples to
be deleted and performing a set difference that subtracts the new table from the
existing table. Modify can be achieved by a delete (of the old tuple) followed by an
add (of the modified tuple).
These operations must be carried out so that the information in the database
remains in a correct and consistent state that agrees with the business rules. Enforcing three “integrity rules” will help. Data integrity requires that the values
for an attribute do indeed come from that attribute’s domain. In our example, for
instance, values for the State attribute of Person must be legitimate two-letter
state abbreviations (or the NULL value). entity integrity, as we discussed earlier,
requires that no component of a primary key value be NULL. These integrity
constraints clearly affect the tuples that can be added to a relation.
referential integrity requires that any values for foreign keys of child relations into parent relations either be NULL or have values that match values in the
corresponding primary keys of the parent relations. The referential integrity constraint affects both add and delete operations (and therefore modify operations).