CSE 562: Database Systems (Spring 2011)

Instructor: Dr. Jan Chomicki (chomicki@buffalo.edu, office hours: W R 1:00-2:30, Bell 213)

Teaching Assistants: Robert Surowka (robertlu@buffalo.edu, office hours: T 11:00-12:00, F 2:00-3:00, Bell 232)

Registration number: 026840

Credit hours: 3.0

Time: MWF 3:00-3:50

Location: 205 NSC

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

Blog: http://ubdbs.wordpress.com/

Lecture slides:

  1. Introduction to DBMS
  2. Relational databases
  3. Indexing
  4. Query evaluation and optimization
  5. Relational database design
  6. Conceptual database design
  7. Transactions

Additional slides (courtesy of Demian Lessa):

  1. Introduction to Oracle

Tests:

  1. Test 1: relational data model and query languages (02/21/11)
  2. Test 2: indexing and query evaluation (03/25/11)
  3. Test 3: database design (04/22/11)

Homeworks:

  • Homework 1 (solutions) (due 02/17/11)

    • Projects:

  • Project 1 (due March 30, 2011)
  • Project 2 (due May 2, 2011)

    • Other resources:

  • Oracle (CSE)
  • Oracle help (login, password)
  • 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. Three tests (30%)
    2. Comprehensive final (30%)
    3. Extended homework in SQL (5%)
    4. Two team projects (team size=3), involving the implementation of SQL query engine components using Java (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 weeks): cost analysis, selection, join, plan enumeration and selection.
    5. Database design (1.5 weeks): Entity-Relationship data model, functional dependencies, normal forms.
    6. Selected issues in contemporary DBMS (3 weeks):
    7. Transaction processing (1.5 weeks): concurrency control, recovery, distribution.