Data Management and Analysis
What Domain 5 tests
Data Management and Analysis covers relational databases, SQL, keys and normalization, data cleaning, aggregation, and responsible interpretation. You need to read schemas, predict query results, and recognize when a visual or statistic misleads.
Core rules
- A primary key uniquely identifies a row and cannot be NULL; a foreign key references a primary key in another table and enforces referential integrity.
- First normal form requires atomic values; second removes partial dependencies on part of a composite key; third removes transitive dependencies on non-key attributes.
- SQL logical evaluation order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.
- WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
- COUNT(*) counts rows; COUNT(column) ignores NULLs. Any comparison with NULL yields UNKNOWN, so use IS NULL or IS NOT NULL.
- A join can multiply rows when the “one” side matches many rows on the other side; aggregate after checking that grain.
Worked example
Query: SELECT dept, AVG(salary) FROM Employee WHERE active = 1 GROUP BY dept HAVING COUNT(*) >= 3;
The FROM clause reads Employee; WHERE keeps only active employees; GROUP BY makes one group per department; HAVING drops departments with fewer than three active employees; SELECT returns the department and average salary for the survivors. If Sales has two active employees, it never appears, even if its average is highest.
Traps
Putting an aggregate condition in WHERE is invalid; use HAVING. Writing WHERE salary = NULL returns no rows; use IS NULL. Averages over joined tables can be skewed by duplicate rows. A graph can show correlation without causation, and a truncated y-axis can exaggerate small differences.