5 ECTS credits
125 h study time
Offer 1 with catalog number 4016908FNR for all students in the 1st semester at a (F) Master - specialised level.
With respect to query processing, we study the whole workflow of how a typical relational database management system optimizes and executes SQL queries. This entails an in-depth study of (1) translating the SQL query to a logical query plan; (2) optimizing the logical query plan; (3) how each logical operator can be algorithmically implemented on the physical (disk) level, and how secondary-memory index structures can be used to speed up these algorithms; and (4) the translation of the logical query plan to a physical query plan using cost-based plan estimation. By having an in-depth understanding of the query-optimization-and-execution pipeline, one becomes more proficient in administring DBMSs, and hand-optimizing SQL queries for fast execution.
Topics studied in transaction processing include logging, serializability, concurrency control, and their combination.
The teaching method is a cocktail of lectures, demonstrations, exercise sessions, and project work
In contrast to a typical introductory course in database systems where one learns to design and query relational databases, the goal of this course is to get a fundamental insight into the implementation aspects of database systems. In particular, we take a look under the hood of relational database management systems, with a focus on query and transaction processing.
Upon successful completion of this course, the student is expected to master the following competences :
1. Translating a given SQL expression into the Relational Algebra
2. Improving a relational algebra expression by, where possible, removing redundant joins in select-project-join subexpressions
3. Improving a relational algebra expression by, where possible, (a) replacing cartesian products by joins ; and (b) pushing selections and projections
4. Describing and being able to implement traditional secondary-memory index structures (BTrees, Hashing)
5. Being able to describe and demonstrate the shortcomings of traditional index structures with respect to multi-dimensional search keys. In addition, being able to describe and implement the studied multidimensional indexes
6. Describing the most important implementation algorithms (one-pass, sorting, hashing, index) for each of the relational algebra operators, as well as judging the cost of each operator, and knowing their limitations of applicability
7. Given a logical query plan and given base statistics about the size and distributions of the database relations, constructing a heuristically optimal physical query plan, by estimating the sizes of the intermediate results and correspondingly comparing the possible implementations. When joins can be reordered, choosing the order with the least cost.
8. Describing, explaining, and implementing the most common mechanisms for recovering from crashes.
9. Describing, explaining, and implementing the most common mechanisms for controlling concurrency during transaction managment.
The final grade is composed based on the following categories:
Written Exam determines 70% of the final mark.
SELF Practical Assignment determines 30% of the final mark.
Within the Written Exam category, the following assignments need to be completed:
Within the SELF Practical Assignment category, the following assignments need to be completed:
The final mark is composed of the mark obtained for the project work and the written exam.
This offer is part of the following study plans:
Master in Applied Sciences and Engineering: Computer Science: Artificial Intelligence (only offered in Dutch)
Master in Applied Sciences and Engineering: Computer Science: Multimedia (only offered in Dutch)
Master in Applied Sciences and Engineering: Computer Science: Software Languages and Software Engineering (only offered in Dutch)
Master in Applied Sciences and Engineering: Computer Science: Data Management and Analytics (only offered in Dutch)
Master of Applied Sciences and Engineering: Computer Science: Artificial Intelligence
Master of Applied Sciences and Engineering: Computer Science: Multimedia
Master of Applied Sciences and Engineering: Computer Science: Software Languages and Software Engineering
Master of Applied Sciences and Engineering: Computer Science: Data Management and Analytics