Data Handling III: Databases

MSc-Course offered at the University of St. Gallen (7,330,1.00)

Course Content

The course will provide a basic overview of the functionality, architecture, and implementation of database systems as a foundation for computer-based information systems. A database system is a general-purpose platform to manage and process data. Information is represented, stored, and managed according to a data model, while it is queried (retrieved) and manipulated using a special-purpose language. The course is organized around three main parts, which follow the steps that are typically required to design, use and maintain a database. The first part is dedicated to data modelling. Conceptual database design is introduced based on the Entity-Relationship (ER) model, whereas logical database design is studied in the context of the relational model of data and its normal forms. The second part is dedicated to database languages. After presenting the relational algebra as a formal foundation, the course will provide a thorough introduction into SQL, which is currently the most widely-used and most important database language. The third and final part of the course is dedicated to the benefits that arise for database users due to the platform-based approach. In this part, the course will give a brief overview of the internal structures and functioning of a database system, such as currency control, indexing, and query processing.

Apart from the theoretical background, the course will feature practical exercises that will enable students to apply the new knowledge by going through the process of setting up and querying their own database. These practical assignments will be based on the open-source database management system PostgreSQL, which students will install on their own computers as it is available for many operating systems and platforms.

Schedule

  1. Thursday, November 8, 2018: 08:15-12:00, 13:00-18:00
    Room 23-103
  2. Friday, November 9, 2018: 08:15-12:00, 13:00-18:00
    Room 01-U102
  3. Saturday, November 10, 2018: 8:15-12:00, 13:00-16:00
    Room 01-U102

Outline

Part I: Database Design

  • Introduction and Overview
  • Data Modeling with the ER Model
  • Relational Database Design Theory

Part II: Database Languages

  • SQL
  • Relational Algebra

Part III: Database Systems

  • Transactions, Concurrency Control, and Recovery
  • NoSQL Data Stores

Materials

Lecture Slides

The complete slide decks for this course can be downloaded below. 

  • Introduction
  • Part I
  • Part II (Demo Queries)
  • Part III

Please note that slides will be made available as the course progresses. Also note that the slides cover some topics that are not covered in the course, but are relevant for the decentralized exam.

Databases

The following demo databases will be used for the practical parts of the course as well as for exercises. The are designed to work with PostgreSQL. If you want to work with them on your personal computer, we recommend that you install pgAdmin 4 as a user interface.

  • Corp
  • HomeworkDB
  • LEGO
  • Monarchs

Exercises

  1. ER Modeling and Relational Design (Exercise, Solution)
  2. SQL Queries (Exercise, Database Schema, Solution, SQL Queries)
  3. Transactions and Locking (Exercise, Solution)

Decentralized Exam

The exam paper is now available! Please complete it on your own and submit via E-Mail for grading no later than December 21, 18:00. If you have questions that related to the understanding of the assignments, please do not hesitate to contact the instructor.