JOUR772 Documentation File

Recreational Boating Accident Data, Maryland, 2000-2009

This is a file prepared by Ira Chinoy for students in JOUR 772

 

Data obtained from:  National Institute for Computer-Assisted Reporting (NICAR)

NICAR’s source for the data: U.S. Coast Guard Boating Accident Report Database (BARD).

Online info:

NICAR data site:  http://www.ire.org/datalibrary/databases/BoatAcc/

U.S. Coast Guard Boating Safety Resource Center: http://www.uscgboating.org/default.aspx

Date obtained:  July 2011

Access database name: Boating_MD_2000-2009.accdb

Period covered by data: 2000-2009

Geographic area covered: Maryland

Type of data:  Boating accidents; see documentation for explanation of the type and severity of accidents that are including in this database.

Changes made to database since it was obtained by the College of Journalism:

The data supplied by NICAR came in a file of boating accidents for the US for 1995-2009.  The data for accidents in Maryland from 2000 to 2009 was imported into an Access database named "Boating_MD_2000-2009.accdb". The importing involved the following conversions:

 

The file primary.dbf became the table PRIMARY and only has records from Maryland for 2000-2009.

 

The file vessel.dbf became the table VESSEL and only has records associated with the accidents in PRIMARY.

 

The file dead.dbf became the table DEAD and only has records associated with the accidents in PRIMARY.

 

The file injury.dbf became the table INJURY and only has records associated with the accidents in PRIMARY.

           

Suggestions and caveats:

There are several layers of documentation that go with this database. 

1). The first to read is this document, which was assembled by your instructor as a guide. 

2). The second one to read is the Readme.txt file in the same folder that contains the data. 

This is documentation from NICAR about the Coast Guard data – which the Coast Guard calls BARD, for “Boating Accident Report Database.”

The Readme.txt file talks about some of the other files you will see as they come from NICAR.

The Readme.txt file deals with the entire database of US boating accidents from 1995 to 2009 (you have only Maryland for 2000-2009 from the larger data set) and a second data set of boating accidents before 1995 (which you do not have here).

The Readme.txt file has important notes about what is and isn’t in the data you have.

IT IS ESSENTIAL THAT YOU READ THIS FILE. 

(NICAR also provides a standard LEGAL.txt file that covers issues related to sale of the data and other legal issues.)

3). The next set of files to examine are the record layouts prepared by NICAR. 

These are in four spreadsheets:

(a). primary_layout.xls

(b). vessel_layout.xls

(c). injury_layout.xls

(d). dead_layout.xls

Though the data comes originally from the Coast Guard, NICAR has added a field named ID as the first field and a field ACC_YEAR as the second field in each table to make the tables easier to use. 

The ID field combines the year of the accident and the report number (contents of the ACC_YEAR and REPORTNO field) to create a unique identifier when you do queries that join tables (more on the joins below).

IT IS ESSENTIAL THAT YOU LOOK AT THESE FILES.

They contain important information about some of the fields, and the dates when use of those fields started or stopped.

4). The fourth layer of documentation is the Coast Guard’s own documentation, which is in the following files:

(a). ReleasableBARD2009Notes.doc

(b). ReleasableBARD2009DatabaseDisctionary.doc

(c). Boating_Statistics_2009.pdf

LOOK AT THESE FILES: They have caveats about the data as well as detail about the meaning of various fields and a large number of statistical summary reports. Note that the Coast Guard summaries are for the NATIONAL data set; you have only a slice of one state for 10 years of data.

 

BIG CAUTION: 

There is a field dealing with vessels in each of the four tables, but it does not mean the same thing across all four tables.  

Specifically, the field named NO_VESSEL in the PRIMARY table has a number indicating how many boats were involved in the accident.

In the other tables, the field VESSEL contains a letter (A, B or C, for example) to identify the different boats in a single accident.

 

BIG CAUTION NO. 2:

Records from PRIMARY link to any other single table using the ID field. 

But tables other than PRIMARY, when linked to each other, need to be joined on both the ID field (indicating which accident and year) and the VESSEL field (indicating which boat the dead or injured person was on).

We will review these joins in class since doing them improperly will yield improper results (including the duplicate counting of dead or injured).

 

BIG CAUTION NO. 3:

We will also talk about various ways of joining tables – the kind of join we learned at the beginning of the semester (called an “inner join”) and other joins called “left” or “right” joins that can be used to compensate for records in one table that have no corresponding records in another (say, a vessel that did not have a dead or injured person).

 

BIG CAUTION NO. 4:

Only use tables in a join that you need to answer the question at hand. 

Adding more tables into the query grid requires caution and may require you to do these “left” or “right” joins – or else you may inadvertently miss certain records.

And for any tables you add to the query grid, you must link them.  Otherwise you will get the dreaded “Cartesian” join and meaningless results.

 

BIG CAUTION NO. 5:

In addition, avoid using the INJURY and DEAD tables in a single query. 

This can be done, but it is tricky, and we can talk about how best to go at accidents that had both injuries and fatalities.

Updated August 6, 2011