 |
|
-
An insertion anomaly occurs when you cannot add a row to a relation because you
do not know the entire primary key value. For example, you cannot add the new client
Cathy Corbett with a ClientID of 3322 to the Client relation when you do not know
her children's names. Entity integrity prevents you from leaving any part of a
primary key null. Because ChildName is part of the primary key, you cannot leave
it null. To add the new client, your only option is to make up a ChildName, even
if the client does not have children. This solution misrepresents the facts and is
unacceptable if a better approach is available.
-
A deletion anomaly occurs when you delete data from a relation and
unintentionally lose other critical data. For example, if you delete ClientID 8112
because Helen Wandzell is no longer a client, you also lose the only instance of
VetID 24 in the database. Thus, you no longer know that VetID 24 is Pets R Us.
-
An update anomaly occurs when you change one attribute value and either the
DBMS must make more than one change to the database or else the database ends up
containing inconsistent data. For example, if you change the ClientName, VetID, or
VetName for ClientID 4519, the DBMS must change multiple rows of the Client relation.
If the DBMS fails to change all the rows, the ClientName, VetID, or VetName now has
two different values in the database and is inconsistent.
Normalization -
Database design is the process of determining the precise relations needed
for a given collection of attributes and placing those attributes into the correct
relations. Crucial to good database design is understanding the functional
dependencies of all attributes; recognizing the anomalies caused by data
redundancy, partial dependencies, and transitive dependencies when they exist; and knowing how
to eliminate the anomalies.
The process of identifying and eliminating anomalies
is called normalization.
Using normalization, you start with a collection of relations, apply sets of rules to
eliminate anomalies, and produce a new collection of problem-free relations. The
sets of rules are called normal forms. Of special interest are the first three
normal forms.
(See Normalization charts above.)
-
Data redundancy - occurs when you store the same data in more than one place.
 -
Determinate - an attribute, or collection of attributes, whose values determine
the values of another attribute (example: in 2nd Normal Form ClientID
determines ClientName, VetID, and VetName).
-
Nonkey attribute - an attribute that is not part of the primary key.
-
Null value - is the absence of a value. A null value is not blank, nor zero, nor
any other value. You give a null value to an attribute when you do not know
its value or when a value does not apply.
-
Partial dependency - a functional dependency on part of the primary key,
instead of the entire primary key.
-
Repeating group - occurs when an attribute (column) can have more than one value.
-
Transitive dependency - a functional dependency between two nonkey attributes,
which are both dependent on a third attribute.
-
(SEE COMPLETE GLOSSARY)
|
|
