databases - PowerPoint

Document Sample
databases - PowerPoint Powered By Docstoc
					  Transportation Safety
   Databases - Accident
Records and Exposure Data

     Special Library Association
     presentation June 9, 2003

              Deborah Bruce, Ph.D.
  Safety Studies and Statistical Analysis Division
       National Transportation Safety Board
  Federal Transportation
     Accident, Incident, Activity, Commodity
     Degree of injury, Property damage,
     Quantity of released substance
     Type of occurrence

Modal Differences
     Aviation, Highway, Pipeline, Railroad,
     Marine, Intermodal

Analytic Purpose
     Need to normalize for comparisons
The Purpose of Accident and
        Injury Data

• Determine the severity of an event

• Provide a basis for prevention / mitigation strategies

• Aid in calculating “cost” of transportation accidents

• Basis for setting priorities and resource allocation
        Haddon Matrix
Phase of Event
      Precrash, Crash, Postcrash

Risk Factors
      Human, Vehicle, Physical Environment
      Socioeconomic Environment
       Safety Report
   Transportation Safety

       All modes represented, 18 Accident
  and Incident databases that represented the
most frequently used by NTSB (Listed in handout)
        Database Review
•   Representativeness
•   Accuracy
•   Timeliness
•   Completeness
•   Intermodal Compatibility
•   Budget and Staff
          User Interviews
• Air Transport Association, Regional Airline
  Association, Allied Pilots Association,
  Aircraft Owners and Pilots Association, Flight
  Safety Foundation, Briling and Associates,
  AAA Foundation for Traffic Safety, American
  Trucking Association, Insurance Institute for
  Highway Safety
• Gas Research Institute, American Petroleum
  Institute, Association of Oil Pipelines
• Association of American Railroads, American
  Short Line Railroad Association, Union
  Pacific, Burlington Northern, CSX
• NASBLA, National Safe Boating Council
  Lifecycle of a Database

• Development – Needs Assessment
  and Design
• Operation – Data Collection, Data
  Entry, Data Auditing, Data Analysis,
• Evaluation – External Feedback,
  Internal Feedback
 Aviation Accident Data

        a closer look
at aviation accident events
   and how we measure
      exposure to risk
    History of NTSB Aviation
         Accident Data
• Accident data starting in 1962 for airlines and
  1964 for GA, commuters and air-taxi
• 1982-1984: Expanded data collection to include
  longer narrative, more detailed sequence of
  events, supplemental forms
• 2000: Transition to fully relational SQL
  Server database – Accident Data
  Management System (ADMS). Includes all
  accident data between 1982 – present
         ADMS System
• Approximately 40,000 accidents and
  incidents from 1982 – present
• Each record can contains up to 650 data
  elements relating to personnel, aircraft and
• Data types include Alphanumeric code (e.g.,
  engine type, sequence of events codes);
  Dates/Times (e.g., event date); Integers (e.g.,
  altitude, runway length); Text (e.g., operator
  name, narrative); Multiple Response (e.g.,
  crew certification)
  What is an Aviation Accident?
An Accident is an occurrence with the operation of an
aircraft which:
• takes place between the time any person boards the
aircraft with the intention of flight until all such
persons have disembarked, AND
• any person suffers death or serious injury, OR
• the aircraft receives substantial damage (49 CFR 830)

Substantial Damage adversely affects the structural
strength, performance, or flight characteristics of the
aircraft AND
Would normally require major repair or replacement
of the affected component.
        What is an Injury?
• Fatal Injury results in death within 30
  days of an accident
• Serious Injury
  - requires hospitalization for more than
  48 hours, commencing within 7 days
  - results in a fracture of any bone
  - causes severe hemorrhages, nerve,
      muscle, or tendon damage
  - involves any internal organ
  - involves 2nd or 3rd degree burns or
      burns affecting >5% of the body
 What types of Aircraft
Operations are Included?
• Part 121 Commercial Air Carriers
   (scheduled and nonscheduled)
• Part 135 Scheduled Operations
• Part 135 Nonscheduled Air Taxis
• Part 91 General Aviation
• Public Use Operations
• Part 103 Ultralights
• Part 129 Foreign Air Carriers
• Part 137 Agricultural Operations
• Part 133 Rotorcraft External Load
What is General Aviation?
  NOT Part 121 Scheduled Air Carriers
        Part 121 Non-scheduled Cargo
        Part 135 Scheduled Commuters
        Part 135 Non-scheduled Air Taxi
        NUSC Non-US Scheduled Commercial

  IS    US Registered (N # aircraft)
        Part 137 Aerial Applications
        Part 91 General Operating Flt Rules
        PUBU Public Use Operations
        Other Work Use
        Stolen, suicides, sabotage, etc
Which Aircraft are not
in the NTSB Accident Database?
• Military aircraft
  (unless the accident also involves civil aircraft)
• Foreign-registered aircraft
• Certain public use (government) aircraft
   (as defined in 49 CFR, Part 830.5)
• Ultralights
   (powered aircraft weighing less than 254 lbs.)
• Commercial space launches
How many Aviation Accidents
 are in the NTSB Database?

 1962-1981 contains 87,048 records
 1982-2002 contains 50,184 records

   Database available in Access
How many accidents per year?
•   Part 121 accidents average than 41/yr
•   Part 121 scheduled avg 36/yr
•   Part 121 nonscheduled avg 5/yr
•   Part 135 scheduled avg 11/yr
•   Part 135 nonscheduled avg 76/yr
•   General Aviation avg 1,898/yr

• Totals around 2000 accidents per year
How Can I Get Accident Data?

• Aviation accident statistics from the web
• Investigations and Safety Studies
• Annual Review publications
• Full database is available on our web site
• Accident queries via web site
Accident Statistics
NTSB Publications
  NTSB Aviation Publications
Annual Aviation Accident Reviews
 (Commercial Air Carrier and General Aviation)
 1998 and 1999 Air Carrier and 1998 GA completed
 1999 Air Carrier and 1999 GA are being prepared
 for publication, 2000 Air Carrier in progress
Safety Studies
Transportation Safety Databases, Public Aircraft Safety,
Survivability, Aircraft Evacuations,
ATC Equipment Outages, Safety in Alaska,
Safety of Air Tour Industry, Commuter Airline Safety,
Wake Vortex Safety Issues, Flightcrew-Involved
Accidents 1978-1990
Accident Investigation Reports
        Accident Reports

History of Flight, Injury information, Damage to
Aircraft, Pertinent personnel information,
Aircraft Information, Meteorological
information, Aids to navigation, Communication,
Aerodrome information, Flight information,
Wreckage or impact information, Medical or
pathological information, Fire, Survival or
forensic aspects, Tests and research,
Organizational and management information,
Effective investigative techniques
Annual Reviews
    NTSB Website Query Tool
• Best for simple queries to find a set of
  accidents with a common basic feature, no
• Allows access to narrative descriptions and
  limited data for 1962-1981 accidents
• Results in report status, narrative, probable
  cause, contributing factors, and basic
  accident data
• Requires internet connection
Accident database query via web
NTSB Website Query Tool
Downloadable datasets from
      NTSB FTP site
• Best for more complex queries
  requiring fields not available using
  Website Query Tool
• Allows highly customized searches
• Allows data aggregation/analysis
• Requires internet connection and 1995
  or later version of MSAccess

• Avdata DIR contains a readme file,
• A diagram of the relational database (admspub.pdf),
• The coding manual (codman.pdf),
• A copy of the SQL database structure,
• A subdirectory of .mdb databases (years 1982-2003),
• Weekly updates for current data
• Within each access database (as a table), there is a
       Data dictionary and translation tables
Accident Investigation Process -
How is the Accident Database
• Accident data input from accident safety
  investigator via distributed data
  collection software
• Preliminary accident record appears
  within 5-10 days
• Factual data entered in about 6 months
• Final report with probable cause 12-18
  months following the accident
Aviation Safety Investigator’s
Data Entry Process
Types of Investigations
  •   Majors
  •   Field
  •   Limited
  •   Foreign

  • What does the
    accident number tell us?
     NTSB Number Decoder

• 3-letter code: Office Identifier, ATL = Southeast
  regional office in Atlanta
• 2-digit fiscal year, 02 = 2002
• 1-letter type of investigation code, L = Limited
• 1-letter transportation mode code, A = Aviation
• 3-digit sequential numbering, 084 = 84th accident
  in 2002 for that regional office
Products of Investigations

• Final Accident Report
• Docket records
• ADMS Database records
 What, When, and Why
    of Accidents
• WHAT – “Occurrences”
• WHEN – “Phase of Flight”
• WHY – “Sequence of Events”
  What’s the meaning of an
 Accident Rate Calculations
and how do we quantify risk

             NTSB Accident Counts
           = ____________________
             FAA Activity Measures
Aviation Accident Rates by Sector
    (per 100,000 Flight Hours)
GA Accidents, Flight Hours
Estimated GA Flight Hours
 by Category (in Millions)
Aviation Flight Hours by Type
  (in Hundred-Thousands)
Revision of FAA Air
Taxi Flight Hour Estimates
Total Aircraft Occupants
    and Injury Totals
GA Aircraft Shipped
Estimated Active GA Aircraft
  Highest Certificate for
Accident Involved GA Pilots
Survivability of Accidents
Involving Part 121 U.S. Air
   Carrier Operations,
       Occupant Survival for All
         Part 121 Accidents


     Survivable Accidents

• Forces transmitted to occupants
  through their seat and restraint systems
  cannot exceed the limits of human
  tolerance for abrupt accelerations
• Structure in the occupants’ immediate
  environment must remain substantially
  intact to the extent that a livable volume is
  provided throughout the crash
        Occupant Survival
for Survivable Part 121 Accidents



       Report Definition of a
        Serious Accident

• At least one serious injury or fatality
• Substantial damage or complete
  destruction of an aircraft
• Fire (Pre-crash or Post-crash)
        Occupant Survival for
      Serious Part 121 Accidents




• 96 percent survive all accidents
• 56 percent survive serious accidents
• 77 percent survive serious-survivable
 Data Sources Related
     to Accidents
NTSB Data Recommendation
NTSB Docket System of Records
Other Sources of Related Safety Data
             NTSB Safety
         Since 1967 Total Recs Issued = 12,026

                    16% 10%

                   37%      16%

Total Aviation Recs 4,419
Currently 342 Open
        NTSB Public Docket
• Archive of materials pertinent to an
• History: Microfiche, Mainframe based,
  Docket Management System (DMS)
• Public Access
  – Public Reference Room at NTSB HQ
  – Online Request Form
  – Contractor: General Microfilm 301-929-8888
    Items in the NTSB
       Public Docket
•   Preliminary report
•   Hearing documents
•   IIC factual reports
•   Group chair factual reports
•   Studies by board staff
•   Board reports
•   Supporting Material
      NTSB Public Docket
• Supporting Materials may include
  – Pilot/operator aircraft report
  – Witness statements or records of
  – Maps/charts
  – Excerpts from manuals
  – Weather reports
  – Transcripts of radio communications
  – Statement of party representatives
      NTSB Public Docket
• Supporting Materials may include
  – Reports from other federal agencies
  – Reports from state or local agencies
  – Submissions or correspondence from
    parties to the investigations
  – Flight Data Recorder (FDR) data
  – Cockpit Voice Recorder (CVR) transcript
  – Release of wreckage form
  – Toxicology reports
      NTSB Public Docket
• Supporting Materials may include
  – Transcripts from hearings/depositions
  – Petitions for reconsideration
  – Related safety recommendations and/or
    safety studies
  – Drawings (e.g., engineering sketches)
  – Photographs pertinent to the accident
  – Data files
  – Other materials (flight plans, fueling
    records, load manifests, etc.)
      NTSB Public Docket
• Supporting Materials may include
  – Reports from other federal agencies
  – Reports from state or local agencies
  – Submissions or correspondence from
    parties to the investigations
  – Flight Data Recorder (FDR) data
  – Cockpit Voice Recorder (CVR) transcript
  – Release of wreckage form
  – Toxicology reports
  What is NOT on the NTSB
       Public Docket
• Autopsy reports or photos
• Proprietary, confidential or “trade
  secret” items
• Contracts or dollar amounts that are
  irrelevant to understanding the event
• Privacy-protected material: names,
  addresses, SSN, etc
• Preliminary documents/notes
Aviation Safety Data Sources
      Other than NTSB
  AirClaims CASE2
  ICAO ADREP 2000 (
  BTS Activity Data (
National Aviation Safety Data
      Analysis Center
NASDAC provides the public with access to
several of the principal aviation safety data
and information sources the Federal Government
 uses for various purposes.
• FAA Air Registry
• Aviation Safety Reporting System
• BTS Airline Traffic and Capacity
• FAA Accident / Incident Data System
• Near Midair Collision System
• NTSB Safety Recommendations w/ FAA Responses
• NTSB Aviation Accident Database
• World Aircraft Accident Summary
       AirClaims – CASE2
• Airclaims – 2 major insurance agencies do
  claims adjustment for 40% of airline
• Client Aviation System Enquiry (CASE)
  software and subscription service
• Worldwide accident/loss information
   – Records back to 1948
   – Full histories of approx 80K aircraft,
     including transfers, hours, cycles, weight,
     seating, etc.
      ICAO Reporting
 for ADREP 2000 System
• Intern’l Accident Data is collected by ICAO
• ICAO’s Accident Investigation and Prevention
     (AIG) Section maintains ADREP 2000
• Member Countries Report based on Annex 13
• Reporting Criteria = max wt over 2,250 kg
• Bimonthly ADREP Summary of Accident
   and Incident Reports
FAA Civil Aerospace Medical
      Institute (CAMI)
  CAMI Library has Online Catalog
         Tech Reports
         Archived & Historical Documents
         Cataloged Websites
  CAMI provides NTSB with toxicological results
         and maintains a confidential tox database
  Aviation Statistical Handbook
         Near Midair Collisions, Operational Errors,
         Pilot Deviations, Vehicle/Pedestrian
         Deviations, Surface Incident Data,
         Flight Assists, Accident Data
 Bureau of Transportation
        Airline Activity Data

Scheduled Part 121 Operators report monthly
Using Form 41, T100 and T-1000f forms

Scheduled Part 135 Operators report
Quarterly using Form 298-C