Instruction

Name Cr Method of study Time Location Organiser
Transaction Management and Query Optimization 5 Cr Lecture Course 12.3.2019 - 2.5.2019
Transaction Management and Query Optimization 5 Cr Course exam 6.5.2019 - 6.5.2019
Transaction Management and Query Optimization (HT/U) 5 Cr General Examination 14.6.2019 - 14.6.2019

Target group

Master's Programme in Computer Science is responsible for the course

CSM14000 - Software Systems study track

The course is available to students from other degree programmes (this course can be available with Master of Data Science)

Prerequisites

Knowledge of relational databases. We will assume that the students already completed the course "Introduction to databases".

Learning outcomes

* Know ACID properties for a transaction. Familiar with the transaction log.
* Know conflict serializability and view serializability. Use the precedence graph to test conflict serializability.
* Know two-Phase Locking on schedules. Understand deadlock and deadlock prevention mechanism. Know the timestamp protocol and the multi-version timestamp protocol.
* Know write-ahead logging and shadow updates. Know the recovery solution based on deferred update and immediate update. Can use the ARIES recovery algorithm to construct the transaction table and dirty page table.
* Know sparse indexes and dense indexes. Can construct the B-tree and B+-tree indexes. Can construct the extendible hashing and linear hashing.
* Can transform a SQL query to a query plan. Can estimate the costs of different query plan with indexes and join algorithms.
* Know XML twig pattern queries. Can use different encoding schemes for XML data. Can understand XML twig query processing algorithms.

Timing

The course is not offered every year. It is offered every two years, alternatively in Spring.

Contents

Transactions and locking. Optimistic concurrency control. Transaction recovery. Degrees of consistency. Multi-model databases. Query plan and query optimization. XML twig pattern processing.

Activities and teaching methods in support of learning

The following is the current plan for Spring 2019: teaching methods evolve from year to year.
The course consists of lectures, exercises, study groups and an exam.
Lectures: Attending lectures is not obligatory but it is useful. Lecture notes covering key facts will be posted on this page, but there will be additional examples and explanations during the lectures. We will also explain the answers to questions in self-assessment forms in lectures.
Exercises: The students should solve the problems at home and be prepared to present their solutions at the exercise session. The students are required to solve ALL problems. Give yourself as much time as possible to complete each assignment so you can complete it to the best of your ability and you don't have to rush or worry about incurring late submission penalties.
Study groups: The students read some material in advance and then discuss the material in groups during the meeting. Attending the study group meetings is mandatory. But if you have a reason (such as an emergency, personal illness or others) for being absent, please let the lecturer know before the meeting.
Course exam: The exam covers the lectures (including self-assessment questions) and the exercises. No notes or other material is allowed in the exam.

Study materials

Fundamentals of database systems (Chapter 16, 17, 19, 20, 21, 22)

Elmasri Ramez, Navathe Shamkant B.
2017 Seventh edition, Global edition.

An introduction to database systems (Chapter 15, 16, 18)

Author: Date, C. J
Pearson Addison-Wesley 2004. Eight edition
Transaction Processing -- Management of the Logical Database and its Underlying Physical Structure
Authors: Seppo Sippu and Eljas Soisalon-Soininen

Assessment practices and criteria

The grading is based on the sum of the points from the exercises, study group and hands-on programming tasks (max. 50 points) and the exam (max. 50 points). 51 points are required to pass and give the lowest grade 1, and 90 points or more gives the highest grade 5.
Renewal Exam:
The renewal exam requires participation to the course and can be taken only if the students submit the answers for all exercises and hands-on tasks.
Separate exams:

The separate exams do not require course participation. But the students are required to submit the answers for all exercises and hands-on tasks before the examination.

Recommended optional studies

Introduction to Big Data Management

Completion methods

There will be lectures, study groups, exercises, and tutorials.

A separate exam can be taken with an independent study, but the submission of exercise and hands-on task answers is required.