This preview shows pages 1–2. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 208 Chapter 20 These queries occur much more frequently than updates, so you should build whatever indexes you need to speed up these queries. However, you should not build any un- necessary indexes, as updates will occur (and would be slowed down by unnecessary indexes). Given this information, design a physical schema for the company database that will give good performance for the expected workload. In particular, decide which attributes should be indexed and whether each index should be a clustered index or an unclustered index. Assume that both B+ trees and hashed indexes are supported by the DBMS, and that both single- and multiple-attribute index keys are permitted. 1. Specify your physical design by identifying the attributes you recommend indexing on, indicating whether each index should be clustered or unclustered and whether it should be a B+ tree or a hashed index. 2. Assume that this workload is to be tuned with an automatic index tuning wizard. Outline the main steps in the algorithm and the set of candidate configurations considered. 3. Redesign the physical schema assuming the set of important queries is changed to be the following: Find the total of the budgets for projects managed by each manager; that is, list proj mgr and the total of the budgets of projects managed by that manager, for all values of proj mgr . Find the total of the budgets for projects managed by each manager but only for managers who are in a user-specified age range. Find the number of male managers. Find the average age of managers. Answer 20.3 The answer to each question is given below. 1. For the first query, we should create a dense unclustered hash index on mgr dept for the Manager relation. We omit sex from the key in this in- dex since it is not very selective; however, including it is probably not very...
View Full Document
This note was uploaded on 01/17/2012 for the course EGN 4302 taught by Professor Dr.vishak during the Fall '12 term at University of Central Florida.
- Fall '12