Courses/Computer Science/CPSC 203/CPSC 203 2007Fall L04/CPSC 203 2007Fall L04 Lectures/Lecture 11
Contents
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:
- In A Database --- Everything is Abstacted to 2-D tables. ("It's Tables all the way down").
- 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).
- 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)
- GROUP BY (which groups do you wish to summarize? -- group on columns)
- WHERE (which rows do you want returned?? -- Constrain on rows that match your criteria)
- FROM (which tables?)
The Query Trace Mechanism
... modified from "The Essence of SQL" by David Rozenshtein
- Take the Cross-Product of all tables in the FROM clause
- Consider every row from Step 1, exactly once and evaluate the WHERE clasue condition for it
- If the condition from Step 2 returns "TRUE", formulate a row according to the SELECT clause and retrieve it.
- Group (re-arrange the ros in the result of step 3 according to the GROUP BY clause.
- Evaluate the HAVING clause condition for each group, and eliminate those groups for which this condition fails.
- 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.