CS 210 · Year 2 · Semester 1 · 3 credits · Computer Science

Databases & SQL for Insurance Data

From raw policy transaction ledgers to audit-proof loss development triangles: mastering SQL, relational architecture, and ASOP 23 data quality.

Full practice exam · 30 Q · 120 min

Season 1 · 8 episodes

The Ledger of Record

When an unexplained twenty-four million dollar reserve deficit threatens to trigger regulatory takeover of a century-old commercial insurer, junior actuary Maya Lin must dismantle decades of corrupted spreadsheets and build a robust, mathematically rigorous relational database pipeline to uncover the true financial state of the firm before statutory filing day.

Protagonist · Maya Lin, Associate Actuary and Data Lead at Apex Mutual

Setting · Apex Mutual Insurance Headquarters in Chicago, Illinois; statutory valuation floor during a high-stakes state examination

Stakes · Apex Mutual faces immediate state receivership and mandatory liquidation if the appointed actuary cannot sign the Statement of Actuarial Opinion with an audited, auditable reserve pipeline adhering to ASOP 23 within four weeks.

Pick an episode

Episodes

Season finale: Maya executes the end-to-end, transaction-level triangle build in SQL live before the state insurance commissioner and board of directors, proving the deficit was an artifact of fan-out join duplications and race conditions, reconciling reserves to the exact penny and securing Apex Mutual's solvency.

From the lecturer: Welcome to CS 210. In actuarial practice, flawed data invalidates the most sophisticated pricing and reserving models. This course bridges the gap between raw enterprise data systems and actuarial analysis. You will learn to construct normalized relational databases, write production-grade analytic SQL queries, optimize execution plans, and enforce the data quality requirements mandated by Actuarial Standard of Practice (ASOP) No. 23. To get the most out of these lecture films, keep two workspaces open: a local SQL database console (PostgreSQL or DuckDB) to run query scripts alongside the video, and an engineering notebook for sketching Entity-Relationship Diagrams (ERDs) and query execution trees before writing a single line of code. Do not passively watch query outputs appear; pause before each query execution, predict the row count and schema shape, and trace the join semantics on paper. While this course does not directly map to a single preliminary SOA or CAS exam, its mastery is an essential prerequisite for predictive modeling, loss reserving under CAS Exam 5, and enterprise actuarial governance.

Cold open

At six thirty on a snowy Monday morning, Maya Lin's chief actuary slams a four-hundred-megabyte Excel workbook onto her desk. The file crashed the overnight reserving run, showing a twenty-four million dollar surplus hole that has triggered an emergency state audit.

preparing lecture…

Transcript

Translate insurance business rules into formal Entity-Relationship (ER) models with correct cardinalities and participation constraints.
Construct relational schemas enforcing primary, candidate, composite, and foreign key integrity.
Apply functional dependency theory line-by-line to decompose unnormalised insurance datasets into Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF).
Evaluate schema design against the Actuarial Standard of Practice No. 23 (ASOP 23) requirements on data appropriateness and structural integrity.