Home Aviation Reports and Queries   AirLine Reports and Queries   Fee Based Services   Database Description

Home
Database Description

The relational database is logically grouped into nine subject areas, roughly corresponding to the data sources. The data is continually updated once a month as new data becomes available. The tables are generally implemented as supplied from the data sources with additional normalization. Referential constraints are enforced to insure data consistency and indexes are defined to enforce unique constraints and to improve performance. Very efficient, high speed text searches are supported. The database is implemented using a Postgres engine, version 8.0.2.

Currently the database contains over 200 tables, 190 indexes, and over 200 million rows.

Every effort is made to insure the data is correct, but there is no guarantee regarding accuracy or timeliness. In no event should this data be used for flight operations. Concorde

This page contains
Schema Information

The content of the database consists of nine subject areas:
NNumber
Civil aircraft registered with the FAA including fixed wing aircraft, helicopters, gliders, and experimental aircraft.
Airman
Includes pilots, ground and flight instructors, flight engineers, navigators, dispatchers, mechanics, riggers, and control tower operators registered with the FAA including both domestic and foreign airman.
Operators
Aviation related businesses including air agencies (FBO's, Airlines, etc.) , pilot schools, training centers, mechanic schools, repair stations.
SDRs
Service Difficulty Reports filed with the FAA.
Accidents
Aircraft accidents and incidents reported to the National Transportation Safety Board, involving both private and commercial aircraft.
Accidents/Incidents
Aircraft accidents and incidents reported to the Federal Aviation Adminstration, involving both private and commercial aircraft.
Airline On Time Performance
Commercial air carrier information including airline on time performance.
Air Carrier Capacity and Utilization
Scheduled and non-scheduled carrier capacity and utilization for passengers, freight, and mail.
Air Carrier Financial
Air carrier expenses, profit, and loss including detail fuel costs.

The overall design goals for the schema design are to provide obvious, consistent, and efficient access to the data. The AviationDB database contains three types of tables:
A reflection of the data as defined by the various data sources.
Since the source data, in most cases, is not normalized, some of the original tables have been redesigned to provide a primary key, eliminate repeating fields, make the data dependent on the key, etc. In the process new tables where created and others eliminated.

Highly denormalized "Data Warehouse" tables.
These tables are derived from the first set, but are optimized for quick response for the provided free queries. They are highly de-normalized and contain a sub-set of the information.

Code tables.
They are generated from existing documentation or supplied by the data source. The data in the underlying tables is generally constrained to those code tables.

Note that the data as supplied from the various government sources contains some inconsistant data. The data has been "cleansed" where appropriate and feasible.


Data Sources

The table below lists the names of the government agencies that provide the data, the URL where the source data can be found, and the URL where documentation can be found.

SUBJECT AGENCY DATA SOURCE
NNumber FAA http://www.faa.gov/licenses_ certificates/aircraft_certification/ aircraft_registry/releasable_aircraft_download/
Airmen FAA http://www.faa.gov/licenses_certificates/airmen_certification/releasable_airmen_download/
Service Difficulty Reports (SDR) FAA http://av-info.faa.gov/dd_sublevel.asp?Folder=\SDRS
Operators (FBOs, Airlines, etc.) FAA http://av-info.faa.gov/dd_sublevel.asp? Folder=%5CAirOperators
Airline On Time Performance BTS http://ww.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time
Air Carrier Capacity and Utilization BTS http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=293&DB_Short_Name=Air%20Carriers
Air Carrier Financial BTS http://www.transtats.bts.gov/Tables.asp? DB_ID=135&DB_Name=Air Carrier Financial Reports %28Form 41 Financial Data%29&DB_Short_Name=Air Carrier Financial
Operators (Mechanic Schools) FAA http://av-info.faa.gov/dd_sublevel.asp? Folder=%5CMechanicSchools
Operators (Pilot Schools) FAA http://av-info.faa.gov/dd_sublevel.asp? Folder=%5CPilotSchools
Operators (Repair Stations) FAA http://av-info.faa.gov/dd_sublevel.asp? Folder=%5CRepairStations
Operators (Training Centers) FAA http://av-info.faa.gov/dd_sublevel.asp? Folder=%5CTrainingCenters
Medical Examiner FAA http://ame.cami.jccbi.gov/
Accident NTSB http://www.ntsb.gov/avdata/Access/
Accident/Incidents FAA http://av-info.faa.gov/dd_sublevel.asp?Folder=%5CAID

Legal Copyright 2001 - 2011 AviationDB.com Corp. All Rights Reserved Contact Us