Mapping Relational Data to Files

1. • Many systems store each relation in a separate file.
   • This allows the database to take full advantage of the file system provided by the operating system.
   • Usually tuples can be represented as fixed-length records (we assume 1NF here).
   • This simple implementation is well suited to databases for personal computers.
   • Simple file structure reduces the amount of code needed to implement the system.
   • This approach becomes less satisfactory as database size increases.
   • A more complicated file structure can provide benefits, even while still storing each relation in its own file.
   • Large databases may not rely on the operating system file system, but use one large operating system file for the database system.
   • All relations are then stored in the one file.
   • Management of this file is left to the database system.
2. How is this advantageous? Consider the following SQL query:

   

3. This query computes a join of the deposit and customer relations.
   • Thus, for each tuple of deposit, the system must locate the customer tuples with the same value for cname.
   • In the worst case, each record will reside on a different block, forcing one block read for each record required.
   • In a concrete example, for the relations of figures 7.17 and 7.18, a file structure for efficient execution of the join is shown in figure 7.19.
   • When queries require the calculation of deposit customer, this structure is useful as it stores tuples of both relations with same cname together.
   • This structure mixes tuples of two relations together, but allows for efficient processing of the join.
   • This file structure is called clustering, and allows us to read many of the required records using one block read.
   • Our use of clustering has improved the efficiency of one particular join, but results in slowing other queries.
4. For example, the query

   

   now requires more block accesses as our customer relation is now interspersed with the deposit relation.

   • In fact, we now need to chain all tuples of a relation together with pointers (see figure 7.20).
   • The determination of when clustering is useful depends on the kinds of queries which the database designer believes will be most frequent.
   • Careful use of clustering can produce significant performance gains in query processing.