Database Systems

Course Description

Entity-relationship model, relational algebra, SQL query language, stored procedure SQL, buffer cache management, storage structures, tree and hash based indexing, external sorting, access methods, query processing, update processing (transactions and recovery). 3 credit hours, elective for major and minor, prerequisite is CS 151.

Objectives

• Understanding of the concepts underlying the design and implementation of modern relational database management systems. These concepts include
  • buffer manager policies
  • disk page and file record formats
  • file organizations
  • tree and hash-based indexing
  • external sorting
  • evaluation of relational operators
  • query optimization
  • the concept of a transaction
  • concurrency control
  • crash recovery
    

• Skill in using relational algebra, interactive SQL and stored procedure SQL (Structured Query Language: the standard interface for relational database management systems).

• Skill in developing client-server database applications using Java and JDBC.

Text

R. Ramakrishnan and J. Gehrke, Database Management Systems, Third Edition, McGraw-Hill, 2003.

Grading Procedure

Grading procedures and factors influencing course grade are left to the discretion of individual instructors, subject to general university policy.

Attendance Policy

Attendance policy is left to the discretion of individual instructors, subject to general university policy.

• Overview and Relational Model (6 days)
  Basic terminology for a relational database management system. The entity-relationship model. Converting an entity-relationship model to a relational model. Views. Reading: Chapter 1-3 of text.
  
  
• SQL Query Language (9 days)
  Interactive SQL queries: select statements, nested queries, aggregate operators, null values, outer joins, insert, update, and delete. Stored procedure SQL: declaring variables, exceptions, cursors. An introduction to transactions in stored procedures. Creation of a database using a commercial database system and developing both interactive and stored procedure SQL for that database. Database programming for client-server applications using PHP or Java and JDBC (Java Database Connectivity). Reading: Chapters 5-7, handouts, and online links.
  
  
• Data Storage and Indexing (6 days)
  Three file organizations (heap, sorted, hashed). Properties of indexes: clustered versus unclustered, dense versus sparse, primary versus secondary. Tree-structured indexing (B+-trees). Reading: Chapters 8, 10.
  
  
• Relational Algebra (4 days)
  Relational algebra: selections, projections, joins, division. Reading: Chapter 4.
  
  
• Query processing (8 days)
  External merge-sort, access paths, implementation of selection and projections, join method (nested loops, sort-merge, hash), scans, and multiple index accesses. Query optimization, an example optimizer. Reading: Chapters 12-14.
  
  
• Transaction management (as time permits)
  Transactions, serializability, two-phase locking, concurrency control without locking, crash recovery, logs, checkpoints, media recovery. Reading: Chapter 16-18.