Refining the Database Design › Estimating I/Os for Transactions › Sample Exercise #1: Estimating I/Os for a Retrieval Transaction

Sample Exercise #1: Estimating I/Os for a Retrieval Transaction

Suppose you need to estimate I/Os for the following transaction:

Identify skills for an employee.

In this transaction, the user specifies an employee ID value and the system returns the employee ID, name, skill code, skill level, and skill description for the specified employee. This transaction uses the EMPLOYEE entity as an entry point to the database.

I/O estimates

By analyzing the access path of the transaction, you can make the following I/O estimates for each entity accessed:

• EMPLOYEE—Because this entity is stored CALC, only one I/O operation is required to retrieve one EMPLOYEE entity occurrence from the database.
• EXPERTISE—Each employee can have as many as five skills. Therefore, the transaction retrieves five EXPERTISE entity occurrences for each EMPLOYEE entity occurrence. However, since EXPERTISE entity occurrences are clustered around a related EMPLOYEE entity occurrence, no I/Os are necessary to retrieve the EXPERTISE entity occurrences.
• SKILL—For each EXPERTISE entity occurrence retrieved, there is an associated SKILL entity occurrence in the database. Therefore five SKILL entity occurrences are retrieved for each employee. Since the SKILL entity is stored CALC, its occurrences are distributed randomly in the database. To retrieve five SKILL entity occurrences, the system must perform five I/Os.

Estimating I/Os for a sample retrieval transaction

A total of six I/O operations will be performed by this transaction, as shown below.