The Second Normal Form requires that there is no partial dependency on a compound key.

This means that for a table that has a compound primary key, each column in the table that is not part of the primary key must depend upon the entire compound key for its existence. If any column only depends upon one part of the compound key, then we say that the entire table has failed Second Normal Form and we must create another table to rectify the failure.

Still not clear? To try and understand this, let's take apart the Order Items table column by column. Also, look at the 2NF tabs of your example spreadsheet. For each column we will ask the question: Can this column exist without one or the other part of the compound primary key? If the answer is "yes" — even once — then the table fails Second Normal Form. Don’t worry about the columns that make up the primary key but do consider the rest...

Product_Description is textual information about what the product is. Obviously, it relies on Product_ID; a product has to have a description, because an ID is usually just a number (that tells you nothing about the product). But can a product description exist without an Order_Number?

The short answer is yes: Product_Description relies on Product_ID, not Order_Number. A product (together with its "description") could sit on a warehouse shelf forever, and never be purchased... It can exist independent of an order. Therefore: Product_Description fails Second Normal Form.

Image showing the Orders and Order Item tables, how the Product_Description column depends only on Product_ID and not on Order_Number

Image: Product_Description column’s dependency

Continuing the hunt

Even though the table has already failed Second Normal Form, we’ll continue to test the other columns. We have to find all the columns that fail the test, and then we will do something special with them.

Product_Quantity refers to the number of items purchased on a particular order. Can this quantity exist without an Product_ID? Impossible: we cannot talk about the "amount of nothing" (at least not in database design). Can the quantity exist without an Order_Number? No: a quantity that is purchased with an order is meaningless without an order. So, this column does not violate Second Normal Form because it depends on both parts of the primary key.

Product_Price is similar to Product_Description. It depends on the Product_ID but not on the Order_Number, so it does violate Second Normal Form.

Product_Total_Amount is tricky. On the one hand, it seems to depend on both Order_Number and Product_ID, in which case it passes Second Normal Form. On the other hand, it is a calculated value: (Product_Quantity * Product_Price). The simple answer is calculated fields do not belong in a database so we will speak of it no more.

Likewise, Order_Total_Price, is another calculated value so we will discard it as well.

Image showing the columns of the order items table: Product_Description Depends on only the Product_ID and not the Order_Number Product_Quantity: Depends on both the Order_Number and the Product_ID Product_Price: Depends on only the Product_ID and not the Order_Number Product_Total_Amount: Calculated fields do not belong in a database

Image: Order Items table testing of columns

Here is the markup from our 2NF analysis of the Orders table:

Image showing the 2NF analysis of the Orders table

Image: 2NF analysis of the Orders table

Now, we have to consider what we do with a table that fails Second Normal Form (given that this one has)? First, we take out the second half of the concatenated primary key (Product_ID) and put it in its own table.

All the columns that depend on Product_ID - whether in whole or in part - follow it into the new table. We call this new table Products.

Image showing the three tables, Orders, Order Items and Products tables

Image: Orders, Order_Items and Products tables

The other fields - those that rely on the full compound key (Order_Number & Product_ID) and those we aren't sure about - stay where they are.

Lets’ see now how the relationship between the three tables is formed:

Image showing the relationship between the three tables, Orders, Order Items and Products

Image: relationship between the three tables

The line that connects the Products and Order_Items tables means the following:

Each Product can be associated with any number of lines on any number of Orders, including zero.
Each Order-Detail is associated with one product, and only one.
The line is an example of a one-to-many relationship. The three-table structure, considered in its entirety, is how we can express a many-to-many relationship.
Each Order can have many Products; each Product can belong to many Orders.

Notice that this time, we did not bring a copy of the Order_Number column into the new table. This is because individual Products do not need to have knowledge of the orders they are part of. The Order_Items table takes care of remembering this relationship via the Order_Number and Product_ID columns. Taken together these columns comprise the primary key of Order_Items, but taken separately they are foreign keys or pointers to rows in other tables.

Notice, too, that our new table does not have a concatenated primary key, so it passes 2NF. At this point, we have succeeded in attaining Second Normal Form!

But what happens if the price of a product changes after an order has been placed? Let’s move on to the next lecture to find out.

When you’re ready, select Next to continue.