Work through the following materials this week, making sure that you can do the given activities.

1. Chapter 17

   1. B+ Trees — Focus on the Section 17.3 (skipping the material on algorithms).

      1. Compare and contrast B-trees and B⁺-trees.

      2. Explain what it means for such a tree to be balanced.

      3. Compare and contrast tree and data pointers.

   2. Explain the nature of multiple-attribute indexes (see the introduction to Section 17.4).

   3. Explain the nature of hash and bitmap indexes (see Sections 17.5.1–2).

   4. Physical Database Design — Focus on the Section 17.7.

      1. Explain the concept of physical database design (PDD).

      2. Compare and contrast the uses for B-tree, hash and bitmap indexes in PDD.

      3. Explain whether denormalization should be considered a form of PDD.

2. Chapter 18

   1. Explain the concept of query optimization (see the introduction to the chapter).

3. Oracle’s EXPLAIN PLAN

   1. Load the small version of the Internet Movie DB defined in the repo (cs342/databases/imdb), configure the auto-trace facility in SQL*Plus (SET SERVEROUTPUT ON; & SET AUTOTRACE ON;) and generate an execuation plan for some simple query. What is the execution plan operation used and what does that operation do?

   2. Explain at least the following operations (see Oracle Execution Plan Operations).

      • TABLE ACCESS FULL
      • TABLE ACCESS BY INDEX
      • NESTED LOOPS
      • HASH JOIN
      • INDEX UNIQUE SCAN

   For Oracle’s reference documentation, see Database Performance Tuning Guide, particularly the “Using EXPLAIN PLAN” section. Note that the text also explains these operations conceptually in Chapter 18.