Courses/Computer Science/CPSC 203/CPSC 203 2008Winter L03/CPSC 203 2008Winter L03 Lectures/Lecture 12

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

Lecture 12

Last class we covered some basic database terminology, and introduced the key notion that in a relational database we combine information across tables by defining intersection sets based on primary and foreign keys. Today we refine that notion by examining the Query Trace Mechanism.

Our focus today is to examine the Query Trace Mechanism. Once you understand how to Trace Queries -- you should be able to predict what a particular query is going to return, and are in a good position to begin developing more complex queries, and analyzing data using queries. Remember -- queries are a way of asking questions of a database.



  • House Keeping
    • Text Reading -- Chapter 11 -- Pay particular attention to the sections: Data Storage, Managing Data , and Data Mining.
    • Midterm Results: Class Average: 16.4/20 (82.2 %). An Exam Key is available in CPSC Office: ICT 602. Ask for Susan Lucas (Undergraduate Administrator)
    • Assignment 1 Extension -- Now Due: Midnight March 24th
    • Additional Database Materials for Practice and Background:(Supplements Tutorials, and TAIT)



  • The Objective of Today's Class is:
  • Review the Basic SQL syntax on which the Query Trace Mechanism is based.
    • To Review the Query Trace Mechanism
    • Look at a few examples of queries using the Query Trace Mechanism



Review of Query Definition and Glossary (from last class)

  • 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, The Query Language we will be used to describing the Query Trace Mechanism is called SQL.


  • 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 With a Few Extra Notes

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

  1. Take the Cross-Product of all tables in the FROM clause. Result -- A 'temporary table' from which you will delete rows and columns according to the rules below
  2. Consider every row from Step 1, exactly once and evaluate the WHERE clause condition for it. Result -- Some rows will meet the condition, "TRUE", and others will not, "FALSE".
  3. If the condition from Step 2 returns "TRUE", formulate a row according to the SELECT clause and retrieve it. Result -- Cross out all lines that are false. Cross out columns that are not in the SELECT clause.
  4. Group (re-arrange the rows in the result of step 3 according to the GROUP BY clause. Result -- You now have one row for each group.
  5. Evaluate the HAVING clause condition for each group, and eliminate those groups for which this condition fails. Result -- Rows representing groups for which HAVING is "FALSE" are crossed out.
  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. Result -- Returns summary data for all 'TRUE' rows in HAVING, based on the (summary" variables chosen in the SELECT clause.

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.

  • "Practical Issues in Database Management. Chapter 5. By Fabian Pascal.

The principles of Database Design are very practically set forth in this book.

Retrieved from "http://wiki.ucalgary.ca/index.php?title=Courses/Computer_Science/CPSC_203/CPSC_203_2008Winter_L03/CPSC_203_2008Winter_L03_Lectures/Lecture_12&oldid=20235"