Assignment Tulsa Essay

2178 Words May 30th, 2016 9 Pages
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 abstraction, as do tables and table columns. While the
…show more content…
[pic]

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,

Related Documents