This assignment helps you understand what table columns deserve an index through a series of exercises on a database schema. You do not type SQL in this assignment, but rather identify which columns deserve indexes, the type of index it deserves, and why. Correct placement of indexes is absolutely critical to database performance. All but the smallest of databases would have terrible performance if no indexes were assigned to any of its table columns.
An index is a physical construct that serves two purposes – speeding up data retrieval and enforcing uniqueness. Indexes are not modeled in logical entity-relationship diagrams, because indexes do not operate at the logical level of ...view middle of the document...
Let us begin exploring index placement by first examining the album schema below.
1. The first item deserving our attention is that many modern relational DBMS, including Oracle and SQL Server, automatically add unique indexes to table columns covered by a primary key constraint.
Identify the primary key columns in the album schema, using the standardized dot notation, that is, TableName.ColumnName. We do not need to add indexes to these since the DBMS will create them automatically for us.
2. The next thing we want to do is to add indexes to all foreign key columns. We do this without concerning ourselves with any SQL queries (as we do in #3 below). Some DBMS, including Oracle, will sometimes escalate a row-level lock to a page-level lock when a SQL join is performed using a foreign key that has no index. The focus of this assignment is not locking, so I will not get into fine details, but suffice it to say that page-level locks are always bad for transactions because they result in deadlocks over which the database developer has no control. Another reason we index all foreign key columns is because the vast majority of the time, the foreign key will be used in the WHERE clause of SQL queries that perform a join on that table.
Identify the foreign key columns in the album schema, using the standardized TableName.ColumnName notation. For each foreign key, indicate whether you would create a non-unique index, or a unique index. A non-unique index speeds up data retrieval, but does not enforce a uniqueness on the covered column, so that values in the column can repeat. A unique index speeds up data retrieval and also enforces uniqueness on the covered column. Unique indexes obtain better performance than non-unique indexes for some queries, because the DBMS query optimizer knows that each key requested from a unique index will at most have one value, while each key requested from a non-unique index may have many values. Therefore if it is guaranteed that values will not repeat, it is better to use unique indexes. However, adding a unique index on a column that has values that can repeat will cause erroneous transaction abortions every time a repeated value is added to the column, so it is important to correctly discern which type of index is needed.
3. This step concerns itself with adding indexes needed by specific queries. We have already identified the primary key columns for which the DBMS creates indexes automatically, and have identified the foreign key indexes. The final step in the process of adding indexes to a schema is to index all columns referenced in the WHERE clause and join conditions of all pertinent SQL queries. The WHERE clause and join conditions in a SQL query contains conditions that specify what rows from the tables will be present in the result set. The query optimizer makes heavy use of these conditions to ensure that the results are retrieved in a timely fashion. For example, if the...