[DATABASE INDEXES] A single-level index is best described as a file containing what?
A file containing index records, where each record consists of a search key value and a data pointer.
WHY: An index acts exactly like a book’s glossary, mapping a searchable term directly to its exact physical page or block location on the disk.
[DATABASE INDEXES] Define the core components required in a single-level database index entry.
A single-level database index entry requires exactly two elements: a SEARCH KEY and a POINTER.
MNEMONIC: KP (Key-Pointer) - The Key tells the database what the data is, the Pointer tells the database where the data lives.
[DATABASE INDEXES] In a real-world scenario with a massive database table spanning millions of rows, why might a single-level index file become highly inefficient?
The single-level index file itself becomes too large to fit into RAM memory and must be searched sequentially on the hard disk.
EDGE CASE FIX: To resolve this, architects use a MULTI-LEVEL index (like a B-Tree structure) to create an index of the index, speeding up lookups.
[DATABASE INDEXES] How many primary indexes can a single sorted database table have?
Exactly ONE.
WHY: A primary index dictates the physical sorting order of the records on the storage disk. A physical file can only be sorted in one physical sequence at a time.
[DATABASE INDEXES] State the fundamental rule for how many primary or clustered indexes can exist on a database table.
A database table can have a maximum of ONE primary (or clustered) index.
MNEMONIC: ONE physical order equals ONE primary index.
[DATABASE INDEXES] If a database table already has a primary index on the EmployeeID column, but the business frequently searches by LastName, what type of index must be created for LastName?
A SECONDARY index (or non-clustered index).
SCENARIO: Because the table is already physically sorted by EmployeeID, any additional index must use secondary pointers that reference the primary data without altering the physical disk order.
[DATABASE INDEXES] In a database, which term is often used interchangeably with “non-clustered index”?
Secondary index.
WHY: A non-clustered index is secondary to the primary or clustered physical order of the table. It stores a completely separate structure of pointers mapping back to the primary data.
[DATABASE INDEXES] Define a secondary (or non-clustered) database index in plain English.
An index that is stored separately from the actual table data rows, containing a copy of the indexed columns and pointers to the actual data.
MNEMONIC: SECONDARY means SEPARATE data structure.
[DATABASE INDEXES] A reporting system heavily queries an Orders database table by OrderDate, but the table is physically sorted by OrderID. Why is a secondary index on OrderDate slower than the primary index?
The secondary index on OrderDate requires an extra read step: looking up the pointer in the index, then performing a BOOKMARK LOOKUP to fetch the actual full record from the primary data disk blocks.
[DATABASE INDEXES] A Products database table has its physical records on disk sorted according to its ProductID. The index built on ProductID is known as what?
A Primary index (or Clustered index).
WHY: When the index search key perfectly dictates the physical, sequential storage order of the data blocks on the disk, it is called a primary or clustered index.
[DATABASE INDEXES] What is the core rule that defines a primary or clustered index in a database system?
The index dictates the actual PHYSICAL sorting order of the table records on the hard disk.
MNEMONIC: PRIMARY dictates PHYSICAL positioning.
[DATABASE INDEXES] An engineering team decides to change the primary index of a massive database Log table from an auto-incrementing LogID to a randomized UserUUID. What is the severe performance consequence?
PAGE SPLITS AND FRAGMENTATION.
SCENARIO: Because the table physically sorts incoming data by the primary index, inserting random UUIDs forces the database engine to constantly shift massive amounts of disk data to maintain physical sorting order, destroying database write speeds.
[DATABASE INDEXES] The choice of database table structure and the creation of indexes are key activities in which phase of database design?
Physical database design phase.
WHY: Creating indexes and deciding on physical storage directly deals with how data is stored on disk hardware to optimize query performance, which happens after conceptual and logical design.
[DATABASE INDEXES] Define the Physical Database Design phase in the database lifecycle.
The phase where the logical schema is translated into actual Database Management System (DBMS) structures, specifically defining disk storage, data types, indexes, and partitioning for performance optimization.
MNEMONIC: PHYSICAL phase equals PERFORMANCE on disk.
[DATABASE INDEXES] During database design, why must Entity Relationship Diagrams and Normalization be completed before creating database indexes?
Entity Relationship Diagrams and Normalization belong to Logical Design.
SCENARIO: You cannot optimize disk reads (Physical Design via indexes) until you have finalized the exact tables, columns, and relationships that will exist to prevent data anomalies.
