Under the invitation of the company a series of lectures were held on introduction to the language of SQL for project managers.
The lectures included theoretical and practical parts on the theory of relational databases.
Before the lectures commenced, the DataLab Team (under NDA report) plunged into the existing physical model of the company’s database to gain knowledge about the business, to present information at the intersection of theory and practice on the current implementation of the model.
“Studying is not about time. It’s about effort”
The course covered the following topics:
- what are Relational Databases?
- basic theorems and principles for working with databases
- CAP theorem
- ACID principle
- PACELC theorem
- BASE semantics
- the historical development of relational database theory since 1970 (when relational theory was announced by Frank Tedd Codd)
- the current topology and data replication of the company between Data Centers
- replication and the used servers for certain purposes (reports, read/write OLTP traffic, read OLAP traffic etc)
- basic failover principles
- basic switchover principles
- Database Designs
- Strategies for analysts (Top-Down, Bottom-Up, Mixed)
- Conceptual Design (~ Infological Design)
- Logical Design (~ Datalogical Design)
- main aspects of Physical Design
- Entity-Relationship Model
- Notations from Peter Pin-Shan Chen
- Relational Model Integrity
- check constraints
- unique / primary / foreign keys
- analysis of existing constraints of the basic model of the company and demonstration of the gained knowledge
- Working with entry-level SQL
- WHERE clause with the use of AND / OR / NOT operators
- Working with Sets (UNION [ALL], MINUS / EXCEPT [ALL], INTERSECT [ALL])
- SQL Joins (from INNER to LATERAL including RECURSIVE and SELF-JOINS with hierarchy)
- Introduction into OLAP (~ Multidimensional Models)
- GROUP BY clause on examples of tables of the main company model
- HAVING on examples of tables of the main company model
- GROUPING SETS / ROLLUP / CUBE on examples of tables of the main company model
- Database Indices
- how to try to speed up the query knowing the indexed columns of the tables
- obtaining information from the database metadata about existing indices in the company model
- analysis of existing indices of the main company model and demonstration of how to use the knowledge
- introduction to the full text search
At the end of the course the final laboratory work was carried out on an individual assignment for obtaining data from the company’s database model based on the assigned task. And to my great pleasure all the managers coped with the tasks and received laboratory certification.