6. DATABASES AND DATA MANAGEMENT (Contd.)

6.9 DESIGN OF FISHERIES SECTOR PROFILES

A fisheries sector profile may be considered an extended “frame survey”, where it has been attempted to describe all elements of the fisheries sector of an administrative division (a province) or the entire country, depending on the size of country.

A fisheries sector profile contains:

• Summary of the detailed catch and effort data in the fisheries database managed by the directorate of fisheries (the database of this manual).

• Additional data, relevant to management and development of the fisheries sector, which is not in the catch effort database.

The additional data can often be found in other databases. They are not data collected by the programme described in this manual, but a recompilation of existing data.

The fisheries sector profiles are used by several groups of fisheries workers:

• Fisheries managers for an overall update of their background knowledge of the sector.

• Decision makers in fisheries (politicians and governments) for general background knowledge.

• Designers of data collection programme for stratification purposes.

• The fishing industry.

• The public in general.

Below follows a tentative checklist of additional data, which may or may not be included in the fisheries sector profiles, dependent on the local situation. The list may be supplemented and/or be reduced, to meet local needs.

6.10 AN EXAMPLE FISHERIES DATABASE

The “FDDB” (Fisheries Demonstration DataBase) was constructed as a tool for teaching the theory and practice of fisheries databases. It was used by the author in the implementation of a fisheries database for Viet Nam's commercial marine capture fishery, the “VIETFISHBase”. FDDB is a simplified and reduced version of VIETFISHBase. However, FDDB contains all the essential features of the commercial fishery part of the database, except for spatial information, such as landing places and fishing grounds by statistical rectangles and divisions of rectangles. Nor does FDDB contain information about landing places or gears. Only “provinces” and “fleets” are considered.

This Section is presented as an example and it aims only at introducing the basic principles of fisheries databases to those who have not got experience in fisheries databases. By working through the example, you should be in better position to understand a full-scale fisheries database, which is a complex system.

FDDB is implemented in ACCESS (the database module of Microsoft Office, see Section 6.2). The database application contained a full set of data and the forms of FDDB. The FDDB was created exclusively using “wizards”. Wizards are utilities for easy generation of simple applications.

6.10.1 Tables of FDDB

FDDB comprises 10 Tables, of which 5 are look-up tables (see Figure 6.10.1). The names assigned to tables and fields of tables (columns) are assumed to be self-explanatory (It is recommended always to use more or less self-explanatory names of fields and tables).

The look-up tables all start their name with “L_”. Except for the vessel register, the only purpose of the look-up tables is to translate a code into a name in the English language. The vessel register contains another type of information, namely the definition of fleets, as it assigns each vessel to a fleet.

The field “"LgtUnit” indicates the length measurement unit, such as “MM” or “CM”, and could also have had a look-up table associated with it. However, as this is a small static list of values, it will appear as a “List-box”, which is fixed and cannot be changed easily.

All field names starting with “ID_” are keys. Whenever they appear as the first field in a table, they are unique keys for that table. All the relations are “one-to-many-relations”, between the key-fields. The other fields are “data”, such as landings and effort. Tables 6.10.2–6 present a complete example of the tables with data.

Note the hierarchy of data tables

1^st Level in hierarchy: Table: “Interview”, key “ID_Interview”

2^nd Level in hierarchy: Table: “Commer_Gr_Weigth”, key: “ID_CommGr_Wgt”

3^rd Level in hierarchy: Table: “Species_Composition”, key: “ID_Spec_Comp”

4^th Level in hierarchy: Table: “LgtFrq”, key: “ID_LgtFrq.”

Each relationship is “one-to-many” downwards in the hierarchy. The links are from the first (unique) key in the higher level to the second field in the lower hierarchical level.

Figure 6.10.1 Tables and relations of FDDB.

6.10.2 Forms of FDDB

	Figure 6.10.2 shows the main menu of the FDDB. There are two options for entry of data, namely “Interview data” and “Frame survey data”. Then there is one option for reports, which in turn leads to a new menu with many options. The FDDB menu for reports contains only the administrative reports, as the exercise behind the present annex is to create the reports, using the SQL language. “About FDDB” is a short description of the software.
	Figure 6.10.3 shows a list of the controls of the FDDB forms, that is “buttons” you can “click” on with the mouse, to instruct the FDDB. These controls are the standard Windows controls. It is tacitly assumed that the reader is already (more or less) familiar with these controls from other software packages.
Figure 6.10.2 The main menu of FDDB

	Close form. Go back to the foregoing form.
	Delete record (row in a table).
	Display/print table (report control, see Fig. 6.10.7)
	Change from interview form to length frequency form
	Show Look-Up table. Select item from the list, to enter in a field of a row.
	Scroll bar. Navigate between rows in a table. Navigate on the screen.
	Go to next record
	Go to last record
	Add new record
Record selector
	Record (row) pointer Pointer to new record (row)

Figure 6.10.3 Controls of the forms.

Figure 6.10.4 shows the form for entry of interview data, which actually contains three forms, “INTERVIEW”, “WEIGHT OF COMMERCIAL GROUP” and “SPECIES COMPOSITION” organised in a hierarchy, as illustrated in Figure 6.10.5. “Hierarchy” means here that you cannot fill in a form before all forms “higher up in the hierarchy” have been filled in. The “SPECIES COMPOSITION” form contains a control “LgtFrq” (Length Frequency). By clicking on “LgtFrq” you enter the “LENGTH FREQUENCY” form, which is the form on the fourth level in the hierarchy. Thus, the interview forms contain the structured hierarchically:

1^st level in Hierarchy: “INTERVIEW”

2^nd level in Hierarchy: “WEIGHT OF COMMERCIAL GROUP”

3^rd level in Hierarchy: “SPECIES COMPOSITION”

4^th level in Hierarchy: “LENGTH FREQUENCY”

Level 1 always has to be filled in to create a record in the database, but then you may fill in from zero to any number of level 2 forms. For each level 2 you may fill in from zero to any number of “species composition forms”, and for each of them any number of “length frequency forms”. Each form corresponds to one record. The length frequency form (Figure 6.10.6) is actually a list of forms, as each line corresponds to a record.

Figure 6.10.4 Interview form

Figure 6.10.5 The hierarchical structure of interview sub-forms

Figure 6.10.6 Length frequency form.

Figure 6.10.7 Frame survey form

The effect of the “report” control is shown in Figure 6.10.7, which shows the complete set of input data to the frame survey. The table also shows the subtotal and the estimated total number of boat days per month (= (number of boats) * (Fishing days/month)).

The forms show the key (in the actual implementation of FDDB as red characters on blue background, whereas other characters are in black and white). Normally, keys are used for the internal bookkeeping of the database, and are of no immediate interest to the user. Keys, which are (arbitrary) numbers, will normally not be shown on the forms. When they are shown on the screen forms of FDDB, it is to demonstrate the structure of the database. Keys, which are also codes, (for example the vessel code) can be of interest to the user, and they will usually be shown on the screen.

The frame survey is executed in parallel to the interviews (see Table 6.10.1), thus it is not a part of the hierarchy of landings data. As appears from Tables 6.10.1 and 6.10.2, there are five provinces and three fleets. This generates 5*3 = 15 combinations, which is the number of records (rows) in the frame survey table (see Table 6.10.2). In this simplified example, there is only one frame survey. In a realistic case, the frame survey would be repeated regularly, if infrequently. With 15 records, the present frame survey represents a complete enumeration. The interview samples, however, are sampled only from three of the five provinces, as can be seen from Table 6.10.3.

Table 6.10.1 Frame survey report showing the input data

Table 6.10.2 Example of tables with data of FDDB

Look-Up: Fleet-Names

Look-Up: Species Names

Look-Up: Commercial groups

(Note: Commercial groups are fleet and province specific)

Look-Up: Province Names

(There are 5 provinces in the country)

(First letter indicates province)

FRAME SURVEY