This document contains the steps needed to generate the data in BogInstruNormalization and DatabaseFields.

Assumptions

The scripts make the following assumptions:

1. The Device table has had columns added to match the list under DeviceMerge.
2. An up-to-date copy of the Instru table is in the SQL Server where these changes will be made.
   • It is OK to change the copy of the Instru table.
   • Must make copy of this table as a different name (Instru is name of view)
3. The person running the script has permissions to:
   • Change Device.
   • Change Instru.
   • Create new tables and leave them (Manufacturers) or delete them later.
4. Either DeviceTypes has been updated to reflect the changes in Type, or the SQL at SqlQueriesFinal#AddDeviceTypes is run to make the necessary changes.

In some cases the steps are 'smart' about using existing data to assess changes, but in other cases it was necessary to assign the values directly for some of the devices. SSDS ID is used as the key for most of these changes; barring unusual changes this should not break.

I made the following assumptions:

1. Paul C approves of all these changes.
2. Someone will quickly look over the code to see if there may be bad side effects. I was careful but I'm not that skillful yet.

The Columns

We start from the following list of columns in the dbo.Instru view, corresponding to SSDS columns as noted (proposed changes in italics):

Initial Normalization of Devices

The first step is to ensure we know which of the items in the BOG Instru database that don't have SSDS_IDs are also in the SSDS database. (It is important that we minimize the number of duplicate entries as of the end of the process, so we might as well do this step first.) This query must first find BOG devices that could be already entered as SSDS devices. To do this, we look for possibly matching serial numbers where there is no meaningful SSDS_ID in the Instru database. We can then cross-check the models and instrument types manually to see if it is likely to be the same instrument.

When in doubt, we should assume the two instruments are different, until it is clear that they are the same.

The following instruments are believed the same. (Queries are at SqlQueries#FindMatchingDevices.) This will be reflected by setting their SSDS ID in Instru to the corresponding SSDS ID.

Implement this by doing updates on the SSDS ID values of the appropriate entries in the instru table. See SqlQueriesFinal#MatchDevices.

Device Merge (Merging Instru Table into Device Table)

The next step is to migrate most of the Instru data 'as is' into the Device Table. This will have two aspects: the devices without SSDS IDs, and the devices with SSDS IDs. In each case, only the fields which did not already exist in SSDS will be migrated. The remaining fields will be accomplished in separate steps below. (An exception is the PersonID_FK field, which is easiest to do as part of this step.)

This step is performed now so that we have a single table with SSDS IDs for every item, which will be useful for future queries.

For this step, we assume the Device table already exists with the new Instru fields required for the merge. Those fields are:

MBARI_ID
Serial
FullSerial (mfgSerialNumber)
Calibration-organization
Features
Pressure-sensor
Depth-Rating
Firmware/EPROM
Memory
Receive-frequency
Transmit-frequency
Enable-code
Release-code
Tilt-option
Purchased-for
Owner
Custodian
Date-new
PO
Transaction
Permanent-comment
document-dir

Devices without SSDS IDs

Create new devices in Device, copying all the new fields from Instru into the corresponding columns of Device.

• manufacturer-web-page gets copied to infoUrl.

Also set the PersonID_FK = 126 (for Paul Coenen – see Technician below).

Then copy the SSDS ID for the new devices back into the SSDS ID column of Instru, to make later SQL simpler.

Use this Detailed SQL for everything in this section.

Devices with SSDS IDs

Update the new Instru fields in Device with the data from Instru.

• manufacturer-web-page gets copied to infoUrl.
  '
  Use this Detailed SQL.

Back to table...

Technician

For any device in Instru, set PersonID_FK in SSDS to 126 (Paul Coenen). (See explanation in BogInstruNormalization.)

Strategy: This is most easily accomplished during the device merge step.
(Note, by the way, there are 15 additional items in Device with PersonID_FK = coenen that aren't already in Instru.)

Originally we planned to change all the 'pc' values in Instru, as shown in SqlQueries#ChangeTechnician. I don't believe this step will be necessary.

MBARI_ID and SSDS ID

For reasons explained in the Design page, we must support MBARI ID field in the BOG database. To ensure proper operations of both systems, we want to guarantee that all devices have both an MBARI ID and an SSDS ID. To do this, as we merge the two systems we will generate any missing IDs, according to the following rules:

• SSDS ID is set for new Instru devices as they are added to the database. This happens as part of the device merge above.
• If MBARI ID doesn't exist, it is set to the SSDS ID as part of the device merge.
• If the MBARI ID already exists, it will need to be changed to match SSDS. (This can happen after everything else is done.)

• if we need to save the Instru MBARI ID for some reason, this can be moved later; else do it as part of device merge.
• prepare any associated changes to the FK in Trans.
• Set the MBARI ID in Device to the SSDS ID.
• Will also have to set appropriate constraints on MBARI_ID column

In the future, any new device receives the next available SSDS ID, and the MBARI ID is set to the same number.

Interim Table Creation

Next we will create a table to use for interim results. (This table will be reused in subsequent steps.) The table will have an entry for each of the devices in SSDS – note that some of these will be in Device only, and the rest will be in both tables (some devices that were originally Instru-only, and others that were originally in both tables). All subsequent queries have to allow for all 3 possibilities, until all the Instru information has been migrated.

SQL for this task will be in SqlQueriesFinal#MakeInterimTable.

Back to table...

Manufacturer

(See normalization strategy.)

The list of permitted manufacturers (so far, based on existing data as of 2/20) is as follows. The name in parentheses provides the complete company name, in case we ever make a database.

• Aanderaa Data Instruments
• Axys Technologies (.. Inc.) (was Axys Environmental Systems)
• Biospherical Instruments (.. Inc.)
• Bluefin Robotics (.. Corporation)
• Brook Ocean Technology (.. Limited)
• Crossbow Technology (.. Inc.)
• Garmin
• HOBI Labs (Hydro-Optics, Biology, & Instrumentation Laboratories)
• Magellan Navigation (.., Inc.)
• MBARI (Monterey Bay Aquarium Research Institute)
• McLane Research Laboratories (.., Inc.)
• NOBSKA
• ORBCOMM (.., Inc.)
• Paroscientific (.., Inc.)
• QUAKE Global (.., Inc.)
• Satlantic (.. Inc.)
• Sea-Bird Electronics (.., Inc.)
• SeaTech (.., Inc.) (no longer exists)
• Simrad (.., Inc)
• Star Engineering
• Teledyne Benthos (was Benthos)
• Teledyne RD Instruments (was RD Instruments)
• UC Santa Barbara (University of California, Santa Barbara)
• WET Labs
• WHOI (Woods Hole Oceanographic Institution)
• Xantrex Technology (.. Inc.)

The following names are also supported

• unknown
• tbd
• testing

The algorithm to follow for the merge will be:

1. If the name in SSDS or Instru matches one in the above lists, use it.
2. If the first 3 letters of the name in SSDS or Instru is contained in (ignoring case)one of the names above, use the name it is contained in.
   • Note that SeaTech entries should have been an exact match in step 1; all others map to Sea-Bird Electronics.
3. Perform manual corrections listed below.

The following entries require manual correction (starred entries should be OK'd with Paul C):

1. Anderraa Instruments -> Aanderaa Instruments (typo fix)
2. * Ashtech -> Magellan Navigation (Ashtech is model not company) (let Paul know)
3. * ASIMET -> Star Engineering (model not company; allegedly Star Eng is only seller)
4. * Asimet/WHOI -> Star Engineering (model not company; allegedly Star Eng is only seller)
5. * Triaxys -> Axys Technologies
6. * UCSB -> UC Santa Barbara

These entries shld be addressed by the 'contained in' rule above.

1. Axys Environmental Systems -> Axys Technologies (name change?)
2. * Benthos -> Teledyne Benthos (bought))
3. Biospherical -> Biospherical Instruments
4. Brook Ocean Technology Ltd. -> Brooke Ocean Technology
5. Nobska -> NOBSKA
6. OrbComm -> ORBCOMM
7. Quake -> QUAKE Global
8. * RD Instruments -> Teledyne RD Instruments (bought)
9. Xantrex -> Xantrex Technology

The plan

To do this, as a first step create a table of allowable Manufacturers (see SqlQueriesFinal#CreateMfgTable). The label column is the name that will be used to fill out the mfgName field in Device. (Conceivably the manufacturers information in Device should all be relational, but for now this is a quick and dirty.)

Then we will copy the appropriate information over to our interim table (TempDevFld) using the mfgName column, from Instru or Device as appropriate, using the rules above.

At this point, all the mfgName rows should be filled in.

Manual changes will be done to the interim table.

Finally, the InterimTable will be used to overwrite the Device table, and the workingText column of Interim table will be cleared.

This is shown in SqlQueriesFinal#UpdateMfgName.

Back to table...

Model

(See normalization strategy.)

The following names are also supported

• unknown
• none
• tbd
• testing

At some point (phase II?), figure out issues with:

1. SSDS id 1009, 1218 (2 'MTM' with serial=1)
2. SSDS id 1216 (GPS, no model)
3. Are Metsys 1321, 1396, 1397 really MBARI mfg?
4. What to do with virtual hardware (1309-1311)?
5. delete test entries? (1509 etc.)
6. SideARM (1278, 1295, 1300, 1411, 1413) vs MSP
7. SSDS id 1275 (MBARI Gashound)
8. Are all Aquadopp entries the same units (mfgModel is long and identical but descriptions differ)
9. SSDS id 1323 (is it a SOON, is it a pco2, is it a platform?)

Before beginning, confirm with Paul that the following changes are OK:

1. all Sea-Bird CTDs have model numbers of the form SBE nn[-IMP]
   • SBE prefix
   • '-' after number, except for 'plus' versions
2. changes listed under manual correction below

The following entries require 'manual' correction (assume Paul has OK'd the above):

1. Make the models for the following SSDS ids as listed:
   • 1313: GPS16-HVS
   • 1395: ECO FLNTUSB

1. • all '(null)' to null
   • all 'ECO-FLNT*' to 'ECO FLNT*'
   • all 'triplet' to 'ECO Triplet'
   • all 'MMC v*' to 'MMCv4'
   • all 'Medusa R*' to 'Medusa Rev 2'
   • all 'Medusa v*' to 'Medusa Rev 2'
   • all 'Fiberglass tor*' to 'OASIS Buoy'
   • all 'Emeter' to 'E-meter'

The algorithm to follow for the merge (order matters) will be:

1. If BOG Instru Model exists use that; else use SSDS mfgModel. This step is accomplished in DeviceMerge.
2. Do manual corrections as listed above (code in SqlQueriesFinal#ManualModel).

Back to table...

Serial

For the merge: Copy the contents of this field without changes.

Back to table...

FullSerial (becomes mfgSerialNumber)

We will fill out mfgSerialNumber in every case, even if it is the same as the shorter Serial. This means that the Access interface will now see an entry for every FullSerial field, although often it will be the same as the Serial field.

Confer with Paul explicitly about SSDS IDs 1429 and 1437, for which the serial numbers are in conflict. For example, 1429 numbers, SSDS first, are 37IM20000-1244 vs. 37IM21734-1244. If the latter is correct, no changes to the algorithm below are necessary.

The algorithm to follow for the merge (order matters) will be:

1. Present the mfgSerialNumber to Access as FullSerial.
2. If Instru has a full serial number, use it for mfgSerialNumber
   • If not, then if Instru has Serial, use that for mfgSerialNumber.

This algorithm works because (a) there are no cases where SSDS has a full serial number in mfgSerialNumber, but Instru only has a short Serial; and (b) there are no conflicts between Device and Instru (other than the two specifically mentioned above).

This algorithm can be implemented by assigning Serial whenever it exists, then assigning FullSerial whenever it exists. The former is done in SqlQueries#insertNewDevices and SqlQueries#updateExistingDevices; the latter is done in SqlQueriesFinal#updateToFullSerial.

Type

(See normalization strategy.)

The list of permitted types (so far, based on existing data as of 2/20) is as follows.

• Air Pressure Sensor
• Aircraft
• AUV
• Backscatterometer
• Backscatterometer/Fluorometer (new)
• Balloon
• Battery (new)
• BatteryLog
• Bioshutter
• Cable Monitor
• CO2 Monitor
• Combined
• Communication
• Communication/GPS (new)
• Compass
• Conductivity Sensor
• Controller
• Controller-CTD (new)
• Controller-Mooring Node
• Controller-Power
• CTD
• CTD Service
• Currents Sensor
• Currents Sensor-ADCP
• Data Acquisition Unit
• Data Acquisition Unit-SmartStar
• Drifter
• Drifter-Profiling
• Drifter-Surface
• Echo Sounder-Singlebeam (new)
• Echo Sounder-Multibeam (new)
• Electrical Sensor
• Electrical Sensor-Ground Fault
• Environment Monitor-System
• Flow Meter
• Flowthrough System
• Fluorometer
• Fluorometer/Nephalometer
• Glider
• GPS
• Humidity Sensor
• Hydrophone
• IMCTD
• Inductive Modem
• Inductive Modem-Surface
• Inductive Modem-Underwater
• Inductive Modem Cable Coupler (new)
• Kite
• Meteorology Package
• Metsys
• Model Service
• Mooring
• Navigation Service
• Navigation-AHRS
• Network Hub
• Network Hub-Power Controller
• Nitrogen Sensor
• Nitrogen Sensor-ISUS
• Optical
• Oxygen
• Oxygen Sensor
• Photometer-Bioluminescence
• platform
• Power Supply
• Power Supply-Battery
• Power Supply-Electric (new)
• Power Switch
• PowerSwitch
• Pressure Sensor
• pressureSensor
• Profiler
• Profiler Package
• Profiler-Moored
• Profiler-Ship
• Profiler-Suspended
• Propulsion Unit
• Pump (new)
• Radiometer
• Radiometer-Hyperspectral
• Radiometer-Longwave
• Radiometer-Multispectral
• Radiometer-Shortwave
• Release (new)
• ROV
• Satellite
• Scatterometer
• Scatterometer-LISST
• Ship
• Shutter-Antifouling
• Sound Speed Sensor
• Sonar-Sidescan Swath Bathymetry (new)
• Sonar-Scanning (new)
• Sonar-Subbottom (new)
• Spectrometer
• Temperature Sensor
• Test Device
• Test Entry-Instrument Type
• Test Simulated Instrument
• Toroid
• Towfish
• Transmissometer (new)
• Transponder (new)
• Velocity Sensor-DVL
• Wave Sensor
• Wave Sensor-Heave
• Wind Sensor

The following device types are also supported

• Test Entry-System
• Not Specific-Unknown
• Not Specified-TBD
• Not Specified-Undefined

Before beginning, confirm with Paul that the following changes are OK:

1. capitalizing first letters of each word
2. 'ADCP' to 'Current Sensors-ADCP'
3. 'modem' to 'Inductive Modem-Surface' or 'Inductive Modem-Underwater' as appropriate
4. OASIS 'Controller' (those with capital C) to 'Controller-Mooring Node'
5. separating 'backscatter' into the components below
   1. Scatterometer: Measures scattered emitted radiation (includes OBS, optical backscatterometer)
   2. Backscatterometer: measures reflected radiation from in-situ surfaces
6. 'fluor/turbidity' to 'Fluorometer/Nephalometer'
7. 'backscatter/fluorometer' to 'Backscatterometer/Fluorometer'
8. 'temperature/humidity' to 'Metereology Package'
9. 'Nitrate' to 'Nitrogen Sensor/ISUS'
10. 'Nitrate analyzer' to 'Nitrogen Sensor' (this is Satlantic unit)
11. 'Oxygen optode' (ignore case) to 'Oxygen Sensor'
12. 'Platform' to 'Toroid'
13. 'power source' to 'Power Supply-Electric'
14. 'pCO2' to 'CO2 Monitor'
15. 'transducer' to 'Transponder'
16. 'sounder' to 'Echo Sounder-Singlebeam'
17. 'shutter' to 'Shutter-Antifouling'
18. 'ICC' to 'Inductive Modem Cable Coupler'
19. 'pump' to 'Pump'
20. 'release' to 'Release'

The questions below were mostly unsettled as of press time.

Questions for Paul:

1. Are ECO BBSBs (161, 202) really Backscatterometers, or just Scatterometers?
   • If the former, change SSDS DeviceType for these to Backscatterometer
   • If the later, use existing SSDS DeviceType
2. Assumption: all HS2, VSFS units (he calls 'backscatter') are Backscatterometers
3. confirm HyperOCRs are Hyperspectral, i.e., controllable
   • If confirmed change 1346-1350 to be Hyperspectral
   • If not put these into appropriate category
4. Are any of our shutters not antifouling? Can we call them all Shutter-Antifouling?
5. How to change 'radiometer' to one of the following, as appropriate:
   1. Radiometer-Multispectral: measures a configurable series of wavelengths of radiation
   2. Radiometer-Hyperspectral: measures multiple specific wavelengths of radiation
   3. Radiometer-Shortwave: measures short-wave radiation ranges
   4. Radiometer-Longwave: measures long-wave radiation ranges

Add the types marked (new) above to the DeviceType table.

• Backscatterometer/Fluorometer: backscatterometer that additionally provides fluorescence data
• Battery: device that stores energy and makes it available in an electrical form; not mediated by electronics (see Power Supply-Battery)
• Communication/GPS: transmits signals from one place to another, and contains GPS locator
• Controller-CTD: controls acquisition of CTD data
• Echo Sounder-Singlebeam: acoustic system which transmits short acoustic pulses from single transmitter into the water column, and then detects echoes from impedance discontinuities (typically fish or plankton)
• Echo Sounder-Multibeam: acoustic system which transmits short acoustic pulses from multiple transmitters into the water column, and then detects echoes from impedance discontinuities (typically from the benthic layer)
• Inductive Modem Cable Coupler: connector for indcutive modems (ICC)
• Power Supply-Electric: power supply (delivers power to other equipment) that does not store energy
• Pump: mechanical device used to move liquids or gases
• Release: mechanism to disconnect two components, typically allowing a buoyant component in water to detach from an anchor and float to the surface
• Sonar-Sidescan Swath Bathymetry: fixed-mounted acoustic bathymetric sounding systems that sweep a wide path; also known as SSBS
• Sonar-Scanning: acoustic system that can move (examples are Articulating, Profiling, and Pencil Beam)
• Sonar-Subbottom: acoustic system designed to penetrate layers beneath the benthic surface (e.g., CHIRP)
• Transmissometer: device to measure the optical transparency of water (also turbidity sensor)
• Transponder: electrical device designed to receive a specific signal and automatically transmit a specific reply

Also, Change the name for device type 158 from 'Fluorometer/Nephalometer' to 'Fluorometer/Nephelometer', and change description to reflect new spelling.
Code to do all this is in SqlQueriesFinal#DeviceTypeInsert.

I have also created code to update the descriptions of the erroneously added deprecated terms at SqlQueriesFinal#UpdateDeprecated.

Implementation

The algorithm to follow for the merge (order matters) will be:

1. Change BOG name 'Controller' (match case) to 'Controller-Mooring Node'
2. SSDS names remain unchanged unless otherwise noted
   #+ If the Instru device has an SSDS entry, use the SSDS device type
   • If the Instru name matches (ignoring case) one in the above lists, use the list name.

These are coded in SqlQueriesFinal#DeviceTypeMerge.

1. Perform explicit corrections listed above.

We will copy the appropriate information over to our InterimTable (using the tempType column), from Instru or Device as appropriate, using the rules above.

Then we will do the entries requiring 'manual' correction (assuming Paul has OK'd the above), code for which is in SqlQueriesFinal#FixDeviceTypes:

At this point, all the tempType cells should be filled in.

Finally, the InterimTable will be used to overwrite the Device table.

Back to table...

Unchanged Fields

The following columns will not be changed in Phase 1, and so will be copied as is during the initial merging of the tables. This is done in SqlQueriesFinal#insertNewDevices and SqlQueriesFinal#updateExistingDevices.

Back to table...

Calibration organization

For phase I, maintain this material as it is now.

For phase II, consider making it compatible with manufacturer table. But realize that more information may need to be tracked for the calibration organization, and it may be a separate part of the same compnay as the manufacturer.

Back to table...

Features

Maintain this material without changes.

Back to table...

Pressure sensor

Maintain this material without changes.

Back to table...

Depth Rating

Maintain this material without changes.

Back to table...

Firmware/EPROM

Maintain this material without changes.

Back to table...

Memory

Maintain this material without changes.

Back to table...

Receive frequency

Maintain this material without changes.

Back to table...

Transmit frequency

Maintain this material without changes.

Back to table...

Enable code

Maintain this material without changes.

Back to table...

Release code

Maintain this material without changes.

Back to table...

Tilt option

Maintain this material without changes.

Back to table...

Purchased for

Maintain this material without changes.

Back to table...

Owner

Maintain this material without changes.

Back to table...

Custodian

Maintain this material without changes.

In the future, this field should become another Person field.

Back to table...

Date new

Maintain this material without changes.

Back to table...

PO

Maintain this material without changes.

Back to table...

Transaction

Maintain this material without changes (or delete this field).

Back to table...

Permanent comment

Maintain this material without changes (or delete this field).

Back to table...

manufacture web page

Maintain this material without changes.

In future, clarify its role and recode appropriately. (Is this the instrument web pages at the manufacturer's site?)

Back to table...

document dir

Maintain this material without changes.