OLAP, data mining and distributed databases + A3 synthesis
Finish analytical/distributed databases and verify the entire A3 block under cumulative Paper 1 conditions.
Close the database block
- explain OLAP and six required data-mining techniques;
- describe distributed consistency, ACID, concurrency control, partitioning, security, transparency, fault tolerance, global query processing, replication and scalability;
- connect data infrastructure to machine-learning data preparation;
- complete cumulative A3 Paper 1 practice.
Rapid Recall Deck
Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.
OLAP vs data mining
OLAP
A retailer explores sales by time × region × product and drills from years to months. Why OLAP?
OLAP supports interactive aggregation across multiple dimensions and movement between summary/detail levels.
Difference
Distinguish OLAP from algorithmic data mining.
OLAP answers structured analytical summary questions across chosen dimensions; data mining discovers/predicts patterns such as classes, clusters, associations, sequences, anomalies or numeric outcomes.
Six required techniques
Classification
Predefined label: fraud/not fraud.
Clustering
Unlabeled grouping by similarity.
Regression
Numeric prediction such as sales.
Association rules
Items/events frequently occurring together.
Sequential patterns
Recurring event orders over time.
Anomaly detection
Unusual records/events.
Match question to output
Choose all six
Select technique: house price; unlabeled image groups; unusual account activity; products bought together; spam/not spam; click sequence before cancellation.
Regression; clustering; anomaly detection; association rule discovery; classification; sequential pattern discovery.
Distributed features
Describe the structure
One logical distributed database ├─ Node A: partition ├─ Node B: replica └─ Node C: partition
Explain partitioning, replication, distribution transparency, location transparency, global query processing and scalability.
Partitioning divides data across nodes; replication stores copies. Distribution transparency hides much of the multi-site design; location transparency means users need not know the physical data site. Global query processing coordinates queries spanning nodes. Adding nodes/partitions can improve scalability but increases coordination complexity.
Consistency, concurrency, fault tolerance, security
Consistency + concurrency
Two replicas update the same customer at nearly the same time. What must the system manage?
Concurrency control must order/resolve concurrent work and consistency mechanisms must prevent replicas remaining contradictory after accepted updates.
Fault tolerance + security
Give one benefit and one challenge of distribution.
Replication/multiple nodes can keep service available after failures. But more nodes/links increase the security/administration surface, so authentication, encryption, access control, monitoring and recovery must be coordinated.
Transactions are still logical units
Distributed order
Stock decreases on one node and payment records on another. Explain atomicity + durability.
Atomicity requires the logically related changes to succeed together or be undone; durability requires committed results to survive failure. Distribution makes coordination harder but does not remove these integrity requirements.
A3 design + SQL
Cumulative A
(1) Explain one relational benefit and limitation. (2) State 2NF vs 3NF. (3) Construct DISTINCT therapy patient query joining PATIENT and VISIT.
Benefit/limitation should use Guide mechanisms; 2NF removes partial dependencies, 3NF transitive dependencies.
SELECT DISTINCT P.PatientName FROM PATIENT AS P JOIN VISIT AS V ON P.PatID = V.PatID WHERE V.Treatment LIKE '%therapy%';
A3 HL analytics
Cumulative B
Answer: materialized-view benefit; atomicity + isolation; warehouse vs operational DB; technique for unusual transactions; replication.
- Stored query result speeds repeated reads but needs refresh/storage.
- Atomicity = all-or-nothing; isolation = safe concurrent behaviour.
- Warehouse = integrated historical analytical store; operational DB = current transaction processing.
- Anomaly detection.
- Replication stores copies across nodes for availability/fault tolerance but requires consistency coordination.
Dataset infrastructure checkpoint
Design responsible data infrastructure for Visionary Studios
Visionary Studios may store millions of training images plus metadata and later needs to audit where data came from and whether representation is biased. Recommend:
1. one scalable storage/distribution approach;
2. one way historical analytical data could support provenance or bias audits;
3. one benefit of partitioning or replication at scale;
4. the provenance, intellectual-property or bias metadata that must not be lost.
Optionally name one data-mining technique that could help inspect distributions or unusual records.
Large irregular image/metadata collections may need scalable cloud/distributed storage and an appropriate data model. Integrated historical analytics can support audits of provenance and dataset changes; clustering/anomaly analysis can inspect distributions/outliers. Partitioning/replication improve scale/availability. Responsible curation also needs licensing/provenance and bias-related metadata so technical scale does not erase accountability.
Finish the learning cycle
Case study / exam preparation
Post-IA focus: update case-study research on dataset scale, provenance/IP and bias, then continue cumulative Paper 1 preparation.
Paper 1 — short practice
Redo only A3 areas you missed, then blank-page map relational design → normalization → SQL/ACID → warehouse/OLAP/mining → distributed DB.
Checkpoint
Before A4, explain how database structures make data usable for machine-learning pipelines.