Refining the Database Design › Estimating I/Os for Transactions › Sample Exercise #2: Estimating I/Os for an Update Transaction

Sample Exercise #2: Estimating I/Os for an Update Transaction

When you estimate I/Os for a transaction that performs update functions, you need to consider I/O operations that must be executed to ensure database integrity. In addition to the I/Os required to access desired entities, update transactions must perform I/Os to access related entities. Some types of integrity checking require that the system access other related entities.

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

Add a skill for an employee.

To protect the relationship between an EMPLOYEE entity and an associated EXPERTISE entity, the EMPLOYEE entity must be accessed before storing the EXPERTISE entity. Likewise, to protect the relationship between a SKILL entity and an associated EXPERTISE entity, the SKILL entity must be accessed before storing the EXPERTISE entity.

I/O estimates

Knowing this information, 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 access one EMPLOYEE entity in the database.
• SKILL—Since the SKILL entity is stored CALC, only one I/O is required to access a single SKILL occurrence in the database.
• EXPERTISE—EXPERTISE entities are clustered around a related EMPLOYEE entity. Therefore one I/O is necessary to store the EXPERTISE entity.

Estimating I/Os for a sample update transaction

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