6. DATABASES AND DATA MANAGEMENT (Contd.)

6.5 REPORTS FROM THE DATA BASE

A database system includes a number of pre-prepared reports, which can be automatically produced. The reports are the output from the sampling programme. In this section, we shall deal only with one type of report, namely the reports specifying the quantitative content of the database, that is the number of records of each type, who collected the data where and when (the administrative reports). The database reports for wider distribution, the primary output from the database will be dealt with in Section 6.8.

The administrative reports are mainly for the internal evaluation of the sampling programme. They are used to modify the sampling programme in order to make the best use of the limited resources available. Furthermore, the administrative reports can be used to compute the average costs of samples for the different fleets at different locations, when combined with the accounting records of the sampling programme. One could include the costs of sampling in the catch effort database, but the costs of sampling should be kept distinct from the fisheries statistics.

When an interview form is filled in, it will contain information on who (the enumerator) filled in the form and who entered the data into the computer. This information is needed for the subsequent validation (and, if applicable, correction) of data.

The database will thus be able to produce tables, which summarises the activities of all enumerators and encoders. The database contains a look-up table with all the particulars of the entire staff of the sampling programme. A person becomes an authorised staff member of the data collection programme, by being included in the table of staff members. Only authorised persons can enter data in the database.

Apart from reports on the staff activities, the administrative reports comprises summaries of data collection activities, such as:

1. Number of interviews by fleet, month, province (or landing place).

2. Number of frame survey samples by province (or homeport) by month and fleet.

3. Number of vessel registrations by province (or homeport) by months and fleet.

4. Number of species composition samples by commercial group, fleet, month, province (or landing place).

5. Number of biological samples (e.g. length frequency samples).

To make the full use of these samples for the improvement of stratification, the administrative reports should also indicate the weight and value of landings represented by the samples.

6.6 SQL (STRUCTURED QUERY LANGUAGE)

The user who wants to apply the data for non-standard (e.g. research) purposes is not satisfied with the pre-prepared reports. She/he will need the data structured a specific way, to allow for some specific processing. It is the experience of the author that in practice the database system never contains all the reports you need. It is more or less impossible to predict every request for a report from every user. The skill to extract of data from the database is the primary skill you need to make full use of the database. Although this skill is often considered restricted to “computer experts” it is recommended that also other users acquire the basic skill to extract data.

In order to do this, the database user must master the SQL language. This is the tool by which you can extract data from a database, and by which you can do the first processing of the data, such as summation, grouping, calculating averages, etc. The part of SQL dealt with here, deals only with the creation of “Queries” (SQL contains a lot more than the query).

This section is by no means an attempt to teach the universal database language, SQL. The interested reader is referred to one of the many textbooks on SQL (e.g. Stephens et al. 1997). This section is to inform the reader with no or little background in database theory about the existence of SQL, and to give brief some idea of what the SQL is.

A query in SQL is most often constructed the following way (SQL-words are in capitals):

You may furthermore add: “GROUP BY” in case you want to sum or average over a group, say, a fleet.

One good reason for knowing some SQL is that it puts the user in a position to define exactly which data she/he wants. Should the knowledge of SQL and the database not be sufficient to complete the job, the user will be competent to complete the job with a little help from a “computer expert”.

We illustrate the SQL by a simple (hypothetical) fisheries database with only three tables. One table is an input data table “Landings” and there are two look-up tables “L_Species” and “L_Vessel_Register” (see Table 6.6.1).

Table 6.6.1 Tables of hypothetical data for demonstrating a fisheries database.

Table: Landings

Table: L_Species

Table: L_Vessel_Register

The table “Landings” contains the total landings (“Landings_Kg”) by species and by vessel (“Species_Code” and “Vessel_Code”, respectively) and the date landed (“Date_Of_Landing”). The look-up table “L-Species” converts the 15 species codes of this example, into 15 species names (“Name_Of_Species”) and it contains the price per kg, which is thus assumed to remain constant. Eventually, there is the vessel register, “L_Vessel_Register”, which in this hypothetical example contains only 4 vessels.

The Landings-table has a key field, “Landings_ID”, which is a unique number for each record. This unique number is used to secure the integrity of data, For example, landings_ID 16 and 17 are both with LOB (Lobster) landed by vessel HP83 on the 17^th January 1998. The reason could be that two different merchants bought lobsters from HP83 on the 17^th January.

The “Landings_ID” values are arbitrary numbers created by the database system. Had the “Landings_ID” not been there, there would have been two records with the same keys (“Species_Code”, “Vessel_Code” and “Date_of_Landing”) and a relational database would not accept it.

Table 6.6.2.a. Queries of hypothetical demonstration fisheries database (presented in SQL language and resulting table).

Query 1: (SQL)

SELECT

Landings.Species_Code,
Landings.Landings_Kg,
Landings.Date_Of_Landing
FROM
Landings;

Query 2: (SQL)
SELECT
Landings.Species_Code,
Landings.Landings_Kg,
Landings.Date_Of_Landing
FROM Landings
WHERE
Landings.Species_Code = ‘LOB’;

Query 3: (SQL)
SELECT
Landings.Species_Code,
Landings.Landings_Kg,
Landings.Date_Of_Landing
FROM
Landings
WHERE
Landings.Species_Code IN

(‘LOB’,‘TPR’,‘SCR’,‘WSH’)
AND
Landings.Date_Of)_Landing >
#1/12/98#;

Query 1

Query 2:

Query 3

Query 4: (SQL)
SELECT
Landings.Species_Code,
L_Species.Name_of_Species,
Landings.Landings_Kg,
Landings.Date_Of_Landing
FROM
L_Species
INNER JOIN Landings
WHERE
Landings.Species_Code IN
(‘LOB’,‘TPR’,‘SCR’,‘WSH’)
AND Landings.Date_Of_Landing
> #1/12/98#;

Query 4

Note that “IN” (Queries 3 and 4) is used to express the condition that a field value belongs to a pre-specified set of values. Note further the character “#” is used to signal that numerical characters refer to a date.

Query 5 (see Table 6.6.2.b) extends and modifies Query 4. It illustrates the calculations and the “AS”-command. The species code has been removed, and the price per kg has been added. The query also makes a calculation, namely the value of the landings “Landings.Landings_Kg*L_Species.Price_Per_Kg AS Value”. The “AS” command is used to give a new name to a variable. Note the expression “INNER JOIN .... ON ....” in Query 5, which links the records with the same species code in two tables.

Query 6 (see Table 6.6.2.b) extends Query 5. It joins Query 5 with the vessel register and adds the columns with the vessel codes.

Query 7 (see Table 6.6.2.c) simplifies Query 6, to facilitate the introduction of the concepts in Query 8.

Query 8 (see Table 6.6.2.c) extends Query 7. It illustrates the “GROUP BY”-command and the “SUM”-function. It groups the records by vessel, and computes the sum for each vessel.

Query 9 (see Table 6.6.2.c) extracts data from another query (Query 8), not from the tables. It computes the sum of all vessels. This works because each query creates its own table.

This example of SQL represents only a small fraction of the SQL commands and functions. The commands presented here are not given a comprehensive explanation. It is hoped, however, that the reader has been given the impression that SQL is a very powerful tool for data manipulation. To be able to utilise a relational database fully, an advanced user must master SQL.

Table 6.6.2.b. Queries of hypothetical demonstration fisheries database (presented in SQL language and resulting table). Continued.

Query 5: (SQL)

SELECT
L_Species.Name_of_Species,
Landings.Landings_Kg,
Landings.Date_Of_Landing,
L_Species.Price_Per_Kg,
Landings.Landings_Kg*L_Species.Price_Per_Kg
AS [Value]
FROM L_Species
INNER JOIN Landings
ON Landings.Species_Code =
L_Species.Species_Code
WHERE Landings. Species_Code IN
(‘LOB’,‘TPR’,‘SCR’,‘WSH’)
AND Landings. Date_Of_Landing>#1/12/98#;

Query 5

Query 6: (SQL)
SELECT
L_Species.Name_of_Species, Landings.Landings_Kg,
Landings.Date_Of_Landing,L_Species.Price_Per_Kg,
Landings.Landings_Kg*L_Species.Price_Per_Kg AS [Value],
Landings.Vessel_Code
FROM L_Vessel_Register
INNER JOIN (L_Species
INNER JOIN Landings
ON Landings. Species_Code = L_Species. Species_Code)
ON L_Vessel_Register.Vessel_Code = Landings.Vessel_Code
WHERE Landings.Species_Code IN (‘LOB’, ‘TPR’, ‘SCR’,‘WSH’)
AND Landings.Date_Of_Landing>#1/12/98#;

Query 6

Table 6.6.2.c. Queries of hypothetical demonstration fisheries database (presented in SQL language and resulting table).

Query 7: (SQL)
SELECT

Landings.Landings_Kg,
Landings.Landings_Kg*L_Species.Price_Per_Kg AS[Value],
Landings.Vessel_Code
FROM L_Vessel_Register
INNER JOIN (L_Species
INNER JOIN Landings
ON Landings.Species_Code = L_Species.Species_Code)
ON L_Vessel_Register.Vessel_Code = Landings.Vessel_Code;

Query 7

Query 8: (SQL)
SELECT
Landings.Vessel_Code,
SUM(Landings.Landings_Kg) AS [Total Kg landed],
SUM(Landings.Landings_Kg*L_Species.Price_Per_Kg) AS [Total Value]
FROM L_Vessel_Register
INNER JOIN (L_Species
INNER JOIN Landings
ON Landings.Species_Code = L_Species.Species_Code)
ON L_Vessel_Register.Vessel_Code = Landings.Vessel_Code

GROUP BY Landings.Vessel_Code;

Query 8

Query 9:(SQL)
SELECT

               Sum([Query_8].[Total Kg landed]) AS [Sum Kg landed],
Sum ([Query_8].[Total Value]) AS [Sum of Value],
Count(*) AS [Count Samples]

FROM Query_8;

Query 9