Chapter 34 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. OLTP systems are optimized for: - A) Huge aggregating reads - B) Many small transactional reads/writes - C) Column storage - D) Star schemas

Q2. OLAP / warehouse systems are optimized for: - A) Transaction throughput - B) Read/aggregation over large history - C) Strong write consistency - D) Normalization

Q3. A star schema has: - A) Many normalized tables - B) A central fact table surrounded by dimension tables - C) No joins - D) Only one table

Q4. A fact table holds: - A) Descriptive attributes - B) Numeric measures + foreign keys to dimensions - C) Indexes - D) Backups

Q5. Dimension tables hold: - A) The measurements - B) Descriptive context to slice facts by (date, product, customer) - C) Only numbers - D) Transactions

Q6. The fact table's "grain" is: - A) Its storage format - B) What one row represents (the level of detail) - C) Its index - D) Its size

Q7. A date dimension is included because: - A) It's required by SQL - B) It lets you group by any time attribute (year/quarter/month) cleanly - C) It speeds writes - D) It encrypts dates

Q8. SCD Type 2: - A) Overwrites the old value - B) Adds a new versioned dimension row with validity dates (keeps history) - C) Deletes the dimension - D) Keeps no history

Q9. Analytical databases use column-oriented storage because: - A) It's simpler - B) Aggregations read only the needed columns over many rows (less I/O) and compress well - C) It prevents injection - D) Rows are faster for analytics

Q10. A warehouse is typically: - A) Normalized like OLTP - B) Deliberately denormalized (star schema) for read/aggregation speed - C) Schemaless - D) Key-value

True/False

Q11. You should run heavy analytics directly on the live transactional database. (True / False)

Q12. Modern analytical databases are still queried with SQL. (True / False)

Short answer

Q13. Why is the warehouse denormalized when Part III taught normalization? Reconcile the two.

---

Answer key

Q1 — B. Many small reads/writes (run the app).

Q2 — B. Read/aggregation over history (analyze the business).

Q3 — B. Central fact + surrounding dimensions.

Q4 — B. Measures + dimension foreign keys.

Q5 — B. Descriptive context to slice by.

Q6 — B. The level of detail of one fact row — the key design decision.

Q7 — B. Group by any time attribute cleanly via a calendar dimension.

Q8 — B. Versioned dimension rows with validity dates (full history).

Q9 — B. Reads only needed columns over many rows; great compression.

Q10 — B. Denormalized star schema for read/aggregation speed.

Q11 — False. It competes with transactions and is slow on the normalized schema; use a warehouse/replica.

Q12 — True. SQL won even in analytics (Snowflake, BigQuery, DuckDB are SQL).

Q13. They optimize for different workloads. OLTP normalizes to protect write integrity under many small concurrent transactions (Part III). A warehouse is loaded in controlled batches (not transactionally updated), and its priority is read/aggregation speed over huge history — so it denormalizes (star schemas duplicate descriptive attributes into dimensions, pre-aggregate) to make analytical queries simple and fast. It's Chapter 20's lesson at scale: the right design depends on the workload — normalize for transactional writes, denormalize for analytical reads.

Scoring: 11–13 you think analytically; 8–10 review star schemas and SCD; below 8, redo Exercises B–C.