#========================================================================== #
# Project Filename:    D:\NEESGrid\TopLevelOntology\RelationalModel_1_2005\neesgrid.dez#
# Project Name:                                                             #
# Author:                                                                   #
# DBMS:                MySQL 4                                              #
# Copyright:                                                                #
# Generated on:        2/8/2005 11:29:16 AM                                 #
#========================================================================== #

#========================================================================== #
#  Tables                                                                   #
#========================================================================== #

CREATE TABLE Project (
    projectID BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    contractID VARCHAR(32),
    localTimeZone VARCHAR(16),
    startDate DATE,
    endDate DATE,
    keywords TEXT,
    description TEXT,
    objectives TEXT,
    acknowledgements TEXT,
    PRIMARY KEY (projectID, title),
    UNIQUE KEY IDX_Project2(projectID)
);

CREATE TABLE Task (
    taskID BIGINT NOT NULL,
    projectID BIGINT,
    title VARCHAR(255),
    localTimeZone VARCHAR(16),
    startDate DATE,
    endDate DATE,
    siteID BIGINT,
    keywords TEXT,
    description TEXT,
    objectives TEXT,
    isMultiSite BOOL,
    PRIMARY KEY (taskID),
    KEY IDX_Task1(projectID),
    UNIQUE KEY IDX_Task3(taskID),
    UNIQUE KEY IDX_Task4(siteID)
);

CREATE TABLE EventGroup (
    eventGroupID BIGINT NOT NULL,
    taskID BIGINT,
    title VARCHAR(255),
    localTimeZone VARCHAR(16),
    startDateTime DATETIME,
    endDateTime DATETIME,
    description TEXT,
    objectives TEXT,
    status VARCHAR(16),
    isMultiSite BOOL,
    PRIMARY KEY (eventGroupID),
    KEY IDX_EventGroup1(taskID),
    UNIQUE KEY IDX_EventGroup2(eventGroupID)
);

CREATE TABLE Event (
    eventID BIGINT NOT NULL,
    eventGroupID BIGINT,
    title VARCHAR(255),
    type VARCHAR(16),
    localTimeZone VARCHAR(16),
    startDateTime DATETIME,
    endDateTime DATETIME,
    description TEXT,
    objectives TEXT,
    status VARCHAR(16),
    isMultiSite BOOL,
    PRIMARY KEY (eventID),
    KEY IDX_Event1(eventGroupID),
    UNIQUE KEY IDX_Event2(eventID)
);

CREATE TABLE Person (
    personID BIGINT NOT NULL,
    firstName VARCHAR(40),
    lastName VARCHAR(40),
    title VARCHAR(40),
    institution VARCHAR(56),
    officePhone VARCHAR(16),
    homePhone VARCHAR(16),
    faxNumber VARCHAR(16),
    email VARCHAR(40),
    address1 VARCHAR(64),
    address2 VARCHAR(64),
    homePage VARCHAR(64),
    otherInfo TEXT,
    PRIMARY KEY (personID),
    UNIQUE KEY IDX_Person1(personID)
);

CREATE TABLE RolePerson (
    personID BIGINT,
    personRole VARCHAR(40),
    projectID BIGINT,
    taskID BIGINT,
    KEY IDX_RolePerson1(personID),
    KEY IDX_RolePerson2(projectID),
    KEY IDX_RolePerson3(taskID)
);

CREATE TABLE File (
    fileID BIGINT NOT NULL,
    name VARCHAR(64),
    type VARCHAR(40),
    description TEXT,
    authors VARCHAR(255),
    dateModified DATETIME,
    uri VARCHAR(255),
    PRIMARY KEY (fileID),
    UNIQUE KEY IDX_File1(fileID)
);

CREATE TABLE InfrastructureSetup (
    infrastructureSetupID BIGINT NOT NULL,
    name VARCHAR(255),
    description TEXT,
    facilityID BIGINT,
    taskID BIGINT,
    PRIMARY KEY (infrastructureSetupID),
    KEY IDX_InfrastructureSetup1(facilityID),
    KEY IDX_InfrastructureSetup2(taskID),
    UNIQUE KEY IDX_InfrastructureSetup3(infrastructureSetupID)
);

CREATE TABLE SpecimenSetup (
    specimenSetupID BIGINT NOT NULL,
    name VARCHAR(255),
    description TEXT,
    eventGroupID BIGINT,
    PRIMARY KEY (specimenSetupID),
    UNIQUE KEY IDX_SpecimenSetup1(specimenSetupID),
    KEY IDX_SpecimenSetup2(eventGroupID)
);

CREATE TABLE SensorSetup (
    sensorSetupID BIGINT NOT NULL,
    name VARCHAR(255),
    description TEXT,
    eventGroupID BIGINT,
    PRIMARY KEY (sensorSetupID),
    UNIQUE KEY IDX_SensorSetup1(sensorSetupID),
    KEY IDX_SensorSetup2(eventGroupID)
);

CREATE TABLE WaveForm (
    waveFormID BIGINT NOT NULL,
    fileID BIGINT,
    name VARCHAR(255),
    description TEXT,
    peakAcceleration DOUBLE,
    peakDisplacement DOUBLE,
    peakVelocity DOUBLE,
    samplingRate DOUBLE,
    scaleFactor DOUBLE,
    PRIMARY KEY (waveFormID),
    UNIQUE KEY IDX_WaveForm1(waveFormID),
    UNIQUE KEY IDX_WaveForm2(fileID)
);

CREATE TABLE WaveFormSetup (
    waveFormSetupID BIGINT NOT NULL,
    eventID BIGINT,
    waveFormID BIGINT,
    name VARCHAR(255),
    description TEXT,
    direction VARCHAR(40),
    PRIMARY KEY (waveFormSetupID),
    KEY IDX_WaveFormSetup1(eventID),
    KEY IDX_WaveFormSetup2(waveFormID)
);

CREATE TABLE Site (
    siteID BIGINT NOT NULL,
    name VARCHAR(255),
    description TEXT,
    organizationID BIGINT,
    PRIMARY KEY (siteID),
    UNIQUE KEY IDX_Site1(siteID),
    KEY IDX_Site2(organizationID)
);

CREATE TABLE Facility (
    facilityID BIGINT NOT NULL,
    siteID BIGINT,
    name VARCHAR(255),
    manufacturer VARCHAR(255),
    description TEXT,
    operators TEXT,
    PRIMARY KEY (facilityID),
    KEY IDX_Facility1(siteID),
    UNIQUE KEY IDX_Facility2(facilityID)
);

CREATE TABLE SensorReadings (
    sensorReadingID BIGINT NOT NULL,
    eventID BIGINT,
    fileID BIGINT,
    name VARCHAR(255),
    description TEXT,
    PRIMARY KEY (sensorReadingID),
    KEY IDX_SensorReadings1(eventID),
    KEY IDX_SensorReadings2(fileID)
);

CREATE TABLE Organization (
    organizationID BIGINT NOT NULL,
    name VARCHAR(255),
    type VARCHAR(64),
    address VARCHAR(255),
    homePage VARCHAR(255),
    PRIMARY KEY (organizationID),
    UNIQUE KEY IDX_Organization1(organizationID)
);

CREATE TABLE SetupFile (
    infrastructureSetupID BIGINT,
    specimenSetupID BIGINT,
    sensorSetupID BIGINT,
    fileID BIGINT,
    KEY IDX_SetupFile1(infrastructureSetupID),
    KEY IDX_SetupFile2(specimenSetupID),
    KEY IDX_SetupFile3(sensorSetupID),
    KEY IDX_SetupFile4(fileID)
);

CREATE TABLE ActivityFile (
    projectID BIGINT,
    taskID BIGINT,
    eventGroupID BIGINT,
    eventID BIGINT,
    fileID BIGINT,
    KEY IDX_ActivityFile1(projectID),
    KEY IDX_ActivityFile2(taskID),
    KEY IDX_ActivityFile3(eventGroupID),
    KEY IDX_ActivityFile4(eventID),
    KEY IDX_ActivityFile5(fileID)
);

CREATE TABLE ProjectOrganization (
    projectID BIGINT,
    organizationID BIGINT,
    KEY IDX_ProjectOrganization1(organizationID),
    KEY IDX_ProjectOrganization2(projectID)
);

CREATE TABLE Sensor (
    sensorID BIGINT NOT NULL,
    name VARCHAR(40),
    sensorType VARCHAR(40),
    owner VARCHAR(64),
    description TEXT,
    serialNumber VARCHAR(40),
    outputQuantity VARCHAR(40),
    minRange DOUBLE,
    maxRange DOUBLE,
    manufacturer VARCHAR(40),
    PRIMARY KEY (sensorID),
    UNIQUE KEY IDX_Sensor1(sensorID)
);

CREATE TABLE DAQUnit (
    sensorID BIGINT,
    sensorSetupID BIGINT,
    calibrationInfo TEXT,
    lastCalibrationDate DATETIME,
    cableID BIGINT,
    channelID BIGINT,
    fileID BIGINT,
    KEY IDX_DAQUnit1(sensorSetupID),
    KEY IDX_DAQUnit2(sensorID),
    KEY IDX_DAQUnit3(fileID),
    KEY IDX_DAQUnit4(cableID),
    KEY IDX_DAQUnit5(channelID)
);

CREATE TABLE DAQCable (
    cableID BIGINT NOT NULL,
    name VARCHAR(40),
    owner VARCHAR(64),
    serialNumber VARCHAR(40),
    description TEXT,
    length DOUBLE,
    connectorType VARCHAR(40),
    PRIMARY KEY (cableID),
    UNIQUE KEY IDX_DAQCable1(cableID)
);

CREATE TABLE DAQChannel (
    channelID BIGINT NOT NULL,
    name VARCHAR(40),
    owner VARCHAR(64),
    serialNumber VARCHAR(40),
    description TEXT,
    channelUnit VARCHAR(40),
    channelFilter DOUBLE,
    channelGain DOUBLE,
    channelOffset DOUBLE,
    excitationVoltage DOUBLE,
    samplingRate DOUBLE,
    manufacturer VARCHAR(40),
    PRIMARY KEY (channelID),
    UNIQUE KEY IDX_DAQChannel1(channelID)
);

#========================================================================== #
#  Foreign Keys                                                             #
#========================================================================== #

ALTER TABLE Task
    ADD FOREIGN KEY (projectID) REFERENCES Project (projectID);

ALTER TABLE EventGroup
    ADD FOREIGN KEY (taskID) REFERENCES Task (taskID);

ALTER TABLE Event
    ADD FOREIGN KEY (eventGroupID) REFERENCES EventGroup (eventGroupID);

ALTER TABLE RolePerson
    ADD FOREIGN KEY (personID) REFERENCES Person (personID);

ALTER TABLE RolePerson
    ADD FOREIGN KEY (projectID) REFERENCES Project (projectID);

ALTER TABLE RolePerson
    ADD FOREIGN KEY (taskID) REFERENCES Task (taskID);

ALTER TABLE InfrastructureSetup
    ADD FOREIGN KEY (facilityID) REFERENCES Facility (facilityID);

ALTER TABLE InfrastructureSetup
    ADD FOREIGN KEY (taskID) REFERENCES Task (taskID);

ALTER TABLE SpecimenSetup
    ADD FOREIGN KEY (eventGroupID) REFERENCES EventGroup (eventGroupID);

ALTER TABLE SensorSetup
    ADD FOREIGN KEY (eventGroupID) REFERENCES EventGroup (eventGroupID);

ALTER TABLE WaveFormSetup
    ADD FOREIGN KEY (eventID) REFERENCES Event (eventID);

ALTER TABLE WaveFormSetup
    ADD FOREIGN KEY (waveFormID) REFERENCES WaveForm (waveFormID);

ALTER TABLE Site
    ADD FOREIGN KEY (siteID) REFERENCES Task (siteID);

ALTER TABLE Site
    ADD FOREIGN KEY (organizationID) REFERENCES Organization (organizationID);

ALTER TABLE Facility
    ADD FOREIGN KEY (siteID) REFERENCES Site (siteID);

ALTER TABLE SensorReadings
    ADD FOREIGN KEY (eventID) REFERENCES Event (eventID);

ALTER TABLE SensorReadings
    ADD FOREIGN KEY (fileID) REFERENCES File (fileID);

ALTER TABLE SetupFile
    ADD FOREIGN KEY (infrastructureSetupID) REFERENCES InfrastructureSetup (infrastructureSetupID);

ALTER TABLE SetupFile
    ADD FOREIGN KEY (specimenSetupID) REFERENCES SpecimenSetup (specimenSetupID);

ALTER TABLE SetupFile
    ADD FOREIGN KEY (sensorSetupID) REFERENCES SensorSetup (sensorSetupID);

ALTER TABLE SetupFile
    ADD FOREIGN KEY (fileID) REFERENCES File (fileID);

ALTER TABLE ActivityFile
    ADD FOREIGN KEY (projectID) REFERENCES Project (projectID);

ALTER TABLE ActivityFile
    ADD FOREIGN KEY (taskID) REFERENCES Task (taskID);

ALTER TABLE ActivityFile
    ADD FOREIGN KEY (eventGroupID) REFERENCES EventGroup (eventGroupID);

ALTER TABLE ActivityFile
    ADD FOREIGN KEY (eventID) REFERENCES Event (eventID);

ALTER TABLE ActivityFile
    ADD FOREIGN KEY (fileID) REFERENCES File (fileID);

ALTER TABLE ProjectOrganization
    ADD FOREIGN KEY (organizationID) REFERENCES Organization (organizationID);

ALTER TABLE ProjectOrganization
    ADD FOREIGN KEY (projectID) REFERENCES Project (projectID);

ALTER TABLE DAQUnit
    ADD FOREIGN KEY (sensorSetupID) REFERENCES SensorSetup (sensorSetupID);

ALTER TABLE DAQUnit
    ADD FOREIGN KEY (sensorID) REFERENCES Sensor (sensorID);

ALTER TABLE DAQUnit
    ADD FOREIGN KEY (fileID) REFERENCES File (fileID);

ALTER TABLE DAQUnit
    ADD FOREIGN KEY (cableID) REFERENCES DAQCable (cableID);

ALTER TABLE DAQUnit
    ADD FOREIGN KEY (channelID) REFERENCES DAQChannel (channelID);