WaMDaM_1.05_PostgreSQL.sql

-- Adel M. 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 PostgreSQL database

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

--Open PostgreSQL, click on Databases>> Postgers>>   at the left Object Browser panel
--Simply copy all this script and paste into the new window of "SQL query"
--Then click execute. The script should run successfully and create the 40 empty tables of WaMDaM"

--Adel has commented this part because it is not used in WaMDaM so far 
--CREATE EXTENSION if not exists postgis;
--CREATE EXTENSION if not exists postgis_topology;
--CREATE EXTENSION if not exists fuzzystrmatch;
--CREATE EXTENSION if not exists postgis_tiger_geoCoder;
-------------------------------------------------------------------------------

drop schema if exists WaMDaM cascade;

create schema WaMDaM;

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

create table WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 WaMDaM.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 double precision  NULL,
	constantfactor double precision  NULL
);
/***************************************************************************/
/**************************** CREATE DATAVALUES ****************************/
/***************************************************************************/

create table WaMDaM.CategoricalValues (
	categoricalvalueid serial  NOT NULL primary key,
	categoricalvalue varchar (500) NOT NULL,
	categoricalvaluecv varchar (255) NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.ElectronicFiles (
	electronicfileid serial  NOT NULL primary key,
	electronicfilename varchar (255) NOT NULL,
	electronicfile bytea  NOT NULL,
	electronicfileformatcv varchar (255) NOT NULL,
	description text  NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.FreeText (
	freetextid serial  NOT NULL primary key,
	freetextvalue varchar (500) NOT NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.MultiAttributeSeries (
	multiattributeseriesid serial  NOT NULL primary key,
	mappingid_attribute integer  NOT NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.MultiAttributeSeriesValues (
	multiattributeseriesvalueid serial  NOT NULL primary key,
	multiattributeseriesid integer  NOT NULL,
	datavalue varchar (255) NOT NULL,
	valueorder integer  NOT NULL
);
create table WaMDaM.NumericValues (
	numericvalueid serial  NOT NULL primary key,
	numericvalue double precision  NOT NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.SeasonalNumericValues (
	seasonalnumericvalueid serial  NOT NULL primary key,
	seasonname varchar (255) NOT NULL,
	seasondateformate timestamp  NULL,
	seasonorder integer  NOT NULL,
	seasonnamecv varchar (255) NULL,
	seasonnumericvalue double precision  NOT NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.TimeSeries (
	timeseriesid serial  NOT NULL primary key,
	yeartype varchar (50) NOT NULL,
	aggregationstatisticcv varchar (255) NOT NULL,
	aggregationinterval double precision  NOT NULL,
	intervaltimeunitcv varchar (255) NOT NULL,
	begindatetime timestamp  NULL,
	enddatetime timestamp  NULL,
	isregular boolean  NULL,
	nodatavalue varchar (50) NULL,
	description text  NULL,
	valuesmapperid integer  NOT NULL
);
create table WaMDaM.TimeSeriesValues (
	timeseriesvalueid serial  NOT NULL primary key,
	timeseriesid integer  NOT NULL,
	datetimestamp timestamp  NOT NULL,
	datavalue double precision  NOT NULL
);
create table WaMDaM.ValuesMapper (
	valuesmapperid serial  NOT NULL primary key
);
/***************************************************************************/
/***************************** CREATE METADATA *****************************/
/***************************************************************************/

create table WaMDaM.Methods (
	methodid serial  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
);
create table WaMDaM.Organizations (
	organizationid serial  NOT NULL primary key,
	organizationname varchar (255) NOT NULL,
	organizationtype varchar (255) NULL,
	organizationwebpage varchar (255) NULL,
	description text  NULL
);
create table WaMDaM.People (
	personid serial  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
);
create table WaMDaM.Sources (
	sourceid serial  NOT NULL primary key,
	sourcename varchar (500) NOT NULL,
	sourcewebpage varchar (500) NULL,
	sourcecitation varchar (500) NULL,
	personid integer  NOT NULL,
	description text  NULL
);
/***************************************************************************/
/***************************** CREATE NETSCEN ******************************/
/***************************************************************************/

create table WaMDaM.Connections (
	connectivityid serial  NOT NULL primary key,
	linkinstanceid integer  NOT NULL,
	startnodeinstanceid integer  NOT NULL,
	endnodeinstanceid integer  NOT NULL
);
create table WaMDaM.InstanceCategories (
	instancecategoryid serial  NOT NULL primary key,
	instancecategory varchar (255) NOT NULL,
	categorydefinition text  NULL
);
create table WaMDaM.Instances (
	instanceid serial  NOT NULL primary key,
	instancename varchar (255) NOT NULL,
	instancenamecv varchar (255) NULL,
	longitude_x double precision  NULL,
	latitude_y double precision  NULL,
	description text  NULL,
	instancecategoryid integer  NULL
);
create table WaMDaM.Mappings (
	mappingid serial  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
);
create table WaMDaM.MasterNetworks (
	masternetworkid serial  NOT NULL primary key,
	masternetworkname varchar (255) NOT NULL,
	spatialreferencenamecv varchar (255) NOT NULL,
	verticaldatumcv varchar (255) NOT NULL,
	description text  NULL
);
create table WaMDaM.ScenarioMappings (
	scenariomappingid serial  NOT NULL primary key,
	scenarioid integer  NOT NULL,
	mappingid integer  NOT NULL
);
create table WaMDaM.Scenarios (
	scenarioid serial  NOT NULL primary key,
	scenarioname varchar (255) NOT NULL,
	scenariostartdate varchar (255) NULL,
	scenarioenddate timestamp  NULL,
	timestepvalue integer  NULL,
	timestepunitcv varchar (255) NULL,
	masternetworkid integer  NOT NULL,
	description text  NULL
);
/***************************************************************************/
/************************** CREATE RESOURCESTRUCT **************************/
/***************************************************************************/

create table WaMDaM.AttributeCategories (
	attributecategoryid serial  NOT NULL primary key,
	attributecategoryname varchar (255) NOT NULL,
	categorydefinition text  NULL
);
create table WaMDaM.Attributes (
	attributeid serial  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
);
create table WaMDaM.ObjectCategories (
	objectcategoryid serial  NOT NULL primary key,
	objectcategoryname varchar (255) NOT NULL,
	categorydefinition text  NULL
);
create table WaMDaM.ObjectTypes (
	objecttypeid serial  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
);
create table WaMDaM.ResourceTypes (
	resourcetypeid serial  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
);
create table WaMDaM.WaMDaMVersion (
	versionnumber decimal (2) NOT NULL primary key
);

alter table WaMDaM.CategoricalValues add constraint fk_CategoricalValues_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.CategoricalValues add constraint fk_DescriptorValues_CV_DescriptorValues
foreign key (CategoricalValueCV) References WaMDaM.CV_Categorical (Term)
on update no Action on delete cascade;

alter table WaMDaM.ElectronicFiles add constraint fk_ElectronicFiles_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.ElectronicFiles add constraint fk_Files_CV_FileFormat
foreign key (ElectronicFileFormatCV) References WaMDaM.CV_ElectronicFileFormat (Name)
on update no Action on delete cascade;

alter table WaMDaM.FreeText add constraint fk_FreeText_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.MultiAttributeSeries add constraint fk_MultiAttributeSeries_Mappings
foreign key (MappingID_Attribute) References WaMDaM.Mappings (MappingID)
on update no Action on delete cascade;

alter table WaMDaM.MultiAttributeSeries add constraint fk_MultiAttributeSeries_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.MultiAttributeSeriesValues add constraint fk_MultiAttributeSeriesValues_MultiAttributeSeries
foreign key (MultiAttributeSeriesID) References WaMDaM.MultiAttributeSeries (MultiAttributeSeriesID)
on update no Action on delete cascade;

alter table WaMDaM.NumericValues add constraint fk_NumericValues_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.SeasonalNumericValues add constraint fk_SeasonalNumericValues_CV_SeasonName
foreign key (SeasonNameCV) References WaMDaM.CV_SeasonName (Name)
on update no Action on delete cascade;

alter table WaMDaM.SeasonalNumericValues add constraint fk_SeasonalNumericValues_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.TimeSeries add constraint fk_TimeSeries_CV_AggregationStatistic
foreign key (AggregationStatisticCV) References WaMDaM.CV_AggregationStatistic (Name)
on update no Action on delete cascade;

alter table WaMDaM.TimeSeries add constraint fk_TimeSeries_CV_Units
foreign key (IntervalTimeUnitCV) References WaMDaM.CV_Units (Name)
on update no Action on delete cascade;

alter table WaMDaM.TimeSeries add constraint fk_TimeSeries_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.TimeSeriesValues add constraint fk_TimeSeriesValues_TimeSeries
foreign key (TimeSeriesID) References WaMDaM.TimeSeries (TimeSeriesID)
on update no Action on delete cascade;

alter table WaMDaM.Methods add constraint fk_Methods_CV_MethodType
foreign key (MethodTypeCV) References WaMDaM.CV_MethodType (Name)
on update no Action on delete cascade;

alter table WaMDaM.Methods add constraint fk_Methods_People
foreign key (PersonID) References WaMDaM.People (PersonID)
on update no Action on delete cascade;

alter table WaMDaM.People add constraint fk_People_Organizations
foreign key (OrganizationID) References WaMDaM.Organizations (OrganizationID)
on update no Action on delete cascade;

alter table WaMDaM.Sources add constraint fk_Sources_People
foreign key (PersonID) References WaMDaM.People (PersonID)
on update no Action on delete cascade;

alter table WaMDaM.Connections add constraint fk_Connections_Instances
foreign key (EndNodeInstanceID) References WaMDaM.Instances (InstanceID)
on update no Action on delete cascade;

alter table WaMDaM.Connections add constraint fk_Connections_Instances_Link
foreign key (LinkInstanceID) References WaMDaM.Instances (InstanceID)
on update no Action on delete cascade;

alter table WaMDaM.Connections add constraint fk_Connections_Instances_startNode
foreign key (StartNodeInstanceID) References WaMDaM.Instances (InstanceID)
on update no Action on delete cascade;

alter table WaMDaM.Instances add constraint fk_Instances_CV_InstanceName
foreign key (InstanceNameCV) References WaMDaM.CV_InstanceName (Name)
on update no Action on delete cascade;

alter table WaMDaM.Instances add constraint fk_Instances_InstanceCategories
foreign key (InstanceCategoryID) References WaMDaM.InstanceCategories (InstanceCategoryID)
on update no Action on delete cascade;

alter table WaMDaM.Mappings add constraint fk_Mappings_Attributes
foreign key (AttributeID) References WaMDaM.Attributes (AttributeID)
on update no Action on delete cascade;

alter table WaMDaM.Mappings add constraint fk_Mappings_Instances
foreign key (InstanceID) References WaMDaM.Instances (InstanceID)
on update no Action on delete cascade;

alter table WaMDaM.Mappings add constraint fk_Mappings_Methods
foreign key (MethodID) References WaMDaM.Methods (MethodID)
on update no Action on delete cascade;

alter table WaMDaM.Mappings add constraint fk_Mappings_Sources
foreign key (SourceID) References WaMDaM.Sources (SourceID)
on update no Action on delete cascade;

alter table WaMDaM.Mappings add constraint fk_Mappings_ValuesMapper
foreign key (ValuesMapperID) References WaMDaM.ValuesMapper (ValuesMapperID)
on update no Action on delete cascade;

alter table WaMDaM.MasterNetworks add constraint fk_MasterNetworks_CV_SpatialReference
foreign key (SpatialReferenceNameCV) References WaMDaM.CV_SpatialReference (Name)
on update no Action on delete cascade;

alter table WaMDaM.MasterNetworks add constraint fk_MasterNetworks_CV_VerticalDatum
foreign key (VerticalDatumCV) References WaMDaM.CV_ElevationDatum (Name)
on update no Action on delete cascade;

alter table WaMDaM.ScenarioMappings add constraint fk_ScenarioMapping_Scenarios
foreign key (ScenarioID) References WaMDaM.Scenarios (ScenarioID)
on update no Action on delete cascade;

alter table WaMDaM.ScenarioMappings add constraint fk_ScenarioMappings_Mappings
foreign key (MappingID) References WaMDaM.Mappings (MappingID)
on update no Action on delete cascade;

alter table WaMDaM.Scenarios add constraint fk_Scenarios_CV_Units
foreign key (TimeStepUnitCV) References WaMDaM.CV_Units (Name)
on update no Action on delete cascade;

alter table WaMDaM.Scenarios add constraint fk_Scenarios_MasterNetworks
foreign key (MasterNetworkID) References WaMDaM.MasterNetworks (MasterNetworkID)
on update no Action on delete cascade;

alter table WaMDaM.Attributes add constraint fk_Attributes_CV_AttributeName
foreign key (AttributeNameCV) References WaMDaM.CV_AttributeName (Name)
on update no Action on delete cascade;

alter table WaMDaM.Attributes add constraint fk_Attributes_CV_AttributeType
foreign key (AttributeDataTypeCV) References WaMDaM.CV_AttributeDataType (Name)
on update no Action on delete cascade;

alter table WaMDaM.Attributes add constraint fk_Attributes_CV_Units
foreign key (UnitNameCV) References WaMDaM.CV_Units (Name)
on update no Action on delete cascade;

alter table WaMDaM.Attributes add constraint fk_Attributes_ObjectTypes
foreign key (ObjectTypeID) References WaMDaM.ObjectTypes (ObjectTypeID)
on update no Action on delete cascade;

alter table WaMDaM.Attributes add constraint fk_ObjectAttributes_AttributeCategory
foreign key (AttributeCategoryID) References WaMDaM.AttributeCategories (AttributeCategoryID)
on update no Action on delete cascade;

alter table WaMDaM.ObjectTypes add constraint fk_ObjectTypes_CV_ObjectTopology
foreign key (ObjectTypologyCV) References WaMDaM.CV_ObjectTypology (Name)
on update no Action on delete cascade;

alter table WaMDaM.ObjectTypes add constraint fk_ObjectTypes_CV_ObjectTypes
foreign key (ObjectTypeCV) References WaMDaM.CV_ObjectType (Name)
on update no Action on delete cascade;

alter table WaMDaM.ObjectTypes add constraint fk_ObjectTypes_Datasets
foreign key (ResourceTypeID) References WaMDaM.ResourceTypes (ResourceTypeID)
on update no Action on delete cascade;

alter table WaMDaM.ObjectTypes add constraint fk_ObjectTypes_ObjectCategory
foreign key (ObjectCategoryID) References WaMDaM.ObjectCategories (ObjectCategoryID)
on update no Action on delete cascade;

alter table WaMDaM.ResourceTypes add constraint fk_ResourceTypes_CV_ResourceTypes
foreign key (ResourceTypeCV) References WaMDaM.CV_ResourceType (Name)
on update no Action on delete cascade;

alter table WaMDaM.ResourceTypes add constraint fk_ResourceTypes_Methods
foreign key (MethodID) References WaMDaM.Methods (MethodID)
on update no Action on delete cascade;