3 Data structures
The Ministry of Fisheries designed the Non-Fish Incidental Catch
Reporting Form in 1996 in consultation with the then New Zealand
Fishing Industry Board. Since this time these data have been stored
in a Microsoft Access database held by the Ministry of Fisheries. In
2003 the responsibility of this database was transferred to NIWA and
ported into a Empress Relational Database Management System (RDBMS).
It is this current implementation of the design and structure of
nonfish_bycatch that is described in this document.
3.2 Database description
The ERD for nonfish_bycatch (Figure 1) shows the logical structure
of the database and its entities (each entity is implemented as a
database table) and relationships between these. All of the table's
attributes are shown in the ERD. The underlined attributes represent
the table's primary keys [A primary key is an attribute or a
combination of attributes that contains a unique value to identify
that record.]. 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 has been selected to be represented in the database. Each
attribute of a table is a defining property or quality of the table.
The tables in the nonfish_bycatch database also contain special
attributes, called foreign keys [A foreign key is any attribute, or a
combination of attributes, in a table that is a primary key of
another table. Tables are linked together through foreign keys.].
Section 5 shows a listing of all the nonfish_bycatch tables as
implemented by the Empress RDBMS. 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:
This prevents records with duplicate key values from being
inserted into the table; e.g., a trip with an existing trip key.
The nonfish_bycatch database is implemented as a relational
database. That is, 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. All relationships in nonfish_bycatch are of the type
This is shown in the ERD by connecting a single line from the
"parent" table; e.g., t_trip, to the "child"
table; e.g., t_station, with crows foot (indicating "many")
pointing to the "child". For example, consider the
relationship between the tables" t_trip (the "parent"
table) and t_station (the "child" table). Any one trip in
t_trip can have one or more associated stations in t_station, but any
one station can only be a part of one trip. Note that the word "many"
applies to the possible number of records another is associated with.
For a given instance, there might be zero, one, two, or more
associated records, but if it is ever possible to have more than one,
we use the word "many" to describe the association.
Note that the one-to-many relationships can be either mandatory or
optional. The optional relationship, denoted in the ERD by the symbol
"O" at one or both ends of the relationship line, means
that a record does not have to have any associated records.
Conversely, the mandatory relationship denoted in the ERD by a bar
symbol across the relationship line, means that a record has to have
at least one associated record. For example, if we consider again the
one-to-many relationship between the tables t_trip and t_station,
which has a mandatory "one" and an optional "many".
This means that one trip record can have zero or more (many) stations
within it, but one station must have one, and only one, associated
record in the trip table.
These relationships are enforced in the database by the use of
referential constraints [Also known as integrity checks.].
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
i. a parent record
i.ii. The parent
record is altered so the relationship is lost;
i.iii. Or a child
record is entered without a parent record.
All constraints in nonfish_bycatch prevent these from occurring.
Constraints are shown in the table listings by the following example:
Foreign key, refer t_trip(trip_key)
This means that the value of the attribute trip_key in the current
record must already exist in the parent table t_trip or the record
will be rejected and an error message will be displayed: All tables
in the database are indexed. 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
Details for each trip are held in the table t_trip. Each trip is
uniquely identified by a trip code, stored as the attribute trip_key.
Similarly, details for each station are held in the table
t_station. Each station is uniquely identified by a station code,
stored as the attribute station_key.
Details for each catch are held in the table t_catch. Each catch
is uniquely identified by a catch code, stored as the attribute