Database Reference
In-Depth Information
It might seem inefficient to use two rows to store information that can be represented in one row. There is
a problem, however, with the arrangement shown in Figure 1-6
8
the table is more complicated. In Figure 1-5,
there is a single entry at each position in the table. In Figure 1-6, some of the individual positions within the
table contain multiple entries, thus making it difficult to track the information between columns. In the row for
order number 21617, for example, it is crucial to know that BV06 corresponds to the 2 in the NumOrdered
column (not to the 4) and that it corresponds to $794.95 in the QuotedPrice column (not to $150.00). In addi-
tion, having a more complex table means that there are practical issues to worry about, such as:
￿
How much room do you allow for these multiple entries?
￿
What if an order has more order lines than you have allowed room for?
￿
Given a part, how do you determine which orders contain order lines for that part?
Certainly, none of these problems is unsolvable. These problems do add a level of complexity, however,
that is not present in the arrangement shown in Figure 1-5. In Figure 1-5, there are no multiple entries to
worry about, it doesn
t matter how many order lines exist for any order, and it is easy to find every order
that contains an order line for a given part (just look for all order lines with the given part number in the
PartNum column). In general, this simpler structure is preferable, which is why order lines appear in a
separate table.
To test your understanding of the Premiere Products data, use the data shown in Figure 1-5 to answer
the following questions.
'
Q&A
Question: What are the numbers of the customers represented by Valerie Kaiser?
Answer: 148, 524, and 842. (Look up the RepNum value for Valerie Kaiser in the Rep table and obtain the
number 20. Then find all customers in the Customer table that have the number 20 in the RepNum column.)
Q&A
Question: What is the name of the customer that placed order 21610, and what is the name of the rep who
represents this customer?
Answer: Ferguson's is the customer, and Juan Perez is the rep. (Look up the CustomerNum value in the
Orders table for order number 21610 and obtain the number 356. Then, find the customer in the Customer
table with a CustomerNum value of 356. Using this customer's RepNum value, which is 65, find the name of
the rep in the Rep table.)
Q&A
Question: List all the parts that appear in order 21610. For each part, give the description, number ordered,
and quoted price.
Answer: Part number: DR93, part description: Gas Range, number ordered: 1, and quoted price: $495.00.
Also, part number: DW11, part description: Washer, number ordered: 1, and quoted price: $399.99. (Look up
each OrderLine table row in which the order number is 21610. Each row contains a part number, the num-
ber ordered, and the quoted price. Use the part number to look up the corresponding description in the Part
table.)
Search WWH ::




Custom Search