Download
1 / 16
160 likes | 338 Views
AKSHAY SHENOY CLASS ID :108 Topic 13.8 Proffesor : T.Y Lin. RECORD MODIFICATION. INTRODUCTION. What is Record ? Record is a single, implicitly structured data item in the database table. Record is also called as Tuple. What is definition of Record Modification ?
E N D
AKSHAY SHENOY CLASS ID :108 Topic 13.8 Proffesor : T.Y Lin RECORD MODIFICATION
INTRODUCTION • What is Record ? • Record is a single, implicitly structured data item in the database table. Record is also called as Tuple. • What is definition of Record Modification ? • We say Records Modified when a data manipulation operation is performed.
STRUCTURE OF A RECORD • RECORD STRUCTURE FOR A PERSON TABLE • CREATE TABLE PERSON ( NAME CHAR(30), ADDRESS CHAR(256) , GENDER CHAR(1), BIRTHDATE CHAR(10));
TYPES OF RECORDS • FIXED LENGTH RECORDS • CREATE TABLE SJSUSTUDENT(STUDENT_ID • INT(9) NOT NULL , PHONE_NO INT(10) NOT NULL); • VARIABLE LENGTH RECORDS • CREATE TABLE SJSUSTUDENT(STUDENT_ID INT(9) NOT NULL, • NAME CHAR(100) ,ADDRESS CHAR(100) ,PHONE_NO INT(10) NOT NULL);
RECORD MODIFICATION • Modification of Record • Insert • Update • Delete • Issues even with Fixed Length Records • More Issues with Variable Length Records
STRUCTURE OF A BLOCK & RECORDS • Various Records are clubbed together and stored together in memory in blocks • STRUCTURE OF BLOCK
BLOCKS & RECORDS • If records need not be any particular order, then just find a block with enough empty space • We keep track of all records/tuples in a relation/tables using Index structures, File organization concepts
Inserting New Records • If Records are not required to be a particular order, just find an empty block and place the record in the block. • eg: Heap Files • What if the Records are to be Kept in a particular Order(eg: sorted by primary key) ? • Locate appropriate block,check if space is available in the block if yes place the record in the block.
INSERTING NEW RECORDS • We may have to slide the Records in the Block to place the Record at an appropriate place in the Block and suitably edit the block header.
What If The Block Is Full ? • We need to Keep the record in a particular block but the block is full. How do we deal with it ? • We find room outside the Block • There are 2 approaches to finding the room for the record. • Find Space on Nearby Block • Create an Overflow Block
APPROACHES TO FINDING ROOM FOR RECORD • FIND SPACE ON NEARBY BLOCK • BLOCK B1 HAS NO SPACE • IF SPACE AVAILABLE ON BLOCK B2 MOVE RECORDS OF B1 TO B2. • IF THERE ARE EXTERNAL POINTERS TO RECORDS OF B1 MOVED TO B2 LEAVE FORWARDING ADDRESS IN OFFSET TABLE OF B1
APPROACHES TO FINDING ROOM FOR RECORD • CREATE OVERFLOW BLOCK • EACH BLOCK B HAS IN ITS HEADER POINTER TO AN OVERFLOW BLOCK WHERE ADDITIONAL BLOCKS OF B CAN BE PLACED.
DELETION • Try to reclaim the space available on a record after deletion of a particular record • If an offset table is used for storing information about records for the block then rearrange/slide the remaining records. • If Sliding of records is not possible then maintain a SPACE-AVAILABLE LIST to keep track of space available on the Record.
TOMBSTONE • What about pointer to deleted records ? • A tombstone is placed in place of each deleted record • A tombstone is a bit placed at first byte of deleted record to indicate the record was deleted ( 0 – Not Deleted 1 – Deleted) • A tombstone is permanent
UPDATING RECORDS • For Fixed-Length Records, there is no effect on the storage system • For variable length records : • If length increases, like insertion “slide the records” • If length decreases, like deletion we update the space-available list, recover the space/eliminate the overflow blocks.
