Understanding Record Modifications: Insertion, Deletion, and Update in Databases
This document details the methods of performing record modifications in databases, focusing on three key operations: insertion, deletion, and update. Insertion can be performed with or without ordering, utilizing overflow blocks when necessary. Deletion involves recovering space and using tombstones to manage deleted records, ensuring pointers do not lead to invalid entries. Updates are categorized by fixed and variable lengths, detailing their impacts on storage and necessary adjustments in space management.
Understanding Record Modifications: Insertion, Deletion, and Update in Databases
E N D
Presentation Transcript
Record Modifications Chapter 13 Section 13.8 Neha Samant CS 257 (Section II) Id 222
Modification type:Insertion 2 • Insertion of records without order Records can be placed in a block with empty space or in a new block. Insertion of records in fixed order • Space available in the block • No space available in the block (outside the block) Structured address Pointer to a record from outside the block.
Insertion in fixed order Offset table header unused Record 4 Record 3 Record 2 Record 1 3 Space available within the block • Use of an offset table in the header of each block with pointers to the location of each record in the block. • The records are slid within the block and the pointers in the offset table are adjusted.
Insertion in fixed order Block B Overflow block for B No space available within the block (outside the block) • Find space on a “nearby” block. • In case of no space available on a block, look at the following block in sorted order of blocks. • If space is available in that block ,move the highest records of first block 1 to block 2 and slide the records around on both blocks. • Create an overflow block • Records can be stored in overflow block. • Each block has place for a pointer to an overflow block in its header. • The overflow block can point to a second overflow block as shown below. 4
Modification type: Deletion 5 • Recover space after deletion • When using an offset table, the records can be slid around the block so there will be an unused region in the center that can be recovered. • In case we cannot slide records, an available space list can be maintained in the block header. • The list head goes in the block header and available regions hold the links in the list.
Deletion • Use of tombstone • The tombstone is placed in a record in order to avoid pointers to the deleted record to point to new records. • The tombstone is permanent until the entire database is reconstructed. • If pointers go to fixed locations from which the location of the record is found then we put the tombstone in that fixed location. (See examples) • Where a tombstone is placed depends on the nature of the record pointers. • Map table is used to translate logical record address to physical address. 6
Record 1 Record 2 Deletion • Use of tombstone • If we need to replace records by tombstones, place the bit that serves as the tombstone at the beginning of the record. • This bit remains the record location and subsequent bytes can be reused for another record Record 1 can be replaced, but the tombstone remains, record 2 has no tombstone and can be seen when we follow a pointer to it. 7
Modification type: Update 8 • Fixed Length update No effect on storage system as it occupies same space as before update. • Variable length update • Longer length • Short length
Update 9 Variable length update (longer length) • Stored on the same block: • Sliding records • Creation of overflow block. • Stored on another block • Move records around that block • Create a new block for storing variable length fields.
Update 10 Variable length update (Shorter length) • Same as deletion • Recover space • Consolidate space.
