CSE 562: Database Systems (Spring 2012)


Instructor: Dr. Jan Chomicki (chomicki@buffalo.edu, office hours: T 11-12, W 2-4, 338I Davis)

Teaching Assistants:

Registration number: 17664

Credit hours: 3.0

Time: T R 2:00-3:20

Location: Cooke 121

Text: H. Garcia-Molina, J. D. Ullman, J. Widom. Database Systems: The Complete Book, Second Edition, Prentice Hall, 2009. Book website.

Blog: ub-cse-562-s12.blogspot.com

Lecture slides:

  1. Introduction to DBMS: slides, handouts
  2. Relational databases: slides, handouts
  3. Indexing: slides, handouts
  4. Query processing: slides, handouts
  5. Preference queries: slides, handouts
  6. Database design: slides, handouts
  7. Transactions: slides, handouts

Additional slides (courtesy of Demian Lessa):

  1. Introduction to Oracle


  1. Test 1: relational data model and query languages (02/23/12)
  2. Test 2: indexing and query evaluation (04/05/12)
  3. Comprehensive final (05/03/12)


  • Homework 1 (due 02/20/12, updated 02/16/12)
  • Homework 1 solutions
  • Projects:

  • Project 1 (due April 9, 2012) :
  • Project 2 (due May 6, 2012)
  • Other resources:

  • Oracle (CSE)
  • Oracle help (login, password)
  • TOra (a graphical GUI for Oracle)
  • Advanced SQL queries (solutions)
  • Joe Celko's SQL for Smarties, Fourth Edition: Advanced SQL Programming, Morgan Kaufmann, 2011.
  • Prerequisites: A solid background in programming and data structures. A significant programming experience in Java. This is not a place to learn Java or programming/data structures.

    Course grade breakdown:

    1. Two tests (30%)
    2. Cumulative final (30%)
    3. Extended homework in SQL (5%)
    4. Two team projects (team size=3, 35%)

    Academic integrity policy: I will follow the CSE department academic integrity policy.

    Make-up policy: The request should be made sufficiently in advance of the test, for valid reasons. The make-up should be scheduled before the next class.

    Late submission policy: The submissions are due at midnight on the due date. No late submissions are accepted. Exceptions will be made only for medical reasons.

    Grading: Questions about the grading have to be raised with the TA within a week after the graded assignment was returned.

    Attendance and punctuality: Attendance is mandatory. No late arrivals in class.

    Disability Services: Students with physical or learning disabilities should register with the university Disability Services Office (http://www.student-affairs.buffalo.edu/ods/) in order to receive accommodation.

    Course objectives: Introduce the students to fundamental data management issues: database design, query languages, database file organization, query processing and optimization, transaction processing.

    Course summary:

    1. Introduction and overview (0.5 weeks).
    2. Relational data model (4 weeks): data definition, integrity constraints, relational algebra, SQL.
    3. Indexing (2 weeks): disk storage, buffering, heap files, sorted files, hashed files, B+ trees, sparse and dense indexes.
    4. Query processing and optimization (2.5 weeks): cost analysis, selection, join, plan enumeration and selection, top-K queries.
    5. Database design (1 week): functional dependencies, normal forms.
    6. Transaction processing (1.5 weeks): concurrency control, recovery, distribution.
    7. Selected issues in contemporary DBMS (3.5 weeks):