Database Reference
In-Depth Information
Summary
A column (attribute) B is functionally dependent on another column A (or possibly a collection of columns)
when each value for A in the database is associated with exactly one value of B.
The primary key is a column (or a collection of columns) A such that all other columns are functionally
dependent on A and no subcollection of the columns in A also has this property.
When there is more than one choice for the primary key, one of the possibilities is chosen to be
the
179
primary key. The others are referred to as candidate keys.
A table (relation) is in first normal form (1NF) when it does not contain repeating groups.
A column is a nonkey column (also called a nonkey attribute) when it is not a part of the primary key.
A table (relation) is in the second normal form (2NF) when it is in first normal form and no nonkey column
is dependent on only a portion of the primary key.
A determinant is any column that functionally determines another column.
A table (relation) is in third normal form (3NF) when it is in second normal form and the only determinants
it contains are candidate keys.
A collection of tables (relations) that is not in third normal form has inherent problems called update anom-
alies. Replacing this collection with an equivalent collection of tables (relations) that is in third normal form
removes these anomalies. This replacement must be done carefully, following a method like the one pro-
posed in this text. If not, other problems, such as those discussed in this chapter, may be introduced.
A table (relation) is in fourth normal form (4NF) when it is in third normal form and there are no multivalued
dependencies.
Key Terms
alternate key
Boyce-Codd normal form (BCNF)
candidate key
concatenation
dependency diagram
determinant
first normal form (1NF)
fourth normal form (4NF)
functional dependence
functionally dependent
functionally determines
interrelation constraint
multidependent
multidetermine
multivalued dependence
nonkey attribute
nonkey column
normal form
normalization process
partial dependency
primary key
repeating group
second normal form (2NF)
third normal form (3NF)
unnormalized relation
update anomaly
Review Questions
1. Define functional dependence.
2. Give an example of a column A and a column B such that B is functionally dependent on A. Give an example of
a column C and a column D such that D is
not
functionally dependent on C.
3. Define primary key.
4. Define candidate key.
5. Define first normal form.
6. Define second normal form. What types of problems would you find in tables that are not in second normal form?
7. Define third normal form. What types of problems would you find in tables that are not in third normal form?
8. Define fourth normal form. What types of problems would you find in tables that are not in fourth normal form?
