Java Reference
In-Depth Information
In addition to its primary key, the Ordered Items Table contains two foreign keys. In
this case, they are the Item_Number, from the Inventory Table, and the
Order_Number, from the Orders Table. The Orders Table is shown in
Table 1-6
.
Table 1-6: Orders Table
Order_Number
Customer_ID
Order_Date
Ship_Date
2
101
12/8/01
12/10/01
3
103
12/9/01
12/11/01
4
104
12/9/01
12/11/01
6
120
12/12/01
12/14/10
5
106
12/10/01
12/12/01
7
121
12/14/01
12/16/01
The Orders Table contains all the information defining the customer's order. Its
primary key is the Order_Number column, and it contains the foreign key column
Customer_ID, pointing to the Customers table, to identify the customer placing the
order.
Notice that the way these tables have been designed eliminates redundancy. No item
of information is saved in more than one place, and each piece of information is
saved as a single row in the appropriate table.
Eliminating redundancy is an important aspect of database
design. By ensuring that information is stored in only one
place, the problems resulting from discrepancies between
different copies of the same data item are eliminated.
Cross-Reference
It is easy to understand how the keys are used if you analyze one of the orders. For
example, you can find out all about the customer who placed order 4 by looking up
customer 104 in the Customers Table. Similarly, by referring to the Ordered_Items
Table, you can see that the items ordered on order 4 were 5 of inventory item 1002
and 2 of inventory item 1003. Looking these numbers up in the Inventory Table tells
you that inventory item number 1002 refers to Rice Krispies, while inventory item
number 1003 refers to Shredded Wheat.
By combining the information in these tables, you can see that order 4 was placed by
customer 104, Vito Corleone, on 12/9/01, and that he ordered 5 boxes of Rice
Krispies and 2 boxes of Shredded Wheat, inventory numbers 1002 and 1003,
respectively, for shipment on 12/11/01. This information is obtained by matching up
the various keys, using a SQL statement such as the following:
