Part I: Access Building Blocks
98
This rule means that a table should contain data that represents a single entity. tblBookOrders3
violates this rule of normalization because the table contains information about two different enti-
ties — books and orders. The individual book titles do not depend on the table’s key field,
OrderID. Only the order date, customer, and quantity depend on the OrderID. (For the mean-
time, I’m ignoring the fact that this table does not contain a primary key. I’ll be adding primary
keys in the “Keys” section, later in this chapter.)
At first glance, it might appear as though the book titles are indeed dependent on the Order ID.
After all, the reason the book titles are in the table is because they’re part of the order. However, a
moment’s thought will clarify the violation of second normal form. The title of a book is com-
pletely independent of the book order in which it is included. The same book title appears in mul-
tiple book orders; therefore, the OrderID has nothing to do with how a book is named. Given an
arbitrary OrderID, you can’t tell anything about the books contained in the order other than
looking at the Orders table. Similarly, given a book title, there is nothing that binds the title to a
specific order. (This is what is meant by dependency — a book title is not dependent on an
OrderID.)
The OrderDate, however, is completely dependent on the OrderID. For each OrderID there is
one and only one OrderDate. Therefore, any OrderDate is dependent on its associated
OrderID. An OrderDate may be duplicated in the table, of course, because multiple orders may
be received on the same day. For each OrderID, however, there is one and only one valid
OrderDate value.
Second normal form often means breaking up a monolithic table into constituent tables, each of
which contains fewer fields than the original table. In this example, second normal form is
achieved by breaking the table containing books and orders data into separate Orders and Order
Details tables.
The order-specific information (such as the order date, customer, payment, and shipping informa-
tion) goes into the Orders table, while the details of each order item (book, quantity, selling price,
and so on) is contained by the Order Details table. (Not all this data is shown in the example
tables.)
The new tables are shown in Figure 3.5. The OrderID is the primary key for the tblBoo-
kOrders4 table. The OrderID field in the tblBookOrderDetails is a foreign key (see
Chapter 1) that references the OrderID primary key field in tblBookOrders4. Each field in
tblBookOrders4 — OrderDate and Customer — is said to be dependent on the table’s pri-
mary key.
tblBookOrders4 and tblBookOrderDetails are joined in a one-to-many relationship.
tblBookOrderDetails contains as many records for each order as are necessary to fulfill the
requirements of the order. The OrderID field in tblBookOrders4 is now a true primary key.
Each row in tblBookOrders4 contains a unique OrderID, and none of the rows has a blank
OrderID.
