← Topic 8 · Databases (HL Option)
Computer Science · Topic Cheatsheet

Topic 8 · Databases (HL Option)

25 key results accumulated across 2 chapters.

Database models
Ch 1
Relational (tables + keys) · Object-oriented · Network · Spatial · Multi-dimensional (cubes).
OLTP
Ch 1
Online Transaction Processing. Operational DB. Many small INSERT/UPDATE. Live data. Normalised (3NF).
OLAP
Ch 1
Online Analytical Processing. Data warehouse. Aggregate queries on historical data. Often denormalised.
Warehouse 4 props
Ch 1
Subject-oriented · Integrated (many sources) · Time-variant (historical) · Non-volatile (read-mostly).
ETL
Ch 1
Extract (pull from sources) → Transform (clean, standardise) → Load (insert into warehouse). Usually nightly.
ELT
Ch 1
Modern variant: Extract → Load (raw) → Transform inside warehouse. Cheaper, more flexible.
Clustering
Ch 1
Group similar items WITHOUT labels (unsupervised). E.g. customer segments.
Classification
Ch 1
Predict KNOWN labels (supervised). E.g. spam / not-spam, fraud / legit.
Association
Ch 1
Find rules 'A → B'. E.g. 'bought bread → bought butter'. Market-basket analysis.
Forecasting
Ch 1
Predict NUMERIC future from historical patterns. E.g. sales next month.
Deviation detection
Ch 1
Find outliers / anomalies. E.g. unusual credit-card spending.
BI dashboards
Ch 1
Business Intelligence tools query the warehouse for reports & dashboards.
Table
Ch 2
Row = record/tuple. Column = attribute/field. Each row UNIQUE by primary key.
Primary Key (PK)
Ch 2
Column(s) uniquely identifying each row. Often an auto-increment integer.
Foreign Key (FK)
Ch 2
Column referencing the PK of ANOTHER table. Links tables. Enforces REFERENTIAL INTEGRITY.
JOIN
Ch 2
Combine rows from 2+ tables on matching key. INNER (only matches), LEFT (all left + matches), etc.
1NF
Ch 2
Atomic values only (no lists in cells); each row unique.
2NF
Ch 2
1NF + every non-key column depends on the FULL primary key.
3NF
Ch 2
2NF + non-key columns depend ONLY on PK, not on other non-key columns.
Why normalise
Ch 2
Eliminate redundancy → no UPDATE/INSERT/DELETE anomalies → data consistency.
Denormalise
Ch 2
Reverse for warehouses: duplicate columns to make analytical queries fast.
SQL SELECT
Ch 2
`SELECT cols FROM table WHERE cond GROUP BY g HAVING agg-cond ORDER BY col LIMIT n`.
ACID
Ch 2
Atomicity (all or nothing) · Consistency (valid state) · Isolation (concurrent = serial) · Durability (survives crash).
Transaction
Ch 2
Group of operations that succeed/fail TOGETHER. BEGIN/COMMIT/ROLLBACK.
Common exam trap
Ch 2
Confusing PK (uniqueness) with FK (foreign reference). Both are 'keys' but very different roles.