Database Reference
In-Depth Information
￿ In addition, an index can be sparse or dense : in a dense index, there is
one entry in the index for every data record. This requires data files to
be ordered on the indexing key. Opposite to this, a sparse index contains
less index entries than data records. Thus, a nonclustered index is always
dense, since it is not ordered on the indexing key.
￿ Finally, indexes can be single-level or multilevel . When an index file
becomes large and extends over many blocks, the search time required for
the index increases. A multilevel index attempts to overcome this problem
by splitting the index into a number of smaller indexes and maintaining
an index to the indexes. Although a multilevel index reduces the number
of blocks accessed when one is searching for a record, it also has problems
in dealing with insertions and deletions in the index because all index
levels are physically ordered files. A dynamic multilevel index solves
this problem by leaving some space in each of its blocks for inserting new
entries. This type of index is often implemented by using data structures
called B-trees and B + -trees , which are supported by most DBMSs.
Most DBMSs give the designer the option to set up indexes on any fields,
thus achieving faster access at the expense of extra storage space for indexes,
and overheads when updating. Because the indexed values are held in a sorted
order, they can be eciently exploited to handle partial matching and range
searches, and in a relational system, they can speed up join operations on
indexed fields.
We will see in Chap. 7 that distinctive characteristics of data warehouses
require physical design solutions that are different from the ones required by
DBMSs in order to support heavy transaction loads.
2.6 Summary
This chapter introduced the background database concepts that will be used
throughout the topic. We started by describing database systems and the
usual steps followed for designing them, that is, requirements specification,
conceptual design, logical design, and physical design. Then, we presented
the Northwind case study, which was used to illustrate the different concepts
introduced throughout the chapter. We presented the entity-relationship
model, a well-known conceptual model. With respect to logical models,
we studied the relational model and also gave the mapping rules that are
used to translate an entity-relationship schema into a relational schema.
In addition, we briefly discussed normalization, which aims at preventing
redundancies and inconsistency in a relational database. Then, we presented
two different languages for manipulating relational databases, namely, the
relational algebra and SQL. We finished this introduction to database systems
by describing several issues related to physical database design.
Search WWH ::




Custom Search