Java Reference
In-Depth Information
placing the order, together with information such as order date and ship date. The Orders Table, as
shown in
Table 9-3
, maps orders to customers.
Table 9-3: Orders Table
Order_Number
Customer_Number
Order_Date
Ship_Date
2
101
12/8/01
12/10/01
3
103
12/9/01
12/11/01
Finally, you need a table listing every item in each order. This table contains the order number, item
number, and quantity for each ordered item, as shown in
Table 9-4
.
Table 9-4: Ordered Items Table
ID
Order_Number
Item_Number
Qty
5000
2
1001
2
5001
2
1004
1
5002
2
1005
3
5003
2
1010
6
5004
3
1006
4
5005
3
1009
2
The structure of these tables follows the basic principle of keeping related data items together and
separated from unrelated items. There is never, for example, a direct relationship between inventory
items and customers. The customer interacts with inventory through the mechanism of placing an
order. The order links to the inventory through the Ordered_Items Table and to the customer via the
customer number. The Ordered_Items Table provides a link between the order number and the items
in the Inventory Table.
Once the data has been divided logically among these four tables, as illustrated in Tables 9-1 through
9-4, you can write the following SQL command to get a list of the products in order number 2:
SELECT Orders.Order_number, Ordered_Items.Item_number,
Ordered_Items.Qty, Inventory.Name,
Inventory.Description
FROM Orders, Ordered_Items, Inventory
WHERE Orders.order_number = Ordered_Items.order_number AND
Inventory.Item_Number = Ordered_Items.Item_Number AND
Orders.order_number = 2;
Notice how the columns used in the
WHERE
clause comparison are the key columns of the various
tables. The dotted notation allows you to tell the database management system which table to look in
