Relational databases and merging data from multiple sources

Welcome to the World of Relational Databases!

In your journey to becoming an actuary, you’ve likely spent a lot of time looking at single, clean spreadsheets. But in the real world of predictive analytics, data is rarely that organized! Information is usually scattered across different tables in what we call Relational Databases. Think of this chapter as learning how to solve a puzzle: you have many pieces (tables), and your job is to connect them correctly to see the whole picture.

By the end of these notes, you’ll understand how databases are structured and, more importantly, how to bring data together from different sources without making common mistakes. Don't worry if this seems a bit "techy" at first—we'll break it down using simple analogies!

1. What is a Relational Database?

A Relational Database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows.

The Spreadsheet Analogy: Imagine an Excel workbook. Each "tab" in that workbook is like a table in a database. One tab might list customer names, while another tab lists their insurance claims. They are separate, but they are "related" because they both talk about the same customers.

Key Components of a Table

1. Rows (Records): Each row represents a single, unique instance (like one specific policyholder).
2. Columns (Fields/Attributes): Each column represents a specific piece of information (like a Date of Birth or a Premium amount).

Quick Review: In a database, we call the structure of these tables the schema. It’s the blueprint that tells us how the data is organized.

2. The "Glue" that Holds Tables Together: Keys

How do we know which claim belongs to which customer? We use Keys. This is the most important concept to master for merging data.

Primary Keys

A Primary Key is a unique identifier for every record in a table. It cannot be null (empty), and no two rows can have the same Primary Key.

Example: Your Social Security Number or a unique Policy ID number.

Foreign Keys

A Foreign Key is a column in one table that points to the Primary Key in another table. This is what creates the link between them.

Real-World Example:
- Table A (Customers) has CustomerID as its Primary Key.
- Table B (Policies) also has a CustomerID column. In Table B, this is a Foreign Key because it refers back to Table A.

Did you know? Using keys helps maintain referential integrity. This is a fancy way of saying "making sure the links between tables always remain valid."

3. Merging Data: The Art of the "Join"

When we want to combine data from two tables for our predictive model, we perform a Join. There are four main types you need to know for Exam ATPA:

Inner Join

An Inner Join only keeps the rows where there is a match in both tables. If a customer doesn't have a policy, or a policy doesn't have a customer listed, those records are dropped.

Left Join (The Actuary's Best Friend)

A Left Join keeps all records from the left table and adds matching data from the right table. If there’s no match on the right, you’ll see a NA or Null.

Why it’s used: If you have a list of all policyholders and you want to see who filed a claim, you’d use a Left Join. You want to keep every policyholder in your study, even if they didn't file a claim!

Right Join

This is the opposite of a Left Join. It keeps all records from the right table and matches them to the left. (Note: Most practitioners just flip the table order and use a Left Join instead).

Full Outer Join

A Full Outer Join keeps everything from both tables. If there’s no match, it just leaves those spots blank. It’s a "safety first" approach that ensures no data is lost, but it can create very messy datasets.

Memory Aid: "Left is for List"
Use a Left Join when you have a master List of subjects (like policies) that you don't want to lose, even if the other table is missing info for them.

4. Common Merging Pitfalls to Avoid

Merging data is where many predictive models go wrong. Watch out for these "Gotchas":

1. Duplicates and "One-to-Many" Explansions

If you join a table of "Customers" to a table of "Monthly Payments," one customer will match with many payments. Your dataset will "explode" in size, creating multiple rows for the same customer. This can bias your model if you aren't expecting it!

2. Grain Mismatch

Grain refers to what one row represents. If Table A is at the "Policy Level" (one row per policy) and Table B is at the "State Level" (one row per US State), you must be careful. You are joining individual data with aggregated data.

3. Cartesian Products (The Nightmare Join)

If you try to join two tables but forget to specify which Keys to use, the computer will try to match every row in Table A with every row in Table B. If both tables have 1,000 rows, you’ll end up with 1,000,000 rows of useless data!

Key Takeaway: Always check your row counts before and after a merge. If your row count jumps significantly, you likely have a "one-to-many" relationship or a duplicate key issue.

5. Data Cleaning During the Merge

Rarely is the data ready to use immediately after merging. You often need to perform these steps:

1. Handling Missing Values: After a Left Join, you will often have missing values (NAs). You must decide: do you fill them with zeros, the average, or remove them?
2. Renaming Columns: If both tables have a column named "Date," the software might rename them to "Date.x" and "Date.y." Rename these immediately to keep your sanity!
3. Data Type Matching: You cannot join a "Policy ID" that is stored as text (String) to a "Policy ID" stored as a number (Integer). Ensure types match first.

Summary Checklist for the Exam

Before moving on, make sure you can answer these questions:

Don't worry if this feels like a lot to juggle. As you practice with the ATPA sample projects, these joins will become second nature. You're building the foundation for great data science!