THE PHYSICAL DESIGN PROCESS - Database Design and Development: An Essential Guide for IT Professionals - page 365

DENSE INDEX

INDEX FILE DATA FILE

Page/Block

EA10 1 Employee record EA10

EH06 1 Employee record EH06

1

EH15 1 Employee record EH15

EH38 2 Employee record EH38

EM22 2 Employee record EM22

2

EM42 2 Employee record EM42

EQ33 3 Employee record EQ33

ER45 3 Employee record EQ45 3

ES33 3 Employee record ES33

SPARSE INDEX

DATA FILE

INDEX FILE

Page/Block

Employee record EA10
Employee record EH06

1

EH15 1 Employee record EH15

Employee record EH38

EM42 2

Employee record EM22

ES33 3 2

Employee record EM42
Employee record EQ33
Employee record EQ45

3

Employee record ES33

Figure 12-11 Data and index files.

Primary Index
One Index Value ==> One Target Record Address
Secondary Index
One Index Value ==> Addresses of Multiple Target Records

Employee Data

EmployeeNo

PRIMARY INDEX

DATA
FILE

SkillType

DeptNo

JobCode

Many SECONDARY INDEXES

Figure 12-12 Primary and secondary indexes for employee file.

Primary Indexes

For primary indexes, one index record points to one data record. That means there
will be as many index records as there are data records. How can you best arrange
the index records in the index file to optimize data access? First, consider search-
ing for a specific data record under three different options—directly from the data