Database Systems

DB2

Lecture  INFO-4141
Readers
Torsten Grust • Denis Hirn • Tim Fischer
Lecture
Mon 10:15-11:45, Sand 1, A301
Lecture
Tue 10:15-11:45, Sand 1, A301
Tutorial
Wed 14:15-15:45, Sand 1, A301
Exam
Mon, July 22 2024, 14:00-16:00, N7 (Morgenstelle)

Based on the lecture DB1, this lecture takes a “look under the hood” and examines the internals of relational database systems (RDBMS). We will try to find answers to questions such as:

  • How can data transport between secondary and main memory be organized without the DBMS having to wait for slow disk accesses?
  • How to structure, sort, and manipulate data of 20 GB size when the available main memory can only hold a fraction of it?
  • How to navigate in data sets of this size? How to translate queries (SQL) into efficient “programs” that can search/filter/transform data of this size?

We will take the architecture of a DBMS as a guide and work our way through from the physical level (file organization, etc.), through index management to query evaluation. This path is paved with interesting algorithms and data structures. Topics: secondary memory access, record and page layout, index structures (B-trees, hashes), sorting on secondary memory, query evaluation, (estimated) query costs, plan generation and optimization, transactions (ACID), logging.

Whenever possible, we will focus our X-ray beam on concrete systems such as PostgreSQL or MonetDB 5, in order to practically experience/understand the internals of DBMS.

Signup and Registration

You do not need to register for the lecture in advance. Just turn up to the first lecture. You will be given all the information you need.

Organization of the exercise and Discord

In the context of the lecture and exercise, we will use our Discord Server for organization, i.e. for official announcements, questions and discussions. Registration and regular attendance on Discord is not optional, but required for participation in the event.

Literatur

  • Winand, Markus: SQL Performance Explained

    Markus Winand, 2012

    ISBN 978-3950307818

    Kondensiertes Wissen eines Praktikers zum SQL-Tuning mit Hilfe von Indizes.

    Website zum Buch

Semesterwochenstunden / Credits / Audience

  • 4 SWS lecture + 2 SWS exercise
  • 9 ECTS
  • For students in computer science bachelor’s and master’s programs

Prerequisites

Course DB1 or comparable knowledge in SQL.

Questions?

For further questions please contact the teaching assistant Denis Hirn.