Database Indexing MCQs: 12 Solved Questions on Index Types

Indexing questions reuse familiar words across two different classification axes. Primary, clustering and secondary describe how the search field relates to physical file order. Dense and sparse describe how many search-key values receive entries. Mixing these axes makes several options appear correct.

These 12 questions test indexing purpose, ISAM, index classification and access-path choice. Attempt each first, writing two notes: Is the data file ordered on this field? and Does the index contain every search-key value? For broader practice, continue with DBMS MCQs.

Fix the indexing vocabulary with one eight-record file

Use EMPLOYEE(emp_id, dept, salary), physically ordered by the unique key emp_id:

• B1 = {(101,CSE,60000),(104,ECE,55000),(109,CSE,60000)}

• B2 = {(115,CSE,70000),(118,ME,55000),(125,ECE,65000)}

• B3 = {(131,ME,70000),(140,CSE,80000)}

Its sparse primary index is {101 -> B1, 115 -> B2, 131 -> B3}. Its dense secondary salary index is 55000 -> {104,118}, 60000 -> {101,109}, 65000 -> {125}, 70000 -> {115,131}, and 80000 -> {140}.

For a clustering-index view, physically regroup the same rows by non-key dept. Store all CSE rows together, then ECE, then ME, and keep CSE -> first CSE block, ECE -> first ECE block, and ME -> first ME block.

These are three views of the same records. The primary view uses the unique ordering key emp_id. The clustering view changes physical order to the repeating field dept. The salary view leaves the emp_id order untouched, so salary remains a non-ordering secondary field even though its own index entries are sorted.

The rule is simple. Primary, clustering or secondary identifies which field controls file order. Dense or sparse identifies entry coverage. If you need insertion, splitting and leaf-link mechanics, study B+ Trees and Database Indexing: A Worked Guide. Index classification and access choice determine the appropriate access path.

Questions 1-2: indexing purpose and ISAM

Question 1

Previous-year question source: TPSC Computer Science, Programmer, 2025.

Which of the following is true about “indexing” in databases ?

• A. Indexing improves the speed of data retrieval.

• B. Indexing reduces the storage requirements of a database.

• C. Indexing increases the complexity of queries.

• D. Indexing prevents SQL injection attacks.

Answer: A.

For emp_id = 125, the sparse index selects B2 instead of scanning all three blocks. Indexes cost storage and maintenance; they neither complicate SQL nor prevent injection.

Question 2

Previous-year question source: DSSSB Computer Science, TGT Shift 2, 2021.

What is the full form of ISAM in file organisation?

• A. Indirect Sequential Access Method

• B. Indexed Sequential Access Method

• C. Indirect Serial Access Method

• D. Index Serial Access Method

Answer: B.

Expand it as I = Indexed, S = Sequential, A = Access, M = Method. Values 101, 104, 109 stay sequential in B1; 101 -> B1 provides indexed access.

Questions 3-5: primary, dense and sparse indexes

Question 3

Previous-year question source: DSSSB Computer Science, TGT, 2024.

A _____ index is an ordered file whose records are of fixed length with two fields primary key and a pointer to a disk block.

• A. Secondary

• B. Multilevel

• C. Primary

• D. Clustering

Answer: C.

In (115, B2), 115 is the primary key starting the second ordered block and B2 is its pointer. Multilevel describes stacked index levels, not this relationship.

Question 4

Previous-year question source: DSSSB Computer Science, TGT Shift 2, 2021.

Primary index in sequential order file organisation is also known as ______.

• A. dense index

• B. clustering index

• C. non-clustering index

• D. sparse index

Answer: D.

Eight records need only three entries, one per block. For 118, choose the greatest index key not exceeding it, 115, read B2, then scan within it.

Question 5

The topic practice hub holds this item without a stable question-specific URL.

Which of the following statements is/are true?

S1) Sparse index has index entries for only some of the search values.

S2) Dense index has index entries for every search key value (which may or may not correspond to every record depending on uniqueness) .

• A. Only S1

• B. Only S2

• C. Both S1 and S2

• D. Neither S1 nor S2

Answer: C.

The sparse index has only 101, 115, 131. The dense salary index represents all five distinct salaries; repeated salaries share an entry, so every search-key value does not mean every record.

Questions 6-8: clustered, clustering and secondary indexes

Question 6

Previous-year question sources: GATE Computer Science, Set 1, 2015; TPSC Computer Science, Senior Informatics Officer, 2025.

A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

• A. Dense

• B. Sparse

• C. Clustered

• D. Unclustered

Answer: C.

Store four CSE rows together, then two ECE and two ME rows. An index ordered CSE, ECE, ME follows that physical grouping; density remains a separate property.

Question 7

Previous-year question sources: ISRO Computer Science, 2016; UGC NET Computer Science, 2018; GATE Computer Science, 2008.

A clustering index is defined on the fields which are of type

• A. non-key and ordering

• B. non-key and non-ordering

• C. key and ordering

• D. key and non-ordering

Answer: A.

dept is non-key because CSE repeats for 101, 109, 115, 140. After regrouping, it orders the file because CSE rows precede ECE and ME.

Question 8

Previous-year question source: RPSC Computer Science, Programmer P1, 2024.

Indices whose search key specifies an order different from the sequential order of the file are called:

• A. Primary Indices

• B. Random Indices

• C. Sequential Indices

• D. Secondary Indices

Answer: D.

File order is 101, 104, 109, 115, 118, 125, 131, 140; salary order is 55000, 60000, 65000, 70000, 80000. Different orders make salary secondary. Random Indices is not standard.

Questions 9-10: composite, ordered and hash access paths

Question 9

Previous-year question source: ISRO Computer Science, 2025.

Consider a table users(userid, country, city, street) with 50 million users. DBA creates the following index for the table CREATE INDEX myindex ON users(country, city, street) For which of the following queries will this index be least useful?

• A. SELECT userid FROM users WHERE country='IN'

• B. SELECT userid FROM users WHERE street='MG Road'

• C. SELECT userid FROM users WHERE country='IN' AND city='Pune' AND street='MG Road'

• D. SELECT userid FROM users WHERE country='IN' AND city='Pune'

Answer: B.

The order ('IN','Delhi','Ring Road'), ('IN','Pune','FC Road'), ('IN','Pune','MG Road'), ('US','Boston','Main Street') supports leftmost prefixes. A street-only predicate skips country and city, so it cannot isolate one contiguous range.

Question 10

Previous-year question source: GATE Computer Science, 2011.

Consider a relational table $r$ with sufficient number of records, having attributes $A_1, A_2, \dots ,A_n$ and let $1 \leq p \leq n$. Two queries $Q1$ and $Q2$ are given below.

$Q1: \pi_{A_1, \dots ,A_p} \left(\sigma_{A_p=c}\left(r\right)\right)$ where $c$ is a constant

\(Q2: \pi_{A_1, \dots ,A_p} \left(\sigma_{c_1 \leq A_p \leq c_2}\left(r\right)\) where $c_1$ and $c_2$ are constants.

The database can be configured to do ordered indexing on $A_p$ or hashing on $A_p$. Which of the following statements is TRUE?

• A. Ordered indexing will always outperform hashing for both queries

• B. Hashing will always outperform ordered indexing for both queries

• C. Hashing will outperform ordered indexing on $Q1$, but not on $Q2$

• D. Hashing will outperform ordered indexing on $Q2$, but not on $Q1$

Answer: C.

With {101, 205, 240, 275, 310}, hashing targets 205 for Q1. For Q2 from 200 to 300, an ordered index seeks to 205 and scans 205, 240, 275; hashing preserves no range order.

Questions 11-12: bitmap choice and dense-index block arithmetic

Question 11

Previous-year question source: ISRO Computer Science, December 2017.

Consider a table that describes the customers :

Customers(custid, name, gender, rating)

The rating value is an integer in the range 1 to 5 and only two values (male and female) are recorded for gender. Consider the query “how many male customers have a rating of 5”? The best indexing mechanism appropriate for the query is

• A. Linear hashing

• B. Extendible hashing

• C. B+ Tree

• D. Bit-mapped hashing

Answer: D.

The option calls this bit-mapped hashing; the underlying mechanism is bitmap indexing. A bitwise AND of the male bitmap 10110101 and the rating-5 bitmap 00100101 gives 00100101. Its three 1-bits identify three male customers rated 5, so the low-cardinality gender and rating fields suit this bitmap approach.

Question 12

Previous-year question source: ISRO Computer Science, 2015.

Given a block can hold either 3 records or 10 key pointers. A database contains n records, then how many blocks do we need to hold the data file and the dense index

• A. 13n/30

• B. n/3

• C. n/10

• D. n/30

Answer: A.

Each full data block holds 3 records, and each dense-index block holds 10 entries. The options treat n as divisible by 30, giving n/3 data blocks and n/10 index blocks. Thus n/3 + n/10 = 10n/30 + 3n/30 = 13n/30. At n = 300, 100 + 30 = 130, and 13(300)/30 = 130. For other values, use ceil(n/3) + ceil(n/10) to include partially filled final blocks.

Diagnose the traps, score the set and choose the next step

These are study thresholds, not official cutoffs. At 10-12, redo Questions 9 and 12 unaided. At 7-9, redraw the three index views and label both axes. At 0-6, revise the opening example, attempt Questions 3-8, then return to the later items.

For a structured semester-oriented CS route, use ZERO TO HERO. For placement-focused DBMS revision, continue with Computer Science Fundamentals for Placements by Sanchit Sir. You can also browse the wider CS Fundamentals collection.

Identify the physical ordering field, count search-key values, then match the structure to equality, range or low-cardinality filtering. Annotate each item with ordering field, key or non-key, dense or sparse, and best access pattern.