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]) |INSERT| |DELETE|

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

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" violation

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 [ , attributes ])

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 (Table 2).

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 be incomplete.

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