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

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 Query Trace mechanism covered in this lecure comes from this book.
 * The Essence of SQL. A Guide to Learning the Most SQL in the Least Amount of Time. 1996. By David Rozenshtein

A practical introduction to developing queries.
 * SQL Visual Quickstart Guide. 2005. By Chris Fehily.