The rational data model and the SQL query language. Conceptual modeling: entity/relationships, normal forms. XML, XPath, and XQuery. Transactions: recovery and concurrency control. Implementation of a database system. A medium sized project using a rational database backend. Prerequisite: CSE 332; CSE 344.
Databases are at the heart of modern commercial application development. Their use extends beyond this to many applications and environments where large amounts of data must be stored for efficient update and retrieval. The purpose of this course is to provide an introduction to the design and use of database systems, as well as an appreciation of the key issues in building such systems. We begin by covering the relational model and the SQL language. We then study methods for database design, covering the entity relationship model. Next, we discuss XML as a data model, and present languages for querying it. We see how XML is used for sharing data among different applications in a distributed environment. We then inspect the architecture of a database system, and discuss efficient storage of data, execution of queries and query optimization. Finally, we touch on some advanced topics in database systems.
Student learning goals
Understand conceptual modeling: how to design a good database schema from scratch.
Becomes masters of SQL: be able to write advanced SQL queries, use them in applications, and understand the performance tradeoffs.
Understand the basics of transaction processing: database recovery and concurrency control. Be able to choose between different SQL isolation levels, and understand the correctness/performance tradeoffs.
Understand data storage basics: organizing data on disk, indexes. Be able to do a simple physical database tuning (index selection, materialized views).
Scalable operators on data: join algorithms, duplicate elimination, aggregates. Understand and reason about query plans.
Manage access control to a database system.
General method of instruction
There are lectures three days per week, and they cover the entire material in this class (i.e. the lecture notes are self contained). There will be one section per week (new this year !), where we will discuss system issues (SQL Serer, postgres) and will practice the theoretical concepts covered in class (e.g. normal forms).
Algorithms and data structures; the big O notation; basics of operating systems (tasks, processes, concurrency, files, cache).
Class assignments and grading
there are two kinds of assignments: (1) homeworks that do not require programming, (2) four small programming projects.
Homeworks, projects, midterm, final.