15.00 - Meaning of Third Normal Form in This Manual - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Meaning of Third Normal Form in This Manual

For purposes of this manual, use of the term 3NF generally covers BCNF as well. Relations in BCNF are often awkward to deal with, however, and are usually more tractable if decomposed into other relations in 4NF.

Example : 3NF

For example, consider the following relations:

 

If these relations had been designed so that the CustName attribute was in the Order relation, 3NF would have been violated because there would be a one-to-one relationship between two nonkey attributes, CustNum and CustName.

Example : Violating 3NF

Suppose the Order relation had been structured like this:

 

The potential update anomalies associated with this violation of 3NF are the following:

  • Changed customer name.
  • To change the name of a customer, you must find every occurrence of its customer name.

  • Inconsistent customer names.
  • Adams might be misspelled Addams in an occurrence, which would cause the tuple containing the misspelled customer name to be missed in a query having the WHERE predicate customername = Adams.

    Worse still, customer number 2 might be labeled Adams in one tuple and Zoller in another.

  • Inability to add new customer names unless they have an order placed.
  • Example : BCNF

    Now consider the following entity, which is in 3NF, but still has some problems:

     

    Note that the buildings determine campus because no two buildings on any of the Teradata campuses have the same abbreviation. As a result, Building/Room    Campus. The relation is not in Boyce-Codd normal form.

    Normalize the relation by decomposing it into the two following BCNF relations: