Physical File Organization and Indexing

Abstract

Chapter Objectives In this chapter, you will learn to: • grasp the basic principles of storage hardware and physical database design; • understand how data items can be organized into stored records; • identify various methods for primary and secondary file organization; Opening Scenario Now that Sober has its relational logical data model from Chapter 6 ready, it wants to understand how it can be physically implemented. The company is also wondering whether there exist any physical means to speed up the response times of frequently used queries. This chapter focuses on the most important principles pertaining to the physical organization of records and files. As such, it can be considered as the prerequisite to Chapter 13, which applies these principles in the context of physical database organization. In this way Chapters 12 and 13 are complementary in covering all facets of physical database design – the translation of a logical data model into an internal data model, including the design of indexes to speed up data access. First, we present some overall properties of storage devices and the impact of the mechanicals of hard disk drives on the performance aspects of data retrieval. After that, we overview the mapping of logical modeling constructs onto physical concepts, and thus of the logical data model onto the internal model. Then, we briefly discuss record organization , covering the different alternatives to organize physical data records, consisting of individual data fields. After that comes the main body of this chapter, focusing on file organization and covering methods to organize records into physical files, as well as techniques to efficiently search for records with certain characteristics. We discuss several approaches, such as sequential files, hashing and the use of different index types, including B+-trees. Chapter 13 follows on from this chapter, applying the insights gained from the file organization section to the particular context of physical database organization . Storage Hardware and Physical Database Design Physical database design pertains to the design of an internal data model and to the way in which the logical database concepts discussed in the previous chapters are realized physically as stored records, physical files, and, ultimately, a physical database.