DATABASE SYSTEMS II - CS662 , Spring 1999

Instructor: Damianos Chatziantoniou

Text : (1) Database System Concepts, 3rd Edition, Silberschatz, Korth, Sudarshan, Mc Graw Hill. (2) The Data Warehouse Toolkit, Ralph Kimball, John Wiley and Sons.

Office Hours : Mondays 4:00-6:00, Tuesdays 4:00-6:00, Lieb313

Teaching Assistant : Urvashi Patel (upatel2@stevens-tech.edu)

Topics : Query Processing, Transactions, Concurrency Control, Recovery, Parallel Databases, Data Warehousing, OLAP.

  • Syllabus (Word Document)

    Lectures:

  • Lecture 1 - Query Processing (postscript)
  • Lecture 2 - Transactions (postscript)
  • Lecture 3 - Concurrency Control (post script)
  • Lecture - - Special Topics (post script)
  • Lecture 4 - Recovery (postscript)

    Homeworks :

  • Homework 1 - due 2/9 : 12.3, 12.4, 12.7, 12.8, 12.11, 12.14, 12.20, 12.19(a),(b)
  • Homework 2 - due 3/9 : 13.6, 13.7, 13.8, 13.9, 14.1, 14.2, 14.14, 14.15, 14.18, 14.20, 14.24
  • Homework 3 - due 3/30 : 15.3 to 15.9

    Project :

    • You will be asked to run your queries over the following relation. A customer Cust purchases a product Prod that belongs to the category ProdCategory. The sale takes place on Day,Month,Year date, on state State and the quantity is Quant.

    Sales(Cust,Prod,ProdCategory,Day,Month,Year,State,Quant)

    Some sample queries that you should try are the following:

  • For each customer, show his/her total sales in "NY", in "NJ", and "CN" (in 3 different columns).
  • Find for each customer the total of 1997 January's sales that had quantity greater than the customer's 1997 average sale, and the total of 1997 June's sales with quantity greater than the customer's 1997 average sale (2 different columns).
  • For each product find the average sale in "NY" and count (i) the number of sales of that product in "NJ", (ii) how many sales of that product in "NJ" had quantity greater the "NY"'s average sale (2 columns per product).