Database Administration Course

Lesson 1 • Data Storage and File Structures

Examines how data is physically stored on disk using pages, extents, and file groups. Grounds learners in storage mechanics before indexing is introduced.

Lesson 2 • Core Database Concepts

Introduces data, information, and database terminology essential for all subsequent topics. Establishes shared vocabulary used throughout the course.

Lesson 3 • DBMS Architecture Layers

Explains the internal layers of a DBMS including storage, query processing, and transaction management. Provides context for later performance and tuning topics.

Lesson 1 • Data Manipulation and DDL

Covers INSERT, UPDATE, DELETE, MERGE, and schema-definition statements. Equips DBAs to manage data changes and maintain schema objects safely.

Lesson 2 • Querying and Filtering Data

Teaches SELECT syntax, filtering with WHERE, sorting, and limiting result sets. Forms the baseline SQL skill required for all administrative tasks.

Lesson 3 • Aggregation and Analytical Functions

Introduces GROUP BY, HAVING, and window functions for summarising and ranking data. Supports reporting and monitoring queries used in DBA workflows.

Lesson 4 • Joins and Subqueries

Covers INNER, LEFT, RIGHT, and FULL joins alongside correlated and non-correlated subqueries. Enables retrieval of data spread across multiple related tables.

Lesson 1 • Normalisation Through Third Normal Form

Explains functional dependencies and the first three normal forms with practical decomposition examples. Eliminates insertion, update, and deletion anomalies from schemas.

Lesson 2 • Entity-Relationship Modelling

Teaches ER diagram notation, cardinality, and participation constraints for capturing business requirements. Serves as the design blueprint before physical schema creation.

Lesson 3 • Schema Patterns for Common Domains

Applies design principles to recurring domains such as hierarchies, time-series, and multi-tenancy. Bridges abstract theory to practical schema templates.

Lesson 4 • Advanced Normal Forms

Covers Boyce-Codd, fourth, and fifth normal forms for handling complex dependency scenarios. Prepares learners to design schemas for sophisticated data relationships.

Lesson 1 • Index Types and Structures

Covers B-tree, hash, bitmap, and full-text index internals and their appropriate use cases. Provides the structural knowledge needed to choose the right index type.

Lesson 2 • Reading Execution Plans

Teaches how to interpret graphical and text-based query execution plans to identify costly operations. Connects plan analysis directly to indexing and rewrite decisions.

Lesson 3 • Query Rewriting Techniques

Demonstrates how to rewrite queries to improve optimizer choices without changing result sets. Reinforces SQL skills from Chapter 2 with a performance focus.

Lesson 4 • Statistics and Optimizer Behaviour

Explains how the query optimizer uses column statistics to estimate cardinality and choose plans. Enables DBAs to diagnose and fix statistics-related plan problems.

Lesson 1 • Concurrency Monitoring and Tuning

Covers wait statistics, blocking queries, and concurrency-related performance counters. Connects monitoring data to actionable tuning decisions.

Lesson 2 • Locking Mechanisms

Explains shared, exclusive, update, and intent locks at row, page, and table granularity. Provides the knowledge to diagnose blocking and lock escalation issues.

Lesson 3 • Isolation Levels and Anomalies

Covers read uncommitted through serialisable isolation and the anomalies each level prevents. Guides DBAs in selecting appropriate isolation for different workloads.

Lesson 4 • Transaction Fundamentals

Defines transactions, ACID guarantees, and the lifecycle of a transaction from begin to commit or rollback. Establishes the theoretical basis for all concurrency topics.

Lesson 1 • Recovery Models and RTO/RPO

Explains simple, full, and bulk-logged recovery models and their impact on recovery point objectives. Aligns backup strategy with business continuity requirements.

Lesson 2 • Disaster Recovery Planning

Covers DR site architecture, backup offsite storage, and recovery runbook creation. Ensures DBAs can execute a full recovery in a disaster scenario.

Lesson 3 • Restore Procedures and Point-in-Time Recovery

Teaches full, differential, and log restore sequences including point-in-time recovery steps. Prepares DBAs to execute recoveries accurately under pressure.

Lesson 4 • High-Availability Technologies

Introduces failover clustering, database mirroring successors, and log shipping for redundancy. Provides architectural options for minimising unplanned downtime.

Lesson 1 • Data Encryption Techniques

Explains transparent data encryption, column-level encryption, and encrypted connections. Protects data at rest and in transit from unauthorised access.

Lesson 2 • Threat Mitigation and Hardening

Addresses SQL injection prevention, surface area reduction, and patch management practices. Reduces the attack surface of the database server and application layer.

Lesson 3 • Authorisation and Privilege Management

Teaches role-based access control, object-level permissions, and the principle of least privilege. Limits exposure by granting only necessary rights to each user.

Lesson 4 • Auditing and Compliance Monitoring

Covers native audit features, change data capture, and log review for regulatory compliance. Provides traceability for all sensitive data access and modifications.

Lesson 1 • Workload Analysis and Profiling

Covers query store, extended events, and trace-based profiling to capture workload patterns. Identifies the highest-impact queries for optimisation efforts.

Lesson 2 • Capacity Planning and Growth Management

Addresses storage growth forecasting, compute scaling decisions, and resource upgrade planning. Ensures the database infrastructure keeps pace with business growth.

Lesson 3 • Baselining and Trend Analysis

Explains how to establish performance baselines and detect deviations using trend analysis. Enables proactive intervention before issues affect end users.

Lesson 4 • Wait Statistics and Resource Bottlenecks

Teaches wait category analysis to pinpoint CPU, memory, I/O, and network bottlenecks. Translates raw wait data into targeted remediation actions.