Databases and Data Warehousing

Entities, attributes, relationships, cardinality and participation constraints, weak entities and ER diagrams.

Relations, tuples, domains; super, candidate, primary, foreign and composite keys.

Assumes: The Entity–Relationship Model

Selection, projection, union, difference, product, rename, and all the join variants, with worked evaluations.

Assumes: The Relational Model and Keys

Declarative query specification with quantifiers, and its equivalence to relational algebra.

Assumes: Relational Algebra

SELECT/FROM/WHERE, predicates, NULL semantics, ordering and set operations.

Assumes: Relational Algebra

Inner, outer, self and cross joins; GROUP BY, HAVING, and correlated subqueries.

Assumes: SQL Fundamentals

OVER/PARTITION BY, ranking, running totals, lag/lead, and analytics without self-joins.

Assumes: SQL Joins and Aggregation

Domain, entity and referential integrity, cascading actions, assertions and triggers.

Assumes: The Relational Model and Keys

FDs, Armstrong's axioms, attribute closure, canonical cover and candidate key computation.

Assumes: The Relational Model and Keys

1NF through BCNF and beyond, decomposition, lossless join and dependency preservation.

Assumes: Functional Dependencies

Heap, sequential, hashed and clustered files, page layout, and record addressing.

Dense vs sparse, primary vs secondary, multilevel indexes, B+ tree operations and hash indexing.

Assumes: File Organisation

Atomicity, consistency, isolation, durability; schedules, serialisability and isolation levels.

Transactional and analytical workloads contrasted on schema, access pattern, volume and latency.

Assumes: SQL Joins and Aggregation

Warehouse architecture, ETL vs ELT, staging, facts, dimensions, grain and slowly changing dimensions.

Assumes: OLTP vs OLAP

Star, snowflake and fact-constellation schemas, with normalisation trade-offs and query implications.

Assumes: Data Warehouse Modelling

Dimension hierarchies, and the distributive, algebraic and holistic measure classes with their computability.

Assumes: Multidimensional Schemas

Roll-up, drill-down, slice, dice and pivot; cube lattice, materialisation and cuboid selection.

Assumes: Concept Hierarchies and Measures

Nominal, ordinal, interval and ratio scales; detecting and repairing dirty data.

Min–max, z-score and decimal scaling; equal-width, equal-frequency and entropy-based binning.

Assumes: Data Types and Data Cleaning

Reservoir and stratified sampling for large data; lossy vs lossless reduction and numerosity reduction.

Assumes: Normalisation and Discretisation

MCAR/MAR/MNAR mechanisms, imputation strategies, and statistical outlier detection.

Assumes: Data Types and Data Cleaning

One-hot, ordinal, target, frequency and hashing encoders, and the leakage risks of each.

Assumes: Normalisation and Discretisation