Information Systems for Engineers

Overview

This course provides the basics of information systems from the perspective of the user. The main focus is on relational databases, including tabular data, the relational algebra, the SQL query language, schema design, normal forms, physical architecture, indices. The course also covers support for data cubes on top of relational databases.

Latest Information

Exam Environment

The exam will be computer-based. Students will have at their disposal an exam environment which features the following software:

Anaconda3 2020.07 with Jupyter notebooks and
- ipython-sql (SQL magic to easily type SQL in Jupyter notebooks)
- psycopg2 (PostgreSQL driver for Jupyter)
PostgreSQL 12.4

This is the same setup as the one used in the exercises. If you have not already done so, feel free to install these locally, and try the environment out, in order to get more acquainted with it prior to the exam. Instructions are in the course material.

Previous paper exams can be found here and here.

The exam takes place in the Moodle environment that you are already familiar with from the exercise quizzes. Most questions will be in the form of multiple choice questions, filling textboxes with short answers, drag and drop, etc.

Datasets will be prepopulated in PostgreSQL. The exam environment will also contain a Jupyter notebook (some sort of cheatsheet) to get you started with them. An obfuscated version of this cheatsheet is available here.The exam may involve questions asking you to query these provided datasets. As a consequence, we recommend preparing yourself well to write SQL. For such questions, some final result is asked and used for grading, and you are also asked to provide the query that you used.

Graded Exercises

23.10.2020 16:00 - 30.10.2020 16:00
06.11.2020 16:00 - 13.11.2020 16:00
20.11.2020 16:00 - 27.11.2020 16:00

For the additional points you need to pass 2 out of 3 graded assignments.

First Graded Exercise

Graded exercise session has a TIME LIMIT - 120 minutes!

You have only one attempt to start the quiz, as soon as you press Start - you can't suspend it and resume later. Please, allocate a two hour slot without breaks for the graded assignment. You can start doing the assignment any time within the time range, written above (maybe just not an hour before the deadline is).

For the first graded assignment you need to be fluent in Relational algebra concepts (Lecture 4), mathematical definitions, know how to apply the following operations to tables: projection, selection, union, join, group, aggregate, cartesian product and etc.

Second Graded Exercise

Graded exercise session has a TIME LIMIT - 180 minutes!

The topic for the second graded exercise is Queries with SQL II.

Third Graded Exercise

Graded exercise session has a TIME LIMIT - 120 minutes!

The topic for the second graded exercise is Database Design Theory II.

Lecture and Exercise Times

People

Lecturer

Ghislain Fourny

Teaching Assistants

Course Material

Lecture

Exercises

Objective

After visiting this course, students should be capable to:

Explain, in the big picture, how a relational database works and what it can do in their own words.
Explain the relational data model (tables, rows, attributes, primary keys, foreign keys), formally and informally, including the relational algebra operators (select, project, rename, all kinds of joins, division, cartesian product, union, intersection, etc).
Perform non-trivial reading SQL queries on existing relational databases, as well as insert new data, update and delete existing data.
Design a new relational schema to store data in accordance to the real world's constraints, such as relationship cardinality.
Adapt and improve an existing schema to make it more robust against anomalies, thanks to a very good theoretical knowledge of normal forms.
Understand how indices work (hash indices, B-trees), how they are implemented, and how to use them to make queries faster.
Access an existing relational database from a host language such as Java, using bridges such as JDBC.
Explain data independence.
Explain how a relational database is physically implemented.
Know and deal with the natural syntax for relational data, CSV.
Explain the data cube model including slicing and dicing.
Store data cubes in a relational database.
Map cube queries to SQL.
Slice and dice cubes in a UI.

Content

Using a relational database

Introduction (historical overview, data independence, data shapes)
The relational model (data models, tables, relational algebra, CSV syntax)
The query language SQL (DML 1 of 2, SQL shell, counterpart of selection, projection, grouping, ordering, renaming)
Schema definitions (DDL, data types, SQL)
Updates (DML 2 of 2, insertion, deletion, updates, SQL)

Taking a relational database to the next level

Best practices and normal forms (update/insert/delete anomalies, first, second, third, BC, fourth)
Physical architecture of a relational database (storage levels, tuple storage)
Indices and optimization (ISAM, B-tree, B+-tree, hash)
Communicating with a SQL database from a host language (Java, JDBC)

Analytics on top of a relational database

Analytics, data warehousing, OLAP vs. OLTP, the data cube model (dimensions, algebra)
Storing and querying data cubes in a relational database (star schema, snowflake schema)
Data cube optimization (indices, bitmaps)

Outlook

Limits of tables and cubes (decision points for when and when not to use them vs. trees, graphs)
Limits of scaling up a single machine and transition to Big Data (introduction to data denormalization on simple cases, "hacking" a relational database onto several machines and issues)

Literature

Lecture material (slides)
Book: "Database Systems: The Complete Book", H. Garcia-Molina, J.D. Ullman, J. Widom (It is not required to buy the book, as the library has it)

Prerequisites / Notice

For non-CS/DS students only, BSc and MSc
Elementary knowledge of set theory and logics
Knowledge as well as basic experience with a programming language such as Pascal, C, C++, Java, Haskell, Python