Database Management: Indexing

Play an AI-generated podcast conversation about this lesson

What is the characteristic of a dense index?

Index records appear for every search-key value in the file. (correct)
Index records are applicable when records are not sequentially ordered.
Index records are sequentially ordered on search-key.
Index records appear for only some search-key values.

What is the advantage of sparse index files?

They are generally faster than dense indices for locating records.
They are applicable when records are not sequentially ordered.
They use less space and have less maintenance overhead for insertions and deletions. (correct)
They require more space and maintenance overhead for insertions and deletions.

What is the disadvantage of indexed-sequential files?

They require less space and maintenance overhead.
They are faster than B+-tree indices.
Their performance degrades as the file grows. (correct)
They are applicable when records are not sequentially ordered.

What is a characteristic of B+-tree indices?

They are an alternative to indexed-sequential files. (C) Signup and view all the answers

What is the procedure to locate a record with search-key value K in a sparse index?

Find the index record with the largest search-key value < K and search file sequentially. (A) Signup and view all the answers

What is the advantage of a sparse index on top of a dense index (multilevel index)?

It is a good tradeoff for unclustered index. (B) Signup and view all the answers

What is the assumption made in the simplest file organization approach?

Each record size is fixed (B) Signup and view all the answers

What is a consequence of not moving records on deletion?

Free space is wasted (D) Signup and view all the answers

What is the purpose of a free list?

To store record pointers (A) Signup and view all the answers

What is a characteristic of variable-length records?

Records are of different types (C) Signup and view all the answers

What is an alternative to moving records on deletion?

Linking free records on a free list (A) Signup and view all the answers

What is a consequence of allowing records to cross block boundaries?

Modification is more complex (B) Signup and view all the answers

What type of record type allows variable lengths for one or more fields?

Varchar record (B) Signup and view all the answers

How are null values represented in a record?

Using a null-value bitmap (D) Signup and view all the answers

What is the method of storing records in a file based on the value of the search key of each record?

Sequential (C) Signup and view all the answers

What is the organization of records method that uses a hash function to determine the block of the file where the record should be placed?

Hashing (B) Signup and view all the answers

How can records of each relation be stored in a file?

In a separate file for each relation (C) Signup and view all the answers

What is the main purpose of a multitable clustering file organization?

To store related records on the same block to minimize I/O (A) Signup and view all the answers

What is the primary concern in a sequential file organization when inserting a new record?

Maintaining the sequential order of the file (A) Signup and view all the answers

What is the function of a hash index?

To distribute search keys uniformly across buckets (B) Signup and view all the answers

What is the primary advantage of indexing mechanisms?

Speeding up access to desired data (B) Signup and view all the answers

What is the purpose of an index file?

To facilitate fast access to desired data (A) Signup and view all the answers

What is an example of an access type supported efficiently by indexing mechanisms?

Records with a specified value in the attribute (C) Signup and view all the answers

What is the primary purpose of ordered indices?

To make searching faster (B) Signup and view all the answers

What is a characteristic of a clustered index?

It is used to group records with similar characteristics (D) Signup and view all the answers

What happens when searching for a record in a database with no index?

The DBMS reads the record after reading the specified number of bytes (C) Signup and view all the answers

What is the benefit of using a clustered index in a department-based employee database?

It groups employees by department for faster lookup (C) Signup and view all the answers

What is the purpose of grouping records in a clustered index?

To identify the record faster (B) Signup and view all the answers

What happens to the records which have similar characteristics in a clustered index?

They are grouped together and indexed (C) Signup and view all the answers