3 Data Structures
3.1 Table relationships
This database encompasses four tables. The ERD for iki (Figure 1)
shows the logical structure [ Also known as a database schema ] of
the database and its entities (each entity is implemented as a
database table) and relationships between these tables and tables in
other databases. All of the table's attributes are shown in the ERD.
The underlined attributes represent the table's primary key [A
primary key is an attribute or a combination of attributes that
contains an 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.
Note that Figure 1 shows the main tables only. All of the tables
in the iki database have some 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.], which contain standard NIWA
fisheries codes, such as species and gear_meth. These attributes
provide links to the rdb (research database) database, which contains
the definitive list of standard codes. External databases such as
rdb, are shown in the ERD (Figure 1), inside a box of dashed lines.
Section 5 shows a listing of all the iki tables as implemented by
the Empress DBMS. As can be seen in the listing of the tables, a
table's primary key has an unique index on it. Primary keys are
generally listed using the following format:
Indices: UNIQUE index_name ON ( attribute[, attribute])
where attribute(s) make up the primary key (the key attributes)
and the index name is the primary key name. Note that the
typographical convention for the above format is that square brackets
 may contain more than one item or none at all. These prevent
records with duplicate keys from being inserted into the tables;
e.g., a trip record with an existing trip number.
Figure 1: Entity Relationship Diagram (ERD) of
the iki database.
The iki database is implemented as a relational database. That is,
tables are linked to one another by their relationships. There is
only one type of relationship between the tables in iki, and that is
oneto- many [ 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 page in t_page can have many fish in t_fish
but one fish can only come from one page.]. This is shown in the ERD
by connecting a single line (indicating "many") from the
child table (e.g., t_page) to the parent table (e.g., t_set) with an
arrow head (indicating "one") pointing to the parent.
Every relationship has a mandatory or optional aspect to it. That
is, 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_page and it's child table t_fish. The symbol "o" by the
child t_fish means that t_fish can have zero or many records, while
the bar by the parent t_page means that for every fish record there
must be a matching page record.
These links 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: 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:
Referential: constraint name (attribute[, attribute])
parent table (attribute[, attribute]) 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
Referential: t_fish (species) INSERT rdb : curr_spp (code)
This means that the value of the attribute species in the current
record must already exist in the parent table curr_spp of the rdb
database or the record will be rejected and the following message
will be displayed:
*** User Error: insert constraint "invalid species"
The database listing (Tables 1-4) show that the tables also have
indices on many of their attributes.
That is, attributes that are most likely to be used as a searching
key have like values linked together so as to speed up searches.
These indices as listed using the following format:
Indices: NORMAL (2, 15) index_name ON (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 to index storage.
3.2 Database design
The main table is t_trip (Table 1). This holds information for
each trip made by a vessel. Each record is identified by a trip code,
represented by the attribute trip_no, which is the primary key for
this table. The trip_no was initially defined as a unique integer
that was incremented for each subsequent trip. However, the advent of
simultaneous catch sampling programmes in different areas led to the
prefixing of a 4-character area code to some trip numbers in order to
maintain the uniqueness of the attribute. Another attribute in this
table, gear_meth uses the NIWA fisheries standard code, and therefore
has a referential to the rdb database. This code can be inserted into
this table if and only if it exists in the rdb table meth_codes.
There are five attributes in the t_trip table that are default
values for the station record, including the gear method, hook type
and size code, and snood length and distance code fields. With the
exception of gear method, these codes are specific to the longline
fishery, the iki database's initial requirement. The inclusion of
other fishing method types associated with the snapper fishery into
longline has made these defaults irrelevant for many records.
One of the changes brought about by the inclusion of other snapper
fishing methods is the requirement to recorded the landed catch
weight of not just snapper, but any other bycatch species as well.
The recording of bycatch landed weights was not a requirement for
these initial catch sampling programmes, never the less, they were
recorded in many cases and have been stored in the t_trip_catch table
In order to catch snapper, each fishing effort has the expended
some effort, specifically: set some longlines, shoot some trawls, or
deploy some seines. The information for each unit of effort (one
longline, one trawl, or one danish seine) is held in t_effort (Table
3) with primary key of trip_no and effort_no, where effort_no is a
sequential number assigned to each subsequent unit of effort.
The time frame of hauling the longline and recording the catch is
recorded by page, (maximum of 60 fish per page). A start time and end
time, are entered at the top of each page, these two times are stored
in the table, t_page (Table 4). The primary key to this table is
trip_no, set_no, species, and page_no. For those catch sampling
programmes where page numbers are irrelevant, a default page number
of 1 is used.
From each unit of effort, the fish caught are measured and details
of the fish status, which may include alive state, hook position,
wounding, external rupture and baratrauma state. The bait species is
recorded in the bait attribute, where attainable, however with mixed
baits used for the same set, it is often not possible to ascertain
the bait by fish caught. These measurements generate records, which
are stored in the table t_fish (Table 5). All individual fish are
recorded, so each fish has one record in t_fish. Length may be null,
for instance where there is a wounding from a predator, the fish may
Note, the table t_fish may have more
than one fish of the same length, with the entire same fish
characteristics recorded, within the same data set. Therefore the
table t_fish is an exception to the standard NIWA table, as it does
not have any primary key; i.e., duplicate records are valid within
the t_fish table.
A feature that is unique within the snapper fishery is the quality
grading of whole fish for the "iki jime" market. The
snapper catch is therefore separated based on this grading. The total
number of bins and the number of bins sampled of each grade of
species for each unit of effort is recorded in the t_catch table
(Table 6). Actual weights are not recorded due to the lack of
suitable scales onboard the vessels. Weights can be estimated by
multiplying the number of bins (no_bins) by the average bin weight
(bin_wgt). The codes used for the quality grading are recorded in the
table t_grade_code (Table 7).
Experiments have been carried out on he effects of longline hook
appendages on incidental mortality and catch rates of snapper. These
experiments consisted of a variety of shots of longlines with blocks
of 50 hooks, with each block having a different treatment such as
hook size and bait type. Details of each of the shots are recorded in
the table t_hook_shot (Table 8), including the date of the shot and
the parameters of the longline used such as backbone type and
breaking strain, hook type, and snood length and distance.
The results of each hook set in the longline shot are recorded in
the table t_hook_append (Table 9).
Each hook is recorded as being part of a block of fifty that
received the same treatment. Hooks are recorded as being empty, still
baited, or successful in catching a fish. If a fish is caught, the
species, fish length, fish condition and position of the hook is