Database Science Course

Lesson 1 • Data and Information Fundamentals

Defines data, information, and metadata and explains their relationships. Establishes vocabulary used throughout the course.

Lesson 2 • History and Evolution of Databases

Traces database development from flat files to modern distributed systems. Provides context for understanding why current designs exist.

Lesson 3 • Database Management System Architecture

Explains DBMS components including storage engine, query processor, and transaction manager. Connects architecture to performance and reliability outcomes.

Lesson 4 • Database Paradigms and Use Cases

Compares relational, document, key-value, columnar, and graph models. Guides selection of the right paradigm for a given problem.

Lesson 1 • Subqueries and Set Operations

Introduces correlated subqueries, derived tables, UNION, INTERSECT, and EXCEPT. Extends query power for complex data retrieval tasks.

Lesson 2 • Data Definition Language

Teaches CREATE, ALTER, and DROP statements for defining schema objects. Connects DDL decisions to long-term data integrity.

Lesson 3 • Joining and Aggregating Data

Explains INNER, LEFT, RIGHT, and FULL joins plus GROUP BY and aggregate functions. Enables multi-table analysis essential for real-world queries.

Lesson 4 • Relational Model Concepts

Covers relations, tuples, attributes, domains, and keys. Provides the theoretical basis for understanding SQL behaviour.

Lesson 1 • Entity-Relationship Modeling

Teaches entities, attributes, and relationships using ER diagrams. Translates business requirements into a visual schema blueprint.

Lesson 2 • Mapping ER Diagrams to Tables

Converts ER diagrams into relational tables following systematic mapping rules. Bridges conceptual design and physical implementation.

Lesson 3 • Functional Dependencies and Normal Forms

Defines functional dependencies and explains 1NF through BCNF. Provides the theoretical basis for eliminating update, insert, and delete anomalies.

Lesson 4 • Higher Normal Forms and Denormalization

Covers 4NF and 5NF for multi-valued and join dependencies. Explains when controlled denormalisation improves performance without sacrificing integrity.

Lesson 1 • Stored Procedures, Functions, and Triggers

Defines server-side routines and event-driven triggers for encapsulating business logic. Reduces application-layer complexity and enforces consistent data rules.

Lesson 2 • Window Functions and Analytical Queries

Covers OVER, PARTITION BY, RANK, DENSE_RANK, and frame clauses. Enables complex analytical calculations without collapsing result sets.

Lesson 3 • Query Tuning Strategies

Applies rewriting techniques, index design, and statistics maintenance to improve query speed. Connects tuning decisions to measurable performance gains.

Lesson 4 • Query Execution Plans and Indexing

Explains how the query optimizer generates execution plans and how indexes affect them. Teaches reading EXPLAIN output to identify bottlenecks.

Lesson 1 • Locking and Deadlock Management

Explains shared, exclusive, and intent locks plus deadlock detection and prevention. Teaches strategies to minimise lock contention in high-throughput systems.

Lesson 2 • Isolation Levels and Their Trade-offs

Covers READ UNCOMMITTED through SERIALIZABLE and their anomaly prevention guarantees. Guides selection of the appropriate isolation level for each workload.

Lesson 3 • Multiversion Concurrency Control

Explains MVCC architecture and how it enables readers and writers to avoid blocking each other. Connects MVCC to vacuum, bloat, and maintenance requirements.

Lesson 4 • Concurrency Anomalies

Identifies dirty reads, non-repeatable reads, phantom reads, and lost updates. Motivates the need for isolation levels and locking protocols.

Lesson 1 • Backup Strategies and Recovery

Compares full, incremental, and differential backups and explains point-in-time recovery. Prepares students to design backup schedules meeting recovery time objectives.

Lesson 2 • Auditing and Compliance Monitoring

Configures audit logging to track data access and schema changes. Connects audit trails to regulatory compliance and incident investigation.

Lesson 3 • User Management and Access Control

Covers creating users, roles, and privilege grants using GRANT and REVOKE. Implements least-privilege access aligned with organisational security policies.

Lesson 4 • Data Encryption and Masking

Applies encryption at rest and in transit plus data masking for non-production environments. Addresses compliance requirements for sensitive data protection.

Lesson 1 • Columnar and Wide-Column Stores

Explains column-family storage for time-series and write-heavy analytical workloads. Covers partition key design and compaction strategies.

Lesson 2 • Document and Key-Value Stores

Covers schema-flexible document storage and high-throughput key-value access patterns. Teaches data modelling strategies specific to each store type.

Lesson 3 • Sharding, Replication, and Partitioning

Explains horizontal sharding, leader-follower replication, and range vs. hash partitioning. Connects distribution strategies to read/write scalability and fault tolerance.

Lesson 4 • Graph Databases and Query Languages

Models connected data as nodes and edges and queries it using graph traversal languages. Applies graph databases to recommendation, fraud detection, and network analysis.

Lesson 1 • Data Transformation and Quality

Applies cleaning, deduplication, type casting, and business rule transformations. Integrates data quality checks into the pipeline to prevent bad data propagation.

Lesson 2 • ETL vs. ELT Architecture

Contrasts extract-transform-load with extract-load-transform patterns and their infrastructure implications. Guides architecture selection based on data volume and transformation complexity.

Lesson 3 • Pipeline Orchestration and Monitoring

Schedules, dependencies, retries, and alerting for production data pipelines. Ensures pipeline reliability through lineage tracking and failure recovery.

Lesson 4 • Data Extraction and Source Systems

Covers full extraction, incremental extraction, and change data capture from operational databases. Addresses source system impact and extraction scheduling.