Complex structured SQL-queries

Practical lesson. We consider the complex queries. Students practice in the preparation of complex queries.

Transactions. MVCC

• What transaction is?
• Providing durability
• Transaction log
• Providing isolation and atomicity MVCC
• Consistency
• Isolation levels

Triggers. Stored procedures. Views

• Using triggers to maintain the integrity and business logic
• Disadvantages triggers
• Stored procedures and functions
• Distributed transactions (XA) and persistent queue
• Views

Query optimization. Indexes

• B-Tree indexes
• Hash indexes
• Bitmap indexes
• Partial indexes
• Clustered indexes and covering

Search bottlenecks. Profiling queries

• Logging of requests
• Query planning EXPLAIN
• Optimize on application level
• Caching

Data structure optimization. Denormalization

• Options for joining tables
• Temporary tables partitioning

Database configuration. Replication

• General configuration principles
• Setting the memory and I / O
• Table spaces
• Information about the current status of the server
• Balancing and Failover
• Replication Problems

Database security

• Backup
• Security
• Users and access rights
• SQL-injection

Data Manipulation Language (DML). Select

• SELECT
• JOINS
• Subqueries
• Aggregation
• HAVING, GROUP, ORDER BY UNION
• Priority queues and hash tables

Running a company

• INSERT
• UPDATE
• DELETE
• Recursive queries
• Hierarchical structures in databases

NoSQL. CAP Theorem

• NoSQL. Overview of existing solutions
• Theoretical foundations of distributed systems
• CAP Theorem
• Types of consistency

Kay-value storages. Documentary storage

• Overview Redis
• Overview Tarantool
• Overview MongoDB

Column-oriented storage. Graph databases

• Overview Cassandra
• Overview OrientDB

Introduction and design basics 

• A brief introduction to the history of the database.
• Why and where are databases used?
• What types of databases exist?
• Introduction to Relational Database
• The terms of relational databases
• Relational Algebra

Design and Data Definition Language (DDL)

• Introduction to database design
• Why database design can be bad
• Normalization and normal forms
• Primary keys
• Data types
• Migration between scheme versions