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:

• Zhouhan Yang (zhouhany@buffalo.edu, office hours R 12:00-2:00, Davis 302)
• Suxin Guo (suxinguo@buffalo.edu, office hours: M 1:00-3:00pm, R 3:30-5:30, Davis 302)

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

Tests:

1. Test 1: relational data model and query languages (02/23/12)
   • review session: 02/21/12
   • textbook: chapter 2, 6.1-6.5, 7.1-7.2, 8.1, 10.2
   • textbook table of contents
2. Test 2: indexing and query evaluation (04/05/12)
   • review session: 04/03/12
   • textbook: 13.1-13.3, 13.5, 13.7.1, 14.1-14.1.7, 14.2-14.3.4, 14.7.1, 15.1-15.6, 16.1-16.2, 16.4-16.7
   • practice problems
3. Comprehensive final (05/03/12)
   • textbook (additional readings): 3.1-3.6, 17.1, 17.4, 18.1, 18.3, 19.1.1-19.1.4, 19.2.1-19.2.3

Homeworks:

Projects:

Other resources:

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):
   • XML databases
   • OLAP
   • database security
   • parallel databases
   • stream databases
   • NoSQL databases
   • multi-dimensional databases, R-trees