Fall Semester 2003

• briefly summarize Chapter Six and then
• discuss the first part of Homework Five

The Lab Notes for tomorrow will look at the rest of the assignment.

Premiere Products Exercises

1. There a number of things we need to take into account:

1. We need to start from the existing design of Premiere Products.
2. If we don't know the existing design it's going to be extremely difficult.
3. So, where's the design, what page in the book.
4. The answer my friends is in the blowing of the wind: it starts on page 178.
5. So what does this problem want?
6. It asks that reps no longer be at most one per customer.
7. That is, a customer could be represented by more than one rep.
8. But that list is known.
9. So we need to take a column out of the Customer table.
10. But we also need a table for a many-to-many relationship between customers and reps.
11. Besides these two changes we need to keep another thing in mind.
12. When a customer places an order a certain sales rep will get the commission.
13. This sales rep must be from the known list of reps associated with the customer.
14. So we must add a column to the Orders table.
15. Moreover we have a composite foreign key in that table to enforce referential integrity.

1. We start from the original design of Premiere Products, described on pages 178-185.
2. Don't think that you should build on the changes from the previous exercises.
3. The exercises are independent, so we start from the Original Premiere Products design, again.
4. And the situation we need to accomodate this time around is the total lack of relationship between reps and customers.
5. Then, we start in the same way: take the RepNum out of Customer.
6. You see, you might have to record more than one.
7. And with the current layout there's no room for more than one.
8. But since there is no relationship, we don't need to build another table.
9. And the only one other thing we need to do is to extend the Orders table.
10. With what? (I am sure you know it.) (Select to highlight).

1. If we don't know which User View 3 they speak of etc. Page is 182.
2. Is this right or wrong? Or is it 187. Think about it.
3. The new thing here is that a Part could be stored in more than one Warehouse.
4. So there are two ways of approaching it and they both yield the same result.
5. Method one says: there's a repeating group in the Part table.
6. That repeating group is identifying a Warehouse and a number OnHand.
7. That's for each PartNum.
8. So turn it in 1NF, etc.
9. You end up with a many-to-many relationship between Parts and Warehouses.
10. The other method says: there should be a repeating group in the Warehouse table.
11. That repeating groups is identifying a PartNum and a quantity OnHand repeatedly.
12. So we normalize, etc.
13. The result is the same, only the order of the columns is a bit changed.
14. As discussed in class Description stays in Warehouse.
15. Moving it in the relationship would break 2NF.
16. But I agree that the text might be a bit misleading at first.

In class we will discuss these at length.

Please use DBDL (and E-R for more points) to communicate your designs.

The Lab Notes of tomorrow will focus on the remaining two problems.

(That's Henry Books Case on pages 218-219).

See you tomorrow in lab!