Account

Blacknest BDS Schema Overview

Date	2008-12-02
Version	0.4

Note that this is a "work in progress" document and is being continually updated.

Introduction

This document provides an overview of the Blacknest Data System (BDS) Database Schema.

Database Schema Overview

The BDS Database holds information on all of the Seismic data and associated metadata. The database will be implemented as a MySQL database. Generally the database is not directly accessed. It is normally only accessed by the BDS Data Engine in response to API requests.

General

Dates and times: These are stored to the nearest micro-second. We are using a MySQL String to store the date/time formated using ISO 8601 format: eg "2002-04-03T11:20:53.103000". An entry of 0000-01-01T00:00:00.000000 is used for unknown and/or start of time. The entry "9999-01-01T00:00:00.000000 is used for end of time.

Overall Management

Groups

The list of user groups.

Name	Type	Attributes	Description
id	String	primary, auto_increment	Unique ID for this entry
group	String	primary	The Group name
description	String		The Groups description
permissions	String		Comma separated list of permissions (or separate table) This may not be present.

Permissions based on Network/(Station/Array)/Period

Users

The list of users.

Name	Type	Attributes	Description
id	Integer	primary, auto_increment	Unique ID for this entry. (Used as the userId)
user	String	primary	The users login ID
password	String		The users login password (Encrypted)
name	String		The users full name
groups	String		The list of groups the user belongs to. (comma separated list of names)
email	String		The users email address
telephone	String		The users telephone number
address	String		The users postal number
enabled	Bool		If this user is enabled

UserGroups

Used to keep the list of groups a user belongs to. This may be integrated into the Users table as shown above.

Name	Type	Attributes	Description
id	Integer	primary, auto_increment	Unique ID for this entry. (Used as the userId)
userId	Integer	primary	The userId for this group
group	String		A group this user belongs to

Overall Information MetaData

I suggest that all the tables have a "date when last altered" column that is automatically updated when the row is modified. TB: We could use a separate Changes table to handle this.

Networks

The list of networks.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
network	String	Primary	The Network name
description	String		The Network description

NetworkStations

Used to keep the list of all stations managed by a network.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
network	String	Primary	The Network name
station	String		The Station/Array name

Stations

Used to keep the list of all station names.
How do we denote stations that are both an array and a co-located three-component set? Will we want to do searches for three-component sets that include those co-located with an array? At present our co-located three-component set has a different name (EKB) from the array (EKA), but will this change after the upgrade? We could have duplicate entries, one to denote the array, one for the three-component set, but we will not be able to use "station" as a primary key if it is not unique within the table. Also, is this table going to contain all the pits within an array? Will they have a different station type from an isolated single station? (they don't in IDC table "site" - array pits are all designated "ss" - "single station")

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
station	String		The Station/Array name
type	String		The station type (Station, Array, Pit)
description	String		A description
stations	String		The array this station/pit belongs to if in an array

Channel MetaData

StationLocations

Used to keep the list of all station locations

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
datum	String		The Datum for the Latitude/Longitude normally WGS84
latitude	Double		The Latitude in degrees using the datum.
longitude	Double		The Longitude in degrees using the datum.
elevation	Double		The ground level elevation in metres from the WGS84 ellipsoid (Sea level)
arrayOffsetEast	Double		For a station in an array, this is the offset from the array's centre in km
arrayOffsetNorth	Double		For a station in an array, this is the offset from the array's centre in km

Channels

Used to keep the list of all channels.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time for this channel, when it came into existence
endTime	String	Primary	The End time for this channel, when it ceased existence
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String		The Channels primary identifier (Usually the channelType and channelAux combined)
channelType	String		The Channels Type. Three characters such as (BHZ)
channelAux	String		The Channels Auxiliary identifier. Could be the location ID (Usually two digits, e.g. "01")
description	String		A Description

Calibrations

Used to keep the list of all calibration entries for a channel.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time for this channel
endTime	String	Primary	The End time for this channel
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String	Primary	The Channels primary identifier
samplingFrequency	Double		The Sampling frequency used
calibrationFrequency	Double		The frequency that the CalibrationFactor value is valid for (Hz)
calibrationFactor	Double		The scaling value to apply to the data to normalise to nanometres. This is a measured value at the calibration frequency. For seisometers it is in nanometres/count. The calibrationUnits specify the actual units.
calibrationUnits	String		Unit for calibration Factor, e.g. volts per (m/s), volts per Pa

ChannelInstruments

Used to keep the list of all digitiser/sensor combinations for a channel by date/time.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
channelId	Integer	Primary	The Channel id
digitiserId	Integer		The Digitiser in use
sensorId	Integer		The Sensor in use

Digitisers

Used to keep the list of all digitisers.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time, when it came into existence
endTime	String	Primary	The End time, when it ceased existence
name	String		The digitiser's model name. Only used when there is a unique physical digitiser
type	String		The digitiser type. Only used when there is a unique physical digitiser
serialNumber	String		The digitisers's serial number. Only used when there is a unique physical digitiser
numberChannels	Integer		The number of supported channels, for cross checking
samplingFrequency	Double		The main sampling frequency (Hz). This could be 0. The SamplingFrequency in Calibrations will be used for data.
initialSamplingFrequency	Double		The initial pre-decimation sampling frequency (Hz)
gain	Double		The overall gain of the digitiser at the manufacturers calibration frequency (counts/volt?). (For information only)

Sensors

Used to keep the list of all sensors.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time, when it came into existence
endTime	String	Primary	The End time, when it ceased existence
name	String		The sensor name.
type	String		The type of sensor. (Seismometer, Hydrophone etc)
serialNumber	String		The sensor's serial number. Only used when there is a unique physical sensor
numberChannels	Integer		The number of supported channels, for cross checking
gain	Double		The overall gain of the sensor at the manufacturers calibration frequency. (For information only)
gainUnits	String		Units in which gain is quoted, e.g. volts per (m/s), volts per Pa
oldId	Integer		Original databases instrument id.

SensorLocations

Used to keep the list of sensor locations.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
sensorId	Integer	Primary	The Sensor this relates to
depth	Double		The depth below ground level (m)
verticalAngle	Double		The Sensor's placement vertical angle in degrees degrees with zero = vertically up (degrees)
horizontalAngle	Double		The Sensor's placement horizontal angle in degrees clockwise from north (degrees)

Responses

Used to keep the list of all frequency responses for a given channels digitiser and sensors.

Name	Type	Attributes	Description
id	Integer	not_null, auto_increment	Unique ID for this entry
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network name
station	String	Primary	The Station/Array name
channel	String	Primary	The Channels primary identifier
name	String		The responses name (Sensor,Digitiser,Anti-Aliasing filter, post filter etc)
type	String		The type of response (PoleZero,AmplitudePhase or FIR Coefficients)
data	Blob		PoleZero, AmplitudePhase or FIR Coefficient data
gain	Double		Overall gain at gain frequency. (For information)
gainFrequency	Double		Frequency that gain is valid for. (For information)
decimation	Double		Decimation performed post filter. (For information)
symmetry	String		Symmetry for FIR coefficients (A = asymmetric, B = symmetric[odd], C = symmetric[even])

Seismic Data

DataFIles

Used to keep the list of data files managed by the BDS.

Name	Type	Attributes	Description
id	Integer	auto_increment,Primary	Unique integer ID
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
location	String		The location of the data. This could be a local archive or even a remote archive
format	String		The data's format
url	String		File location. This can embody a protocol, host, path and filename.
comment	String		A general comment string.

DataChannels

Used to keep the list of all of the data channels and which file they are in.

Name	Type	Attributes	Description
id	Integer	auto_increment,Primary	Unique integer ID
startTime	String	Primary	The Start time
endTime	String	Primary	The End time
network	String	Primary	The Network organisation the original data is from
station	String	Primary	The Station/Array name
source	String	Primary	Some information on the source of this data (DIRECT, TAPE, PROCESSED ...). This would allow multiple sources of data from the same Array and TimePeriod. It could also support processed data.
sampleRate	Double		The Sample rate
sampleFormat	Integer		The format of each sample (Int16, Int32, Float32 etc)
dataFileId	Integer		The Data Files ID
dataFileChannel	Integer		The channel number within the data file

Misc Data

Notes

Used to keep the list of data outages and other notes.

Name	Type	Attributes	Description
id	Integer	auto_increment,Primary	Unique integer ID
startTime	String		The Start time
endTime	String		The End time
network	String		The Network organisation the original data is from
station	String		The Station/Array name
channel	String		The Channels primary identifier
type	String		The entry type: (Outage, Note, Warning etc)
source	String		The source of the note
title	String		A Title for the note
description	String		A Comment for the note

Changes

Used to keep the list of individual changes made to the database.

Name	Type	Attributes	Description
id	Integer	auto_increment,Primary	Unique integer ID
changeGroupId	Integer		The Change Group ID
time	String		The time the change was made
table	String		The Table that was altered. (Should be have a list of tables ?)
id	Integer		The tables entry that was altered (We would need a unique ID in each record for this)(Should this be in effect a list ?)

ChangeGroups

Used to keep the list of changes made to the database.

Name	Type	Attributes	Description
id	Integer	auto_increment,Primary	Unique integer ID
time	String		The time the change was made
type	String		The Change type
user	String		The User that made the change
title	String		A Title for the change
description	String		A Comment for the change

We will also need database information store on the following:

Data Request queues
Data Requests serviced
Data additions made ?