Index Structure for Files Sahar Mosleh Palomar College

Index Allocation Map (IAM) for table 1 Start of table 1 Headers PFS Page

Data File – Dept. Id is the Clustering key Field Dept. Id Name Emp.

Example of a B-Tree of order 3 • We want to insert the following

• Insert index for record: 2 Before After Sahar Mosleh Jack 30, 000

• Insert index for record: Before After Sahar Mosleh 80 Steve 32, 000

• Insert index for record: Before 2 After John 50, 000 80 8

• Insert index for record: Before 71 55, 000 8 2 After 80

• Insert index for record: Before 15 Rose 8 2 After 71 8

• Insert index for record: Before 63 8 2 Abdul 35, 000 71

• Insert index for record: Before 2 After 2 Sahar Mosleh 90 Pat

• Insert index for record: Before 2 After 55 Kathy 8 71 15

• Insert index for record: 35 Melissa 38, 000 55 Before 8 2

• Insert index for record: 51 Joe 55 Before 8 71 2 15

90 80 63 15 51 71 8 35 55 Emp. Id Name Salary 2

Slides: 17

Download presentation

Index Structure for Files Sahar Mosleh Palomar College Page 1

Index Allocation Map (IAM) for table 1 Start of table 1 Headers PFS Page Free space GAM Global Allocation Map Index Allocation Map (IAM) for table 2 SGAM Start of table 2 Secondary Global Allocation Map Index Allocation Map (IAM) for index 90 Start of index 90 Sahar Mosleh Palomar College Page 2

T 1 x 5 1 y 15 2 T 1 w 75 3 T 3 4 ……………. . 5 ……………. . 6 ……………. . 89 9 Write-ahead Log Cash 500 ……………. . Clean Pages ……………. . Check Point Min. LSN Flushing Dirty pages Database Sahar Mosleh Palomar College Page 3

Data File – Dept. Id is the Clustering key Field Dept. Id Name Emp. Id 1 Jack 101 Index File 1 Steve 102 1 1 John 103 2 1 Nancy 104 2 Rose 105 2 Abdul 106 2 Pat 107 3 Melissa 108 3 Joe 109 3 Sahar Mosleh Palomar College This is the primary key Page 4

Data File – Dept. Id is the Clustering key Field Dept. Id Name Emp. Id 1 Jack 101 Index File 1 Steve 102 1 1 John 103 2 1 Nancy 104 3 2 Rose 105 2 Abdul 106 2 Pat 107 1 null pointer 3 Melissa 108 3 Joe 109 David 112 null pointer Sahar Mosleh Palomar College Page 5

Example of a B-Tree of order 3 • We want to insert the following records into a B-tree of order 3 Emp. Id Name Salary 2 Jack 30, 000 80 Steve 32, 000 8 John 50, 000 71 Nancy 55, 000 15 Rose 90, 000 63 Abdul 35, 000 90 Pat 42, 000 55 Kathy 45, 000 35 Melissa 38, 000 51 Sahar Mosleh Joe 39, 000 Palomar College Page 6

• Insert index for record: 2 Before After Sahar Mosleh Jack 30, 000 Null Pointer 2 Palomar College Page 7

• Insert index for record: Before After Sahar Mosleh 80 Steve 32, 000 2 2 80 Palomar College Page 8

• Insert index for record: Before 2 After John 50, 000 80 8 2 Sahar Mosleh 8 80 Palomar College Page 9

• Insert index for record: Before 71 55, 000 8 2 After 80 8 2 Sahar Mosleh Nancy 71 Palomar College 80 Page 10

• Insert index for record: Before 15 Rose 8 2 After 71 8 2 Sahar Mosleh 90, 000 80 71 15 Palomar College 80 Page 11

• Insert index for record: Before 63 8 2 Abdul 35, 000 71 15 80 After 2 Sahar Mosleh 8 71 15 63 Palomar College 80 Page 12

• Insert index for record: Before 2 After 2 Sahar Mosleh 90 Pat 8 71 15 63 Palomar College 42, 000 80 80 90 Page 13

• Insert index for record: Before 2 After 55 Kathy 8 71 15 63 Sahar Mosleh 80 90 55 8 2 45, 000 71 15 63 Palomar College 80 90 Page 14

• Insert index for record: 35 Melissa 38, 000 55 Before 8 2 71 15 63 After Sahar Mosleh 90 55 8 2 80 71 15 35 Palomar College 63 80 90 Page 15

• Insert index for record: 51 Joe 55 Before 8 71 2 15 After Sahar Mosleh 35 63 80 90 55 8 2 39, 000 35 15 71 51 Palomar College 63 80 90 Page 16

90 80 63 15 51 71 8 35 55 Emp. Id Name Salary 2 Jack 30, 000 80 Steve 32, 000 8 John 50, 000 71 Nancy 55, 000 15 Rose 90, 000 63 Abdul 35, 000 90 Pat 42, 000 55 Kathy 45, 000 35 Melissa 38, 000 Joe 39, 000 2 51 Sahar Mosleh Palomar College Page 17