Database Design Example

Premiere Products

Consider the Premiere Products Database from earlier in the chapter: Chart

PremiereProductsOriginalDesign.png

For each of the problems below, consider the following modifications that need to be made to the oiriginal database:

1. A customer is not necessarily represented by a single sales rep but can be represented by several sales reps. When a customer places an order, the sales rep who gets the commission on the order must be one of the collection of sales reps who represents the customer.

  • What do we need to change in the existing tables?
  • Do we need to add any tables? What tables?
  • Create an ER for the modified database
2. Consider the case where there is no relationship between customers and sales reps. When a customer places an order, it may be through any sales rep. On the order, identify both the customer placing the order and the sales rep responsible for the order.
  • What do we need to change in the existing tables?
  • Do we need to add any tables? What tables?
  • Create an ER for the modified database
3. We want to update the database to store information about warehouses, and which warehouse a part is kept at. A given part can be located t more than one warehouse. For a part, store the part’s number, description, item class, and price. In addition, for each warehouse in which the part is located, store the number of the warehouse, description of the warehouse, and number of units of the part stored in that warehouse.
  • What do we need to change in the existing tables?
  • Do we need to add any tables? What tables?
  • Create an ER for the modified database
4. We want to modify the design to divide the US into territories. Each territory has one or more sales reps, and each rep continues to have one or more customers. For each territory, store the territory number (a unique identifier) and territory name. Each sales rep is assigned to a single territory. Each customer is also assigned to a single territory, but the territory must be the same as the territory to which the customer’s sales rep is assigned.
  • What do we need to change in the existing tables?
  • Do we need to add any tables? What tables?
  • Create an ER for the modified database
5. We want to modify the design to divide the US into territories. Each territory has one or more sales reps, and each rep continues to have one or more customers. For each territory, store the territory number (a unique identifier) and territory name. Each sales rep is assigned to a single territory. Each customer is also assigned to a single territory, which may not be the same as the territory to which the customer’s sales rep is assigned.
  • What do we need to change in the existing tables?
  • Do we need to add any tables? What tables?
  • Create an ER for the modified database
Teams

Team 1 Team 2 Team 3 Team 4 Team 5
Joshua A Kaleb Brandon Trenton Joseph
David Aaron Logan Nicholas Trent
Jason Joshua S   Luke  
Topic revision: r1 - 2013-12-02 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback