Lecture 3

Question 1

What is an index? Index entry?

Answer 1

Index: A data structure that organizes records on disk for quick access
Index entry: A pair of search key, and record address

Question 2

Explain Hash based indexing

Answer 2

We have a hash function, H, our queries hit that hash function to determine the right bucket in the index file. The bucket stores a collection of pages, so we search bucket to find the right record.

Question 3

In a tree based index structure, where is the data stored? Index values?

Answer 3

Data: Leaf nodes
Index: Internal nodes

Question 4

Explain the difference between a clustered and unclustered index

Answer 4

Clustered is when the order of the index file is near identical to the order of the records

Question 5

Explain the difference between a dense and sparse index

Answer 5

Dense: At least 1 index for every search key
Sparse: Search keys may not have a index.

Question 6

Explain the difference between a primary index and a secondary index

Answer 6

Primary: index on the primary, unique key.
Secondary: Indexes on other columns, possible to have multiple indexes for the same search key.

Question 7

Does ISAM use overflow pages or does it use node splitting?

Answer 7

Overflow pages.

Question 8

In B+ trees, what do internal nodes store? Leaf nodes?

Answer 8

Internal: Index entries/Search keys
Leaf: Data entries

Question 9

What is the only scenario where redistribution in B+ tree insert is useful?

Answer 9

When it delays/postpones/prevents a split.

Question 10

When redistribution, what key value takes the position of the parent node?

Answer 10

The smallest key value in the second node

Question 11

Does a larger height of a B+ tree increase or decrease the operational speed of the tree?

Answer 11

Decrease

Question 12

What is fanout and how do we increase it?

Answer 12

Fanout is when we make the tree more “wide”
To increase fanout we can: increase page size, decrease size of index entry (key compression)