Lectures Technopark. Semester 2 Database

Another post in the framework of our regular column "Lectures Technopark." This time we bring to your attention lectures devoted to databases. The goal of the course is to provide students with knowledge in the design of relational databases, efficient work with databases, optimization of queries and data schemas, studying the specifics of using databases in high-load projects and / or using large data arrays, noSQL and its application to solve applied tasks in www.

Lecture 1. Introduction and the beginning of design

The lecture discusses the meaning and role of databases in IT, provides definitions of databases and DBMS. Then it tells about the relational data model, the basics of relational algebra. Next, it says directly about the design of the database: what are the criteria for evaluating the data model, the features of conceptual design, the design stages, what is logical design. The rest of the lecture is devoted to MySQL data types: NULL, integers, floating-point numbers, binary, strings, date and time.


')

Lecture 2. Modification of data. Data sampling (start)

First, the review of the Date and Time MySQL data type is completed. Then it is told about creating tables (CREATE TABLE): create_definition, column_definition, reference_defenition, update of the tuple in the parent relation. The following describes 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, data modification and the UPDATE statement, data deletion and the DELETE statement. Then a small part of the lecture is devoted to user variables. After this, information about the subqueries is given: SUBQUERIES, ROW SUBQUERIES, SUBQUERIES in FROM. Further, it is told about aggregation (UNION), about the family of JOIN functions and table joining, and at the end of the lecture - about the concept of VIEW (what are the advantages, limitations and features, algorithms of use and change).

Lecture 4. Advanced database capabilities. Triggers and stored procedures

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

Lecture 5. Identifying bottlenecks

First, it describes the storage subsystems: MyISAM, InnoDB, Memory, the selection criteria for storage subsystems, and gives practical examples. Then the topic of indexing (B-tree, hash indices) and EXPLAIN (columns id, table, possible_keys, key, key_len) is discussed.

Lecture 6. Query profiling. Complex SQL queries

The lecture opens with a story about what is query profiling, what are the stages of its execution in MySQL. It tells how to plan a query, how logging of queries is carried out, how statistics are collected. The basics of indexing are explained, indexing strategies for achieving high performance are discussed in detail: column isolation, cluster indices (advantages and disadvantages), data placement in MyISAM and InnoDB, covering indexes. Further, the topic of normalization and denormalization, as well as tables of counters, is touched upon. At the end, the author describes the versioning of the database schema: methods of incremental changes, idempotent changes, and likening the database structure to the source code.

Lecture 7. Query Optimization and Indexing

First, it discusses data access optimization, connection decomposition, and request status. Next comes a large block dedicated to the query optimizer (changing the join order, applying algebraic equivalence rules, optimizing COUNT (), MIN (), MAX (), calculating and convolving constant expressions, covering indexes, optimizing subqueries, early completion, comparing the IN list () and the spread of equality). Then, aspects such as the connection (JOIN) in MySQL, the sorting optimizer, correlated subqueries, merging and nonsequential viewing of indexes, SELECT & UPDATE, COUNT () functions are sequentially considered. After that, it is told about query optimization using JOIN, GROUP BY, DISTINCT and LIMIT with offset. At the end of the lecture, information is given on query caching, concatenated tables, and partitioning.

Lecture 8. Database Configuration

First, the basics of configuration and general setup principles are explained. The following describes the scope, setting memory usage, discusses the key block size key_cache_block_size. It discusses the design and use of InnoDB caches, as well as thread and table caches. It then talks about the features of I / O in InnoDB. The following describes the tablespace, optimization of file sorting, state variables. The final part of the lecture is devoted to replication: configuration, 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 copy). Then the terminology is determined to discuss further questions. After that, the basics of user accounts are considered: access tables, privileges, record types. Discusses SQL-injection, a list of adjacent vertices (Adjacency Set), nested set (Nested Set), the materialized path (Materialized Path) and the combined approach.

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, it describes how to use it. The types of NoSQL DB, the theoretical foundations of NoSQL are considered, 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 technologies
• 1 semester Algorithms and data structures
• 1 semester C / C ++

Subscribe to Technopark's youtube channel !