The following figure lists some indexes defined in the Sports 2020 database, showing why the index is defined.

Table 1. Reasons for defining some Sports 2020 database indexes
Table Index name Index column(s) Primary Unique
Customer cust-num cust-num YES YES
– Why the index is defined:
  • Rapid access to a customer given a customer's number.
  • Reporting customers in order by number.
  • Ensuring that there is only one customer row for each customer number (uniqueness).
  • Rapid access to a customer from an order, using the customer number in the order row.
name name NO NO
Why the index is defined:
  • Rapid access to a customer given a customer's name.
  • Reporting customers in order by name.
zip zip NO NO
Why the index is defined:
  • Rapid access to all customers with a given zip code or in a zip code range.
  • Reporting customers in order by zip code, perhaps for generating mailing lists.
Item item-num item-num YES YES
– Why the index is defined:
  • Rapid access to an item given an item number.
  • Reporting items in order by number.
  • Ensuring that there is only one item row for each item number (uniqueness).
  • Rapid access to an item from an order-line, using the item-num column in the order-line row.
Order-line order-line order-num

line-num

 YES YES
– Why the index is defined:
  • Ensuring that there is only one order-line row with a given order number and line number. The index is based on both columns together since neither column alone needs to be unique.
  • Rapid access to all of the order-lines for an order, ordered by line number.
item-num item-num NO NO
Why the index is defined:
  • Rapid access to all the order-lines for a given item.
Order order-num order-num YES YES
– Why the index is defined:
  • Rapid access to an order given an order number.
  • Reporting orders in order by number.
  • Ensuring that there is only one order row for each order number (uniqueness).
  • Rapid access to an order from an order-line, using the order-num column in the order-line row.
cust-order cust-num

order-num

 NO YES
Why the index is defined:
  • Rapid access to all the orders placed by a customer. Without this index, all of the records in the order file would be examined to find those having a particular value in the cust-num column.
  • Ensuring that there is only one row for each customer/order combination (uniqueness).
  • Rapid access to the order numbers of a customer's orders.
order-date order-date NO NO
Why the index is defined:
  • Rapid access to all the orders placed on a given date or in a range of dates.