Courses/Computer Science/CPSC 203/CPSC 203 2007Fall L04/CPSC 203 2007Fall L04 Lectures/Lecture 11

From wiki.ucalgary.ca
Jump to: navigation, search

Lecture 11

Most of the interactions you are likely to have with databases (unless you become a database designer or administrator) consist of trying to get useful information out of a database -- i.e. querying it. The purpose of today's lecture is to understand, "what is happening under the hood" when we do a query. To understand how a database handles queries, we have to understand the query trace mechanism.

First recall our triad of ideas:

  1. In A Database --- Everything is Abstacted to 2-D tables. ("It's Tables all the way down").
  2. A table represents is a set of values for some object -- identified by the Primary Key. Each row in a table specifies a set of attributes for an "instance" of an object (an individual).
  3. All operations in a database are operations on sets of values.

The query Trace method described below is a way of manipulating data (values) in 2D tables based on set theoretic ideas. In essence you could 'trace' any query on paper -- but it's much more efficient to let a computer do it.


The objectives of today's class are:

  • House Keeping
    • Text Reading -- Chapter 11
    • Class Noise Levels -- Please be considerate to your fellow students.
    • Reminders:
      • I'll put up a small practice test on BB Thursday (about a half dozen questions).
      • Half of next Tuesday's session will be Quetion/Answer session to help you prepare for the first mid-term.
      • Midterm is next Thursday.



  • Topics
    • Quick Review of Books Database (from end of last class) w.r.t. Database Concepts.
    • Review Query Definition
    • Query Trace Mechanism
    • Examine the Query Trace Mechanism from P. of V. of the Books Database


Review of Query Definition and Glossary

  • Query – A way of asking questions of a database. In general a query of a table(s) returns a subset of the table(s) for which the query parameters are true. For example, which people in the class listed Google as their most frequently visited website. More formally, a query is a proposition about data extracting a subset of information based on specific requirements using the primary and foreign keys. A query language is used to set up these requests and the query can be repeated at different times for updated information


  • SQL -- "Structured Query Language" -- the language underlying most relational database
  • SQL Keywords (also called 'clauses')
    • SELECT -- Identifies columns that will be selected
    • FROM -- Identifies tables from which data will originate
    • WHERE -- Identifies constraints on which ROWS of data will be returned
    • GROUP BY -- In an Aggregate Query (one with groups) identifes the columns for which data will be grouped.
    • HAVING -- In an Aggregate Query, identifes constraints on which GROUPS will be returned -- each row is data on a group.

It is important to understand the role of each of these key words to understand the Query Trace Mechanism -- in general

  • SELECT (which columns?)
    • FROM (which tables?)
      • WHERE (which rows do you want returned?? -- Constrain on rows that match your criteria)
        • GROUP BY (which groups do you wish to summarize? -- group on columns)
          • HAVING ( which groups do you want returned? -- Constrain on groups that match your criteria)

The Query Trace Mechanism

... modified from "The Essence of SQL" by David Rozenshtein

  1. Take the Cross-Product of all tables in the FROM clause
  2. Consider every row from Step 1, exactly once and evaluate the WHERE clasue condition for it
  3. If the condition from Step 2 returns "TRUE", formulate a row according to the SELECT clause and retrieve it.
  4. Group (re-arrange the ros in the result of step 3 according to the GROUP BY clause.
  5. Evaluate the HAVING clause condition for each group, and eliminate those groups for which this condition fails.
  6. Formulate one resulting row for each remaining group (i.e. the ones for which HAVING was true according to the SELECT clause and retrieve it.

Steps 1-3 are called the "Type 1" query evaluation mechanism. When extended to step 6, they represent the query evaluation method for Aggregation (data summarization).





TEXT READINGS

TIA 3rd Edn: Chapter 11. 462 -- 503

TIA 4th Edn: Chapter 11. pp 484 -- 525

Resources

  • The Essence of SQL. A Guide to Learning the Most SQL in the Least Amount of Time. 1996. By David Rozenshtein

The Query Trace mechanism covered in this lecure comes from this book.

  • SQL Visual Quickstart Guide. 2005. By Chris Fehily.

A practical introduction to developing queries.