The Samples Data Management System was developed to:

	Capture collecting event data for incoming ROV-collected samples (implementation: database is coordinated with video annotations)
	Manage the inventory of specimens in the archives (implementation: database, spreadsheet import, and other interfaces developed)
	Make the information available over the web (implementation: web interfaces to database developed)

The system architecture and data flow for the system are summarized the following diagram. Data is acquired about samples either from video annotations or spreadsheets. The sequence for acquiring data from video annotations is: ROV video is annotated to indicate that a sample was collected; a script extracts collecting event information from the annotation file, merges it with navigation data and frame grabs, and generates a list of samples as a staging database; this list is loaded into the database. Alternatively, the sequence for acquiring data about non-annotated samples is to load data directly from spreadsheets. Data is stored in the database on shore. Web interfaces permit queries to be made of the data; other interfaces permit data editing and retrieval as well. Each of these steps is highlighted below and discussed in detail in the subsequent sections of this system documentation.

Staging database and associated procedures
The staging database is the intermediate product of the process for acquiring collecting event information from video annotations. The staging database is a table listing all the samples annotated in a particular VICKI file, as well as fields to be filled in prior to loading the samples into the database. It is an HTML page generated by the script "VIF2staging". VIF2staging runs automatically by the shipboard process on the R/V Pt. Lobos and may also be run from shore to incorporate corrections made to the video annotations. A button at the bottom of the staging database executes the script "loadcoll.asp" to load data into the shore-based database.

	VIF2staging The perl script "VIF2staging" merges the sampling events extracted from the VICKI annotation file with frame grabs and navigation data, and creates the web page with the list of samples (staging database) in HTML.
	shipboard process The script runs VIF2staging automatically and opens the staging database in a web browser.
	loadcoll.asp The script "loadcoll.asp" distributes selected information from the staging database into the appropriate tables of the MBARI_Samples database. It is an Active Server Page (ASP) script activated by a button on the staging database web page. Permission to run this script is limited to Mike McCann and Jenny Paduan.

MBARI_Samples database and associated procedures
The MBARI_Samples database and associated procedures run on the shore-based server "cyclone". The database is in Microsoft SQL Server. It contains the tables that store the data and views that are called by web-based queries. The following pages contain detailed information about the database:

	Samples database Discussion of database structure and the design criteria.
	Design diagram Diagram illustrating database table relationships.
	Table design Spreadsheet of database field names, descriptions, formats, keys, default values, and natural language names for the web interfaces.
	MS SQL views List of views and an example of view syntax.

The following procedures update or back up information in the database:

	CTDO lookup procedure A Java procedure, "UpdateAllRovctd.bat", retrieves from the MBARI Sybase database salinity, temperature, depth, and oxygen (CTDO) data, recorded at the time the samples were collected.
	VIMS explosion procedure A Java procedure, "UpdateAllSampleKeywords.bat", retrieves keywords from the VIMS knowledge base, so specimens can be queried for in this database using the same set of descriptors as for the video annotations.
	Import spreadsheet data procedure A MS SQL stored procedure "migrate_import" supports the import of spreadsheet data into the database. Data first must be formatted in the spreadsheet program to match the design of a temporary table in the MS Access database "sampleForms.mdb", and next be imported into the temporary table. Then the stored procedure is executed to verify data from database lookup tables, retrieve IDs of people, create records in a series of tables, and provide feedback to the user on samples that did not load correctly.
	Backup procedure Backups of the system are performed nightly by the MBARI IS group. Aternatively, backups may manually be done by following the procedure outlined here.

Interfaces to the database
Interfaces to the data are through web-based queries and MS Access satellite databases.

Web interfaces 1. The main web query entry page contains a list of questions, each of which hyperlink to a HTML web form where the query is constructed. The form fields are where the user specifies the values of the variables that will become the criteria for the query, and the form action is to send the variables in an SQL statement to the database and return a table of the samples that satisfy the query. From this table, the user can choose to download a comma-delimited version of the table or browse to more data for individual specimens. The following pages contain detailed information about the elements of the web interfaces: Web interfaces IDC/HTX examples and formats IDC/HTX file list MS SQL views

2. Samples collected on individual cruises are also listed as part of the Pt. Lobos Expeditions and Western Flyer Expeditions web pages; they are included in these web pages by the expedition page process.

MS Access satellite databases Satellite databases in Microsoft Access have also been created that have their tables linked to MBARI_Samples in MS SQL Server. These contain forms, label templates, and queries, and are more user-friendly interfaces to the data. One of these, "sampleForms.mdb", is on "Cyclone", but others may be created to run from any NT or Windows95 machine.