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
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
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
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:
*** User Error
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.