ITPEC FE Morning April 2023 Question 30

Efficient access to where the data is stored, for faster searches — an index maps column values to the locations of their rows, so the DBMS need not scan the whole table.

An index, usually a B+ tree and sometimes a hash, keeps the values of the indexed column in search order, each with a pointer to its row. A search or join on that column follows the index straight to the matching rows instead of reading every row. The price is extra storage and slower inserts and updates, because the index must be kept up to date.

Answer (b)

Why not others:

• •(a) — spreading one large table over several disks is partitioning, and it does not save space; an index actually takes extra space.
• •(c) — preventing duplicate values is the job of a UNIQUE or PRIMARY KEY constraint; an ordinary index allows duplicates.
• •(d) — matching a foreign key to another table's primary key is referential integrity, enforced by a FOREIGN KEY constraint.

Key rule: Indexes are for performance and constraints are for correctness: an index speeds retrieval, while UNIQUE, PRIMARY KEY and FOREIGN KEY protect the data.

Databases · AI-written, may contain errors