If you have been following the examples in the book, you may have created the tables in the Claims database already. This page takes you through that exercise and also provides data for these tables and shows you how to load this data. If you do this, you will be able to run most of the queries in the book, and hopefully some of your own, with reasonable volumes of generated sample data.
We start with Iteration 1 of the Claims database, as described in in:
You can create the Iteration 1 tables, either using Ruby on Rails (scaffold_iteration_1.sh) or SQL (migration_001.sql), as described in the book.
Here's the example using the SQL migration:
Mac$ psql -U postgres claims
Password for user postgres:
psql (10.1)
Type "help" for help.
claims=# \i migration_001.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
claims=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | claim | table | postgres
public | claimant | table | postgres
public | damage | table | postgres
public | driver | table | postgres
public | passenger | table | postgres
public | person | table | postgres
public | policy | table | postgres
public | vehicle | table | postgres
public | witness | table | postgres
(9 rows)
claims=#
Now we can load some data into these tables. First download each of the data files:
| Table | Download link |
|---|---|
| claim | claim.csv |
| claimant | claimant.csv |
| damage | damage.csv |
| driver | driver.csv |
| passenger | passenger.csv |
| person | person.csv |
| policy | policy.csv |
| vehicle | vehicle.csv |
| witness | witness.csv |
Now download the script load_data_iteration_1.sql. In a command window (Terminal on Mac) change directory to the place where you downloaded the files. Then log in to the PostgreSQL command line and execute the script like this:
Mac$ psql -U postgres claims
Password for user postgres:
psql (10.1)
Type "help" for help.
claims=# \i load_data_iteration_1.sql
COPY 12538
COPY 4739
COPY 14355
COPY 6726
COPY 16211
COPY 3499
COPY 6093
COPY 572
COPY 0
claims=# \q
Mac$
There is no data for the table called damage, hence the zero result for the last copy.
If you want to get to the Iteration 2 database and then the Iteration 3 database, the best way is to run the migrations described in Chapter 12 - Database Migrations That Preserve Data.
The migration from Iteration 1 to Iteration 2 creates the incident table and moves data to it from the claims table, creating the necessary links on the way. It also upgrades the vehicle table. The incident table is populated by the migration, but the extra columns in the vehicle table are new, so there is no data to migrate.
Download the migration script migration_002_preserve_data.sql, and run it like this:
claims=# \i migration_002_preserve_data.sql
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP SEQUENCE
ALTER TABLE
CREATE TABLE
INSERT 0 0
CREATE TABLE
INSERT 0 0
Tuples only is on.
Tuples only is off.
Tuples only is on.
Tuples only is off.
ALTER TABLE
ALTER TABLE
ALTER TABLE
test_result
--------------------------
Success. Claim counts OK
(1 row)
test_result
-----------------------------
Success. Incident counts OK
(1 row)
test_result
------------------------------------
Success. No data lost in migration
(1 row)
test_result
----------------------------------------
Success. No spurious data in migration
(1 row)
test_result
-------------
(1 row)
test_result
-------------
(1 row)
ALTER TABLE
claims=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | claim | table | postgres
public | claimant | table | postgres
public | damage | table | postgres
public | driver | table | postgres
public | incident | table | postgres
public | old_claim | table | postgres
public | passenger | table | postgres
public | person | table | postgres
public | policy | table | postgres
public | vehicle | table | postgres
public | witness | table | postgres
(11 rows)
claims=# drop table old_claim;
DROP TABLE
claims=#
To populate the extra columns in the vehicle table:
Here's what happens, with a select of the first 5 rows to show that we did add the make, model and year_first_licensed, and give them values:
claims=# \i populate_new_columns_in_vehicle.sql
CREATE TABLE
COPY 14355
UPDATE 14355
DROP TABLE
claims=# select
claims-# id,
claims-# policy_id,
claims-# license_number,
claims-# state_where_licensed,
claims-# make,
claims-# model,
claims-# year_first_licensed
claims-# from
claims-# vehicle
claims-# limit 5;
id | policy_id | license_number | state_where_licensed | make | model | year_first_licensed
------+-----------+----------------+----------------------+-----------+------------+---------------------
6631 | 4393 | GIO 337 | NM | Audi | A4 Allroad | 2013
41 | 73 | APYVLG | NH | Mazda | 3 | 2014
42 | 74 | RRE 656 | WV | Chevrolet | Tahoe | 2009
43 | 76 | BPO 968 | OH | Chrysler | 300 | 2010
44 | 77 | QPE KYD | MI | Audi | Q7 | 2008
(5 rows)
migration_003.sql converts the database from Iteration 2 to Iteration 3 by creating the claim_head and transaction tables. Download it and run it as before:
claims=# \i migration_003.sql
CREATE TABLE
CREATE TABLE
claims=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | claim | table | postgres
public | claim_head | table | postgres
public | claimant | table | postgres
public | damage | table | postgres
public | driver | table | postgres
public | incident | table | postgres
public | old_claim | table | postgres
public | passenger | table | postgres
public | person | table | postgres
public | policy | table | postgres
public | transaction | table | postgres
public | vehicle | table | postgres
public | witness | table | postgres
(13 rows)
claims=#
Neither of these tables have data that can be migrated from earlier iterations. You can download the data: claim_head.csv and transaction.csv, and the load script: load_data_iteration_3.sql. Now run the script as before:
claims=# \i load_data_iteration_3.sql
COPY 23
COPY 170127
claims=#
Let's get a summary of what we have loaded:
claims=# select 'person ' as table_name, count(*) as row_count from person union
claims-# select 'policy ' as table_name, count(*) as row_count from policy union
claims-# select 'vehicle ' as table_name, count(*) as row_count from vehicle union
claims-# select 'claim ' as table_name, count(*) as row_count from claim union
claims-# select 'claimant ' as table_name, count(*) as row_count from claimant union
claims-# select 'witness ' as table_name, count(*) as row_count from witness union
claims-# select 'driver ' as table_name, count(*) as row_count from driver union
claims-# select 'passenger ' as table_name, count(*) as row_count from passenger union
claims-# select 'damage ' as table_name, count(*) as row_count from damage union
claims-# select 'incident ' as table_name, count(*) as row_count from incident union
claims-# select 'claim_head ' as table_name, count(*) as row_count from claim_head union
claims-# select 'transaction ' as table_name, count(*) as row_count from transaction;
table_name | row_count
--------------+-----------
vehicle | 14355
transaction | 170127
claim_head | 23
claim | 6726
incident | 6302
policy | 4739
person | 12538
claimant | 16211
damage | 0
driver | 6093
witness | 3499
passenger | 572
(12 rows)
claims=#
In a live system we would be receiving data very frequently and we would usually run queries on the most recent data. To do this we often use
claims=# \i up_to_date.sql
CREATE TABLE
INSERT 0 1
UPDATE 6726
UPDATE 6726
UPDATE 6726
UPDATE 23
UPDATE 23
UPDATE 16211
UPDATE 16211
UPDATE 0
UPDATE 0
UPDATE 6093
UPDATE 6093
UPDATE 6093
UPDATE 6093
UPDATE 6302
UPDATE 6302
UPDATE 572
UPDATE 572
UPDATE 572
UPDATE 572
UPDATE 12538
UPDATE 12538
UPDATE 4739
UPDATE 4739
UPDATE 170127
UPDATE 170127
UPDATE 14355
UPDATE 14355
UPDATE 3499
UPDATE 3499
UPDATE 14355
DROP TABLE
claims=#
Transactions will now be distributed, realistically, over the last three years, with the latest transactions being dated today. Other dates throughout the system will have been adjusted to be consistent with the transaction dates.