Phase 4: Query Evaluation.

In this phase of the project you will learn about Query Analyzer

Requirements

DBMS come with support for investigating query execution. This is usually part of a tool called Query Analyzer. It usually comes with a UI, which allows you to inspect execution plans. In this phase you will explore the functionalities of a Query Analyzer with the following 5 queries
  1. Q1: Find all authors whose names start with a user defined substring.
  2. Q2: Find all authors who have published in a given venue, say ICDE.
  3. Q3: Give the average paper count by year. Retain only those years where there are at least 1000 publications.
  4. Q4: Find the minimum and maximum h-index.
  5. Q5: Find the average h-index for the authors who have published at least one paper in 2010.
Report: For each query, give
  1. The initial parse tree and the parse tree of the fastest execution you could manage to optimize the execution of the query. Give the time difference between them.
  2. Give a brief explanation of each step you took to optimize a query. You may even chose to re-organize your database, i.e., splt a table into multiple tables (normalization).
  3. Start each query evaluation from scratch, that is, remove all indexes or other constraints you may have created for another query.
  4. Give a brief statement about what you observed.
  5. Make your database so that all queries are executed as fast as could manage. Run Q1 - Q5 in succession 5 times recording each time the execution time. Give the average execution time for each query.

Deliverables

  • Update your report.
  • Include screen captures for each parse tree.
  • Give a table that summarizes the time executions for each query. The table must have at least four columns: the intial excutiona time, your best execution time, the average time mentioned above, and a commnet about what made the most improvement in the query execution time in your opinion (this may vary among you).
  • Give a final paragraph summarizing your overall observations.
    • Start early!
    Collaborate! Compare and discuss your approaches. The end product is expected to be an individual effort!