Indexes can also be used to represent nonunique secondary keys, but because each set of related records
can contain an unknown number of members the entries in the index are of variable length. For
example, Figure H-36 shows the index for the CreditLimit sets for the CUSTOMER data. The $500 set and
the $700 set both have three members, so there are three account numbers in each entry. The $1000
set has five members, so there are five account numbers in that entry.
In reality, representing and processing nonunique secondary keys are complex tasks. Several different
schemes are used by commercial DBMS products. One common method uses a values table and an occurrence
table. Each values table entry consists of two fields, the first of which has a key value. For the
CUSTOMER CreditLimit key, the values are 500, 700, and 1000.
The second field of the values table entry is a pointer into the occurrence table. The occurrence table
contains record addresses, and those having a common value in the secondary-key field appear together
in the table. Figure H-37 shows the values and occurrence tables for the CreditLimit key.
To locate records having a given value of the secondary key, the values table is searched for the desired
value. After the given key value is located in the values table, the pointer is followed to the occurrence
table to obtain the addresses of those records having that key value. These addresses are then used to
obtain the desired records.