Table 4–1: Reasons for Defining Some Sports Database Indexes
Table
|
Index Name
|
Index Column(s)
|
Primary
|
Unique
|
Customer
|
cust-num
|
cust-num
|
YES
|
YES
|
–
|
Why the Index Was 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 Was Defined:
- Rapid access to a customer given a customer’s name.
- Reporting customers in order by name.
|
zip
|
zip
|
NO
|
NO
|
Why the Index Was Defined:
- Rapid access to all customers with a given zip code or in a zip code range.
- Reporting customers in order by zip code.
|
Item
|
item-num
|
item-num
|
YES
|
YES
|
–
|
Why the Index Was Defined:
- Rapid access to a 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 Was 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 alone need 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 Was Defined:
- Rapid access to all the order-lines for a given item.
|
Order
|
order-num
|
order-num
|
YES
|
YES
|
–
|
Why the Index Was Defined:
- Rapid access to a 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 Was 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 suterom/order combination (uniqueness).
- Rapid access ot the order numbers of a customer’s orders.
|
order-date
|
order-date
|
NO
|
NO
|
Why the Index Was Defined:
- Rapid access to all the orders placed on a given date or in a range of dates.
|