Data Management and Databases (MSc)

Course topics:

This module provides an introduction to data management and database systems, focusing on the design, implementation, and optimization of modern databases. It covers both relational and non-relational databases, emphasizing their roles in handling large-scale and complex data. The aim is to equip students with the skills to address sophisticated data management challenges and to prepare them for the more advanced data engineering modules.

Students will learn the principles of database design, SQL querying, and data management techniques. The module also covers NoSQL databases, providing a comparison with traditional SQL databases.

Content: Database design and architecture; Performance tuning and optimization strategies; Complex querying techniques and advanced SQL features; Exploration of NoSQL databases and their applications; Data storage solutions for big data environments; Considerations for data security, governance, and compliance.

Lecture slides

[ Introduction | Project Introduction | Database Design | Relational Model | SQL | Applicaiton Development | Frontend Development | Relational Algebra | Query Processing | Physical Design (see also this post) | Normal Forms | Transactions | Security | NoSQL | XQuery | SPARQL | Arrays | Big Data | OLAP | Wrapup ]

...plus the talk given by Keith Hare, Convenor of the ISO SQL Working group.

Project

Team up in groups of 2-3 and develop the nucleus of a database-backed, Web-based information system - on a topic you choose! See the project intro slides for more information.

Exams

first time, so none available yet

Literature

Course reference:

• H. Garcia-Molina, J.D. Ullman, J.D. Widom: Database Systems: The Complete Book. 2nd edition, Pearson, 2008 Fundament providing all the basics
• M. Stonebraker, J.M. Hellerstein, J. Hamilton: Readings in Database Systems, 5th edition, Morgan Kaufmann Publishers, 2018 Interesting facets and views
• Elvis C. Foster, Shripad V. Godbole: Database Systems. O’Reilly, 2014 Inspection of concrete DBMSs
• P. Trivett: Python Programming and SQL. ISBN-13: 979-8868124884M.
• Grinberg: Flask Web Development: Developing Web Applications with Python. O’Reilly, 2018 Two alternatives providing important how-to for the project the Web – manifold tutorials, find your favourite

• Books on MariaDB (the DBMS used in the project)
• Databases: Database Design for Mere Mortals 2020, Database Design and Relational Theory: Normal Forms and All That Jazz 2020
• SQL: SQL Pocket Guide: A Guide to SQL Usage 2021, SQL QuickStart Guide 2019
• Application programming:
  • PHP: PHP and MySQL Web Development
  • python: MySQL for Python, Flask-SQLAlchemy

Extra links

For the Unified Modeling Language (UML) there is many good tutorials available on the Web, for example Borland's tutorial, Sparx Systems' tutorial by with a particular part on Database Modeling in UML. There is also a list of UML tutorials maintained by uml.org itself.
Meantime there is also a host of UML tools available - such as Rational Rose and Enterprise Architect (EA) by Sparx Systems, or Omondo's free UML extension to Eclipse - collected in lists like this one.

Some LAMP links: The LAMP page by O'Reilly. LAMP, as you know, stands for..., well, at first: Linux. Compare this and that site! Sometimes helpful: TLPD - The Linux Documentation Project. Then, about the rest: Apache and mySQL and PHP.
Here some food for those who want to practise their German: Linux für alle and LAMP at eFactory.

For PHP, there is some discussion of Apache configuration aspects, also touching security issues. This input sanitizing script might save your life one day.

Tuning of databases is something only marginally addressed in lecture, albeit a large (and fascinating) area, and of immense practical importance. Just two tools here: Practical query analysis produces HTML reports on slowest queries, most frequent queries, queries by type (select/insert/update/delete), and all that sort of thing for PostgreSQL and MySQL database logs. Using a template postgresql.conf and test data, pgAutotune will run through a process of testing different settings on your PostgreSQL server and determine which settings have the best performance. For additional material, see e.g. Software Engineering for Internat Applications.

Given the large variety of Web app implementation languages available it is no surprise that there is hot discussions on which platform is the best one. One line of arguments, which I share to a large extent (but not completely) is presented by two webcasts of a NASA/JPL guy (webcast1, webcast2).