Fundamentals of Database Indexing Quiz

Explore essential concepts of database indexing with this quiz, designed to enhance your understanding of how indexes improve query performance and storage efficiency. Perfect for learners seeking foundational knowledge on types of indexes, how they work, and best practices for database optimization.

1. Purpose of Indexes

   What is the primary purpose of an index in a relational database table?

   1. To prevent duplicate data
   2. To encrypt stored data
   3. To increase storage usage intentionally
   4. To speed up data retrieval operations

   Explanation: The main function of a database index is to enhance the speed of data retrieval operations, making searches more efficient. Preventing duplicate data is the job of constraints, not indexes. Encrypting stored data is handled by security mechanisms, not indexing. Although indexes may increase storage usage, their purpose is not to do so intentionally.

2. Index Types

   Which type of index only contains references to the storage location of data instead of storing the actual data rows?

   1. Text index
   2. Non-clustered index
   3. Hashed index
   4. Clustered index

   Explanation: A non-clustered index stores pointers to the data rather than the data itself, facilitating faster searches without rearranging physical row order. Clustered indexes sort and store the actual data rows, not just references. Hashed and text indexes are specific types for certain queries and do not specifically refer to this approach.

3. Index Columns

   If a query frequently searches for employees by last name, which column should be indexed for optimal performance?

   1. First name
   2. Last name
   3. Employee ID
   4. Date of birth

   Explanation: Indexing the 'last name' column is ideal if queries often filter or sort by last name, resulting in faster data retrieval. The other columns—first name, employee ID, or date of birth—would not provide the same benefit if they are not frequently queried, making them less appropriate for indexing in this scenario.

4. Downside of Over-Indexing

   What is a potential disadvantage of creating too many indexes on a single database table?

   1. Reduces query speed
   2. Deletes data accidentally
   3. Decreases table size
   4. Increases storage requirements and slows down write operations

   Explanation: Too many indexes increase storage needs and can degrade performance for insert, update, or delete operations since each index must be updated. While well-chosen indexes improve query speed, over-indexing can have the opposite effect for write-heavy tables. Indexes do not cause accidental data deletion or decrease table size.

5. Unique Index Function

   Which type of index ensures that no two rows have the same value in one or more columns?

   1. Partial index
   2. Bitmap index
   3. Composite index
   4. Unique index

   Explanation: A unique index enforces uniqueness by preventing duplicate values in the indexed column(s). Partial and composite indexes have different purposes, such as indexing a subset of data or multiple columns respectively but do not enforce uniqueness. Bitmap indexes are typically used for columns with few distinct values and also do not ensure uniqueness.

6. Index and WHERE Clause

   When can a database make the most effective use of an index during a query?

   1. When no indexes are present
   2. When the index is created after the query runs
   3. When an index covers columns used in the WHERE clause
   4. When the index is on columns in the SELECT clause only

   Explanation: An index is most useful if it includes columns commonly used in search conditions (WHERE clause), as it speeds up locating relevant rows. Indexes on only SELECTed columns do not improve filtering speed. Creating an index after a query does not help that query, and having no indexes provides no performance gain.

7. B-Tree Index Explanation

   Which data structure is most commonly used to implement a general-purpose database index for ordered retrieval?

   1. Queue
   2. B-tree
   3. Hash table
   4. Linked list

   Explanation: B-tree structures keep data sorted and allow for efficient range and equality searches, making them ideal for most database indexes. Linked lists and queues are better for sequential operations, while hash tables excel at direct lookups but not ordered retrieval, which is essential in many indexing scenarios.

8. Index Impact on Delete Operations

   How does the presence of indexes on a table affect delete operations?

   1. Delete speed is unaffected
   2. Deletes are blocked completely
   3. Deletes are always faster
   4. Deletes may be slower due to index updates

   Explanation: When deleting rows, all corresponding index entries must also be updated or removed, which can slow down the operation. Indexes do not make deletes inherently faster or unaffected. Deletions are never completely blocked by indexes; only the speed may be influenced.

9. Composite Index Usage

   If a composite index is created on (first_name, last_name), which query will utilize the index most efficiently?

   1. SELECT * WHERE last_name = 'Smith'
   2. SELECT * WHERE first_name = 'Jane' AND last_name = 'Smith'
   3. SELECT * WHERE city = 'Boston'
   4. SELECT * WHERE age = 25

   Explanation: A composite index on (first_name, last_name) is best utilized when queries include both columns starting with the first component. Using only the second column or unrelated columns, as in other options, provides limited or no benefit from this index.

10. Indexing NULL Values

    Are NULL values included in most default database indexes?

    1. NULL indexes cannot exist
    2. Always excluded
    3. Always included
    4. Included depending on database settings

    Explanation: Whether NULLs are indexed varies based on database settings and index type due to different treatment across systems. They are not always excluded or always included by default. Saying NULL indexes cannot exist is incorrect, as indexes can reference rows containing NULLs.