Course detail

Databases for Process Control

FSI-VDCAcad. year: 2027/2028

This course presents technical and technological solutions for data storage and management in industrial applications. It covers the principles of design, implementation, and use of modern database systems, with an emphasis on their practical application in industrial environments. It introduces students to data requirements analysis, conceptual modeling, the creation of ER diagrams, and the design of relational databases. Emphasis is placed on the SQL language, creating advanced queries, working with transactions, and database programmability using PL/pgSQL. It also covers modern database technologies, particularly time-series databases (TimescaleDB, InfluxDB), document databases (MongoDB), cloud database services (Firebase Firestore), key-value databases (Redis/Valkey), full-text search systems (Elasticsearch), and graph databases (Neo4j). The course also includes a comparison of various database technologies, determining their appropriate use, and designing software solutions that combine multiple types of databases based on the nature of the data and the requirements of industrial applications (polyglot persistence).

Language of instruction

Czech

Number of ECTS credits

6

Assignment to study programme types

Bachelor's

Mode of study

Not applicable.

Learning outcomes of the course unit

Upon completion of the course, students will be able to analyze the requirements for storing and processing industrial data, design data models, and, above all, implement database solutions using both relational and non-relational database technologies. They will learn to work hands-on with various database systems, create database queries, and select appropriate technologies based on the nature of the data and application requirements, including combinations of these technologies.

Entry knowledge

To study database systems, basic knowledge of algorithm development is sufficient.

Rules for evaluation and completion of the course

A prerequisite for receiving credit is the implementation of two non-trivial database applications using the appropriate tools (in the form of group projects).

The exam is administered in written or electronic form. In it, students demonstrate i.e. their knowledge of data structure design, relational algebra, the SQL language, and programming.

Since both the seminars and lectures are mandatory, the instructor will regularly check attendance. In the event of an excused absence, the student will receive exercises to complete independently so that they can make up for the missed.

Aims

The aim of the course is to introduce students to basic database technologies for effective design and management of applications with large amounts of data in the Internet environment.
The course is designed to introduce students to the theoretical aspects of database application design and implementation in an Internet environment, including the programming techniques involved. They will be able to create an application for efficient management of large amounts of data and also learn how to use cloud services.

Study aids

STEPHENS, R., Beginning Database Design Solutions: Understanding and Implementing Database Design Concepts for the Cloud and Beyond, 2023, 978-1394155729

DATE, C.J., Database Design and Relational Theory: Normal Forms and All That Jazz, 2019, 978-1484255391

KLEPPMANN, M. Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems, 2017, 978-1449373320

HERNANDEZ, Michael, J., Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, 2003, 978-020175284

CARLSON, Josiah, L., Redis in Action, 2013, 978-1617290855

Prerequisites and corequisites

Not applicable.

Basic literature

Connoly, T.M. and Begg, C.E.: Database Systems : A Practical Approach to Design, Implementation and Management. Pearson Education, 2004 (4th edition).
Date, C.J.: An Introduction to Database Systems. Addison Wesley, New York, 2003 (8th edition).
Fortier, P.J.: Database Systems Handbook. McGraw-Hill, 1997.
Holubová, I, Kosek, J., Minařík, K., Novák, D.: Big Data a NoSQL databáze. Grada, Praha, 2015.
Lacko, L.: SQL. Hotová řešení pro SQL Server, Oracle a MySQL. Computer Press, Brno, 2003.
Pratt, J.P.: A Guide to SQL. Course Technology, 2003 (6th edition).
Redmond, E., Wilson, J. R.: Seven Databases in Seven Weeks. A Guide to Modern Databases and the NoSQL. Movement-Pragmatic Bookshelf, 2012.
Stanoevska-Slabeva, K., Wozniak, T., Ristol, S. (eds.): Grid and Cloud Computing. A Business Perspective on Technology and Applications. Springer-Verlag, Berlin, 2010.
Ullman, L.: PHP and MySQL for Dynamic Web Sites. Pearson Education, 2003 (český překlad: PHP a MySQL: Názorný průvodce tvorbou dynamických www stránek. Computer Press, Brno, 2004).
Velte, T. A., Velte, T. J., Elsenpeter, R: Cloud Computing. A Practical Approach. McGraw Hill, New York, 2010.
Williams, H.E. and Lane, D.: Web Database Applications with PHP and MySQL. O’Reilly, 2002 (český překlad: Programujeme webové aplikace pomocí PHP a MySQL. Computer Press, Praha, 2002).

Recommended reading

Date, C.J.: An Introduction to Database Systems. Addison Wesley, New York, 2003 (8th edition).
Elmasri, R., Navathe, S.B.: Fundamentals of Database Systems. Addison Wesley, 4th edition, 2003.

Classification of course in study plans

  • Programme B-MAI-P Bachelor's 3 year of study, winter semester, compulsory-optional

  • Programme B-STR-P Bachelor's

    specialization AIŘ , 3 year of study, winter semester, compulsory, profile core courses

  • Programme N-MET-P Master's 2 year of study, winter semester, elective

Type of course unit

 

Lecture

26 hours, compulsory

Teacher / Lecturer

Syllabus

  1. Definition of the subject, Data Requirements, Conceptual Modeling, and Decision-Making Framework
  2. Data Modeling – Entity-Relationship (ER) Diagrams
  3. ER Diagrams – Continued, SQL Syntax, and Physical Data Models
  4. Database Querying I – SELECT, JOIN, and Aggregation
  5. Database Querying II – Subqueries, Views, Transactions, and Triggers
  6. PL/pgSQL – Database Programmability
  7. NoSQL Databases – Introduction and Time-Series Data (TimescaleDB, InfluxDB)
  8. MongoDB – Document Databases
  9. Firebase – Firestore
  10. Redis/Valkey – Key-Value Databases, Caching, and Queues
  11. Elasticsearch – Full-Text Search
  12. Graph Databases – Neo4j and Cypher
  13. Polyglot Persistence and Decision-Making Framework – Course Summary

Computer-assisted exercise

26 hours, compulsory

Teacher / Lecturer

Syllabus

  1. Introduction to the Environment, PostgreSQL (Docker/Server), Project 1 Assignment, and Team Formation
  2. Design of an ER Diagram for the Selected Project 1 Topic, Peer Review
  3. CREATE TABLE, Foreign Keys, and CHECK Constraints – Implementation of the ER Diagram in PostgreSQL
  4. Practicing SELECT and JOIN Queries Using the MetalWorks Database, Querying Project Data
  5. Advanced SQL Queries, Views, and Transactions; SQL Test
  6. Triggers and Stored Procedures – Extending the Project
  7. Hands-on Experience with TimescaleDB (CSV Import, time_bucket, Continuous Aggregates); Project 2 Assignment
  8. Project 1 Defense
  9. Firebase/Firestore – Console, Collections, Security Rules, and Querying via the Web SDK
  10. Redis CLI – Data Structures, TTL, Pub/Sub; Integration with FastAPI and Caching
  11. Elasticsearch and Kibana – Indexing, Mapping, Query DSL, and Full-Text Search
  12. Project 2 Defense
  13. Final Test and Course Summary