Lectures Technopark. Databases (Spring 2017)

We invite all those who thirst for knowledge to get acquainted with the new lectures of Technopark dedicated to databases. The course is led by Artem Nawrotsky, the lead programmer at the Allods Team.

List of lectures:

1. Introduction
2. Modification and selection of data
3. Data sampling (continued)
4. Transactions Triggers and stored procedures
5. Indices and Performance
6. Query optimization. Data structure optimization
7. Replication, full-text search, JSON
8. Data integrity

The goal of the course is to provide knowledge on:

• Concepts and principles of the relational data model.
• Database design methods.
• Performance Profiling Approaches.
• The basics of the architecture of modern DBMS.
• The basics and limits of applicability of the relational model.
• The main types of non-relational databases, as well as their areas of applicability when building data warehouses.

Instill skills:

• Designing conceptual and logical schemes for high-load databases.
• Building queries on the selection and modification of data to relational DBMS.
• Query optimization and relational database structure to improve performance.
• Execution of queries to non-relational document-oriented DBMS.
• Development of application programs that interact with the database.

Lecture 1. Introduction

From the first lecture you will learn why database systems are needed, what a brief history of database development is, what relational databases and NoSQL are. Get acquainted with the relational data model and basic operations within the database. Also, during the lecture, primary and surrogate keys are discussed, and data types in PostgreSQL are discussed. You will get acquainted with examples of database schemas, schema versioning. The problem of generating a database based on the source code, methods of incremental and idempotent changes are discussed.

Lecture 2. Modification and selection of data

The first part of the lecture is devoted to an introduction to database design. The second part focuses on the basics of SQL: basic commands, creating tables, retrieving data. The topic of JOINs is touched upon, the INFORMATION_SCHEMA is being reviewed. Finally, it discusses how hierarchical structures can be stored in databases.

Lecture 3. Data sampling (continued)

The beginning of the third lecture is devoted to COLLATION and case-insensitive search. Then it is told about the task of data sampling (SELECT): the formation of subqueries, window functions, UNION, the JOINs topic is raised again, recursive queries are discussed. And in the end you will learn about the operation VIEW.

Lecture 4. Transactions. Triggers and stored procedures

The lecture consists of four parts. The first part is devoted to transactions: the task of ensuring longevity, the formation of a transaction log, ensuring isolation and atomicity, as well as levels of isolation of transactions are discussed. Then it describes the triggers: how can they be used to maintain integrity and business logic, what are their disadvantages. Next, stored procedures and functions are discussed, and finally, the distributed transactions and persistent queues are discussed.

Lecture 5. Indices and performance

From the fifth lecture, you will learn what database indexes are, how query logging is performed, what is needed and how the EXPLAIN query plan is made. Also discusses options for joining tables.

Lecture 6. Query optimization. Data structure optimization

This lecture is also devoted to the problem of improving database performance. You will learn about the normalization and denormalization of data. Get acquainted with the optimization of specific types of queries. The following describes the difference between current and historical data, partitioning, optimization at the application level. Finally, there are examples of effective bulk data changes.

Lecture 7. Replication, full-text search, JSON

This lecture consists of four parts. The first is devoted to replication: it describes physical, logical, synchronous, and asynchronous replication, balancing and fault tolerance, and replication problems. Then the issue of full-text search is discussed. Further, the question of storing geographic data in the database is addressed. And finally, it tells about the storage of semi-structured data (JSON).

Lecture 8. Data security

The last lecture is devoted to various aspects of ensuring the integrity of information in databases. First, it tells about backup, about its difference from replication, about creating logical and physical backups. Then you will learn how to configure databases: what are the general configuration guidelines, how to configure memory and I / O, what table spaces are, how to collect information about the current state of the server. And the final part of the lecture is devoted to ensuring security: policies of access rights and protection against SQL injections.

Playlist of all lectures is on the link . Recall that current lectures and master classes on programming from our IT specialists in Technopark, Technosphere and Tehnotrek projects are still published on Tekhnostrim channel.

Other courses Technopark on Habré:

• Java
• Design of high-load systems
• Algorithms and data structures
• Web technologies
• C / C ++

Information on all our educational projects can be found in a recent article .