Lectures of the Technopark. 2 semester. Database

Another post in the framework of our regular column “Lectures of the Technopark”. This time we bring to your attention lectures on databases. The purpose of the course is to provide students with knowledge in the field of relational database design, efficient work with databases, query optimization and data schemes, studying the features of using databases in projects with high load and / or using large data arrays, noSQL and its application for solving applied tasks in the www.

Lecture 1. Introduction and start of design

The lecture discusses the meaning and role of databases in IT, and defines the database and DBMS. Then we talk about the relational data model, the basics of relational algebra. The following directly refers to database design: what are the criteria for evaluating a data model, features of conceptual design, design stages, what is logical design. The rest of the lecture deals with MySQL data types: NULL, integers, floating-point numbers, binary, strings, date and time.

Lecture 2. Data modification. Data sampling (start)

First, the consideration of the MySQL datatype Date and Time is completed. Then it talks about creating tables (CREATE TABLE): create_definition, column_definition, reference_defenition, updating the tuple in the parent relation. The following discusses the procedure for changing the table (ALTER TABLE), data sampling and the SELECT statement, as well as filtering (grouping, aggregators).

Lecture 3. Data sampling (continued). Transactions

The lecture begins with a story about adding data and the INSERT statement, about updating data and the UPDATE statement, about deleting data and the DELETE statement. Then a small part of the lecture is devoted to user variables. After that, information about the subqueries is given: SUBQUERIES, ROW SUBQUERIES, SUBQUERIES in FROM. The following is about aggregation (UNION), about the JOIN family of functions and table joining, and at the end of the lecture, about the concept of VIEW (what are the advantages, limitations and features, usage algorithms and changes).

Lecture 4. Advanced features of working with databases. Triggers and Stored Procedures

The first part of the lecture is devoted to stored procedures (usage, code examples, security aspects when using stored procedures): LOOP, REPEAT, WHILE, HANDLER, cursors, EXECUTE. The second part describes triggers: BEFORE, AFTER, INSTEAD OF, special inserted, updated, and deleted tables, the use of triggers to maintain integrity and business logic, problems with using triggers, and code examples. The final part of the lecture discusses issues related to transactions: the concept of ACID; isolation levels ANSI / ISO (SET TRANSACTION ISOLATION LEVEL); transactional interaction and problems lost update, dirty read, non-repeatable read and phantom read; MVCC technology (competition versioning control); competition; parallelism and coherence.

Lecture 5. Identification of bottlenecks

At first, it tells about storage subsystems: MyISAM, InnoDB, Memory, about the criteria for choosing storage subsystems, gives practical examples. Then the topic of indexing (B-tree, hash indexes) and EXPLAIN (columns id, table, possible_keys, key, key_len) are discussed.

Lecture 6. Profiling queries. Sophisticated SQL Queries

The lecture opens with a story about what profiling a query is, what are the stages of its execution in MySQL. It tells about how to plan a request, how to log requests, and how statistics are collected. The basics of indexing are explained, indexing strategies for achieving high performance are discussed in detail: column isolation, cluster indexes (advantages and disadvantages), data placement in MyISAM and InnoDB covering indexes. Next, the topic of normalization and denormalization, as well as counter tables, is discussed. In the end, we talk about versioning the database schema: about methods of incremental changes, idempotent changes, likening the database structure to the source code.

Lecture 7. Query Optimization and Indexing

First, it talks about optimizing data access, decomposing a connection, and query status. Next comes a large block devoted to the query optimizer (changing the join order, applying algebraic rules of equivalence, optimizing COUNT (), MIN (), MAX (), calculating and convolving constant expressions, covering indexes, optimizing subqueries, early completion, comparing according to the IN list () and the spread of equality). Then, aspects such as joining (JOIN) in MySQL, sorting optimizer, correlated subqueries, merging and inconsistent index scrolling, SELECT & UPDATE, COUNT () are sequentially considered. After that, we talk about query optimization using JOIN, GROUP BY, DISTINCT, and LIMIT with an offset. At the end of the lecture, information is provided on query caching,

Lecture 8. Database Configuration

First, the basics of configuration and general principles of configuration are explained. The following describes the scope, the memory usage settings, and discusses the size of the key_cache_block_size key block. The design and use of the InnoDB cache, as well as stream and table caches, are discussed. It then talks about I / O features in InnoDB. The following describes table space, file sort optimization, and state variables. The final part of the lecture is devoted to replication: tuning, synchronization, topology, bandwidth planning, administration and maintenance, problems and their solutions.

Lecture 9. Database Security

The lecture opens with a story about backups (about logical and physical backups, about choosing data to back up). Then, terminology is determined to discuss further issues. After that, the basics of accounts are considered: access tables, privileges, types of entries. SQL-injection, Adjacency Set list, Nested Set, Materialized Path, and combined approach are discussed.

Lecture 10. Non-Relational Database Solution - NoSQL

The introductory part is devoted to the definition and history of the development of the NoSQL concept. Characteristics are given, and usage methods are described. The types of NoSQL databases, the theoretical foundations of NoSQL are examined, and at the end of the lecture, the disadvantages of NoSQL solutions are discussed, and various NoSQL solutions are compared.



Previous issues:

• 1 semester. Web technology
• 1 semester. Algorithms and data structures
• 1 semester. C / C ++

Subscribe to the Technopark youtube channel !