Relation Names

Indexing

Standard Physical Design

Defines the following for each relation name $R$

1. Primary index for $R$ materializing its extension as a concrete data structure
2. Zero or more secondary indices for $R$ materializing projections of the primary index for $R$ as concrete data structures

→ Materialization of a relation adds an additional record identifier (RID) attribute to the relation
→ Secondary Indices usually include the RID attribute of the primary index in their projection of $R$

Index Scan ${\sigma }_{\phi }(⟨index⟩)$

• $\phi$ - Condition supplies search values for the underlying data structure

Assuming $k$ = Cnum($⟨$index$⟩$) and $\phi$ is the condition $\mathrm{\#}i=c$ then an index scan is equivalent to ${\pi }_{\mathrm{\#}1,...,\mathrm{\#}k}({\sigma }_{\mathrm{\#}i=\mathrm{\#}k+1}(⟨index⟩\times c))$

Primary Indices

Btree index PROF-PRIMARY on pnum

Secondary Indices

Btree index PROF-SECONDARY on lname