PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 209

Index Salesperson File

Salesperson Record Record Salesperson Salesperson

Name Address Number Number Name City

Adams 3 1 119 Taylor New York

Baker 2 2 137 Baker Detroit

Carlyle 6 3 186 Adams Dallas

Dickens 4 4 204 Dickens Dallas

F I G U R E 8.10
Salesperson file on the right with index
built over the Salesperson Name field, on
the left

Green 7 5 255 Lincoln Atlanta

Lincoln 5 6 361 Carlyle Detroit

Taylor 1 7 420 Green Tucson

considered another way of ''physically'' locating a record in a file, just as a cylinder
and track address is a physical address.
On the left-hand side of Figure 8.10 is an index built over the Salesperson
Name field of the Salesperson file. Notice that the three rules for building an index
in a book were observed here, too. The indexed items were copied over from the
file to the index and the file was not disturbed in any way. The items in the index
were sorted. Finally, each indexed item was associated with a physical address, in
this case the relative record number (the equivalent of a page number in a book)
of the record of the Salesperson file from which it came. The first ''index record''
shows Adams 3 because the record of the Salesperson file with salesperson name
Adams is at relative record location 3 in the Salesperson file. Notice the similarity
between this index and the index in the back of a book. Just as you can quickly find
an item you are looking for in a book's index because the items are in alphabetic
order, a programmed procedure could quickly find one of the salespersons' names
in the index because they are in sorted order. Then, just as the item that you found
in the topic's index has a page number next to it telling you where to look for the
detailed information you seek, the index record in the index of Figure 8.10 has the
relative record number of the record of the Salesperson file that has the information,
i.e. the record, that you are looking for.
Figure 8.11, with an index built over the City field, demonstrates another point
about indexes. An index can be built over a field with non-unique values.

Index Salesperson File

Record Record Salesperson Salesperson

City Address Number Number Name City

Atlanta 5 1 119 Taylor New York

Dallas 3 2 137 Baker Detroit

Dallas 4 3 186 Adams Dallas

Detroit 2 4 204 Dickens Dallas

Detroit 6 5 255 Lincoln Atlanta

F I G U R E 8.11
Salesperson file on the right with index
built over the City field, on the left

New York 1 6 361 Carlyle Detroit

Tucson 7 7 420 Green Tucson