Draft of PolarGrid database table design

CREATE DATABASE PolarGrid;
use PolarGrid


# possible entry unit of dataset
# CREATE TABLE Expedition{
# ExpeditionID bigint,
# }
# possible entry unit of dataset
# CREATE TABLE Radar{
# RadarID bigint,
# }

#
# DataChunk
#
# DataChunk is a unit of dataset which is identified by
# (1) spatial information
# (2) temporal information
# (3) triplet of radar information (waveform, transmit antenna, receive antenna)
#
CREATE TABLE DataChunk(
DataChunkID BIGINT NOT NULL AUTO_INCREMENT,
UUID VARCHAR(255),
Desctiption VARCHAR(255),
SamplingFrequency int,
SampleAverage int,
NumberOfWaveform int,
DSPMode VARCHAR(255),
SystemDelay int,
StartPoint point,
StopPoint point,
StartUTC double,
StopUTC double,
Microformat MEDIUMBLOB,
CreationTimestamp timestamp,
RevisionTimestamp timestamp,
PGContactID bigint,
PRIMARY KEY(DataChunkID),
INDEX(StartPoint),
INDEX(StopPoint),
INDEX(StartUTC),
INDEX(StopUTC)

);

#
# FileObject:
#
# FileObject represents minimum unit of dataset. In general
# we assume that this object can be instrumental data or
# output visualization file, or revised data file.
# Please note that WaveformName,TXAntennaName, and RXAntennaName
# are from the file name. There is no validation about this name
# based on the antenna/waveform tables.
#
CREATE TABLE FileObject(
FileObjectID bigint NOT NULL AUTO_INCREMENT,
DataChunkID bigint,
UUID VARCHAR(255),
FileName VARCHAR(255),
RecordTimestamp timestamp,
RadarType VARCHAR(255),
DistributionFormat VARCHAR(255),
WaveformName VARCHAR(255),
TXAntennaName VARCHAR(255),
RXAntennaName VARCHAR(255),
OnlineResource VARCHAR(255),
CreationTimestamp timestamp,
RevisionTimestamp timestamp,
PRIMARY KEY (FileObjectID),
INDEX(DataChunkID),
INDEX(WaveformName),
INDEX(TXAntennaName),
INDEX(RXAntennaName),
INDEX(RecordTimestamp),
);

#
# Waveform
#
# This table defines waveform that transmited between antennas. Each
# radar system can have several different waveforms that it
# can transmit. And that transmitted waveform on that transmit antenna
# can be received on any combinations of antenna. Individual waveform
# describes single waveform that is used by datachunk.
#

CREATE TABLE Waveform(
WaveformID bigint NOT NULL AUTO_INCREMENT,
DataChunkID bigint,
WaveformName VARCHAR(255),
StartFrequency int,
StopFrequence int,
PulseWidth double,
ZeroPiMode int,
PRIMARY KEY (WaveformID),
INDEX(DataChunkID),
);

#
# DataAquisition
#
# This table defines how we describe the setup of antenna.
# This information is included for the waveform and data chunk
# AssociationType field specifies either this setup information is
# used for waveform or data chunk. Similarly, AssociationId field
# specified ID which is exact identity of the item.
#

CREATE TABLE DataAcquisition(
DataAcquisitionID bigint NOT NULL AUTO_INCREMENT,
NumberOfSamples int,
SampleDelay int,
BlankingTime int,
AssociationType VARCHAR(255),
AssociationID bigint,
PRIMARY KEY (DataAcquisitionID),
INDEX(AssociationType),
INDEX(AssociationID)
);

#
# Antenna
#
# This table specifies how we describe the antenna.
#

CREATE TABLE Antenna(
AntennaID bigint NOT NULL AUTO_INCREMENT,
AntennaName VARCHAR(255),
AntennaType VARCHAR(255),
Antennuation int,
AssociationType VARCHAR(255),
AssociationID bigint,
PRIMARY KEY (AntennaID),
INDEX(AssociationType),
INDEX(AssociationID)
);


#
# PGContact
#
# This table specifies contact information.
#
#

CREATE TABLE PGContact(
PGContactID bigint NOT NULL AUTO_INCREMENT,
IndividualName VARCHAR(255),
UNIXLoginName VARCHAR(255),
Email VARCHAR(255),
OrganizationName VARCHAR(255),
PositionName VARCHAR(255),
Voice VARCHAR(255),
Facsimile VARCHAR(255),
Address VARCHAR(255),
OnlineResource VARCHAR(255),
HoursOfService VARCHAR(255),
ContactInstruction VARCHAR(255),
PRIMARY KEY (PGContactID),
INDEX(UNIXLoginName),
INDEX(Email)
);