Topic 5

Lesson 5. Modern Cloud Databases and Storage Organization. Relational Databases, Tables, Keys. Introduction to Query Language SQL (SELECT, WHERE, JOIN) and Cloud Platforms (Supabase / PostgreSQL)

Objective: formation of students with a complex of fundamental theoretical knowledge, engineering thinking and practical skills in the field of designing relational data models, writing analytical queries in the declarative SQL language and administering modern cloud storage systems (Managed PostgreSQL / Supabase). The student must master the architectural evolution of storage systems from local files to Cloud-Native DBMS, the mathematical foundations of Edgar Codd's relational model (relations, tuples, attributes, domains, 1NF rules), the transactional requirements of the ACID model, and also understand the conceptual differences between row-based transactional OLTP systems and columnar analytical OLAP storages. During the training, special attention is paid to mastering the key system of relational databases - designing primary (PK) and foreign (FK) keys, implementing the principle of referential integrity with cascading actions (ON DELETE CASCADE/RESTRICT), modeling relationships (1:1, 1:N, N:M through intermediate tables), as well as mastering the syntactic and logical order of query execution in the DBMS. Students should learn how to practically compose optimized SQL queries for data selection and projection (SELECT, DISTINCT, calculated fields), apply multi-criteria filtering (WHERE with operators AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL) and perform horizontal joining of sets using JOIN operators (INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN). An important applied and comparative result is the formation of the ability to evaluate the architectural advantages of managed PostgreSQL (PITR-recovery, Read-replicas, support for hybrid JSONB types and PostGIS/pgvector extensions) and deploy cloud backends on the new generation Supabase platform (using Auto-REST API, Realtime mechanisms via WebSockets, and Row-Level Security (RLS) settings) to create scalable, secure, and high-performance analytical repositories.