Basic Knowledge of Databases and Tables
What this domain tests
This domain tests relational fundamentals as they appear in insurance systems: tables, keys, relationships, grain, and joins. Expect questions where the right answer depends on knowing what one row represents.
Core rules
- A table stores one entity type; rows are records and columns are attributes.
- A primary key uniquely identifies a row; a foreign key references another table's primary key.
- Relationships are usually one-to-many: one policy term has many claims; one claim has many payments.
- Grain is the meaning of one row. Summing a payment-level table by claim requires care because one claim contributes several rows.
- Referential integrity keeps foreign keys valid; orphan records signal load or matching problems.
- Normalization reduces redundancy; analysts often query denormalized or flattened reporting tables instead.
- NULL means unknown, not zero or blank; aggregates ignore NULLs.
- A data dictionary defines each field, its codes, and its allowed values.
Worked example
A claims payment table has one row per payment, a policy table one row per policy term, and a party table one row per person or entity. A request asks for "claim counts and dollars per policy." Reasoning: counting rows in the payment table counts payments, not claims, so use distinct claim numbers; summing paid amounts is valid at payment grain but must not be joined twice, or the amounts double through fan-out. Joining claim to policy on policy number is many-to-one and safe; joining policy to party on a non-unique name field is many-to-many and will multiply dollars.
Traps that cost marks
- Counting payment rows as claims.
- Many-to-many joins that inflate paid totals.
- Joining on names or addresses that are not unique keys.
- Assuming a table has one row per claim without checking the dictionary.