WaMDaM_1.05_SQLite.sql

-- Adel Abdallah, PhD Candidate, Utah State University, December 2018

-- This is a Data Definition Language (DDL) script that
-- generates a blank schema of the Water Management Data Model (WaMDaM)
-- for SQLite database

-- Generated by Adel Abdallah December, 2018 based on WaMDaM XML design named WaMDaM_DBWrench_Schema_1.05.xml generated by DbWrench V4.04 @ http://www.dbwrench.com/ 
-- WaMDaM All rights reserved. See BSD 3-Clause Licence @ https://github.com/WamdamProject/WaMDaM-software-ecosystem 

--Use the SQLite Manager Add-on to Mozilla Firefox
--Create a new empty database. Click on the Execute SQL button and delete the text "SELECT * FROM tablename"
--Simply copy all this script and paste into this Execute SQL window
--Then click Run SQL. The script should run successfully and create the 41 empty tables of WaMDaM

--------------------------------------------------------------------------------------------------
/***************************************************************************/
/******************************* CREATE CVS ********************************/
/***************************************************************************/

CREATE TABLE CV_AggregationStatistic (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_AttributeDataType (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_AttributeName (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Category VARCHAR (255)  NULL,
	Definition VARCHAR (5000)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_Categorical (
	Term VARCHAR (255)  NOT NULL PRIMARY KEY,
	Name VARCHAR (255)  NOT NULL,
	AttributeName VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_ElectronicFileFormat (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_ElevationDatum (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_InstanceName (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_MethodType (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_ObjectType (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_ObjectTypology (
	Name VARCHAR (50)  NOT NULL PRIMARY KEY,
	Term VARCHAR (50)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_ResourceType (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_SeasonName (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (255)  NULL
);

CREATE TABLE CV_SpatialReference (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NULL,
	SourceVocabularyURI VARCHAR (5000)  NULL
);

CREATE TABLE CV_Units (
	Name VARCHAR (255)  NOT NULL PRIMARY KEY,
	Term VARCHAR (255)  NOT NULL,
	Definition VARCHAR (5000)  NULL,
	Category VARCHAR (255)  NOT NULL,
	UnitSystem VARCHAR (255)  NULL,
	UnitAbbreviation char (50)  NOT NULL,
	SourceVocabularyURI VARCHAR (5000)  NULL,
	LinearFactor FLOAT   NULL,
	ConstantFactor FLOAT   NULL
);

/***************************************************************************/
/**************************** CREATE DATAVALUES ****************************/
/***************************************************************************/

CREATE TABLE CategoricalValues (
	CategoricalValueID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	CategoricalValue VARCHAR (500)  NOT NULL,
	CategoricalValueCV VARCHAR (255)  NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (CategoricalValueCV) REFERENCES CV_Categorical (Term)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ElectronicFiles (
	ElectronicFileID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ElectronicFileName VARCHAR (255)  NOT NULL,
	ElectronicFile BLOB   NOT NULL,
	ElectronicFileFormatCV VARCHAR (255)  NOT NULL,
	Description TEXT   NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ElectronicFileFormatCV) REFERENCES CV_ElectronicFileFormat (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE FreeText (
	FreeTextID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	FreeTextValue VARCHAR (500)  NOT NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE MultiAttributeSeries (
	MultiAttributeSeriesID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	MappingID_Attribute INTEGER   NOT NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (MappingID_Attribute) REFERENCES Mappings (MappingID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE MultiAttributeSeriesValues (
	MultiAttributeSeriesValueID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	MultiAttributeSeriesID INTEGER   NOT NULL,
	DataValue VARCHAR (255)  NOT NULL,
	ValueOrder INTEGER   NOT NULL,
	FOREIGN KEY (MultiAttributeSeriesID) REFERENCES MultiAttributeSeries (MultiAttributeSeriesID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE NumericValues (
	NumericValueID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	NumericValue FLOAT   NOT NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE SeasonalNumericValues (
	SeasonalNumericValueID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	SeasonName VARCHAR (255)  NOT NULL,
	SeasonDateFormate DATETIME   NULL,
	SeasonOrder INTEGER   NOT NULL,
	SeasonNameCV VARCHAR (255)  NULL,
	SeasonNumericValue FLOAT   NOT NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (SeasonNameCV) REFERENCES CV_SeasonName (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE TimeSeries (
	TimeSeriesID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	YearType VARCHAR (50)  NOT NULL,
	AggregationStatisticCV VARCHAR (255)  NOT NULL,
	AggregationInterval DOUBLE   NOT NULL,
	IntervalTimeUnitCV VARCHAR (255)  NOT NULL,
	BeginDateTime DATETIME   NULL,
	EndDateTime DATETIME   NULL,
	IsRegular BIT   NULL,
	NoDataValue VARCHAR (50)  NULL,
	Description TEXT   NULL,
	ValuesMapperID INTEGER   NOT NULL,
	FOREIGN KEY (AggregationStatisticCV) REFERENCES CV_AggregationStatistic (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (IntervalTimeUnitCV) REFERENCES CV_Units (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE TimeSeriesValues (
	TimeSeriesValueID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	TimeSeriesID INTEGER   NOT NULL,
	DateTimeStamp DATETIME   NOT NULL,
	DataValue FLOAT   NOT NULL,
	FOREIGN KEY (TimeSeriesID) REFERENCES TimeSeries (TimeSeriesID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ValuesMapper (
	ValuesMapperID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY
);

/***************************************************************************/
/***************************** CREATE METADATA *****************************/
/***************************************************************************/

CREATE TABLE Methods (
	MethodID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	MethodName VARCHAR (255)  NOT NULL,
	MethodWebpage VARCHAR (255)  NULL,
	MethodCitation VARCHAR (500)  NULL,
	MethodTypeCV VARCHAR (255)  NULL,
	DataQuality TEXT   NULL,
	PersonID INTEGER   NOT NULL,
	Description TEXT   NULL,
	FOREIGN KEY (MethodTypeCV) REFERENCES CV_MethodType (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (PersonID) REFERENCES People (PersonID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE Organizations (
	OrganizationID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	OrganizationName VARCHAR (255)  NOT NULL,
	OrganizationType VARCHAR (255)  NULL,
	OrganizationWebpage VARCHAR (255)  NULL,
	Description TEXT   NULL
);

CREATE TABLE People (
	PersonID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	PersonName VARCHAR (255)  NOT NULL,
	Address VARCHAR (255)  NULL,
	Email VARCHAR (255)  NULL,
	Phone VARCHAR (50)  NULL,
	PersonWebpage VARCHAR (255)  NULL,
	Position VARCHAR (255)  NULL,
	OrganizationID INTEGER   NOT NULL,
	FOREIGN KEY (OrganizationID) REFERENCES Organizations (OrganizationID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE Sources (
	SourceID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	SourceName VARCHAR (500)  NOT NULL,
	SourceWebpage VARCHAR (500)  NULL,
	SourceCitation VARCHAR (500)  NULL,
	PersonID INTEGER   NOT NULL,
	Description TEXT   NULL,
	FOREIGN KEY (PersonID) REFERENCES People (PersonID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

/***************************************************************************/
/***************************** CREATE NETSCEN ******************************/
/***************************************************************************/

CREATE TABLE Connections (
	ConnectivityID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	LinkInstanceID INTEGER   NOT NULL,
	StartNodeInstanceID INTEGER   NOT NULL,
	EndNodeInstanceID INTEGER   NOT NULL,
	FOREIGN KEY (EndNodeInstanceID) REFERENCES Instances (InstanceID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (LinkInstanceID) REFERENCES Instances (InstanceID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (StartNodeInstanceID) REFERENCES Instances (InstanceID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE InstanceCategories (
	InstanceCategoryID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	InstanceCategory VARCHAR (255)  NOT NULL,
	CategoryDefinition TEXT   NULL
);

CREATE TABLE Instances (
	InstanceID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	InstanceName VARCHAR (255)  NOT NULL,
	InstanceNameCV VARCHAR (255)  NULL,
	Longitude_x FLOAT   NULL,
	Latitude_y FLOAT   NULL,
	Description TEXT   NULL,
	InstanceCategoryID INTEGER   NULL,
	FOREIGN KEY (InstanceNameCV) REFERENCES CV_InstanceName (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (InstanceCategoryID) REFERENCES InstanceCategories (InstanceCategoryID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE Mappings (
	MappingID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	AttributeID INTEGER   NOT NULL,
	InstanceID INTEGER   NOT NULL,
	SourceID INTEGER   NOT NULL,
	MethodID INTEGER   NOT NULL,
	ValuesMapperID INTEGER   NULL,
	Verified VARCHAR (5)  NULL,
	FOREIGN KEY (AttributeID) REFERENCES Attributes (AttributeID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (InstanceID) REFERENCES Instances (InstanceID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (MethodID) REFERENCES Methods (MethodID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (SourceID) REFERENCES Sources (SourceID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ValuesMapperID) REFERENCES ValuesMapper (ValuesMapperID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE MasterNetworks (
	MasterNetworkID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	MasterNetworkName VARCHAR (255)  NOT NULL,
	SpatialReferenceNameCV VARCHAR (255)  NOT NULL,
	VerticalDatumCV VARCHAR (255)  NOT NULL,
	Description TEXT   NULL,
	FOREIGN KEY (SpatialReferenceNameCV) REFERENCES CV_SpatialReference (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (VerticalDatumCV) REFERENCES CV_ElevationDatum (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ScenarioMappings (
	ScenarioMappingID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ScenarioID INTEGER   NOT NULL,
	MappingID INTEGER   NOT NULL,
	FOREIGN KEY (ScenarioID) REFERENCES Scenarios (ScenarioID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (MappingID) REFERENCES Mappings (MappingID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE Scenarios (
	ScenarioID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ScenarioName VARCHAR (255)  NOT NULL,
	ScenarioStartDate VARCHAR (255)  NULL,
	ScenarioEndDate DATETIME   NULL,
	TimeStepValue INTEGER   NULL,
	TimeStepUnitCV VARCHAR (255)  NULL,
	MasterNetworkID INTEGER   NOT NULL,
	Description TEXT   NULL,
	FOREIGN KEY (TimeStepUnitCV) REFERENCES CV_Units (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (MasterNetworkID) REFERENCES MasterNetworks (MasterNetworkID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

/***************************************************************************/
/************************** CREATE RESOURCESTRUCT **************************/
/***************************************************************************/

CREATE TABLE AttributeCategories (
	AttributeCategoryID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	AttributeCategoryName VARCHAR (255)  NOT NULL,
	CategoryDefinition TEXT   NULL
);

CREATE TABLE Attributes (
	AttributeID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	AttributeName VARCHAR (255)  NOT NULL,
	AttributeName_Abstract VARCHAR (255)  NULL,
	AttributeNameCV VARCHAR (255)  NULL,
	ObjectTypeID INTEGER   NOT NULL,
	UnitName VARCHAR (255)  NOT NULL,
	UnitNameCV VARCHAR (255)  NULL,
	AttributeScale INTEGER   NULL,
	AttributeDataTypeCV VARCHAR (255)  NOT NULL,
	AttributeCategoryID INTEGER   NULL,
	ModelInputOrOutput char (50)  NULL,
	Description TEXT   NULL,
	FOREIGN KEY (AttributeNameCV) REFERENCES CV_AttributeName (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (AttributeDataTypeCV) REFERENCES CV_AttributeDataType (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (UnitNameCV) REFERENCES CV_Units (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ObjectTypeID) REFERENCES ObjectTypes (ObjectTypeID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (AttributeCategoryID) REFERENCES AttributeCategories (AttributeCategoryID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ObjectCategories (
	ObjectCategoryID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ObjectCategoryName VARCHAR (255)  NOT NULL,
	CategoryDefinition TEXT   NULL
);

CREATE TABLE ObjectTypes (
	ObjectTypeID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ObjectType VARCHAR (255)  NOT NULL,
	ObjectTypeCV VARCHAR (255)  NULL,
	ObjectTypologyCV VARCHAR (50)  NOT NULL,
	Layout TEXT   NULL,
	Description TEXT   NULL,
	ObjectCategoryID INTEGER   NULL,
	ResourceTypeID INTEGER   NOT NULL,
	FOREIGN KEY (ObjectTypologyCV) REFERENCES CV_ObjectTypology (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ObjectTypeCV) REFERENCES CV_ObjectType (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ResourceTypeID) REFERENCES ResourceTypes (ResourceTypeID)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (ObjectCategoryID) REFERENCES ObjectCategories (ObjectCategoryID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ResourceTypes (
	ResourceTypeID INTEGER  AUTO_INCREMENT NOT NULL PRIMARY KEY,
	ResourceType VARCHAR (255)  NOT NULL,
	ResourceTypeAcronym VARCHAR (255)  NOT NULL,
	ResourceTypeCV VARCHAR (255)  NULL,
	MethodID INTEGER   NOT NULL,
	Description TEXT   NULL,
	FOREIGN KEY (ResourceTypeCV) REFERENCES CV_ResourceType (Name)
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	FOREIGN KEY (MethodID) REFERENCES Methods (MethodID)
	ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE WaMDaMVersion (
	VersionNumber decimal (2)  NOT NULL PRIMARY KEY
);