Fatality Analysis Reporting System

Question

The National Highway Traffic Safety Administration has a database of fatal crashes called Fatality Analysis Reporting System (FARS) http://www.nhtsa.gov/FARS One can download each years data going back to 1975.

I have been assigned to build a SQL database of the entire FARS. Unfortunately over the course of the past 38 years there have been many changes to the data structure. What is the best way to build a queryable copy of the entire data?

Answer 1

First you will need to define the schema of your final database and determine how you will map each file to your schema.

Then I recommand using ETL tool (Extract Transform and Load) such as Talend Open Studio (TOS) or Penthao (both are open source solution). Using such tools will allow you to:

1. automate the process (and not process each file individually)
2. create mapping for each file between the original format and your SQL schema

In the case of talend you will do something like this:

1. find the right component to connect to the input file (I don't know the format made available by FARS)
2. connect this component to a tmap
3. connect the tmap to a tSQLout component to update your database

Then using a tLoop and a putting your file name as a variable, you can loop through the different file and process them and define a different route depending on the file format from FARS (and update the tmap accordingly).