Cape Codd Sports

• We will use the DB from the previous exercise to learn how to query a database.
• In this case, the story is,
  • This is a read only data base which has been extracted from the companies main db.
  • Many fields have been dropped.
  • As have many tables.
  • This db has been created for Marketing to study sales.
  • See Figure 2-2 page 33.
• This is apparently a normal thing.
  • Sub-groups require access to the data.
  • But you don't want them messing with your production DB.
• The tables are as follows:
  • RETAIL_ORDER(OrderNumber,StoreNumber, StoreZip, OrderMonth, OrderYear, OrderTotal)
  • This has a primary key OrderNumber
  • There is one row per order placed.
  • ORDER_ITEM(OrderNumber, SKU,Quantity, Price, ExtendedPrice)
  • This has a composite foreign primary key, OrderNumber and SKU
  • Each row in this table represents an item in an order.
  • This is a many to one relationship.
  • Many rows in ORDER_ITEM correspond to one row in RETAIL_ORDER
  • Or, a RETAIL_ORDER can consist of meny ORDER_ITEMS.
  • SKU_DATA(SKU,SKUDescription, Department, Buyer)
  • This has a primary key SKU
  • Agan, may items in the ORDER_ITEM can correspond to a single row in the SKU_DATA table.
• The following constitue a schema or a complete logical view of the database.
  • RETAIL_ORDER(OrderNumber,StoreNumber, StoreZip, OrderMonth, OrderYear, OrderTotal)
  • SKU_DATA(SKU,SKUDescription, Department, Buyer)
  • ORDER_ITEM(OrderNumber, SKU,Quantity, Price, ExtendedPrice)
• It describes
  • All columns in all of the tables.
  • All of the primary keys
  • All of the foreign keys