Skip to main content

Course Introduction

  • Welcome to Advanced Database Concepts, the third course in your journey toward understanding how applications store, manage, and work with data. The course explores high-level techniques for designing, optimizing, and managing modern data systems. Students will learn query optimization strategies, advanced indexing methods, and approaches to partitioning and sharding for large-scale performance.

  • The course also covers high availability, replication architectures, and advanced transaction and concurrency control. Additional modules focus on database security and compliance, NoSQL systems from an advanced perspective, and integrating cloud-based databases into DevOps workflows.

What You'll Learn​

Query Optimization​

  • This module focuses on improving database performance through advanced query analysis and tuning techniques. Students will learn to interpret and optimize execution plans using deep EXPLAIN / ANALYZE, apply query rewriting strategies, leverage index-only scans, and identify common performance anti-patterns that impact efficiency.

Advanced Indexing​

  • This module explores powerful indexing strategies that enhance query performance and scalability. Students will learn to design covering indexes, work with multicolumn indexes, and apply MySQL’s advanced indexing options including FULLTEXT, SPATIAL, functional (generated column) indexes, and InnoDB’s clustered and secondary B-tree structures.

  • The module also covers full-text search techniques and how to build efficient, index-driven search capabilities.

Partitioning and Sharding​

This module examines techniques for scaling databases through strategic data distribution. Students will learn table partitioning, compare horizontal and vertical partitioning approaches, and explore sharding strategies such as range, hash, and consistent hashing.

  • The module also covers multi-tenant database design patterns for supporting large, distributed applications.

High Availability and Replication​

  • This module covers the core techniques used to keep databases resilient, scalable, and continuously available. Students will learn master–replica replication architectures, failover and disaster recovery strategies, and methods for read scaling in distributed systems.

  • The module also explores backup and restore options, including the differences between logical and physical backups for reliable data protection.

Advanced Transactions and Concurrency​

  • This module explores how modern databases manage simultaneous operations safely and efficiently. Students will learn how deadlocks occur and how to prevent them, gain a deep understanding of Multi-Version Concurrency Control (MVCC), and compare row-level versus table-level locking mechanisms to optimize transactional performance.

Security and Compliance​

  • This module focuses on protecting data and ensuring regulatory compliance within database systems. Students will learn how to manage roles and permissions, implement encryption at rest and in transit, prevent SQL injection through secure coding practices, and use audit logging to track and monitor database activity for security and compliance requirements.

NoSQL Overview​

  • This module introduces modern non-relational database models and when to use them over traditional relational systems. Students will explore key-value, document, columnar, and graph databases such as Redis, MongoDB, Cassandra, and Neo4j and learn how these systems address scalability and flexibility challenges.

  • The module also covers polyglot persistence and how to combine multiple data models within a single application architecture.

Cloud Databases and DevOps Integration​

  • This module explores how modern databases operate in cloud and DevOps environments. Students will learn to work with managed cloud database services, deploy and manage dockerized databases, and integrate databases into CI/CD pipelines for automated testing.

  • The module also covers database migration tools for maintaining consistent and reliable schema changes.

How To Go Through The Course​

  • It is recommended you go through each module in the course, in the order in which it is presented. Each module in the course is set up very much the same.

Grading​

  • The course is graded by completing a post-assessment test and lab at the end of each module. You will only have one attempt at completing the post-assessment test. Your final grade will be determined by the total score for each post-assessment test and lab.

  • When you have completed all the modules, post-assessment tests, and labs, you are required to email your Instructor: roderick.bernardo@georgebrown.ca who will process your grade. In your email, please include the CRN #.

Assistance​

  • We wish you well as you go through the course and hope this course meets your needs. If you require any assistance while you go through this course, you can always contact your instructor: roderick.bernardo@georgebrown.ca please indicate which module you require assistance in.