File Organization Record Organization and Storage Access Database

File Organization, Record Organization and Storage Access Database System Concepts - 6 th Edition 10. 1 ©Silberschatz, Korth and Sudarshan

File Organization n The database is stored as a collection of files. Each file is a sequence of records. A record is a sequence of fields. n One approach: lassume leach record size is fixed file has records of one particular type only ldifferent files are used for different relations This case is easiest to implement; will consider variable length records later. Database System Concepts - 6 th Edition 10. 2 ©Silberschatz, Korth and Sudarshan

Fixed-Length Records n Simple approach: l Store record i starting from byte n (i – 1), where n is the size of each record. l Record access is simple but records may cross blocks 4 Modification: do not allow records to cross block boundaries n Deletion of record i: alternatives: l move records i + 1, . . . , n to i, . . . , n – 1 l move record n to i l do not move records, but link all free records on a free list Database System Concepts - 6 th Edition 10. 3 ©Silberschatz, Korth and Sudarshan

Deleting record 3 and compacting Database System Concepts - 6 th Edition 10. 4 ©Silberschatz, Korth and Sudarshan

Deleting record 3 and moving last record Database System Concepts - 6 th Edition 10. 5 ©Silberschatz, Korth and Sudarshan

Free Lists n Store the address of the first deleted record in the file header. n Use this first record to store the address of the second deleted record, and so on n Can think of these stored addresses as pointers since they “point” to the location of a record. n More space efficient representation: reuse space for normal attributes of free records to store pointers. (No pointers stored in in-use records. ) Database System Concepts - 6 th Edition 10. 6 ©Silberschatz, Korth and Sudarshan

Variable-Length Records n Variable-length records arise in database systems in several ways: l Storage of multiple record types in a file. l Record types that allow variable lengths for one or more fields such as strings (varchar) l Record types that allow repeating fields (used in some older data models). n Attributes are stored in order n Variable length attributes represented by fixed size (offset, length), with actual data stored after all fixed length attributes n Null values represented by null-value bitmap Database System Concepts - 6 th Edition 10. 7 ©Silberschatz, Korth and Sudarshan

Variable-Length Records: Slotted Page Structure n Slotted page header contains: l number of record entries l end of free space in the block l location and size of each record n Records can be moved around within a page to keep them contiguous with no empty space between them; entry in the header must be updated. n Pointers should not point directly to record — instead they should point to the entry for the record in header. Database System Concepts - 6 th Edition 10. 8 ©Silberschatz, Korth and Sudarshan

Organization of Records in Files n Heap – a record can be placed anywhere in the file where there is space n Sequential – store records in sequential order, based on the value of the search key of each record n Hashing – a hash function computed on some attribute of each record; the result specifies in which block of the file the record should be placed n Records of each relation may be stored in a separate file. In a multitable clustering file organization records of several different relations can be stored in the same file l Motivation: store related records on the same block to minimize I/O Database System Concepts - 6 th Edition 10. 9 ©Silberschatz, Korth and Sudarshan

Sequential File Organization n Suitable for applications that require sequential processing of the entire file n The records in the file are ordered by a search-key Database System Concepts - 6 th Edition 10. 10 ©Silberschatz, Korth and Sudarshan

Sequential File Organization (Cont. ) n Deletion – use pointer chains n Insertion –locate the position where the record is to be inserted l if there is free space insert there l if no free space, insert the record in an overflow block l In either case, pointer chain must be updated n Need to reorganize the file from time to restore sequential order Database System Concepts - 6 th Edition 10. 11 ©Silberschatz, Korth and Sudarshan

Multitable Clustering File Organization Store several relations in one file using a multitable clustering file organization department instructor multitable clustering of department and instructor Database System Concepts - 6 th Edition 10. 12 ©Silberschatz, Korth and Sudarshan

Multitable Clustering File Organization (cont. ) n good for queries involving department instructor, and for queries involving one single department and its instructors n bad for queries involving only department n results in variable size records n Can add pointer chains to link records of a particular relation Database System Concepts - 6 th Edition 10. 13 ©Silberschatz, Korth and Sudarshan

Data Dictionary Storage The Data dictionary (also called system catalog) stores metadata; that is, data about data, such as n Information about relations names of relations l names, types and lengths of attributes of each relation l names and definitions of views l integrity constraints n User and accounting information, including passwords n Statistical and descriptive data l number of tuples in each relation n Physical file organization information l How relation is stored (sequential/hash/…) l Physical location of relation n Information about indices (Chapter 11) l Database System Concepts - 6 th Edition 10. 14 ©Silberschatz, Korth and Sudarshan