There are two basic requirements for a database to be in Third Normal Form:

The requirements of both 1NF and 2NF have been met
Remove columns that are not fully dependent upon the primary key.

Let’s assume that we have a table of orders that contains the following attributes:

  • Order Number
  • Customer Number
  • Unit Price
  • Quantity
  • Total

Our first requirement is that the table must satisfy the requirements of 1NF and 2NF. Are there any duplicate columns? No. Is there a primary key? Yes, the order number. Therefore, the requirements of 1NF have been satisfied. Are there any subsets of data that apply to multiple rows? No, so the requirements of 2NF have also been satisfied.

Are all of the columns fully dependent upon the primary key? The customer number varies with the order number and it doesn’t appear to depend upon any of the other fields. What about the unit price? This field could be dependent upon the customer number in a situation where we charged each customer a set price. However, we sometimes charge the same customer different prices. Therefore, the unit price is fully dependent upon the order number. The quantity of items also varies from order to order, so we’re OK there.

What about the total? It looks like there might be some trouble here. The total can be derived by multiplying the unit price by the quantity; therefore it’s not fully dependent upon the primary key. It must be removed from the table to comply with the third normal form.

We will use the following attributes:

  • Order Number
  • Customer Number
  • Unit Price
  • Quantity

Now our table is in 3NF. But, you might ask, what about the total? This is a derived field and its best not to store it in the database at all. We can simply compute it “on the fly” when performing database queries. For example, we might have previously used this query to retrieve order numbers and totals:

SELECT OrderNumber, Total FROM Orders

We can now use the following query:

SELECT OrderNumber, UnitPrice * Quantity AS Total FROM Orders

to achieve the same results without violating normalization rules.

Unstructured data such as video