3 Data Structures

3.1 Table relationships

The ERD for aer_sight (Figure 1) shows the logical structure 3 of the database and it's entities (each entity is implemented as a database table) and relationships between these tables and tables in other databases. This schema is valid regardless of the database system chosen, and it can remain correct even if the Database Management System (DBMS) is changed. Each table represents an object, event, or concept in the real world that is selected to be represented in the database. Each attribute of a table is a defining property or quality of the table. All of the table's attributes are shown in the ERD. The underlined attributes represent the table's primary key 4.

The aer_sight database is implemented as a relational database; i.e., each table is a special case of the mathematical construct known as a relation and hence elementary relation theory is used to deal with the data within tables and the relationships between them. There are three types of relationships possible between tables, but only two exist in aer_sight: one-to-many 5, and one to one. These relationships can be seen in ERDs by connecting a single line (indicating 'many') from the child table; e.g., t_flight, to the parent table; e.g., t_flight_group, with an arrowhead (indicating 'one') pointing to the parent.

Only one to many relationships are generally acceptable in a normalised database but, in the present case, one to one linkages are also used (see Figure 1). There are two main reasons why; to increase accessibility; and to avoid large unwieldy tables. A number of the tables which are linked by one to one relationships can be regarded as ancillary tables, providing either specialised information which is used infrequently (e.g., t_astro_moon), or subsets of time series groupings which cannot be readily obtained using standard EMPRESS routines (e.g., t_flt_days). The five main tables contain data collected by the pilots and it is convenient to restrict them accordingly - grouping ancillary data into tables dedicated to specific tasks or selected data precludes development of huge, unwieldy tables, making the available information more transparent to the user. Table comments in Section 5 closely define the uses of all the database tables.

Every relationship has a mandatory or optional aspect to it. If a relationship is mandatory, then it has to occur at least once, while an optional relationship might not occur at all. For example, in Figure 1, consider that relationship between the table t_flight and it's child table t_school_sight. The symbol 'o' by the child t_school_sight means that a flight record can have zero or many school sighting records, while the bar by the parent t_flight means that for every school sighting record there must be a matching flight record.

These links are enforced by referential constraints 6. Constraints do not allow orphans to exist in any table; i.e., where a child record exists without a related parent record. This may happen when: a parent record is deleted; the parent record is altered so the relationship is lost; or a child record is entered without a parent record Constraints are shown in the table listings by the following format:

Note that the typographical convention for the above format is that square brackets [] may contain more than one item or none at all. Items stacked between vertical lines | | are options of which one must be chosen.

For example, consider the following constraint found in the table t_flight:

This means that the value of the attribute flt_grp in the current record must already exist in the parent table flt_grp or the record will be rejected and the following message will be displayed:

Section 5 lists all the aer_sight tables as implemented by the Empress DBMS. As can be seen in the listing of the tables, a table's primary key has a unique index on it. Primary keys are generally listed using the following format: 6 Also known as integrity checks.

Indices: UNIQUE index_name ON (attribute[, attribute])

where attribute(s) make up the primary key and the index name is the primary key name.

These prevent records with duplicate keys from being inserted into the tables; e.g., a record with an existing flt_grp number.

The database listing (Section 5) show that the tables also have indices on many attributes.

That is, attributes that are most likely to be used as a searching key have like values linked together to speed up searches. These indices are listed using the following format: Indices: NORMAL (2, 15) index_name ON (attribute[, attribute]) Note that indices may be simple, pointing to one attribute or composite pointing to more than one attribute. The numbers "...(2, 15)..". in the syntax are Empress DBMS default values relating to the amount of space allocated for the index.

Due to difficulties in the collection of these data which are recorded on a voluntary basis by pilots, some historic data will fail referential constraints. This is because it has not been possible to associate a school sighting with a particular flight, hence these records were entered into t_school_sight with flt_num equal to zero. Some sets do not have a set time recorded, and therefore cannot be linked back to a sighting in the table t_school_sight.

3.2 Database design

Tables can be summarised under three categories: the main tables, which are five in number and contain the main body of the data; the code tables which enable decoding of the numeric codes used in the main tables - there are nine of these, identified by the suffix "code"; and accessory tables which either allow data extracts to follow particular time frames or contain environmental data used in some analyses - there are six of these.

3 Also known as a database schema.

4 A primary key is an attribute or a combination of attributes that contains an unique value to identify that record.

5 A one-to-many relationship is where one record (the parent) in a table relates to one or many records (the child) in another table; e.g., one landing in t_flight_group can have many catches in t_flight but one catch can only come from one landing.

6 Also known as integrity checks.