Data Management, Databases and Business Intelligence
What the exam tests
This domain covers database design, relational concepts, data quality, warehousing and analytics. Expect keys, normalisation, SQL, ACID, OLTP versus OLAP, ETL and star schemas.
Core rules and concepts
- A database management system controls storage, access, concurrency and recovery. The relational model uses tables, rows and columns with primary and foreign keys.
- Normalisation reduces redundancy: 1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies. Warehouses often denormalise for query speed.
- ACID means atomicity, consistency, isolation and durability. Transactions protect integrity.
- OLTP systems support day-to-day transactions; OLAP systems support analysis. ETL extracts, transforms and loads data into a warehouse.
- A star schema has a fact table of measures and dimension tables of context. Data quality dimensions include accuracy, completeness, consistency and timeliness.
Worked example
A retailer builds a sales star schema. The fact table stores revenue and units, with foreign keys to date, product, store and promotion dimensions. Reasoning: analysts can slice revenue by product, month and store without scanning operational tables. The operational database stays normalised for transactions; the warehouse is denormalised for analysis. A common error is to put descriptive text directly in the fact table, losing the dimension structure.
Traps that cost marks
Do not confuse primary and foreign keys. Normalisation is for integrity, not always for query performance. A data lake is not automatically governed. A dashboard is not a decision. Correlation in analytics does not prove causation.
Key points
- Relational integrity depends on primary and foreign keys.
- Normalisation reduces redundancy; warehousing denormalises for speed.
- ACID protects transaction integrity.
- OLTP is transactional; OLAP is analytical.
- Star schemas use fact and dimension tables.