3 Data structures

3.1 Introduction

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 one-tomany.

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 when:

i. a parent record is deleted;

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:

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 example:

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 catch_key.