Database for our reports

We will use a MySQL database to obtain data for our reports. The database is a subset of public domain data that can be downloaded from http://dl.flightstats.us. The original download is 1.3 GB, so we deleted most of the tables and a lot of data to trim the download size considerably. MySQL dump of the modified database can be found as part of this book's code download at http://www.packtpub.com/files/code/8082_Code.zip.

The flightstats database contains the following tables:

• aircraft

• aircraft_models

• aircraft_types

• aircraft_engines

• aircraft_engine_types

The database structure can be seen in the following diagram:

Note

The flightstats database uses the default MyISAM storage engine for the MySQL RDBMS, which does not support referential integrity (foreign keys). That is why we don't see any arrows in the diagram indicating dependencies between the tables.

Let's create a report that will show the most powerful aircraft in the database. Let's say, those with horsepower...